Spring transaction by connecting to multiple databases

Question

I am trying to work with a small program where I can connect to several databases using Spring and try to use Spring transactions by deploying my web application to the weblogic server. The problem is that transaction management is not working properly. I am trying to insert records into 2 databases, the first inserts without any exceptions, where when the second insert request is written in such a way that it throws an exception. Ideally, in this situation, the transaction should rollback, but the first transaction is completed without any problems.

Here is my spring-config.xml file

<context:component-scan base-package="com.examples" /> <!-- Database1 --> <bean id="db1DataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mydb1" /> <property name="username" value="root" /> <property name="password" value="" /> </bean> <!-- Database2 --> <bean id="db2DataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mydb2" /> <property name="username" value="root" /> <property name="password" value="" /> </bean> <tx:annotation-driven transaction-manager="txManager" /> <bean id="txManager" class="org.springframework.transaction.jta.JtaTransactionManager" /> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" /> <property name="prefix"> <value>/WEB-INF/views/</value> </property> <property name="suffix" value=".jsp" /> </bean> 

This is my controller:

 @Controller public class EmployeeController { @Autowired private CommonEmployeeService commonService; @RequestMapping(value = "/employee", method = GET) public String showPersonListForGivenAge( @RequestParam(value = "id") int id, @RequestParam(value = "name") String name, @RequestParam(value = "email") String email, Map<String, Object> model) { Employee e = new Employee(id,name); EmployeeDetails details = new EmployeeDetails(id,email); commonService.insert(e, details); return "welcome"; } } 

This is my shared service:

 @Service public class CommonEmployeeService { @Autowired EmployeeDetailsService detailsService; @Autowired EmployeeService service; @Transactional public boolean insert(Employee e, EmployeeDetails details) { service.insert(e); detailsService.insert(details); return true; } } 

These are my other services:

EmployeeService.java

 @Service public class EmployeeService { @Autowired EmployeeDao dao; //@Transactional(propagation=Propagation.REQUIRED) public boolean insert(Employee e) { return dao.insert(e); } } 

EmployeeDetailsService.java . The DAO in this service throws a NullPointerException

 @Service public class EmployeeDetailsService { @Autowired EmployeeDetailsDao dao; //@Transactional(propagation=Propagation.REQUIRED) public boolean insert(EmployeeDetails e) { return dao.insert(e); } } 

Update: Adding Dao classes:

EmployeeDao.java:

 @Repository public class EmployeeDao { JdbcTemplate template; @Resource(name = "db1DataSource") public void setDataSource(DataSource dataSource) { this.template = new JdbcTemplate(dataSource); } public boolean insert(Employee e) { int cnt = template.update("insert into Employee values(?,?)", e.getId(), e.getName()); if (cnt > 0) { return true; } return false; } } 

EmployeeDetailsDao.java

 @Repository public class EmployeeDetailsDao { JdbcTemplate template; @Resource(name = "db2DataSource") public void setDataSource(DataSource dataSource) { this.template = new JdbcTemplate(dataSource); } public boolean insert(EmployeeDetails e) { if(e != null){ throw new NullPointerException(); } int cnt = template.update("insert into EmployeeDetails values(?,?)", e.getId(), e.getEmail()); if (cnt > 0) { return true; } return false; } } 

Answer 1

Try adding below in spring-config.xml :

 <context:component-scan base-package="your.packagename.contatining.EmployeeDetailsDao" /> <mvc:annotation-driven /> 

and changing @Transactional to CommonEmployeeService to @Transactional(rollbackFor=Exception.class, propagation=Propagation.REQUIRED) as shown below

 @Service public class CommonEmployeeService { @Autowired EmployeeDetailsService detailsService; @Autowired EmployeeService service; @Transactional(rollbackFor=Exception.class, propagation=Propagation.REQUIRED) public boolean insert(Employee e, EmployeeDetails details) { service.insert(e); detailsService.insert(details); return true; } }