Spring @Transactional is not working as expected with Sql2o

[avanishnandan]

The first transaction gets committed, even though the second transaction failed.

The same code works fine with JdbcTemplate.

Could you please help me to resolve this by using Sql2o

Notice the log message says that data inserted into customer table successfully but exception thrown by H2 database driver clearly says that value is too long for the address column. if you will check the Customer table, you find row there that means that transaction is not rolled back completely. It commits the Customer table.

Log

INFO: Loaded JDBC driver: org.h2.Driver 15:53:40.829 [main] DEBUG org.sql2o.Query - Executing query: insert into Customer (id, name) values (?,?) 15:53:40.852 [main] DEBUG org.sql2o.Query - total: 38 ms; executed update [No name] 15:53:40.852 [main] DEBUG org.sql2o.Query - Executing query: insert into Address (id, address,country) values (?,?,?) Exception in thread "main" org.sql2o.Sql2oException: Error in executeUpdate, Value too long for column "ADDRESS VARCHAR(20)

Spring configuration

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org schema/tx/spring-tx-4.0.xsd">

<!-- Enable Annotation based Declarative Transaction Management -->
<tx:annotation-driven proxy-target-class="true"
    transaction-manager="transactionManagerBase" />

<!-- Creating TransactionManager Bean, since JDBC we are creating of type 
    DataSourceTransactionManager -->
<bean id="transactionManagerBase"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

<!-- H2 DB DataSource -->
<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:tcp://localhost/~/demo" />
    <property name="username" value="root" />
    <property name="password" value="root" />
</bean>

<bean id="sql2o" class="org.sql2o.Sql2o">
    <constructor-arg type='javax.sql.DataSource' ref='dataSource'/>
</bean>

<bean id="customerDAO" class="com.dev.dao.impl.CustomerDAOImpl"/>

CustomeDAO

package com.dev.dao;

import com.dev.model.Customer;

public interface CustomerDAO { void create(Customer customer); }

CustomerDAOImpl

package com.dev.dao.impl;

import java.io.Serializable; import com.dev.dao.CustomerDAO;

import com.dev.model.Address; import com.dev.model.Customer;

import org.sql2o.Connection; import org.sql2o.Sql2o;

import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional;

@Repository("customerDAO")
public class CustomerDAOImpl implements CustomerDAO, Serializable { private static final long serialVersionUID = 1L;

@Autowired
private Sql2o sql2o;

@Override
@Transactional("transactionManagerBase")
public void create(Customer customer) {
    String insertCustomer = "insert into Customer (id, name) values (:id,:name)";
    String insertAddress = "insert into Address (id, address,country) values (:id,:address,:country)";

    Address address = customer.getAddress();

    try (Connection con = sql2o.open()) {
        con.createQuery(insertCustomer).bind(customer).executeUpdate();
        con.createQuery(insertAddress).bind(address).executeUpdate();
    }
}

}

TransactionTest

package com.dev.main;

import com.dev.dao.CustomerDAO; import com.dev.dao.impl.CustomerDAOImpl; import com.dev.model.Customer; import com.dev.model.Address;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TransactionTest {

public static void main(String[] args) {
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");

    CustomerDAO customerDAOImpl = ctx.getBean("customerDAO",CustomerDAOImpl.class);

    Customer cust = createDummyCustomer();
    customerDAOImpl.create(cust);

    ctx.close();
}

private static Customer createDummyCustomer() {
    Customer customer = new Customer();
    customer.setId(1);
    customer.setName("Arul");

    Address address = new Address();
    address.setId(1);
    address.setCountry("India");
    // setting value more than 20 chars, so that SQLException occurs
    address.setAddress("#2 Main Road, Raja Colony, Chennai 600 015");

    customer.setAddress(address);
    return customer;
}

}

[zapodot]

@avanishnandan: in order to use transactions with sql2o, use the "beginTransaction()" method and remember to commit your transaction in the end.

try (Connection con = sql2o.beginTransaction()) {
        con.createQuery(insertCustomer).bind(customer).executeUpdate();
        con.createQuery(insertAddress).bind(address).executeUpdate();
                con.commit();
    }

[avanishnandan]

Thanks. I understand. Initially I used the beginTransaction and commit.

Now, I am trying to integrate spring Declarative Transaction Management to handle the data inconsistency. It works as expected by using JdbcTemplate. But doesn't work by using sql2o.

What I am doing wrong?