Stuck with Transactions

[twocoasttb]

I have in the past successfully built web applications that use Spring and Hibernate with transaction support provided by HibernateTransactionManager. My grasp on how all that worked isn't very strong, but there were plenty of good examples to follow.

Now, I'm building a standalone application that (1) won't have access to a container-provided transaction manager and (2) won't be using any kind of BeanFactory.

I'd very much like to use Spring's JDBC abstractions, but am clueless how to get transaction support. Here's how things are going:

I'm using commons-dbcp for connection pooling; I set up a dataSource like this:

private void init() {
  GenericObjectPool connectionPool = new GenericObjectPool();
  connectionFactory = new DriverManagerConnectionFactory(getDatabaseURI(),
        getUsername(), getPassword());
  PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
        connectionFactory, connectionPool, null, getValidationQuery(), false, false);
  new PoolingDriver().registerPool(this.getClass().getName(), connectionPool);
  dataSource = new PoolingDataSource(connectionPool);
}

public JdbcTemplate getJdbcTemplate() {
  return new JdbcTemplate(dataSource);
}

This code is contained in a Database object, which defines the connectionURI, the validation query, username, password, etc.

I have other classes which use the Database object to get a JdbcTemplate:

public void doSomething() throws SQLException {
  JdbcTemplate jdbcTemplate = database.getJdbcTemplate();
  jdbcTemplate.update&#40;"<some update query">&#41;;
  jdbcTemplate.update&#40;"<some other update query">&#41;;
&#125;

This all works when the connection factory is instructed to create connections with autoCommit = true, which is undesirable. Otherwise, work done by the JdbcTemplate is never commited.

Work done in subclasses of SqlUpdate isn't committed either:

public class DeleteSuspension extends SqlUpdate {
  public DeleteSuspension(Database database) throws SQLException {
    setDataSource(database);
    setSql(database.getStatement("DELETE_SUSPENSION").getSql());
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.VARCHAR));
    compile();
  }

  public int run(AgentSuspension suspension) {
    Object[] params = new Object[] { suspension.getServerId(),
        suspension.getDatabaseId(),
        suspension.isScheduledDowntime() ? "Y" : "N" };
    
    return update(params);
  }
}

...
DeleteSuspension s = new DeleteSuspension(dataSource);
s.run(suspension);
...

Concurrently running threads will get JdbcTemplates from the Database object and perform work. The sum of work done by each thread should be considered a transaction. It seems to me that I should be using a PlatformTransactionManager (probably DataSourceTransactionManager). But I'm stumped on how to hook it in. I'd greatly appreciate if someone could point me in the right direction.

[twocoasttb]

Sigh. Sometimes walking away from the computer for a while helps clarify things. I think I've figured it out, although I'm not 100% certain it's the correct approach.

1. Each Database now has its own DataSourceTransactionManager. The DataSource is wrapped with a TransactionAwareDataSourceProxy:

connectionFactory = new DriverManagerConnectionFactory(getDatabaseURI(),
        getUsername(), getPassword());
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
        connectionFactory, connectionPool, null, getValidationQuery(), false, false);
new PoolingDriver().registerPool(this.getClass().getName(), connectionPool);
dataSource = new TransactionAwareDataSourceProxy(new PoolingDataSource(connectionPool));

txManager = new DataSourceTransactionManager(dataSource);
txDef = new DefaultTransactionDefinition();
txDef.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

2. When a thread wants to execute a transaction in the Database, it calls this method on the Database object:

public TransactionStatus startTransaction() {
  return txManager.getTransaction(txDef);
}

3. And when a thread wants to commit a transaction, it calls:

public void commit(TransactionStatus txStatus) {
  txManager.commit(txStatus);
}

[Juergen Hoeller]

Using a JDBC DataSourceTransactionManager programmatically through the PlatformTransactionManager API seems the right way to go for your needs. JDBC data access code will automatically participate in such transactions.

