Insert into two tables in two different database

Question

My Java web application on spring framework is using two database say database-1 and database-2. Both database have User table. What I am trying to do is to insert record into both tables at same time.

There are two persistence-unit in the persistence.xml that is pointing out to database.

Here is my persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
                                 http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
    version="1.0">

    <persistence-unit name="p1-jpa" transaction-type="RESOURCE_LOCAL">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>


         <jta-data-source>java:jboss/datasources/MySqlDS2</jta-data-source> 

        <class>com.xyz.entity.User</class>
        <exclude-unlisted-classes />

        <properties>
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
        </properties>

    </persistence-unit>

    <persistence-unit name="p2-jpa" transaction-type="RESOURCE_LOCAL">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>


         <jta-data-source>java:jboss/datasources/MySqlDS2</jta-data-source>         
         <class>com.mmxhealthcare.entity.MMASCUser</class>  
         <exclude-unlisted-classes />

        <properties>
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
        </properties>

    </persistence-unit>
    </persistence>

Now Whenever I am trying to add user, it is inserted only in Database-1. I am not getting any exception.

Here is my spring-servlet.xml file:-

<?xml version="1.0" encoding="utf-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jaxrs="http://cxf.apache.org/jaxrs"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:util="http://www.springframework.org/schema/util" xmlns:security="http://www.springframework.org/schema/security"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
        http://cxf.apache.org/jaxrs http://cxf.apache.org/schemas/jaxrs.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
        http://www.springframework.org/schema/security  http://www.springframework.org/schema/security/spring-security-3.1.xsd 
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">

    <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="viewClass" value="org.springframework.web.servlet.view.JstlView"/>
        <property name="prefix" value="/WEB-INF/jsp/"/>
        <property name="suffix" value=".jsp"/>
        <property name="order" value="0"/>
    </bean>

    <!-- <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"></bean> -->
    <context:property-placeholder location="classpath:config.properties" />
    <context:annotation-config />

    <context:component-scan base-package="com.xyz.controller" />
    <context:component-scan base-package="com.xyz.service" />
    <context:component-scan base-package="com.xyz.dao" />
    <context:component-scan base-package="com.xyz.security" />
    <context:component-scan base-package="com.xyz.dto" />
    <context:component-scan base-package="com.xyz.util" />
    <context:component-scan base-package="com.xyz.entity" />

    <tx:annotation-driven />

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

    <bean id="transactionManager1" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="aaentityManagerFactory" />
    </bean>

    <bean id="transactionManager2" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="mmascentityManagerFactory" />
    </bean>

    <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />


    <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="d1SourceLocal" />
        <property name="persistenceUnitName" value="p1-jpa" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </property>
    </bean>

    <bean id="mmascentityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="d2DataSourceLocal" />
        <property name="persistenceUnitName" value="p2-jpa" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </property>
    </bean>

    <!-- <bean id="dataSourceLocal" class="org.springframework.jndi.JndiObjectFactoryBean">  
        <property name="jndiName" value="java:jboss/datasources/MySqlDS2"/>  
    </bean> -->

    <!-- Local -->
    <bean id="d1SourceLocal"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${database.driver}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
    </bean>


    <bean id="d2DataSourceLocal"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${d2-database.driver}" />
        <property name="url" value="${d2-database.url}" />
        <property name="username" value="${d2-database.username}" />
        <property name="password" value="${d2-database.password}" />
    </bean>
</beans>

Here it is my service class:

public class Userservice{
    @Autowired
    Database2IUserDAO immDao;

    @Autowired 
    Database1IUserDAO iaaDao;

    public User saveUser(fname,address){
    User u  = new User(); // This points Database-1 User table.
    u.setFname(fname);
    u.setAddress(address);
    iaaDao.save(u);
    User2 u2 = new User2(); // This points Database-2 User table.
    u2.setFname(fname);
    u2.setAddress(address);
    immDao.save(u2);
    }
}

Here is my Database1IUserDAO //This is an Interface

  package com.xyz.dao;
    public interface Database1IUserDAO {

        public Object save(Object ob);

    }

Here is my Database2IUserDAO //This is an another Interface for database2

package com.xyz.dao;
public interface Database2IUserDAO {

    public Object save(Object ob);

}

** And this is finally DAO class for Database-1 and Database 2** My both DAO class extends BaseDao class, that have Save() method that we are using to insert or save.

BaseDao.java

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.transaction.annotation.Transactional;


public class BaseDAO {

    protected EntityManager entityManager;

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this. entityManager = entityManager;
    }

    @Transactional
    public Object save(Object ob) {
        Object object = entityManager.merge(ob);
        return object;
    }

    @Transactional
    public void remove(Object ob) {
        Object object = entityManager.merge(ob);
        entityManager.remove(object);
    }

    @Transactional
    public int update(String query) {
        return entityManager.createQuery(query).executeUpdate();
    }

}

Please help.

Can you also show relevants of the code where you add a user to your database? — mthmulders, Mar 21 '14 at 08:47

score 3 · Accepted Answer · answered Mar 21 '14 at 09:40

You have to use database specific Transaction Manager as you mentioned in your spring-context.xml file as below.

<bean id="transactionManager2" class="org.springframework.orm.jpa.JpaTransactionManager">
      <property name="entityManagerFactory" ref="mmascentityManagerFactory" />
</bean>

You should create a save function in your DAO Class with database specific Transaction Manager as follows:

@Transactional(value="transactionManager2")
public Object save(Object ob) {
     Object object = entityManager.merge(ob);
     return object;
}

I hope it will work for you.

score 1 · Answer 2 · edited May 23 '17 at 10:26

You seem to be using the same EntityManager (EM) from the BaseDAO for all your DAOs. Since you don't specify which TransactionManager the EM is using, it will default to this one:

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

... as noted in the documentation http://docs.spring.io/spring/docs/2.0.8/reference/transaction.html (find table 9.2)

table 9.2

If you want to write to another database, you will need to explicity tell Spring which one you want to use. See this answer as an example:

Spring multiple @Transactional datasources

score 0 · Answer 3 · answered Mar 21 '14 at 09:01

0

It looks you are using the same datasource for both the persistence unit.

ie java:jboss/datasources/MySqlDS2

so it is inserting into only one DB which is corresponding to above data source mstly Mysql DB.

so add different datasource in both the persistence-unit , and that should help you to insert to both the databases.

it wont give any exception because one datasource that you are using might be valid datasource(i cant guarantee as we dont have data source information in your post).

let me know for any thing else.

answered Mar 21 '14 at 09:01

Karibasappa G C

2,686
1
18
27

I have edited my question and add all the relevant code, Please look. And I am using Tomcat right now. – Ankit Mar 21 '14 at 09:12
hey have u checked what i answered above – Karibasappa G C Mar 21 '14 at 09:17

Insert into two tables in two different database

3 Answers3