Spring Data repositories with multiple databases

The Spring Data project keeps making it easier to do database access in Spring applications, and one of the neatest  improvements of recent times is that by defining an interface which extends JpaRepository and referencing a JPA entity, an implementation will automatically be injected with all the usual CRUD methods: findAll(), findOne(id), save(entity), delete(id), etc.

Recently I was working on a project, where I had taken full advantage of this, and for which I needed to add domain objects from an additional database. Unfortunately, as soon as I added references to entities in a different database I started experiencing troubles. For instance:

Not an managed type: class com.sctrcd.multidsdemo.domain.bar.Bar

… which was being caused by my repository being injected with the entityManager and transactionManager for the other database. Here I walk through how I resolved the problems and got things working.

After naming my beans to ensure that I would not be referencing the wrong one, I started seeing:

No bean named 'entityManagerFactory' is defined.

… because the repository implementation defaults to a by-name search for a bean called “entityManagerFactory”.

I was struggling to find any documentation of how to do it right, so to help me work through the steps of such a configuration, I created a minimal demo project at GitHub, containing two entities with those traditional names: Foo and Bar.

https://github.com/gratiartis/multids-demo

Here I shall explain the configuration that I ended up with in the hope that readers might understand that it can be done, and that it’s actually quite easy and requires very little code, if you know how!

First of all, we set up two JPA entities, Foo and Bar:

@Entity public class Foo { /* Constructors, fields and accessors/mutators */ }
@Entity public class Bar { /* Constructors, fields and accessors/mutators */ }

Associated with these we create two repositories: FooRepository and BarRepository. Thanks to the awesomeness of Spring Data, we can get ourselves some pretty full-featured repositories purely by defining interfaces which extend JpaRepository:

public interface FooRepository extends JpaRepository<Foo, Long> {}
public interface BarRepository extends JpaRepository<Bar, Long> {}

We need to ensure that each of these maps to a table in its own database. To achieve this, we will need two separate entity managers, each of which has a different datasource. However, in a Spring Java config @Configuration class, we can only have one @EnableJpaRepositories annotation and each such annotation can only reference one EntityManagerFactory. To achieve this, we create two separate @Configuration classes: FooConfig and BarConfig.

Each of these @Configuration classes defines a DataSource based on an embedded HSQL database. The following is the BarConfig. FooConfig is identical except for some different names and package paths.


@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "barEntityManagerFactory",
transactionManagerRef = "barTransactionManager",
basePackages = { "com.sctrcd.multidsdemo.integration.repositories.bar" })
public class BarConfig {
@Autowired
JpaVendorAdapter jpaVendorAdapter;
/**
* Primary because if we have activated embedded databases, we do not want
* the application to connect to an external database.
*/
@Bean(name = "barDataSource")
public DataSource dataSource() {
return new EmbeddedDatabaseBuilder()
.setName("bardb")
.setType(EmbeddedDatabaseType.HSQL)
.build();
}
@Bean(name = "barEntityManager")
public EntityManager entityManager() {
return entityManagerFactory().createEntityManager();
}
@Bean(name = "barEntityManagerFactory")
public EntityManagerFactory entityManagerFactory() {
LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
lef.setDataSource(dataSource());
lef.setJpaVendorAdapter(jpaVendorAdapter);
lef.setPackagesToScan("com.sctrcd.multidsdemo.domain.bar");
lef.setPersistenceUnitName("barPersistenceUnit");
lef.afterPropertiesSet();
return lef.getObject();
}
@Bean(name = "barTransactionManager")
public PlatformTransactionManager transactionManager() {
return new JpaTransactionManager(entityManagerFactory());
}
}

view raw

BarConfig.java

hosted with ❤ by GitHub

Each configuration should define a DataSource, EntityManager, EntityManagerFactory and PlatformTransactionManager. You need to make sure that @Entity beans for different data sources are in different packages. We then need to put the correct references in the @EnableJpaRepositories annotation for each @Configuration class.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "barEntityManagerFactory", 
        transactionManagerRef = "barTransactionManager",
        basePackages = {"com.sctrcd.multidsdemo.integration.repositories.bar"})
public class BarConfig {
    // ...
}

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "barEntityManagerFactory", 
        transactionManagerRef = "barTransactionManager",
        basePackages = { "com.sctrcd.multidsdemo.integration.repositories.bar" })
public class BarConfig {
    // ...
}

