Spring and JPA with two data sources (with annotations)

A few days I recevied a comment from my friend @sock_osg (you can follow on twitter) he recomends me to rewrite my previous post with annotations.

And well here is it. But the are more few things to comment about it, for example in the previous post there are not a Transactional capabilities between DB’s because I’m not using JTA transaction type.

Now for this example I write the code to support transactions between multple data bases with different data sources, like I read on stack overflow:

if you find yourself with multiple entity managers, with corresponding tx managers, then you should consider using a single JtaTransactionManager instead. The entity managers should be able to participate in JTA transactions, and this will give you full transactionality across both entity managers, without having to worry about which entity manager you’re in at any one time.

You can download all code here, it’s hosted on my Github account, pull the code from the branch named “annotations”.

Let’s review the most important files, frst the DAO classes:

package org.oz.persistence.dao.db1;

import org.springframework.stereotype.Repository;

import javax.persistence.*;
import java.util.Collection;

/**
 * Created by <a href="https://twitter.com/jaehoox">jaehoo</a> on 16/03/2018
 */
@Repository
public class CustomerDao {

    public static final String SEL_TABLES="select.tablesh2";

    @PersistenceContext(unitName = "unit1" , type = PersistenceContextType.TRANSACTION)
    private EntityManager em;

    public Collection loadCustomers() {
        Query query = em.createQuery("FROM Customer");
        return query.getResultList();

    }

    public Collection getTables(){
        return em.createNamedQuery(SEL_TABLES).getResultList();
    }

}

package org.oz.persistence.dao.db2;

import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceContextType;
import javax.persistence.Query;
import java.util.Collection;

/**
 * Created by <a href="https://twitter.com/jaehoox">jaehoo</a> on 16/03/2018
 */
@Repository
public class ProductDao {

    public static final String SEL_TABLES="select.tablesh2";

    @PersistenceContext(unitName = "unit2", type = PersistenceContextType.TRANSACTION, name = "unit2")
    private EntityManager em;

    public Collection loadProducts() {
        Query query = em.createQuery("FROM Product");
        return query.getResultList();

    }

    public Collection getTables(){
        return em.createNamedQuery(SEL_TABLES).getResultList();
    }

}

There aren’t much to comment only it’s necesary inject the EntityManager factory with each one the persistence unit names.

The trick is in the configuration classes, you must to declare one class per database with their data source and the EntityManagerFactory bean, something like these:

package org.oz.conf;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import org.h2.jdbcx.JdbcDataSource;
import org.oz.persistence.dao.db1.CustomerDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.*;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * Created by <a href="https://twitter.com/jaehoox">jaehoo</a> on 10/04/2018
 */

@Configuration
@PropertySource({ "classpath:app-props.properties" })
@DependsOn("transactionManager")
@EnableJpaRepositories(
        basePackages = "org.oz.persistence.dao.db1.model",
        entityManagerFactoryRef = "entityManagerFactory1",
        transactionManagerRef = "transactionManager"
)
public class FirstDB {

    @Autowired
    private Environment env;

    @Primary
    @Bean(name = "dataSource1")
    @Lazy
    public DataSource getDataSource() {

        JdbcDataSource dataSource= new JdbcDataSource();
        dataSource.setURL(env.getProperty("jdbc1.url"));
        dataSource.setUser(env.getProperty("jdbc1.user"));
        dataSource.setPassword(env.getProperty("jdbc1.pass"));

        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(dataSource);
        xaDataSource.setUniqueResourceName("xads1");

        return dataSource;
    }

    @Primary
    @Bean(name = "entityManagerFactory1")
    @DependsOn("transactionManager")
    @Lazy
    public LocalContainerEntityManagerFactoryBean getEntityManager() {

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();

        em.setPersistenceXmlLocation(env.getProperty("persistenceXmlLocation"));
        em.setPackagesToScan(new String[] {"org.oz.persistence.dao.db1" });
        em.setPersistenceUnitName(env.getProperty("persitence.unit1"));
        em.setJtaDataSource(getDataSource());

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        return em;
    }

