Configure transaction in Spring 4.1.5 without XML

Question

I'm writing application which connects with Oracle Database. I call function from DB which inserts new records to table. And after this callback I can decide what I want to do: commit or rollback.

Unfortunalety I'm new in Spring, so I have problems with configuration. And what's more I want to make this configuration in Java class, not in XML. And here I need your help.

UPDATED CODE:

ApplicationConfig code:

@Configuration
@EnableTransactionManagement
@ComponentScan("hr")
@PropertySource({"classpath:jdbc.properties", "classpath:functions.properties", "classpath:procedures.properties"})
public class ApplicationConfig {

    @Autowired
    private Environment env;

    @Bean(name="dataSource")
    public DataSource dataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(env.getProperty("jdbc.driver"));
        dataSource.setUrl(env.getProperty("jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.username"));
        dataSource.setPassword(env.getProperty("jdbc.password"));
        dataSource.setDefaultAutoCommit(false);
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        return jdbcTemplate;
    }

    @Bean(name="txName")
    public PlatformTransactionManager txManager() {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dataSource());
        return txManager;
    }
}

I have Dao and Service, where both implements proper interface.

Service implementation:

@Service
public class HumanResourcesServiceImpl implements HumanResourcesService {

    @Autowired
    private HumanResourcesDao hrDao;

    @Override
    public String generateData(int rowsNumber) {
        return hrDao.generateData(rowsNumber);
    }

    @Override
    @Transactional("txName")
    public void shouldCommit(boolean doCommit, Connection connection) throws SQLException {
        hrDao.shouldCommit(doCommit, connection);
    }
}

Dao implementation:

@Repository
public class HumanResourcesDaoImpl implements HumanResourcesDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcCall generateData;

    @Autowired
    public HumanResourcesDaoImpl(JdbcTemplate jdbcTemplate, Environment env) {
        this.jdbcTemplate = jdbcTemplate;
        generateData = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName(env.getProperty("procedure.generateData"));
    }

    @Override
    public String generateData(int rowsNumber) {
        HashMap<String, Object> params = new HashMap<>();
        params.put("i_rowsNumber", rowsNumber);
        Map<String, Object> m = generateData.execute(params);
        return (String) m.get("o_execution_time");
    }

    @Override
    @Transactional("txName")
    public void shouldCommit(boolean doCommit, Connection connection) throws SQLException {
        if(doCommit) {
            connection.commit();
        } else {
            connection.rollback();
        }
    }
}

Main class code:

public class Main extends Application implements Initializable {
    @Override
    public void initialize(URL url, ResourceBundle resourceBundle) {
        ApplicationContext context = new AnnotationConfigApplicationContext(ApplicationConfig.class);

        hrService = context.getBean(HumanResourcesService.class);

        BasicDataSource ds = (BasicDataSource)context.getBean("dataSource");
        Connection connection = ds.getConnection();

        //do something and call
        //hrService.generateData
        //do something and call
        //hrService.shouldCommit(true, connection);
        //which commit or rollback generated data from previoues callback
    }
}

UPDATE:

I think that the problem is with connection, because this statement:

this.jdbcTemplate.getDataSource().getConnection();

creates new connection, so then there is nothing to commit or rollback. But still I can't figure why this doesn't work properly. No errors, no new records... What is wierd, is that when I debuged connection.commit(); I found out that in DelegatingConnection.java, parameter this has proper connection, but there is something like:

_conn.commit();

and _conn has different connection. Why?

Should I in some way synchronize connection for those 2 methods or what? Or this is only one connection? To be honest, I'm not sure how it works exactly. One connection and all callbacks to stored procedures are in this connection or maybe with each callback new connection is created?

Real question is how to commit or rollback data from previous callback which do insert into table?

Answer 1

One easy way to do this is to annotate the method with @Transactional:

@Transactional
public void myBeanMethod() {
    ...
    if (!doCommit)
        throw new IllegalStateException(); // any unchecked will do
}

and spring will roll all database changes back.

