Spring rollback issue for JDBC Template

Question

I have 2 insert / update statements that i want to wrap in a Spring transaction. If either statement fails i want to have the statements rolled back. I tried to test this with the following scenario:

I have an application which has a DB constraint that it's CODE can only be 25 characters max. I'm forcing a constraint violation while saving a second application by supplying a CODE that is too large.

I would expect that the first insertion / update on application A would have been rolled back. However my test indicates that this is not the case.

I am not quite sure what I am missing ...

My Transation test:

public class SpringTransactionTest {

private static final Logger LOGGER =  LoggerFactory.getLogger(SpringTransactionTest.class.getName());

private ApplicationDAO applicationDAO = SpringUtils4Test.getBean(ApplicationDAO.class);

@Transactional
public void doStuff() {
    Application a = new Application();
    a.setCode("A");
    Application b = new Application();
    b.setCode("BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB");
    a = applicationDAO.insertOrUpdate(a);
    LOGGER.debug("a: "+a.getIdentifier());

    b = applicationDAO.insertOrUpdate(b);
    LOGGER.debug("b: "+b.getIdentifier());
}

@Before
public void cleanIt() {
    Application a = applicationDAO.getApplicationByCode("A");
    if(a != null && a.getIdentifier() > 0) {
        applicationDAO.deleteById(a.getIdentifier());
        LOGGER.debug("removed A");
    }
    Application b = applicationDAO.getApplicationByCode("BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB");
    if(b != null && b.getIdentifier() > 0) {
        applicationDAO.deleteById(b.getIdentifier());
        LOGGER.debug("removed B");
    }
}

@Test
public void runTransactionTest() {
    try {
        doStuff();
    } catch (org.springframework.jdbc.UncategorizedSQLException e) {
        e.printStackTrace();
    }

    Assert.assertNull("A should be null, but instead A was found", applicationDAO.getApplicationByCode("A"));
    Assert.assertNull("B should be null, but instead B was found", applicationDAO.getApplicationByCode("B"));
}
}

Console output:

16:42:38.272 [main] DEBUG bla.dao.AbstractDAO - getApplicationDetails for : A
16:42:38.779 [main] DEBUG bla.dao.AbstractDAO - application found with id: 1008
16:42:39.062 [main] DEBUG bla.view.SpringTransactionTest - removed A
16:42:39.062 [main] DEBUG bla.dao.AbstractDAO - getApplicationDetails for : BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
16:42:39.358 [main] DEBUG bla.dao.AbstractDAO - no application found
16:42:39.676 [main] DEBUG bla.dao.AbstractDAO - new application id: 1009
16:42:39.677 [main] DEBUG bla.view.SpringTransactionTest - a: 1009
02-Sep-2014 16:42:40 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
02-Sep-2014 16:42:40 org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "BLA"."APPLICATION"."CODE" (actual: 36, maximum: 25)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "BLA"."APPLICATION"."CODE" (actual: 36, maximum: 25)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941)
at bla.dao.ApplicationDAO.insert(ApplicationDAO.java:98)
at bla.dao.ApplicationDAO.insertOrUpdate(ApplicationDAO.java:90)
at bla.view.SpringTransactionTest.doStuff(SpringTransactionTest.java:35)
at bla.view.SpringTransactionTest.runTransactionTest(SpringTransactionTest.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:77)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:195)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.sql.SQLException: ORA-12899: value too large for column "BLA"."APPLICATION"."CODE" (actual: 36, maximum: 25)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:944)
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:941)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:642)
... 31 more
16:42:40.411 [main] DEBUG bla.dao.AbstractDAO - getApplicationDetails for : A
16:42:40.717 [main] DEBUG bla.dao.AbstractDAO - application found with id: 1009
java.lang.AssertionError: A should be null, but instead A was found expected null, but was:<bla.domain.Application@12a66ea>
at org.junit.Assert.fail(Assert.java:88)
at org.junit.Assert.failNotNull(Assert.java:664)
at org.junit.Assert.assertNull(Assert.java:646)
at bla.view.SpringTransactionTest.runTransactionTest(SpringTransactionTest.java:67)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:77)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:195)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

Process finished with exit code -1

Maven Dependency properties:

<java.version>1.6</java.version>
<spring.version>4.0.0.RELEASE</spring.version>
<jsf.version>2.2.5</jsf.version>
<servlet.version>3.0.1</servlet.version>
<ojdbc14.version>10.2.0.3.0</ojdbc14.version>

SpringUtils4Test:

public class SpringUtils4Test {

    private static final ClassPathXmlApplicationContext CTX = new ClassPathXmlApplicationContext("applicationContext.xml");

    public static <T> T getBean(Class<T> clazz) {
        return CTX.getBean(clazz);
    }
}

ApplicationDAO:

public Application insertOrUpdate(final Application application) {
    if (application.getIdentifier() == null) {
        return insert(application);
    } else {
        return update(application);
    }
}

private Application insert(final Application application) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(
            new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(
                        "insert into application (CODE) values (?)",
                        new String[]{"id"});
                    JdbcUtil.setValues(ps, application.getCode());
                    return ps;
                }
            },
            keyHolder);

    final Long applicationId = keyHolder.getKey().longValue();
    LOGGER.debug("new application id: " + applicationId);
    application.setIdentifier(applicationId);
    return application;
}

private Application update(final Application app) {
    jdbcTemplate.update("update APPLICATION set CODE = ? where id = ?",
            app.getCode(), app.getIdentifier());
    return app;
}

My ApplicationContext:

<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:aop="http://www.springframework.org/schema/aop"
   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.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
   ">

<context:component-scan base-package="bla"/>
<aop:aspectj-autoproxy proxy-target-class="true"/>

<bean id="transactionManager"
      class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

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

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@bla:1540:bla"/>
    <property name="username" value="bla"/>
    <property name="password" value="blabla"/>
</bean>

</beans>

SpringUtils4Test what is that? – Kalpesh Soni Sep 02 '14 at 15:39 — Kalpesh Soni, Sep 02 '14 at 15:39
what spring version are you using? – Kalpesh Soni Sep 02 '14 at 15:49 — Kalpesh Soni, Sep 02 '14 at 15:49

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

I think @Transactional in junit test is too late

you need to move it to dao (or create business object layer)

when spring creates ctx.getBean, it needs to proxy that class to apply transaction

see if these help Does Spring @Transactional attribute work on a private method?

http://www.intertech.com/Blog/secrets-of-the-spring-aop-proxy/

edited May 23 '17 at 12:23

Community

1
1

answered Sep 02 '14 at 15:57

Kalpesh Soni

6,879
2
56
59

I am actually trying to troubleshoot the @Transactional annotation on a business service method. I noticed it didn't work as expected, so i tried to create unit test for something similar – Filip Sep 02 '14 at 16:03
You're correct. When moving the "doStuff" method outside my UT and inside a SpringBean, the transaction seem to work. I guess it is due to the fact that my UT is not a SPringBean and thus doen't support the @Transaction annotation – Filip Sep 03 '14 at 09:02

Spring rollback issue for JDBC Template

1 Answers1