Spring JDBC Transaction

[antonyrajesh]

hi i am new new to spring
i am using JDBC Template for database access.
now i my application needs transaction.

this is my JNDI.xml

<beans>
<bean id="db2DataSource" class="org.springframework.jndi.JndiObjectFactoryB ean">
<property name="jndiName">
<value>java:comp/env/jdbc/DB2DS</value>
</property>
</bean>

<bean id="db2" class="com.dnb.cors.dbconnection.DataSourceConfig" >
<property name="dataSource">
<ref bean="db2DataSource"/>
</property>
</bean>

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

<bean id="myDao" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager" ref="transactionManager"/>
<property name="target" ref="db2"/>
<property name="proxyTargetClass" value="true"/>
<property name="transactionAttributes">
<props>
<prop key="*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="get*">PROPAGATION_SUPPORTS</prop>
</props>
</property>
</bean>


</beans>

everything loaded properly.
but i dont know how to use transaction from program?
can anyone help...
please forgive if my approach is totally wrong and advise me how to acheive transaction.

Thanks in Advance
Antony

[Marten Deinum]

use [ code][/code ] tags when posting code. You already achieved transactions. Just call methods on the bean named myDao and you have transactions. I suggest chapter 9 of the reference guide.

[antonyrajesh]

Hi Marten Deinum,

Thanks for ur reply...
can u please send me the link for the reference guide...

i changed lot of things in my code, and its working fine for me now.
but i dont know, the approach i have taken is correct.
i have attached all my code here please check and let me know your valueable comments.

i am not using AOP, is it a problem?

Thanks in advance

JNDI.XML

<beans>
	<bean id="db2DataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    	<property name="jndiName">
      		<value>java:comp/env/jdbc/DB2DS</value>
    	</property>
  	</bean>
  
  	<bean id="db2" class="com.dnb.cors.dbconnection.DataSourceConfig">
    	<property name="dataSource">
      		<ref bean="db2DataSource"/>
    	</property>
  	</bean>
  	
  	<bean id="db2TransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    	<property name="dataSource" ref="db2DataSource"/>
  	</bean>
  	
  	<bean id="trans" class="com.dnb.cors.dbconnection.DataSourceTransactionManagerConfig">
    	<property name="dataSourceTransactionManager">
      		<ref bean="db2TransactionManager"/>
    	</property>
  	</bean>
</beans>

DBManagerImpl.java

package com.dnb.cors.dbconnection;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;

public class DBManagerImpl implements DBManager {
	
	private DataSource obj_datasource = null;
	private JdbcTemplate obj_jdbctemplate = null;
	private DataSourceTransactionManager  obj_transmanager = null;
	private TransactionTemplate obj_trnastemplate = null;
	DefaultTransactionDefinition def = null;
	TransactionStatus status = null;
	//private static DBManager singleton;
	
	public DBManagerImpl()
	{
		obj_datasource=(ConnectDB.getInstance("db2","trans")).getDataSourceConnection();
		obj_jdbctemplate=new JdbcTemplate(obj_datasource);
		obj_transmanager = (ConnectDB.getInstance("db2","trans")).getTransactionManager();
		obj_trnastemplate = new TransactionTemplate(obj_transmanager);
		def = new DefaultTransactionDefinition();
        def.setPropagationBehavior(obj_trnastemplate.PROPAGATION_REQUIRED);
	}

	public List select(String Query, Object[] param) {
		List list=obj_jdbctemplate.queryForList(Query,param);
		return list;
	}

	public List select(String Query) {
		List list=obj_jdbctemplate.queryForList(Query);
		return list;
	}
	
	public int save(String Query, Object[] param) {
		return obj_jdbctemplate.update(Query,param);
	}

	public int delete(String Query, Object[] param) {
		return obj_jdbctemplate.update(Query,param);
	}
	
	public int deleteAll(String Query) {
		return obj_jdbctemplate.update(Query);
	}
	
	public void startTransaction(){
		status = obj_transmanager.getTransaction(def);
	}
	
	public void commit(){
		obj_transmanager.commit(status);
	}
	