Remember to add @EnableTransactionManagement to your spring application/main class

Answer 2

You can use @Transactional and @EnableTransactionManagement to setup transactions without using the XML configuration. In short, annotate the methods/classes you want to have transactions with @Transactional. To setup the transactional management you use the @EnableTransactionManagement inside your @Configuration.

See Springs docs for example on how to use both. The @EnableTransactionManagement is detailed in the JavaDocs but should match the XML configuration.

UPDATE

The problem is that you are mixing raw JDBC calls (java.sql.Connection) with Spring JDBC. When you execute your SimpleJdbcCall, Spring creates a new Connection. This is not the same Connection as the one you later try to commit. Hence, nothing happens when you perform the commit. I tried to somehow get the connection that the SimpleJdbcCall uses, but could not find any easy way.

To test this I tried the following (I did not use params):

@Override
public String generateData(int rowsNumber) {
    //HashMap<String, Object> params = new HashMap<>();
    //params.put("i_rowsNumber", rowsNumber);
    //Map<String, Object> m = generateData.execute(params);

     Connection targetConnection = DataSourceUtils.getTargetConnection(generateData.getJdbcTemplate().getDataSource().getConnection());
     System.out.println(targetConnection.prepareCall((generateData.getCallString())).execute());
  targetConnection.commit();

    return (String) m.get("o_execution_time");
}

If I don't save the targetConnection, and instead try to get the connection again by calling DataSourceUtils.getTargetConnection() when committing, nothing happens. Thus, you must commit on the same connection that you perform the statement on. This does not seem to be easy, nor the proper way.

The solution is to drop the java.sql.Connection.commit() call. Instead, you use Spring Transactions completly. If you use @Transaction on the method that performs database call, Spring will automatically commit when the method has finished. If the method body experiences any Exception (even outside the actual database call) it will automatically rollback. In other words, this should suffice for normal Transaction management.

However, if you are doing batch processing, and wish to have more control over your transactions with commits and rollbacks, you can still use Spring. To programatically control transactions with Spring, you can use TransactionTemplate which have commit and rollback methods. Don't have time to give you proper samples, but may do so in later days if you are still stuck ;)

Answer 3

@Configuration
@EnableTransactionManagement
@ComponentScan(basePackages="org.saat")
@PropertySource(value="classpath:resources/db.properties",ignoreResourceNotFound=true)
public class AppConfig {

    @Autowired
    private Environment env;

    @Bean(name="dataSource")
    public DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("db.driver"));
        dataSource.setUrl(env.getProperty("db.url"));
        dataSource.setUsername(env.getProperty("db.username"));
        dataSource.setPassword(env.getProperty("db.password"));
        return dataSource;
    }


    @Bean(name="entityManagerFactoryBean")
    public LocalContainerEntityManagerFactoryBean getSessionFactory() {
        LocalContainerEntityManagerFactoryBean  factoryBean = new LocalContainerEntityManagerFactoryBean ();
        factoryBean.setDataSource(getDataSource());
        factoryBean.setPackagesToScan("org.saat");
        factoryBean.setJpaVendorAdapter(getJpaVendorAdapter());
        Properties props=new Properties();
        props.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
        props.put("hibernate.hbm2ddl.auto",env.getProperty("hibernate.hbm2ddl.auto"));
        props.put("hibernate.show_sql",env.getProperty("hibernate.show_sql"));
        factoryBean.setJpaProperties(props);
        return factoryBean;
    }


    @Bean(name="transactionManager")
    public JpaTransactionManager getTransactionManager() {
        JpaTransactionManager jpatransactionManager = new JpaTransactionManager();
        jpatransactionManager.setEntityManagerFactory(getSessionFactory().getObject());
        return jpatransactionManager;
    }

    @Bean
    public JpaVendorAdapter getJpaVendorAdapter() {
        HibernateJpaVendorAdapter hibernateJpaVendorAdapter = new HibernateJpaVendorAdapter();
        return hibernateJpaVendorAdapter;
    }
}