    @Bean(name = "customerDao")
    public CustomerDao getCustoerDao(){
        return new CustomerDao();
    }
}

package org.oz.conf;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import org.h2.jdbcx.JdbcDataSource;
import org.oz.persistence.dao.db1.CustomerDao;
import org.oz.persistence.dao.db2.ProductDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.*;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * Created by <a href="https://twitter.com/jaehoox">jaehoo</a> on 10/04/2018
 */

@Configuration
@PropertySource({ "classpath:app-props.properties" })
@DependsOn("transactionManager")
@EnableJpaRepositories(
        basePackages = "org.oz.persistence.dao.db2.model",
        entityManagerFactoryRef = "entityManagerFactory2",
        transactionManagerRef = "transactionManager"
)
public class SecondDB {

    @Autowired
    private Environment env;

    @Primary
    @Bean(name = "dataSource2")
    @Lazy
    public DataSource getDataSource() {

        JdbcDataSource dataSource= new JdbcDataSource();
        dataSource.setURL(env.getProperty("jdbc2.url"));
        dataSource.setUser(env.getProperty("jdbc2.user"));
        dataSource.setPassword(env.getProperty("jdbc2.pass"));

        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(dataSource);
        xaDataSource.setUniqueResourceName("xads2");

        return dataSource;
    }

    @Primary
    @Bean(name = "entityManagerFactory2")
    @DependsOn("transactionManager")
    @Lazy
    public LocalContainerEntityManagerFactoryBean getEntityManager() {

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();

        em.setPersistenceXmlLocation(env.getProperty("persistenceXmlLocation"));
        em.setPackagesToScan(new String[] {"org.oz.persistence.dao.db2" });
        em.setPersistenceUnitName(env.getProperty("persitence.unit2"));
        em.setJtaDataSource(getDataSource());

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        return em;
    }

    @Bean(name = "productDao")
    public ProductDao getProductDao(){
        return new ProductDao();
    }
}

If you want to know more about the explanation of the combination of the annotations you can read this entry (it’s not mine), I pick this example from there but the difference is I’m using declarative persistence xml referenced by the spring beans with annotations.

As you can see, both has a common transaction manager, this is only one transaction manager to manage the transactionality in both data bases:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" 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_2_0.xsd">

    <persistence-unit name="unit1" transaction-type="JTA">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <mapping-file>META-INF/native-querys.xml</mapping-file>
        <class>org.oz.persistence.dao.db1.model.Customer</class>

        <exclude-unlisted-classes>true</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.transaction.jta.platform" value="org.oz.persistence.dao.AtomikosJtaPlatform" />
            <property name="javax.persistence.transactionType" value="JTA" />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
            <property name="hibernate.archive.autodetection" value="class,hbm"/>
            <property name="useUnicode" value="true"/>
            <property name="characterSetResults" value="UTF8"/>
            <property name="characterEncoding" value="UTF8"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.use_sql_comments" value="false"/>
            <property name="hibernate.hbm2ddl.keywords" value="auto-quote"/>
            <property name="hibernate.bytecode.use_reflection_optimizer" value="true"/>
            <property name="hibernate.connection.useUnicode" value="true"/>
            <property name="hibernate.connection.characterEncoding" value="UTF8"/>
            <property name="hibernate.connection.charSet" value="UTF8"/>
            <property name="hibernate.connection.characterSetResults" value="UTF8"/>

            <property name="hibernate.default_schema" value="BASEA"/>
        </properties>

    </persistence-unit>

    <persistence-unit name="unit2" transaction-type="JTA">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <mapping-file>META-INF/native-querys.xml</mapping-file>
        <class>org.oz.persistence.dao.db2.model.Product</class>

        <exclude-unlisted-classes>true</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.transaction.jta.platform" value="org.oz.persistence.dao.AtomikosJtaPlatform" />
            <property name="javax.persistence.transactionType" value="JTA" />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
            <property name="hibernate.archive.autodetection" value="class,hbm"/>
            <property name="useUnicode" value="true"/>
            <property name="characterSetResults" value="UTF8"/>
            <property name="characterEncoding" value="UTF8"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.use_sql_comments" value="false"/>
            <property name="hibernate.hbm2ddl.keywords" value="auto-quote"/>
            <property name="hibernate.bytecode.use_reflection_optimizer" value="true"/>
            <property name="hibernate.connection.useUnicode" value="true"/>
            <property name="hibernate.connection.characterEncoding" value="UTF8"/>
            <property name="hibernate.connection.charSet" value="UTF8"/>
            <property name="hibernate.connection.characterSetResults" value="UTF8"/>

            <property name="hibernate.default_schema" value="BASEB"/>
        </properties>

    </persistence-unit>