	public void rollback(){
		obj_transmanager.rollback(status);
	}
	/*public void doSomething() {System.out.println("Transaction");
	//obj_transmanager.g
		//DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        //def.setPropagationBehavior(obj_trnastemplate.PROPAGATION_REQUIRED);
        System.out.println("DefaultTransactionDefinition");
        TransactionStatus status = obj_transmanager.getTransaction(def);
        System.out.println("TransactionStatus");
        try {System.out.println("JDBC TEMPLATE STARTS");
            obj_jdbctemplate.execute("insert into rss05_func_user_map values('FN_11','URN_19')");
            obj_jdbctemplate.execute("insert into rss05_func_user_map values('FN_12','test9')");
            obj_jdbctemplate.execute("insert into rss05_func_user_map values('FN_50','URN_19')");
            obj_jdbctemplate.execute("insert into rss05_func_user_map values('FN_14','URN_19')");
            System.out.println("JDBC TEMPLATE ENDS");
        }
        catch (Exception ex) {System.out.println("catch" + ex);
        obj_transmanager.rollback(status);
        obj_jdbctemplate.execute("insert into rss05_func_user_map values('FN_11','URN_19')");
            return;
        }System.out.println("Start Commit");
        obj_transmanager.commit(status);System.out.println("END Commit");
        obj_jdbctemplate.execute("insert into rss05_func_user_map values('FN_15','URN_19')");
    }
*/
	/*public void test() {
		obj_jdbctemplate.batchUpdate("update A set B = null where id = ?", new BatchPreparedStatementSetter() {
				   public void setValues(PreparedStatement ps, int i) throws SQLException {
				    ps.setString(1, ids[i]);
				   }
				   public int getBatchSize() {
				    return ids.length;
				   }
				  });

	}*/
	/*public static DBManager getDBManager()
	{
		if(singleton==null)
			singleton=new DBManagerImpl();		
		return  singleton;
	}*/
}

DBManager.java

package com.dnb.cors.dbconnection;

import java.util.List;

public interface DBManager {
	
	public List select(String Query, Object[] param);
	public List select(String Query);
	public int save(String Query, Object[] param);
	public int delete(String Query, Object[] param);
	public int deleteAll(String Query);
	public void startTransaction();
	public void rollback();
	public void commit();

}

DataSourceTransactionManagerConfig.java

package com.dnb.cors.dbconnection;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

public class DataSourceTransactionManagerConfig {
	private DataSourceTransactionManager dataSourceTransactionManager;

	public void setDataSourceTransactionManager(DataSourceTransactionManager dataSourceTransactionManager)
	{		   
		this.dataSourceTransactionManager=dataSourceTransactionManager;   
	}
	public DataSourceTransactionManager getDataSourceTransactionManager()
	{
		return  dataSourceTransactionManager;
	}
}

DataSourceConfig.java

package com.dnb.cors.dbconnection;

import javax.sql.DataSource;

public class DataSourceConfig
{
	private DataSource dataSource;

	public void setDataSource(DataSource dataSource)
	{		   
		this.dataSource=dataSource;   
	}
	public DataSource getDataSource()
	{
		return  dataSource;
	}
}

ConnectDB .java

package com.dnb.cors.dbconnection;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

public class ConnectDB  {
	private static ConnectDB singleton;
	private DataSourceConfig ObJndi =null;
	private DataSourceTransactionManagerConfig ObTran =null;
	
	private ConnectDB(String simple, String transaction) {
		ApplicationContext context=new ClassPathXmlApplicationContext("com/dnb/cors/dbconnection/jndi.xml");
		ObJndi = (DataSourceConfig)context.getBean(simple);
		ObTran = (DataSourceTransactionManagerConfig)context.getBean(transaction);
	}
	
	public synchronized static ConnectDB getInstance(String simple, String transaction){
		if(singleton==null)
			singleton=new  ConnectDB(simple,transaction);		
		return  singleton;
	}
	
	public DataSource getDataSourceConnection() {
		return ObJndi.getDataSource();
	}
	
	public DataSourceTransactionManager getTransactionManager()
	{
		return ObTran.getDataSourceTransactionManager();
	}
}