As you can see, each of these @EnableJpaRepositories annotations defines a specific named EntityManagerFactory and PlatformTransactionManager. They also specify which repositories should be wired up with those beans. In the example, I have put the repositories in database-specific packages. It is also possible to define each individual repository by name, by adding includeFilters to the annotation, but by segregating the repositories by database, I believe that things should end up more readable.

At this point you should have a working application using Spring Data repositories to manage entities in two separate databases. Feel free to grab the project from the link above and run the tests to see this happening. And please do let me know if you can spot any good opportunities for improvement.

Update

Since writing the post above, I have had the opportunity to implement a multiple datasource solution in a Spring Boot application. As a few people asked about it, here’s a follow-up post describing what needs to be done in a Spring Boot application.

https://scattercode.co.uk/2016/01/05/multiple-databases-with-spring-boot-and-spring-data-jpa/

43 thoughts on “Spring Data repositories with multiple databases”

  2. I got error for mvn clean install

    C:devmvnmultids-demo-master>mvn clean install
    [INFO] Scanning for projects…
    [INFO]
    [INFO] Using the builder org.apache.maven.lifecycle.internal.builder.singlethrea
    ded.SingleThreadedBuilder with a thread count of 1
    [INFO]
    [INFO] ————————————————————————
    [INFO] Building Spring-data multiple datasource demo 1.0.0-SNAPSHOT
    [INFO] ————————————————————————
    [INFO]
    [INFO] — maven-clean-plugin:2.5:clean (default-clean) @ multids-demo —
    [INFO] Deleting C:devmvnmultids-demo-mastertarget
    [INFO]
    [INFO] — maven-resources-plugin:2.6:resources (default-resources) @ multids-de
    mo —
    [INFO] Using ‘UTF-8’ encoding to copy filtered resources.
    [INFO] skip non existing resourceDirectory C:devmvnmultids-demo-mastersrcma
    inresources
    [INFO]
    [INFO] — maven-compiler-plugin:2.3.2:compile (default-compile) @ multids-demo
    —
    [INFO] Compiling 7 source files to C:devmvnmultids-demo-mastertargetclasses

    [INFO] ————————————————————-
    [ERROR] COMPILATION ERROR :
    [INFO] ————————————————————-
    [ERROR] Failure executing javac, but could not parse the error:
    javac: invalid target release: 1.7
    Usage: javac
    use -help for a list of possible options

    [INFO] 1 error
    [INFO] ————————————————————-
    [INFO] ————————————————————————
    [INFO] BUILD FAILURE
    [INFO] ————————————————————————
    [INFO] Total time: 1.791 s
    [INFO] Finished at: 2014-04-23T21:51:43-05:00
    [INFO] Final Memory: 5M/120M
    [INFO] ————————————————————————
    [ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:2.
    3.2:compile (default-compile) on project multids-demo: Compilation failure
    [ERROR] Failure executing javac, but could not parse the error:
    [ERROR] javac: invalid target release: 1.7
    [ERROR] Usage: javac
    [ERROR] use -help for a list of possible options
    [ERROR] -> [Help 1]
    [ERROR]
    [ERROR] To see the full stack trace of the errors, re-run Maven with the -e swit
    ch.
    [ERROR] Re-run Maven using the -X switch to enable full debug logging.
    [ERROR]
    [ERROR] For more information about the errors and possible solutions, please rea
    d the following articles:
    [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureExc
    eption
    C:devmvnmultids-demo-master>

    Like

    Reply

  3. The error message …

    javac: invalid target release: 1.7

    … means that you don’t have the Java 7 JDK installed. If you don’t want to install it, change the line in the pom.xml to set the compile target to be 1.6 instead of 1.7. I don’t think that there’s anything in the project which requires 1.7, so there’s a decent chance that will work.

    Like

    Reply

    1. Thank you Stephen, That works, My next issue I have is I am using Oracle 11g database and how do I use the DataSource databasebuilder for non embedded databases. Appreciate any help.
      Thank again.

      Like

      Reply

      1. You need to define an appropriate JpaVendorAdaptor:

        @Bean
        public JpaVendorAdapter jpaVendorAdapter() {
        HibernateJpaVendorAdapter adaptor = new HibernateJpaVendorAdapter();
        adaptor.setShowSql(false);
        adaptor.setGenerateDdl(false);
        adaptor.setDatabase(Database.ORACLE);
        return adaptor;
        }

        And then you can define an appropriate DataSource. The following is a basic example, which assumes that the DB connection details have been loaded as environment properties:

        @Bean(name = "barDataSource")
        public DataSource barDataSource(Environment env) {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(env.getProperty("bar.db.driverClassName"));
        ds.setUrl(env.getProperty("bar.db.url"));
        ds.setUsername(env.getProperty("bar.db.username"));
        ds.setPassword(env.getProperty("bar.db.password"));
        return ds;
        }

        Liked by 1 person

  4. Hi,

    When I tried this example with generateDdl as true, it created both the tables in both databases. I thought that it will create required table in given database only. Any solutions for the same.

    Shikhar

    Like

    Reply

  5. Please help me to configure multiple datasources(SQL Server and DB2)

    I need sample code for creating muliple datasources in spring 3.2

    Thanks in advance.
    Varaprasad

    Like

    Reply

  7. Great Stuff Stephen!

    I wonder (and will be researching) how can we integrate this (multiple datasources) by using the “application.properties” in order to have a more flexible definition instead of having many of these configurations in the middle of the Java code…

    Any thoughts on that?

    Cheers!

    Like

    Reply

    1. I like the Spring Boot way of doing things based on a simple convention. It lets us assume certain defaults, so we don’t need to define everything explicitly. In turn this lets us define a minimum of configuration in a properties file. However, as soon as anyone starts connecting to multiple databases, I suspect that it may be difficult to keep those conventions simple. For instance, they will need to explicitly define which classes are the repository classes for each database and which are the domain entities. My only tip would be to try using the YAML config rather than .properties files. It ends up as the same thing, but it should make it a bit easier to read a hierarchical structure, defining these things.

      Like

      Reply

      1. I am looking for tutorial for Spring Boot + Spring Jpa for multiple databases. There is no need of @EnableJpaRepositories with Spring Boot, although I can have it for more control.

        Do you know what is best way of doing it ? Great blog post by the way.

        Thanks,
        Sudhir

        Like

      2. I haven’t experimented yet, to see whether there’s a simple way of doing it. In theory, you should be able to do it as above, although it may involve excluding the repositories from component scanning, so that you can configure them explicitly. There have been a few related questions on StackOverflow, so you might find something there. I suspect I may revisit this post and produce a new version for Spring Boot.

        Like

  9. Wow. This helped a lot. Do you know what can be the best way if I need to connect to replica of same database from my application. ex. I need to connect to 5 databases which are replica’s of each other and I want to use same entities / repositories for CRUD operations. is there a neat way or Do i need to declare different base packages for each database? I guess that will create lot of code duplication? do you have any suggestions?

    Like

    Reply

    1. I may be wrong, but I don’t know of any neat way within the same application. At least not with JPA, as each entity is assigned an entity manager for a single database. It may be feasible to extend the entity classes and create multiple repo interfaces pointing at those subclasses. The approach I am taking for a project at the moment is to create multiple executables (Spring Boot), with different database config against each. So I use the same codebase, but I need to run more processes.

      Like

      Reply

      1. Thanks Stephen! Yes, it looks like i need to adopt some custom approach as it’s not common use case.

        Like

  10. Its telling Found 2 beans for TransactionManager, one is the default transactionManager bean and the other custom one.

    Like

    Reply

    1. Have Resolved it by adding @Primary Annotation, but after doing so the following exception is thrown

      org.springframework.transaction.IllegalTransactionStateException: Pre-bound JDBC Connection found! JpaTransactionManager does not support running within DataSourceTransactionManager if told to manage the DataSource itself. It is recommended to use a single JpaTransactionManager for all transactions on a single DataSource, no matter whether JPA or JDBC access.
      at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:359)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:457)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
      at com.sun.proxy.$Proxy104.delete(Unknown Source)
      at com.tracebucket.x1.organization.api.test.repository.OrganizationRepositoryTest.tearDown(OrganizationRepositoryTest.java:68)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
      at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
      at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
      at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:33)
      at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
      at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
      at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:217)
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
      at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
      at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
      at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
      at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
      at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
      at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
      at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
      at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
      at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
      at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)

      Like

      Reply

  11. Hi

    My application doesnt use persistence.xml. So I tried using LocalContainerEntityManagerFactoryBean to create a custom EntityManager. But I get error as DataSource must not be null. Can anyone help on this issue?

    PersistenceJPAConfig.java

    @Configuration
    @EnableTransactionManagement
    @EnableJpaRepositories
    public class PersistenceJPAConfig {

    @Autowired
    private DataSource dataSource;

    @Autowired
    JpaVendorAdapter jpaVendorAdapter;

    @Bean(name = “personEntityManager”)
    public EntityManager entityManager() {
    return entityManagerFactory().createEntityManager();
    }

    @Bean(name = “personEntityManagerFactory”)
    public EntityManagerFactory entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
    lef.setDataSource(dataSource);
    lef.setJpaVendorAdapter(jpaVendorAdapter);
    lef.setPackagesToScan(“com.myc.cc.domain.Person”);
    lef.setPersistenceUnitName(“personPersistenceUnit”);
    lef.afterPropertiesSet();
    return lef.getObject();
    }

    @Primary
    @Bean(name = “personTransactionManager”)
    public PlatformTransactionManager transactionManager() {
    return new JpaTransactionManager(entityManagerFactory());
    }
    }

    SearchPersonPredicates.java

    public static Predicate findIdByEM(final Long pId) {
    PersistenceJPAConfig jpaConfig = new PersistenceJPAConfig();
    EntityManager manager= jpaConfig.entityManager();
    QPerson qPerson = QPerson.person;
    QAddress qAddress = QAddress.address;
    BooleanBuilder booleanBuilder = new BooleanBuilder();
    JPAQuery jpaQuery = new JPAQuery(manager);
    JPASubQuery subQuery = new JPASubQuery();
    Predicate predicate;
    if (pId != null) {
    booleanBuilder.or(QAddress.person_no.eq(pId));
    }
    predicate = booleanBuilder.getValue();
    jpaQuery = jpaQuery.from(qAddress).join(qPerson).on(predicate);
    subQuery.from(qPerson, qAddress);
    return predicate;
    }

    SearchPersonServiceImpl.java

    public List findPnumberbyEM(Long id) {
    Iterable person = personRepository.findAll(findIdByEM(id));
    return constructList(person);
    }

    private List constructList(Iterable persons) {
    List list = new ArrayList();
    for (Person person : persons) {
    list.add(person);
    }
    }

    Error

    17:52:35.337 ERROR 7016 — [nio-8080-exec-2] o.a.c.c.C.[.[.[.[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [/cc_dev] threw exception [Request processing failed; nested exception is java.lang.IllegalArgumentException: DataSource must not be null] with root cause

    java.lang.IllegalArgumentException: DataSource must not be null
    at org.springframework.util.Assert.notNull(Assert.java:112)
    at org.springframework.jdbc.datasource.lookup.SingleDataSourceLookup.(SingleDataSourceLookup.java:40)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.setDataSource(LocalContainerEntityManagerFactoryBean.java:238)
    at com.myc.cc.repository.PersistenceJPAConfig.entityManagerFactory(PersistenceJPAConfig.java:43)
    at com.myc.cc.repository.PersistenceJPAConfig.entityManager(PersistenceJPAConfig.java:37)
    at com.myc.cc.repository.SearchPersonPredicates.findIdByEM(SearchPersonPredicates.java:121)
    at com.myc.cc.service.impl.SearchPersonServiceImpl.findPnumberbyEM(SearchPersonServiceImpl.java:70)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)
    at com.sun.proxy.$Proxy114.findPnumberbyEM(Unknown Source)
    at com.myc.cc.web.SearchPersonController.searchPerson(SearchPersonController.java:109)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)

    Like

    Reply
  12. Pingback: Multiple databases with Spring Boot and Spring Data JPA | Scattercode
  13. Pingback: Spring + multiple H2 instances in memory - HTML CODE

  17. Hi Sir, i’m a student trying to understand this Java world :). here is my interview question, is one entity manager sufficient to perform CRUD operations on Different Databases like SQL, Oracle, DB2?. after searching for this question i thought this is the right place to ask. Can anyone help with explanation?. Thanks in advance

    Like

    Reply

    1. Hi Tillu. As is shown in the example here, you need a separate entity manager for each database you need to connect to simultaneously. Whether that is SQL Server, MySQL or Oracle, doesn’t matter.

      Like

      Reply

      1. Thank you…sir, BTW is there any way to do this?. I heard like using JTA something we can do it with single entity manager. is it possible?

        Like

  18. Hi ,
    Any one can help me on this (Two mysql DataBases connection using Spring boot JPA using Spring liquibase and HikariDataSource ). below is using file DatabaseConfiguration.java and Property file application-test.yml

    @Configuration
    @EnableJpaRepositories(“com.xxx.test.repository”)
    @EnableJpaAuditing(auditorAwareRef = “springSecurityAuditorAware”)
    @EnableTransactionManagement
    public class DatabaseConfiguration {

    private final Logger log = LoggerFactory.getLogger(DatabaseConfiguration.class);

    @Inject
    private Environment env;

    @Autowired(required = false)
    private MetricRegistry metricRegistry;

    @Bean(destroyMethod = “close”)
    @ConditionalOnExpression(“#{!environment.acceptsProfiles(‘” + Constants.SPRING_PROFILE_CLOUD + “‘) && !environment.acceptsProfiles(‘” + Constants.SPRING_PROFILE_HEROKU + “‘)}”)
    @ConfigurationProperties(prefix= “spring.datasource.hikari”)
    public DataSource dataSource(DataSourceProperties dataSourceProperties, CacheManager cacheManager) {
    log.debug(“Configuring Datasource —–“);
    if (dataSourceProperties.getUrl() == null) {
    log.error(“Your database connection pool configuration is incorrect! The application” +
    ” cannot start. Please check your Spring profile, current profiles are: {}”,
    Arrays.toString(env.getActiveProfiles()));

    throw new ApplicationContextException(“Database connection pool is not configured correctly”);
    }
    HikariDataSource hikariDataSource = (HikariDataSource) DataSourceBuilder
    .create(dataSourceProperties.getClassLoader())
    .type(HikariDataSource.class)
    .driverClassName(dataSourceProperties.getDriverClassName())
    .url(dataSourceProperties.getUrl())
    .username(dataSourceProperties.getUsername())
    .password(dataSourceProperties.getPassword())
    .build();

    if (metricRegistry != null) {
    hikariDataSource.setMetricRegistry(metricRegistry);
    }
    return hikariDataSource;
    }

    @Bean
    public SpringLiquibase liquibase(DataSource dataSource, DataSourceProperties dataSourceProperties,
    LiquibaseProperties liquibaseProperties) {

    // Use liquibase.integration.spring.SpringLiquibase if you don’t want Liquibase to start asynchronously
    SpringLiquibase liquibase = new AsyncSpringLiquibase();
    liquibase.setDataSource(dataSource);
    liquibase.setChangeLog(“classpath:config/liquibase/master.xml”);
    liquibase.setContexts(liquibaseProperties.getContexts());
    liquibase.setDefaultSchema(liquibaseProperties.getDefaultSchema());
    liquibase.setDropFirst(liquibaseProperties.isDropFirst());
    if (env.acceptsProfiles(Constants.SPRING_PROFILE_NO_LIQUIBASE)) {
    liquibase.setShouldRun(false);
    } else {
    liquibase.setShouldRun(liquibaseProperties.isEnabled());
    log.debug(“Configuring Liquibase”);
    }

    return liquibase;
    }

    @Bean
    public Hibernate4Module hibernate4Module() {
    return new Hibernate4Module();
    }

    Property file Like :-application-test.yml

    spring:
    devtools:
    restart:
    enabled: true
    livereload:
    enabled: false # we use gulp + BrowserSync for livereload
    jackson:
    serialization.indent_output: true
    datasource:
    url: jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&useSSL=false
    name:
    username: root
    password: root
    hikari:
    data-source-properties:
    cachePrepStmts: true
    prepStmtCacheSize: 250
    prepStmtCacheSqlLimit: 2048
    useServerPrepStmts: true

    demosource:
    url: jdbc:mysql://localhost:3306/testdemo?useUnicode=true&characterEncoding=utf8&useSSL=false
    name:
    username: root
    password: root
    hikari:
    data-source-properties:
    cachePrepStmts: true
    prepStmtCacheSize: 250
    prepStmtCacheSqlLimit: 2048
    useServerPrepStmts: true

    Like

    Reply

  20. Hi , i have tried the same example, the first entity manager works fine but the second one for the second datasource can select but does not insert, do you have any idea ? Thank you

    Like

    Reply

    1. There should be no difference between the two connections from the application perspective, so I would guess that perhaps that account in use for the second datasource does not have permission to insert.

      Like

      Reply

      1. I had to annotate my service class with @Transactional(value = “secondaryTransactionManager”) and it work like a charm

        Like

      2. It’s an interesting point. My tutorial stopped at getting the repositories to work. Maybe it would be worth me extending it with an example of a service that uses both?

        Like

  21. Hi sir, I want use multiple database but one database at a time. i have to select datasource per request and configure with entity manager. Previously i used DataSourceRouter to select datasource with jdbcTemplate but now i am stuck with jpaRepository. can you help me how to select datasource dynamicaly if it possible.

    Like

    Reply
  22. Pingback: Running Spring batch on multiple databases in parallel | inneka.com

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.