</persistence>

The main part, to manage transactions I’m using Atomikos,  you need to create a class extended from AbstactJtaPlataform this is because there are a issue on spring with atomikos integration:

package org.oz.persistence.dao;

import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;

import lombok.Setter;
import org.hibernate.engine.transaction.jta.platform.internal.AbstractJtaPlatform;

/**
 * Created by <a href="https://twitter.com/jaehoox">jaehoo</a> on 10/04/2018
 */
public class AtomikosJtaPlatform extends AbstractJtaPlatform {

    private static final long serialVersionUID = 1L;

    @Setter
    static TransactionManager transactionManager;
    @Setter
    static UserTransaction transaction;

    @Override
    protected TransactionManager locateTransactionManager() {
        return transactionManager;
    }

    @Override
    protected UserTransaction locateUserTransaction() {
        return transaction;
    }
}

Now in the persistence.xml you need to change the configuration tto use JTA transaction type,  modify an add the next highlitghted lines (hibernate.transaction.jta.platform and javax.persistence.transactionType)




    
        org.hibernate.jpa.HibernatePersistenceProvider

        META-INF/native-querys.xml
        org.oz.persistence.dao.db1.model.Customer

        true
        
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            

            
        

    

    
        org.hibernate.jpa.HibernatePersistenceProvider

        META-INF/native-querys.xml
        org.oz.persistence.dao.db2.model.Product

        true
        
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            

            
        

    


And here is it the configuration Properties file, explained by self:

# TEST Properties Don't modify

jdbc1.driver=org.h2.Driver
jdbc1.url=jdbc:h2:mem:db_1;MODE=ORACLE;INIT=RUNSCRIPT FROM 'classpath:dataset1.sql';LOCK_MODE=0;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=1
jdbc1.user=
jdbc1.pass=

jdbc2.driver=org.h2.Driver
jdbc2.url=jdbc:h2:mem:db_2;MODE=ORACLE;INIT=RUNSCRIPT FROM 'classpath:dataset2.sql';LOCK_MODE=0;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=1
jdbc2.user=
jdbc2.pass=

persistenceXmlLocation=classpath:META-INF/persistence.xml
persitence.unit1=unit1
persitence.unit2=unit2

Now we can test it with Junit:

package org.oz.persistence.dao.db1;

import lombok.extern.slf4j.Slf4j;
import org.hibernate.annotations.UpdateTimestamp;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.oz.conf.FirstDB;
import org.oz.conf.MainConf;
import org.oz.conf.SecondDB;
import org.oz.persistence.dao.db1.model.Customer;
import org.oz.persistence.dao.db2.ProductDao;
import org.oz.persistence.dao.db2.model.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

import java.util.ArrayList;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { FirstDB.class, SecondDB.class,MainConf.class })
@TransactionConfiguration(transactionManager = "transactionManager")
@Slf4j
public class CustomerDaoTest {

    @Resource(name = "customerDao")
    private CustomerDao customerDao;

    @Resource(name = "productDao")
    private ProductDao productDao;

    @Test
    @Transactional
    public void loadCustomers() throws Exception {

        List customers = (List) customerDao.loadCustomers();

        log.info("customers:{}",customers.size());

        for(Customer c : customers){
            log.info("{}",c);
        }

        List tables = (List) customerDao.getTables();

        log.info("tables:{}",tables.size());
        for(Object c : tables){
            log.info("{}",c);
        }

    }