Wrapping your DataSource in a TransactionAwareDataSourceProxy is actually not strictly necessary if you're using Spring's JDBC support: JdbcTemplate and Spring's JDBC operation objects will automatically detect active transactions, even if working on the plain target DataSource. TransactionAwareDataSourceProxy is mainly intended for non-Spring JDBC data access code which is not aware of Spring transactions.

Juergen

[carlos_santiago]

Hi, I am with problems to understand and to implement the manager of transactions of the Spring. I have that to insert a register in two tables. The implementation of my DAO is:

public class FornecedoresImpl extends LinkPecasAbstract{
    
    private JdbcTemplate jdbcTemplate;
    private TransactionTemplate transactionTemplate;
    private static Logger logger = Logger.getLogger("br.com.rodobens");
    
    private int insertFornecedor(FornecedoresBean bean){
        StringBuffer sql = new StringBuffer();
        sql.append(" insert into DMMLP009 (IDSTATFOR, IDTIPFOR, NMFOR, NRCPJFOR, NMFANFOR, IDMRCFOR, ");
        sql.append(" DSENDFOR, NRENDFOR, NMBAIFOR, CDCEPFOR, NMCIDFOR, SIUNFFOR, NRDDDTELFOR, NRTELFOR, DSEMAILFOR) ");
        sql.append(" values (" + DominioStatusFornecedor.ATIVO + ", ?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,?, ?, ?, ? ) ");
       
        Object[] parametros = new Object[]{ new Integer(bean.getTipoFornecedor()), bean.getNomeFornecedor(),
                                            new Long(bean.getCnpj()), bean.getNomeFantasia(), new Integer(bean.getMarca()),
                                            bean.getRua(), new Integer(bean.getNumero()), bean.getBairro(), new Integer(bean.getCep()),
                                            bean.getCidade(), bean.getUf(), new Integer(bean.getDdd()), new Integer(bean.getTelefone()),
                                            bean.getEmailFornecedor() };  
                        
       return jdbcTemplate.update(sql.toString(), parametros);
    }
    
    private int insertVendedorDeFornecedor(final String nomeVendedor, final String emailVendedor){
        StringBuffer sql = new StringBuffer();
        sql.append(" insert into DMMLP010 (CDVENFOR, CDFOR, NMVENFOR, DSEMAILVENFOR) ");
        sql.append(" values ((select coalesce(max(CDVENFOR)+1, 1) ");
        sql.append(" from DMMLP010 where CDFOR = (select max(CDFOR) from DMMLP009)),");
        sql.append(" (select max(CDFOR) from DMMLP009), ?, ?");

        Object[] parametros = new Object[]{ nomeVendedor, emailVendedor};
        
        return jdbcTemplate.update(sql.toString(), parametros);        
    }

    public void insereFornecedor(final FornecedoresBean bean){
        transactionTemplate.execute(new TransactionCallbackWithoutResult(){
            protected void doInTransactionWithoutResult(TransactionStatus status){
                try{
                    // insere o fornecedor
                    insertFornecedor(bean);
                    
                    // insere vendedor
                    insertVendedorDeFornecedor(bean.getNomeVendedor1(), bean.getEmailVendedor1());
    
                    // se existe outro vendedor, insere
                    if(bean.getNomeVendedor2() != null){
                        insertVendedorDeFornecedor(bean.getNomeVendedor2(), bean.getEmailVendedor2());
                    }
                }
                catch(Exception ex){
                    logger.error(ex.getMessage());
                    logger.error("Fornecedor não foi inserido.");
                }
            }
        });
    }
    /**
     * @param jdbcTemplate  seta o valor de jdbcTemplate na variável jdbcTemplate.
     */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * @param transactionTemplate  seta o valor de transactionTemplate na variável transactionTemplate.
     */
    public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
        this.transactionTemplate = transactionTemplate;
    }
    
}

My context is thus:

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

	<!-- associa um template a um data source -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<bean id="fornecedoresDAO" class="br.com.rodobens.dao.fornecedores.FornecedoresImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate"/>
		<property name="transactionTemplate" ref="transactionTemplate"/>
	</bean>

First insert functions, but as it throws an exception. But first insert commit!!!
Because?
Thank's
Carlos