A little while back I knocked up a post describing how to enable a Spring application to connect to multiple data sources. At the time, I had only just heard about Spring Boot at the SpringOne 2GX conference in Santa Clara, so the examples didn’t take advantage of that and also didn’t work around some of the autowiring that it does.
Recently, I was working on a little ETL project to migrate data from one database to another with a different structure, so I returned to this problem and the following is the result.
First, if you want to get hold of a working (including some simple tests) example project, here it is:
https://github.com/gratiartis/multids-demo/tree/now-with-spring-boot
As previously, when you define an entity manager, you can define where it should scan for entities and repository classes. The classes can be named individually, but it is easiest if you put your domain entities and repository classes into their own packages and point the entity manager factory at the package. In this example, I used:
com.sctrcd.multids.foo.domaincom.sctrcd.multids.foo.repocom.sctrcd.multids.bar.domaincom.sctrcd.multids.bar.repo
I suspect that it’s certainly possible to get around it, but I found that due to Spring Boot trying to inject beans based on default names, it was easiest to set up one of the data sources to use the defaults and the other to use bean names that I defined. As you can see in the application.yml below:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| spring: | |
| datasource: | |
| url: jdbc:mysql://localhost/foo_schema | |
| username: root | |
| password: d4t4b4s3sForLif3 | |
| driverClassName: com.mysql.jdbc.Driver | |
| test-on-borrow: true | |
| test-while-idle: true | |
| validation-query: select 1; | |
| maxActive: 1 | |
| jpa: | |
| show-sql: false | |
| generate-ddl: false | |
| properties: | |
| hibernate: | |
| dialect: org.hibernate.dialect.MySQL5InnoDBDialect | |
| ddl-auto: validate | |
| hbm2ddl: | |
| import_files: | |
| bar: | |
| datasource: | |
| url: jdbc:mysql://localhost/bar_schema | |
| username: root | |
| password: d4t4b4s3sForLif3 | |
| driverClassName: com.mysql.jdbc.Driver | |
| test-on-borrow: true | |
| test-while-idle: true | |
| validation-query: select 1; | |
| maxActive: 1 |
… the spring.datasource.url, spring.datasource.username and spring.datasource.password properties are all defined for the ‘default’ datasource. I define some additional non-conventional properties for the additional schema. We will see how those are picked up shortly.
Beyond the application.yml configuration, all we need to do is define @Configuration beans which will pick up the properties. First, a @Configuration to wire up the ‘default’ data source. This defines each bean as @Primary, to ensure that they are the beans picked up by anything which does not specify a @Qualifier:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package com.sctrcd.multidsdemo; | |
| import javax.persistence.EntityManagerFactory; | |
| import javax.sql.DataSource; | |
| import org.springframework.beans.factory.annotation.Qualifier; | |
| import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder; | |
| import org.springframework.boot.context.properties.ConfigurationProperties; | |
| import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; | |
| import org.springframework.context.annotation.Bean; | |
| import org.springframework.context.annotation.Configuration; | |
| import org.springframework.context.annotation.Primary; | |
| import org.springframework.data.jpa.repository.config.EnableJpaRepositories; | |
| import org.springframework.orm.jpa.JpaTransactionManager; | |
| import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; | |
| import org.springframework.transaction.PlatformTransactionManager; | |
| import org.springframework.transaction.annotation.EnableTransactionManagement; | |
| @Configuration | |
| @EnableTransactionManagement | |
| @EnableJpaRepositories( | |
| entityManagerFactoryRef = "entityManagerFactory", | |
| basePackages = { "com.sctrcd.multidsdemo.foo.repo" }) | |
| public class FooConfig { | |
| @Primary | |
| @Bean(name = "dataSource") | |
| @ConfigurationProperties(prefix="spring.datasource") | |
| public DataSource dataSource() { | |
| return DataSourceBuilder.create().build(); | |
| } | |
| @Primary | |
| @Bean(name = "entityManagerFactory") | |
| public LocalContainerEntityManagerFactoryBean entityManagerFactory( | |
| EntityManagerFactoryBuilder builder, | |
| @Qualifier("dataSource") DataSource dataSource) { | |
| return builder | |
| .dataSource(dataSource) | |
| .packages("com.sctrcd.multidsdemo.foo.domain") | |
| .persistenceUnit("foo") | |
| .build(); | |
| } | |
| @Primary | |
| @Bean(name = "transactionManager") | |
| public PlatformTransactionManager transactionManager( | |
| @Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) { | |
| return new JpaTransactionManager(entityManagerFactory); | |
| } | |
| } |
Second a @Configuration to wire up the additional datasource. It is essentially identical to the ‘default’ configuration, except that it defines non-conventional names for the data source, entity manager factory and transaction manager and scans different packages for the entities and repositories. It also defines the named transaction manager in the @EnableJpaRepositories annotation and does not define the beans as @Primary.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package com.sctrcd.multidsdemo; | |
| import javax.persistence.EntityManagerFactory; | |
| import javax.sql.DataSource; | |
| import org.springframework.beans.factory.annotation.Qualifier; | |
| import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder; | |
| import org.springframework.boot.context.properties.ConfigurationProperties; | |
| import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; | |
| import org.springframework.context.annotation.Bean; | |
| import org.springframework.context.annotation.Configuration; | |
| import org.springframework.data.jpa.repository.config.EnableJpaRepositories; | |
| import org.springframework.orm.jpa.JpaTransactionManager; | |
| import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; | |
| import org.springframework.transaction.PlatformTransactionManager; | |
| import org.springframework.transaction.annotation.EnableTransactionManagement; | |
| @Configuration | |
| @EnableTransactionManagement | |
| @EnableJpaRepositories( | |
| entityManagerFactoryRef = "barEntityManagerFactory", | |
| transactionManagerRef = "barTransactionManager", | |
| basePackages = { "com.sctrcd.multidsdemo.bar.repo" }) | |
| public class BarConfig { | |
| @Bean(name = "barDataSource") | |
| @ConfigurationProperties(prefix="bar.datasource") | |
| public DataSource barDataSource() { | |
| return DataSourceBuilder.create().build(); | |
| } | |
| @Bean(name = "barEntityManagerFactory") | |
| public LocalContainerEntityManagerFactoryBean barEntityManagerFactory( | |
| EntityManagerFactoryBuilder builder, | |
| @Qualifier("barDataSource") DataSource barDataSource) { | |
| return builder | |
| .dataSource(barDataSource) | |
| .packages("com.sctrcd.multidsdemo.bar.domain") | |
| .persistenceUnit("bar") | |
| .build(); | |
| } | |
| @Bean(name = "barTransactionManager") | |
| public PlatformTransactionManager barTransactionManager( | |
| @Qualifier("barEntityManagerFactory") EntityManagerFactory barEntityManagerFactory) { | |
| return new JpaTransactionManager(barEntityManagerFactory); | |
| } | |
| } |
Beyond those configuration classes, everything is just the standard setup for a Spring Boot / Spring Data JPA application, so if you have an application connecting to a single database already, there isn’t a lot of modification to support connecting to additional databases.
Scattercode 
Hi, I’m have a similar problem. Over stackoverflow i found your blog post. One thing, you write that in the second config should not define as primary, but you do it in the example.
I implemented your solution but it didn’t work, my JpaRepo (Interface) has the wrong entityManager (from the base, but i need the second). Have you andy idea how could it be resolved? If you like i can add the link to stackoverflow.
Thank’s in advance.
LikeLike
Thanks for pointing that out. When I created the Gist, I copied the default config instead of basing it on my working code. Doh! I had removed the @Primary annotation from the datasource, but not the other beans. It’s fixed now. 🙂
If you have the code available somewhere public I’d be happy to take a look. There may be something I should add to the blog post, which I missed.
LikeLike
Thanks for your response. This is the link: http://stackoverflow.com/q/34611428/1809221
LikeLike
I’ll probably re-tweak the code embedded in the blog post, but for a full working example, take a look here:
https://github.com/gratiartis/multids-demo/tree/now-with-spring-boot
LikeLike
Great example!… but at this moment I need connect as your previus post https://scattercode.co.uk/2013/11/18/spring-data-multiple-databases/
LikeLike
Good to hear it was useful. Hopefully you can switch to Spring Boot before too long. It makes life so much easier!
LikeLike
Hi, very interesting post! Exactly what I’m looking for. I have referred your working example here https://github.com/gratiartis/multids-demo/tree/now-with-spring-boot and tried the same. but I’m facing the below exception always! Do you have any clue on this?
2016-02-11 16:50:02.773 INFO 5156 — [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [
name: external
…]
2016-02-11 16:50:02.907 INFO 5156 — [ main] org.hibernate.Version : HHH000412: Hibernate Core {4.3.11.Final}
2016-02-11 16:50:02.911 INFO 5156 — [ main] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found
2016-02-11 16:50:02.914 INFO 5156 — [ main] org.hibernate.cfg.Environment : HHH000021: Bytecode provider name : javassist
2016-02-11 16:50:03.257 INFO 5156 — [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
2016-02-11 16:50:03.464 WARN 5156 — [ main] o.a.tomcat.jdbc.pool.PooledConnection : Not loading a JDBC driver as driverClassName property is null.
2016-02-11 16:50:03.498 ERROR 5156 — [ main] o.a.tomcat.jdbc.pool.ConnectionPool : Unable to create initial connections of pool.
java.sql.SQLException: The url cannot be null
at java.sql.DriverManager.getConnection(DriverManager.java:649) ~[na:1.8.0_60]
at java.sql.DriverManager.getConnection(DriverManager.java:208) ~[na:1.8.0_60]
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:305) ~[tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:200) ~[tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:708) [tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:642) [tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:464) [tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.(ConnectionPool.java:141) [tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115) [tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102) [tomcat-jdbc-8.0.30.jar:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:126) [tomcat-jdbc-8.0.30.jar:na]
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:139) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcServicesImpl.java:279) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:124) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:857) [hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:850) [hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.withTccl(ClassLoaderServiceImpl.java:425) [hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:849) [hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) [spring-orm-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFa
ctoryBean.java:343) [spring-orm-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:318) [spring-orm-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1637) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1054) [spring-context-4.2.4.RELEASE.jar:4.2.4.RELEASE]
LikeLike
How are you running it? It looks like the application.yml in the config directory might not have been loaded, as that provides things like the URL for the database.
Note that there is a SQL script in the ‘db’ directory, which creates the two schemas that the application will try to connect to. Assuming that you are testing with MySQL, then that script should work, and a few tweaks to the application.yml (change URL, passwords, etc for your local config) should let you connect to it.
LikeLike
Thanks this was helpful. I setup an application to run 4 datasources at startup. This created 10 connections to each Database.
LikeLiked by 1 person
Nice work. Hopefully it was easy enough! btw – In case you hadn’t seen them, you can set properties such as spring.datasource.min-idle, spring.datasource.max-idle and spring.datasource.max-active to control how many connections there are in each pool.
LikeLike
Thanks, I need this to setup 4 database connections = 4 datasources. There are pieces here I need to incorporate to make it work. Appreciate the details!
LikeLike
Seems to be exactly what I need. But where does EntityManagerFactoryBuilder come from? When I try to launch the code, I get the exception:
“Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {}”
Should I define the bean myself of somehow ask Spring to do it for me?
LikeLike
Spring should be injecting that for you, but that message indicates that the application isn’t being wired up correctly. Maybe your database config has an error, which means that Spring is unable to create the beans relating to it?
Also, are you running as a Spring Boot application? If not, then as xtreye mentions below, you may need to do some of the additional configuration described in my previous post: https://scattercode.co.uk/2013/11/18/spring-data-multiple-databases/
LikeLike
Seem to be exactly what I need, but could you please tell where does EntityManagerFactoryBuilder come from?
I am asking because I getting message “No qualifying bean of type [org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder] found for dependency” when try to use this solution. I do not use @SpringBootApplication because this not the app but one of the modules.
LikeLike
Find your previous post, it explains my situation, nevermind 🙂
LikeLike
Good news 🙂
LikeLike
as i can see there is only one jpa. can you help me how to import 2 or 3 JPAs (one for oracle and another for mysql)?
LikeLike
Above, both datasources defined in application.yml are using the MySQL driver. Just put the Oracle drivers on the classpath and change one of the datasources to use them. You shouldn’t need to change anything anywhere else.
LikeLike
Hi,
I did the same configuration as you did. But getting the following exception.
Could you please provide your inputs.
Caused by: org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: barEntityManagerFactory,entityManagerFactory
at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.findDefaultEntityManagerFactory(PersistenceAnnotationBeanPostProcessor.java:572)
at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.findEntityManagerFactory(PersistenceAnnotationBeanPostProcessor.java:531)
at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$PersistenceElement.resolveEntityManager(PersistenceAnnotationBeanPostProcessor.java:697)
at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$PersistenceElement.getResourceToInject(PersistenceAnnotationBeanPostProcessor.java:670)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:169)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.postProcessPropertyValues(PersistenceAnnotationBeanPostProcessor.java:354)
LikeLike
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘genericDaoImpl’: Injection of persistence dependencies failed; nested exception is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: barEntityManagerFactory,entityManagerFactory
at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.postProcessPropertyValues(PersistenceAnnotationBeanPostProcessor.java:357)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1210)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:755)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:757)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:480)
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:118)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:686)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:320)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:957)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:946)
LikeLike
Does this work if foo and bar are under the same package?
LikeLike
Unfortunately not. This works by using Spring’s
@EnableJpaRepositoriesannotation to define the package which should be scanned to find repositories for each data source and inject entity and transaction managers. So anything in the same package will use the same data source.LikeLike
We have slightly different issue that it works in eclipse tomcat but not as external tomcat container. Can you please help. Have added more details in the below stackoverflow
http://stackoverflow.com/questions/38184517/springboot-multiple-datasource-fails-only-in-external-tomcat-container
LikeLike
Does it work okay if you run it as a jar with embedded Tomcat?
LikeLike
Hi, I’m implementing your example and I’m able to start my spring boot application without errors but at runtime I have this error:
ERROR: Bean named ‘entityManagerFactory’ must be of type [org.springframework.transaction.PlatformTransactionManager], but was actually of type [com.sun.proxy.$Proxy81]
It seems that I applied all your indications, what’s wrong?
Thanks
LikeLike
It’s hard to say. That error usually means that for some reason Spring is unable to use CGLIB. Going by a quick look at StackOverflow, sometimes also, a third party tool, such as Cobertura could be manipulating your bytecode to replace references to concrete classes with proxies.
LikeLike
FYI, to use non-default values for the naming strategy etc I had to do the following (the property values don’t need to be hard-coded, obviously). There may be a better way to do this….
@Bean(name = “barEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier(“barDataSource”) DataSource barDataSource) {
HashMap properties = new HashMap();
properties.put(“hibernate.hbm2ddl.auto”, “update”);
properties.put(“hibernate.dialect”, “org.hibernate.dialect.PostgreSQL9Dialect”);
properties.put(“hibernate.ejb.naming_strategy”, “org.hibernate.cfg.EJB3NamingStrategy”);
return builder
.dataSource(barDataSource)
.packages(“com.sctrcd.multidsdemo.bar.domain”)
.persistenceUnit(“bar”)
.properties(properties)
.build();
}
LikeLike
Thanks for the tip.
LikeLike
Thanks for the great sample. It seems like it breaks chacheManager autoconfig. Any idea how to fix this?
pringframework.beans.factory.NoSuchBeanDefinitionException: No bean named ‘cacheManager’ is defined
at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanDefinition(DefaultListableBeanFactory.java:702)
at org.springframework.beans.factory.support.AbstractBeanFactory.getMergedLocalBeanDefinition(AbstractBeanFactory.java:1180)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:284)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:296)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1076)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:851)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:541)
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:369)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:313)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1185)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1174)
at com.wiser.WpmApplication.main(WpmApplication.java:20)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
LikeLike
Greate example!!
Thank you to share your example with us!
Good bye.
LikeLike
Hey , Why does spring boot throw this expection –> “Caused by: java.lang.IllegalArgumentException: Repository interface must not be null on initialization!
“
LikeLike
That’s not enough information to say. You may be better asking on Stack Overflow, but make sure you provide details of what you’re doing.
LikeLike
Hi sctrcdr,
I have implemented Multi data-source using spring boot. I have a requirement to 20 data sources. Is there any way i can implement dynamically with out include all the data sources in below config class?
Please advice, much appreciated. Thanks
DataSourceConfig.java
@Bean(name = “ds-aus”)
@Primary
@ConfigurationProperties(prefix=”spring.datasource”)
public DataSource dataSourceAUS() {
return DataSourceBuilder.create().build();
}
@Bean(name = “ds-ind”)
@ConfigurationProperties(prefix=”spring.datasource.cl”)
public DataSource dataSourceIND() {
return DataSourceBuilder.create().build();
}
• Reply•Share ›
LikeLike
Hi Narendra,
It’s certainly possible, as you can add beans to the Spring context programmatically, as shown here:
https://comsysto.com/blog-post/how-to-create-your-own-dynamic-bean-definitions-in-spring
However, you may find that you end up with more complex code, in order to implement this.
LikeLike
hi narendra,
Could you please share me examples :
Based on user request the different mysql DataBases connection using Spring boot JPA using Spring liquibase and HikariDataSource (Two mysql DataBases)
LikeLike
Great article. This addresses exactly what I’m having trouble with right now, however I’m still getting “Not a managed type” exception for one of my entities. Interestingly enough, the entity that gets this exception is the one that does not have the LocalContainerEntityManagerFactoryBean named “entityManagerFactory” (I’ve named the other config’s bean “entityManagerFactory” and the other entity became the unmanaged type). All the properties in both config’s @EnableJpaProperties are set correctly. I’ve also tried to specify bean names as you outlined in your other article, in which case I receive the “No bean named ‘entityManagerFactory’ is defined” error.
Other info: I’m using spring boot. One datasource is MySQL and the other is Vertica. If I isolate either datasource in my project everything works as expected.
Have you ever run in to this or have any idea why this might be happening? Thanks in advance.
LikeLike
Do you have your repositories and entities in separate packages, so that you can ensure that classpath scanning doesn’t pick up the wrong ones?
And the correct path to the domain entities in the LocalContainerEntityManagerFactoryBean definitions?
LikeLike
Thanks for your very prompt response. I figured out the issue – I had too many annotations in my main method. Specifically, I had @SpringBootApplication while also having @ComponentScan and @EntityScan… I removed everything but @SpringBootApplication and everything finally worked!
LikeLike
Ah yes – the @SpringBootApplication annotation does all sorts for you! Great news that you sorted it.
LikeLike
I’m facing same issue for now. Need to use mongo db as well as vertica db in same application. Not sure how should i configure. If you/anyone have found a way, can you/anyone please help?
LikeLike
Great article. This addresses exactly what I’m having trouble with right now, however I’m still getting “Not a managed type” exception for one of my entities. Interestingly enough, the entity that gets this exception is the one that does not have the LocalContainerEntityManagerFactoryBean named “entityManagerFactory” (I’ve named the other config’s bean “entityManagerFactory” and the other entity became the unmanaged type). All the properties in both config’s @EnableJpaProperties are set correctly. I’ve also tried to specify bean names as you outlined in your other article, in which case I receive the “No bean named ‘entityManagerFactory’ is defined” error.
Other info: I’m using spring boot. One datasource is MySQL and the other is Vertica. If I isolate either datasource in my project everything works as expected.
Have you ever run in to this or have any idea why this might be happening? Thanks in advance.
LikeLike
We have struggled for days to get two data sources to work in a Spring Boot application. This approach seems like the most sane, but we still cannot make it work. As far as I can see we have followed the pattern exactly, but the application fails to create the second repository with this error:
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘lookupRestController’ defined in file [/home/nils/workspace/RAÄ/platsr-sb/platsrapi/build/classes/main/se/raa/platsr/controller/LookupRestController.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘lookupService’ defined in file [/home/nils/workspace/RAÄ/platsr-sb/platsrapi/build/classes/main/se/raa/platsr/service/LookupService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘LMPlaceRepository’: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Not a managed type: class se.raa.platsr.model.lookup.LMPlace
I thought the .packages(…) call here should take care of that:
@Bean(name = “lookupEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean lookupEntityManager(final EntityManagerFactoryBuilder builder,
@Qualifier(“lookupDataSource”) final DataSource dataSource) {
return builder.dataSource(dataSource).packages(“se.raa.platsr.model.lookup”).persistenceUnit(“lookup”).build();
}
So, what is it that I don’t understand/have missed out?
LikeLike
We have spent two days getting two data sources to work in a Spring Boot application. This approach is the sanest we have found, but we still cannot get it to work. We get this for an Entity class to be managed by the secondary data source/entity manager:
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘lookupRestController’ defined in file [/home/nils/workspace/RAÄ/platsr-sb/platsrapi/build/classes/main/se/raa/platsr/controller/LookupRestController.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘lookupService’ defined in file [/home/nils/workspace/RAÄ/platsr-sb/platsrapi/build/classes/main/se/raa/platsr/service/LookupService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘LMPlaceRepository’: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Not a managed type: class se.raa.platsr.model.lookup.LMPlace
I thought this would be adressed by the call to .packages(…) in the EntityManagerFactory builder:
@Bean(name = “lookupEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean entityManager(final EntityManagerFactoryBuilder builder,
@Qualifier(“lookupDataSource”) DataSource dataSource) {
return builder.dataSource(dataSource).packages(LMPlace.class).persistenceUnit(“lookup”).build();
}
I have tried with a package name as well
@Bean(name = “lookupEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean entityManager(final EntityManagerFactoryBuilder builder,
@Qualifier(“lookupDataSource”) DataSource dataSource) {
return builder.dataSource(dataSource).packages(“se.raa.platsr.model.lookup”).persistenceUnit(“lookup”).build();
}
with no success. So, what can it be that I don’t understand, or have configured wrong? The error comes from the initialization of a class that should use this configuration:
@Service
@Transactional(“lookupTransactionManager”)
public class LookupService {
Is this where we are going wrong?
LikeLike
It looks like the problem is with detecting se.raa.platsr.model.lookup.LMPlace as an @Entity. As in my example, I’d change that LCEMFB implementation to be an EntityManagerFactory and use setPackagesToScan to point at se.raa.platsr.model.lookup. It’s generally better to return an interface rather than an implementation.
LikeLike
Great! Thanks for sharing.
I have a question. My project structure is like this:
com.app.controller
com.app.service
com.app.domain
com.app.domain.Foo1
com.app.domain.Foo2
com.app.domain.Foo3
com.app.domain.Bar1
com.app.domain.Bar2
com.app.domain.Bar3
com.app.repository
com.app.repository.Foo1Repository
com.app.repository.Foo3Repository
com.app.repository.Foo2Repository
com.app.repository.Bar1Repository
com.app.repository.Bar2Repository
com.app.repository.Bar3Repository
as you suggest it should be structured like this?:
com.app.controller
com.app.service
com.app.Foo1.domain
com.app.Foo1.repository
com.app.Foo2.domain
com.app.Foo2.repository
com.app.Foo3.domain
com.app.Foo3.repository
com.app.Bar1.domain
com.app.Bar1.repository
com.app.Bar2.domain
com.app.Bar2.repositroy
com.app.Bar3.domain
com.app.Bar3.repository
And if i have 50 classes to deal with databases i need 50 packeges each (an additionaly 2: domain and repo)?
Ani idea how to do it right?
Thanks
LikeLike
You don’t need a separate package for each entity. Just a separate package for each database. So more like:
com.app.foo.domain.Foo1
com.app.foo.repository.Foo1Repository
com.app.bar.domain.Bar1
etc…
LikeLike
Nice, thanks
LikeLike
bar:
datasource:
url: jdbc:mysql://localhost/bar_schema
username: root
password: d4t4b4s3sForLif3
driverClassName: com.mysql.jdbc.Driver
test-on-borrow: true \\ in spring boot 1.5.3 it seems this is moved under tomcat.
test-while-idle: true \\ but when I tried something like below it is not working can anyone please help?
validation-query: select 1;\\
bar:
datasource:
url: jdbc:mysql://localhost/bar_schema
username: root
password: d4t4b4s3sForLif3
driverClassName: com.mysql.jdbc.Driver
tomcat:
test-on-borrow: true
test-while-idle: true
validation-query: select 1;
LikeLike
Great post, i have used to implement my new project, i would like to write a version un spanish. Thanks you so much.
LikeLike
Very usefull post, i have used to create my new project and it work!!!!!. Thank you.
I would like write similar version in my blog in spanish. Thanks.
LikeLike
I’m happy to hear that you were able to use the examples so easily. Please feel free to make use of it in your blog in Spanish. If it helps more people to do this, then that’s all good! Please do let me know if you do that and I shall link to it.
LikeLike
Thanks to you. I can easily use datasorces.
long time a go, I’m confused to adapt multiple it.
LikeLike
Hello sir, I am trying to configure multiple databases, I am getting the following exception while inserting the data to table
AddressTypeID –>> is Auto incremented in DB, i have given AUTO here still it is throwing this exception?? please help me. thank you.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table ‘AddressTypeMaster’ when IDENTITY_INSERT is set to OFF.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:232) ~[mssql-jdbc-6.1.0.jre7.jar:?]
@Entity
@Table(name = “AddressTypeMaster”)
public class AddressType implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = “AddressTypeID”)
private Integer addressTypeID;
@Column(name = “AddressTypeName”)
private String addressTypeName;
@Column(name = “IsActive”)
private Boolean isActive;
@Column(name = “CreatedOn”)
private Date createdOn;
@Column(name = “CreatedBy”)
LikeLike
Hi there. That looks like you have your database table configured to auto-increment, with “IDENTITY_INSERT” disabled. This means that your application cannot insert an ID itself.
One option is to change it to GenerationType.Identity. That will let SQL Server do the ID generation for you.
However, GenerationType.AUTO *should* work. One reason this can fail is if you’re not using the most recent dialect supported by Hibernate. It would be worth checking exactly which version of SQL Server you are using and set jpa.properties.hibernate.dialect appropriately. It should be the most recent dialect supported by your database, from:
org.hibernate.dialect.SQLServerDialect
org.hibernate.dialect.SQLServer2005Dialect
org.hibernate.dialect.SQLServer2008Dialect
org.hibernate.dialect.SQLServer2012Dialect
If that is the problem, then by updating it, you’ll also get access to lots of nice field types that weren’t available to you before! 🙂
LikeLike
Nicely written. Very helpful. Quick question – where are CRUD operations using repositories? Where should we declare DAO. Request you to add those examples.
LikeLike
A Spring Data repository is a DAO. You don’t need to write one of your own, although you can extend them with your own implementations if some of the standard methods are not sufficient.
LikeLike
spring:
datasource:
url: jdbc:mysql://localhost/foo_schema
username: foouser
password: 12345
driverClassName: com.mysql.jdbc.Driver
bar:
datasource:
url: jdbc:mysql://localhost/bar_schema
username: baruser
password: 12345
driverClassName: com.mysql.jdbc.Driver
How to connect to database with there different users (like above config properties )
LikeLike
Hi,
I have connected to the two datasources, but I want different operations to be done by both the datasources.
Ex.
for Primary datasoure- only write/update/delete operations should be done.
for Secondary datasource- only read operation should be done.
— (the database and tables are same so the repository also same.)
In my code both operations goes to primary datasource. could you please let me know how this can be done, or any working reference so it will be helpful for me.
Thanks,
Sandeep.
LikeLike
Very good example, Thanks for sharing.
LikeLike
Description:
Parameter 0 of method entityManagerFactoryBean in com.example.anil.config.BookDBConfig required a bean of type ‘org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder’ that could not be found.
Action:
Consider defining a bean of type ‘org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder’ in your configuration.
LikeLike