    @Test
    @Transactional
    public void loadProducts() throws Exception {

        List products = (List) productDao.loadProducts();

        log.info("products:{}",products.size());
        log.info("{}",products.get(0));

        List tables = (List) productDao.getTables();

        log.info("tables:{}",tables.size());
        for(Object c : tables){
            log.info("{}",c);
        }

    }

    @Test
    @Transactional
    public void queryngTwoSoruces() throws Exception {

        log.info("getting data from two DS in one method");
        List tables = new ArrayList();

        tables.addAll(productDao.getTables());
        tables.addAll(customerDao.getTables());

        log.info("tables:{}",tables.size());
        for(Object c : tables){
            log.info("{}",c);
        }

    }

}

The three methods has now Transactional capabilities, the third method began only a one composite transaction and when the proccess finish it you can see in the log how is rolledbacked over the two data bases:

2018-04-10 18:23:41 INFO  c.a.i.imp.BaseTransactionManager - createCompositeTransaction ( 10000000 ): created new ROOT transaction with id 172.18.0.40.tm0000100005
2018-04-10 18:23:41 INFO  o.s.t.c.t.TransactionalTestExecutionListener - Began transaction (1) for test context [DefaultTestContext@f4c0275 testClass = CustomerDaoTest, testInstance = org.oz.persistence.dao.db1.CustomerDaoTest@4735572b, testMethod = queryngTwoSoruces@CustomerDaoTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@7eedec92 testClass = CustomerDaoTest, locations = '{}', classes = '{class org.oz.conf.FirstDB, class org.oz.conf.SecondDB, class org.oz.conf.MainConf}', contextInitializerClasses = '[]', activeProfiles = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]; transaction manager [org.springframework.transaction.jta.JtaTransactionManager@3646a658]; rollback [true]
2018-04-10 18:23:41 INFO  o.o.p.dao.db1.CustomerDaoTest - getting data from two DS in one method
2018-04-10 18:23:41 INFO  c.a.i.imp.CompositeTransactionImp - registerSynchronization ( com.atomikos.icatch.jta.Sync2Sync@49239780 ) for transaction 172.18.0.40.tm0000100005
2018-04-10 18:23:41 INFO  c.a.i.imp.CompositeTransactionImp - registerSynchronization ( com.atomikos.icatch.jta.Sync2Sync@55641ee0 ) for transaction 172.18.0.40.tm0000100005
...
2018-04-10 18:23:41 INFO  com.atomikos.icatch.jta.Sync2Sync - afterCompletion ( STATUS_ROLLEDBACK ) called  on Synchronization: org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization@672ae2bc
2018-04-10 18:23:41 INFO  com.atomikos.icatch.jta.Sync2Sync - afterCompletion ( STATUS_ROLLEDBACK ) called  on Synchronization: org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization@308bd98e
2018-04-10 18:23:41 INFO  c.a.i.imp.CompositeTransactionImp - rollback() done of transaction 172.18.0.40.tm0000100005
2018-04-10 18:23:41 INFO  o.s.t.c.t.TransactionalTestExecutionListener - Rolled back transaction after test execution for test context [DefaultTestContext@f4c0275 testClass = CustomerDaoTest, testInstance = org.oz.persistence.dao.db1.CustomerDaoTest@4735572b, testMethod = queryngTwoSoruces@CustomerDaoTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@7eedec92 testClass = CustomerDaoTest, locations = '{}', classes = '{class org.oz.conf.FirstDB, class org.oz.conf.SecondDB, class org.oz.conf.MainConf}', contextInitializerClasses = '[]', activeProfiles = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]
2018-04-10 18:23:41 INFO  o.s.c.s.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@207f5580: startup date [Tue Apr 10 18:23:38 CDT 2018]; root of context hierarchy
2018-04-10 18:23:41 INFO  o.s.o.j.LocalContainerEntityManagerFactoryBean - Closing JPA EntityManagerFactory for persistence unit 'unit2'
2018-04-10 18:23:41 INFO  o.s.o.j.LocalContainerEntityManagerFactoryBean - Closing JPA EntityManagerFactory for persistence unit 'unit1'

Referencias

Cheers =)

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s