Spring Download multiple data sources

Question

I am new to spring loading and I would like to create some data sources for my project. Here is my current case. I have two packages for an entity for multiple databases. Let them talk

com.test.entity.db.mysql ; for entities that belong to MySql com.test.entity.db.h2 ; for entities that belong to H2 Databases 

So now I have two class classes

UserMySql.java

 @Entity @Table(name="usermysql") public class UserMysql{ @Id @GeneratedValue public int id; public String name; } 

UserH2.java

 @Entity @Table(name="userh2") public class Userh2 { @Id @GeneratedValue public int id; public String name; } 

I would like to get a configuration where, if I create a user from UserMySql, it will be saved in the MySql database, and if I create a user from Userh2, it will be saved in H2 Databases. So, I also have two DBConfig, say MySqlDbConfig and H2DbConfig.

(com.test.model is the package in which I put my Repositories class, which will be defined below)

MySqlDbConfig.java

 @Configuration @EnableJpaRepositories( basePackages="com.test.model", entityManagerFactoryRef = "mysqlEntityManager") public class MySqlDBConfig { @Bean @Primary @ConfigurationProperties(prefix="datasource.test.mysql") public DataSource mysqlDataSource(){ return DataSourceBuilder .create() .build(); } @Bean(name="mysqlEntityManager") public LocalContainerEntityManagerFactoryBean mySqlEntityManagerFactory( EntityManagerFactoryBuilder builder){ return builder.dataSource(mysqlDataSource()) .packages("com.test.entity.db.mysql") .build(); } } 

H2DbConfig.java

 @Configuration @EnableJpaRepositories( entityManagerFactoryRef = "h2EntityManager") public class H2DbConfig { @Bean @ConfigurationProperties(prefix="datasource.test.h2") public DataSource h2DataSource(){ return DataSourceBuilder .create() .driverClassName("org.h2.Driver") .build(); } @Bean(name="h2EntityManager") public LocalContainerEntityManagerFactoryBean h2EntityManagerFactory( EntityManagerFactoryBuilder builder){ return builder.dataSource(h2DataSource()) .packages("com.test.entity.db.h2") .build(); } } 

My application.properties file

 #DataSource settings for mysql datasource.test.mysql.jdbcUrl = jdbc:mysql://127.0.0.1:3306/test datasource.test.mysql.username = root datasource.test.mysql.password = root datasource.test.mysql.driverClassName = com.mysql.jdbc.Driver #DataSource settings for H2 datasource.test.h2.jdbcUrl = jdbc:h2:~/test datasource.test.h2.username = sa # DataSource settings: set here configurations for the database connection spring.datasource.url = jdbc:mysql://127.0.0.1:3306/test spring.datasource.username = root spring.datasource.password = root spring.datasource.driverClassName = com.mysql.jdbc.Driver spring.datasource.validation-query=SELECT 1 # Specify the DBMS spring.jpa.database = MYSQL # Show or not log for each sql query spring.jpa.show-sql = true # Hibernate settings are prefixed with spring.jpa.hibernate.* spring.jpa.hibernate.ddl-auto = update spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy spring.jpa.hibernate.show_sql = true spring.jpa.hibernate.format_sql = true server.port=8080 endpoints.shutdown.enabled=false 

And then for crud I have UserMySqlDao and UserH2Dao

UserMySqlDao.java

 @Transactional @Repository public interface UserMysqlDao extends CrudRepository<UserMysql, Integer>{ public UserMysql findByName(String name); } 

UserH2Dao.java

 @Transactional @Repositories public interface UserH2Dao extends CrudRepository<Userh2, Integer>{ public Userh2 findByName(String name); } 

And for the latter, I have a UserController as an endpoint for accessing my service

UserController.java

 @Controller @RequestMapping("/user") public class UserController { @Autowired private UserMysqlDao userMysqlDao; @Autowired private UserH2Dao userH2Dao; @RequestMapping("/createM") @ResponseBody public String createUserMySql(String name){ UserMysql user = new UserMysql(); try{ user.name = name; userMysqlDao.save(user); return "Success creating user with Id: "+user.id; }catch(Exception ex){ return "Error creating the user: " + ex.toString(); } } @RequestMapping("/createH") @ResponseBody public String createUserH2(String name){ Userh2 user = new Userh2(); try{ user.name = name; userH2Dao.save(user); return "Success creating user with Id: "+user.id; }catch(Exception ex){ return "Error creating the user: " + ex.toString(); } } } 

Application.java

 @Configuration @EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) @EntityScan(basePackages="com.test.entity.db") @ComponentScan public class Application { public static void main(String[] args) { System.out.println("Entering spring boot"); ApplicationContext ctx = SpringApplication.run(Application.class, args); System.out.println("Let inspect the beans provided by Spring Boot:"); String[] beanNames = ctx.getBeanDefinitionNames(); Arrays.sort(beanNames); for (String beanName : beanNames) { System.out.print(beanName); System.out.print(" "); } System.out.println(""); } } 

In this configuration, my spring boot works fine, but when I access

 http://localhost/user/createM?name=myname it writes an exception Error creating the user: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement 

I have googling and not yet a solution. Any ideas why this exception occurs? And is this the best way to implement multiple data source to implement my case above? I am open to full refactoring, if necessary.

thank

Answer 1

I think you may find it useful.

http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-two-datasources

It shows how to identify multiple data sources and designate one of them as primary.

Here is a fairly complete example, also contains transaction distribution - if you need it.

http://fabiomaffioletti.me/blog/2014/04/15/distributed-transactions-multiple-databases-spring-boot-spring-data-jpa-atomikos/

You need to create 2 configuration classes, separate model / repository packages, etc. to simplify the configuration.

In addition, in the above example, it creates data sources manually. You can avoid this by using the method in spring doc, with @ConfigurationProperties annotation. Here is an example of this:

http://xantorohara.blogspot.com.tr/2013/11/spring-boot-jdbc-with-multiple.html

Hope this helps.

Answer 2

I ran into the same problem a few days ago, I followed the link mentioned below and was able to overcome the problem.

http://www.baeldung.com/spring-data-jpa-multiple-databases

Answer 3

I solved the problem (how to connect multiple databases using spring and Hibernate) this way, I hope this helps :)

NOTE. I added the appropriate code, kindly make dao with impl, which I used in the code below.

web.xml

 <?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <display-name>MultipleDatabaseConnectivityInSpring</display-name> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>dispatcher</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <load-on-startup>1</load-on-startup> </servlet> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <context-param> <param-name>contextConfigLocation</param-name> <param-value> /WEB-INF/dispatcher-servlet.xml </param-value> </context-param> <servlet-mapping> <servlet-name>dispatcher</servlet-name> <url-pattern>*.htm</url-pattern> </servlet-mapping> <session-config> <session-timeout>30</session-timeout> </session-config> </web-app> 

persistence.xml

 <?xml version="1.0" encoding="UTF-8"?> <persistence version="1.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_1_0.xsd"> <persistence-unit name="localPersistenceUnitOne" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <class>in.india.entities.CustomerDetails</class> <exclude-unlisted-classes /> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" /> <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" /> <property name="hibernate.jdbc.batch_size" value="0" /> <property name="hibernate.show_sql" value="false" /> <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/shankar?sslmode=require" /> <property name="hibernate.connection.username" value="username" /> <property name="hibernate.connection.password" value="password" /> <property name="hibernate.hbm2ddl.auto" value="update" /> </properties> </persistence-unit> <persistence-unit name="localPersistenceUnitTwo" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <class>in.india.entities.CompanyDetails</class> <exclude-unlisted-classes /> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" /> <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" /> <property name="hibernate.jdbc.batch_size" value="0" /> <property name="hibernate.show_sql" value="false" /> <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/shankarTwo?sslmode=require" /> <property name="hibernate.connection.username" value="username" /> <property name="hibernate.connection.password" value="password" /> <property name="hibernate.hbm2ddl.auto" value="update" /> </properties> </persistence-unit> </persistence> 

dispatcher servlet

 <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:task="http://www.springframework.org/schema/task" xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:util="http://www.springframework.org/schema/util" default-autowire="byName" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd"> <!-- Configure messageSource --> <mvc:annotation-driven /> <context:component-scan base-package="in.india.*" /> <bean id="messageResource" class="org.springframework.context.support.ResourceBundleMessageSource" autowire="byName"> <property name="basename" value="messageResource"></property> </bean> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix"> <value>/WEB-INF/jsp/</value> </property> <property name="suffix"> <value>.jsp</value> </property> </bean> <bean id="entityManagerFactoryOne" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" autowire="constructor"> <property name="persistenceUnitName" value="localPersistenceUnitOne" /> </bean> <bean id="messageSource" class="org.springframework.context.support.ResourceBundleMessageSource" autowire="byName"> <property name="basename" value="messageResource" /> </bean> <bean id="entityManagerFactoryTwo" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" autowire="constructor"> <property name="persistenceUnitName" value="localPersistenceUnitTwo" /> </bean> <bean id="manager1" class="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactoryOne" /> </bean> <bean id="manager2" class="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactoryTwo" /> </bean> <tx:annotation-driven transaction-manager="manager1" /> <tx:annotation-driven transaction-manager="manager2" /> <!-- declare dependies here --> <bean class="in.india.service.dao.impl.CustomerServiceImpl" /> <bean class="in.india.service.dao.impl.CompanyServiceImpl" /> <!-- Configure MVC annotations --> <bean class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping" /> <bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" /> <bean class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter" /> </beans> 

Java class to store in one database

 package in.india.service.dao.impl; import in.india.entities.CompanyDetails; import in.india.service.CompanyService; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.springframework.transaction.annotation.Transactional; public class CompanyServiceImpl implements CompanyService { @PersistenceContext(unitName = "entityManagerFactoryTwo") EntityManager entityManager; @Transactional("manager2") @Override public boolean companyService(CompanyDetails companyDetails) { boolean flag = false; try { entityManager.persist(companyDetails); flag = true; } catch (Exception e) { flag = false; } return flag; } } 

Java class to save to another database

 package in.india.service.dao.impl; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.springframework.transaction.annotation.Transactional; import in.india.entities.CustomerDetails; import in.india.service.CustomerService; public class CustomerServiceImpl implements CustomerService { @PersistenceContext(unitName = "localPersistenceUnitOne") EntityManager entityManager; @Override @Transactional(value = "manager1") public boolean customerService(CustomerDetails companyData) { boolean flag = false; entityManager.persist(companyData); return flag; } } 

customer.jsp

 <%@page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Insert title here</title> </head> <body> <center> <h1>SpringWithMultipleDatabase's</h1> </center> <form:form method="GET" action="addCustomer.htm" modelAttribute="customerBean" > <table> <tr> <td><form:label path="firstName">First Name</form:label></td> <td><form:input path="firstName" /></td> </tr> <tr> <td><form:label path="lastName">Last Name</form:label></td> <td><form:input path="lastName" /></td> </tr> <tr> <td><form:label path="emailId">Email Id</form:label></td> <td><form:input path="emailId" /></td> </tr> <tr> <td><form:label path="profession">Profession</form:label></td> <td><form:input path="profession" /></td> </tr> <tr> <td><form:label path="address">Address</form:label></td> <td><form:input path="address" /></td> </tr> <tr> <td><form:label path="age">Age</form:label></td> <td><form:input path="age" /></td> </tr> <tr> <td><input type="submit" value="Submit"/></td> </tr> </table> </form:form> </body> </html> 

company.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>ScheduleJobs</title> </head> <body> <center><h1>SpringWithMultipleDatabase's</h1></center> <form:form method="GET" action="addCompany.htm" modelAttribute="companyBean" > <table> <tr> <td><form:label path="companyName">Company Name</form:label></td> <td><form:input path="companyName" /></td> </tr> <tr> <td><form:label path="companyStrength">Company Strength</form:label></td> <td><form:input path="companyStrength" /></td> </tr> <tr> <td><form:label path="companyLocation">Company Location</form:label></td> <td><form:input path="companyLocation" /></td> </tr> <tr> <td> <input type="submit" value="Submit"/> </td> </tr> </table> </form:form> </body> </html> 

index.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Home</title> </head> <body> <center><h1>Multiple Database Connectivity In Spring sdfsdsd</h1></center> <a href='customerRequest.htm'>Click here to go on Customer page</a> <br> <a href='companyRequest.htm'>Click here to go on Company page</a> </body> </html> 

success.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>ScheduleJobs</title> </head> <body> <center><h1>SpringWithMultipleDatabase</h1></center> <b>Successfully Saved</b> </body> </html> 

Companycontroller

 package in.india.controller; import in.india.bean.CompanyBean; import in.india.entities.CompanyDetails; import in.india.service.CompanyService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.ui.ModelMap; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.servlet.ModelAndView; @Controller public class CompanyController { @Autowired CompanyService companyService; @RequestMapping(value = "/companyRequest.htm", method = RequestMethod.GET) public ModelAndView addStudent(ModelMap model) { CompanyBean companyBean = new CompanyBean(); model.addAttribute(companyBean); return new ModelAndView("company"); } @RequestMapping(value = "/addCompany.htm", method = RequestMethod.GET) public ModelAndView companyController(@ModelAttribute("companyBean") CompanyBean companyBean, Model model) { CompanyDetails companyDetails = new CompanyDetails(); companyDetails.setCompanyLocation(companyBean.getCompanyLocation()); companyDetails.setCompanyName(companyBean.getCompanyName()); companyDetails.setCompanyStrength(companyBean.getCompanyStrength()); companyService.companyService(companyDetails); return new ModelAndView("success"); } } 

CustomerController

 package in.india.controller; import in.india.bean.CustomerBean; import in.india.entities.CustomerDetails; import in.india.service.CustomerService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.ui.ModelMap; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.servlet.ModelAndView; @Controller public class CustomerController { @Autowired CustomerService customerService; @RequestMapping(value = "/customerRequest.htm", method = RequestMethod.GET) public ModelAndView addStudent(ModelMap model) { CustomerBean customerBean = new CustomerBean(); model.addAttribute(customerBean); return new ModelAndView("customer"); } @RequestMapping(value = "/addCustomer.htm", method = RequestMethod.GET) public ModelAndView customerController(@ModelAttribute("customerBean") CustomerBean customer, Model model) { CustomerDetails customerDetails = new CustomerDetails(); customerDetails.setAddress(customer.getAddress()); customerDetails.setAge(customer.getAge()); customerDetails.setEmailId(customer.getEmailId()); customerDetails.setFirstName(customer.getFirstName()); customerDetails.setLastName(customer.getLastName()); customerDetails.setProfession(customer.getProfession()); customerService.customerService(customerDetails); return new ModelAndView("success"); } } 

CompanyDetails Entity

 package in.india.entities; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; import javax.persistence.Table; @Entity @Table(name = "company_details") public class CompanyDetails { @Id @SequenceGenerator(name = "company_details_seq", sequenceName = "company_details_seq", initialValue = 1, allocationSize = 1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "company_details_seq") @Column(name = "company_details_id") private Long companyDetailsId; @Column(name = "company_name") private String companyName; @Column(name = "company_strength") private Long companyStrength; @Column(name = "company_location") private String companyLocation; public Long getCompanyDetailsId() { return companyDetailsId; } public void setCompanyDetailsId(Long companyDetailsId) { this.companyDetailsId = companyDetailsId; } public String getCompanyName() { return companyName; } public void setCompanyName(String companyName) { this.companyName = companyName; } public Long getCompanyStrength() { return companyStrength; } public void setCompanyStrength(Long companyStrength) { this.companyStrength = companyStrength; } public String getCompanyLocation() { return companyLocation; } public void setCompanyLocation(String companyLocation) { this.companyLocation = companyLocation; } } 

CustomerDetails Entity

 package in.india.entities; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.SequenceGenerator; import javax.persistence.Table; @Entity @Table(name = "customer_details") public class CustomerDetails { @Id @SequenceGenerator(name = "customer_details_seq", sequenceName = "customer_details_seq", initialValue = 1, allocationSize = 1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customer_details_seq") @Column(name = "customer_details_id") private Long customerDetailsId; @Column(name = "first_name ") private String firstName; @Column(name = "last_name ") private String lastName; @Column(name = "email_id") private String emailId; @Column(name = "profession") private String profession; @Column(name = "address") private String address; @Column(name = "age") private int age; public Long getCustomerDetailsId() { return customerDetailsId; } public void setCustomerDetailsId(Long customerDetailsId) { this.customerDetailsId = customerDetailsId; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmailId() { return emailId; } public void setEmailId(String emailId) { this.emailId = emailId; } public String getProfession() { return profession; } public void setProfession(String profession) { this.profession = profession; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } } 

Answer 4

There is an example with two databases, the database - mysql and orm - mybatis.Hope can help you.

https://github.com/jinjunzhu/spring-boot-mybatis.git

Answer 5

Use multiple data sources or understand the separation of read and write. you should know the AbstractRoutingDataSource class, which supports a dynamic data source.

Here is my datasource.yaml and I will datasource.yaml out how to resolve this case. You can refer to this spring-boot + quartz project. Hope this helps you.

 dbServer: default: localhost:3306 read: localhost:3306 write: localhost:3306 datasource: default: type: com.zaxxer.hikari.HikariDataSource pool-name: default continue-on-error: false jdbc-url: jdbc:mysql://${dbServer.default}/schedule_job?useSSL=true&verifyServerCertificate=false&useUnicode=true&characterEncoding=utf8 username: root password: lh1234 connection-timeout: 30000 connection-test-query: SELECT 1 maximum-pool-size: 5 minimum-idle: 2 idle-timeout: 600000 destroy-method: shutdown auto-commit: false read: type: com.zaxxer.hikari.HikariDataSource pool-name: read continue-on-error: false jdbc-url: jdbc:mysql://${dbServer.read}/schedule_job?useSSL=true&verifyServerCertificate=false&useUnicode=true&characterEncoding=utf8 username: root password: lh1234 connection-timeout: 30000 connection-test-query: SELECT 1 maximum-pool-size: 5 minimum-idle: 2 idle-timeout: 600000 destroy-method: shutdown auto-commit: false write: type: com.zaxxer.hikari.HikariDataSource pool-name: write continue-on-error: false jdbc-url: jdbc:mysql://${dbServer.write}/schedule_job?useSSL=true&verifyServerCertificate=false&useUnicode=true&characterEncoding=utf8 username: root password: lh1234 connection-timeout: 30000 connection-test-query: SELECT 1 maximum-pool-size: 5 minimum-idle: 2 idle-timeout: 600000 destroy-method: shutdown auto-commit: false 

Answer 6

Using two data sources, you will need your own transaction managers.

 @Configuration public class MySqlDBConfig { @Bean @Primary @ConfigurationProperties(prefix="datasource.test.mysql") public DataSource mysqlDataSource(){ return DataSourceBuilder .create() .build(); } @Bean("mysqlTx") public DataSourceTransactionManager mysqlTx() { return new DataSourceTransactionManager(mysqlDataSource()); } // same for another DS } 

And then use it accordingly in @Transaction

 @Transactional("mysqlTx") @Repository public interface UserMysqlDao extends CrudRepository<UserMysql, Integer>{ public UserMysql findByName(String name); } 

Answer 7

Thanks to everyone for your help, but it is not difficult, as it seems; almost everything is handled internally by SpringBoot.

In my case, I want to use Mysql and Mongodb, and the solution was to use the EnableMongoRepositories and EnableJpaRepositories for my application class.

 @SpringBootApplication @EnableTransactionManagement @EnableMongoRepositories(includeFilters = @ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, value = MongoRepository)) @EnableJpaRepositories(excludeFilters = @ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, value = MongoRepository)) class TestApplication { ... 

NB: All mysql objects must extend JpaRepository , and mongo enities must extend MongoRepository .

The datasource configurations are straightforward, as represented by the spring documentation:

 //mysql db config spring.datasource.url= jdbc:mysql://localhost:3306/tangio spring.datasource.username=test spring.datasource.password=test #mongodb config spring.data.mongodb.host=localhost spring.data.mongodb.port=27017 spring.data.mongodb.database=tangio spring.data.mongodb.username=tangio spring.data.mongodb.password=tangio spring.data.mongodb.repositories.enabled=true 

Answer 8

as soon as you start working with jpa, and some driver is in your path to the spring boot class, it immediately puts it inside your data source (for example, h2) to use an unused data source, so u will only need to determine

 spring.datasource.url= jdbc:mysql://localhost:3306/ spring.datasource.username=test spring.datasource.password=test 

if we take a step further and you want to use two I would recommend using two data sources, for example, explained here: Spring Download Setting up and using two data sources

Answer 9

Update 2018-01-07 with Spring Boot 1.5.8.RELEASE

If you want to learn how to configure it, how to use it and how to manage a transaction. I may have answers for you.

You can see the runnable example and some explanations at https://www.surasint.com/spring-boot-with-multiple-databases-example/

I copied the code here.

First you must set application.properties like this

 #Database database1.datasource.url=jdbc:mysql://localhost/testdb database1.datasource.username=root database1.datasource.password=root database1.datasource.driver-class-name=com.mysql.jdbc.Driver database2.datasource.url=jdbc:mysql://localhost/testdb2 database2.datasource.username=root database2.datasource.password=root database2.datasource.driver-class-name=com.mysql.jdbc.Driver 

Then define them as providers (@Bean) as follows:

 @Bean(name = "datasource1") @ConfigurationProperties("database1.datasource") @Primary public DataSource dataSource(){ return DataSourceBuilder.create().build(); } @Bean(name = "datasource2") @ConfigurationProperties("database2.datasource") public DataSource dataSource2(){ return DataSourceBuilder.create().build(); } 

Please note that I have @Bean (name = "datasource1") and @Bean (name = "datasource2"), then you can use it when we need a data source like @Qualifier ("datasource1") and @Qualifier ( "datasource2"), for example

 @Qualifier("datasource1") @Autowired private DataSource dataSource; 

If you care about the transaction, you need to define a DataSourceTransactionManager for both of them, for example:

 @Bean(name="tm1") @Autowired @Primary DataSourceTransactionManager tm1(@Qualifier ("datasource1") DataSource datasource) { DataSourceTransactionManager txm = new DataSourceTransactionManager(datasource); return txm; } @Bean(name="tm2") @Autowired DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) { DataSourceTransactionManager txm = new DataSourceTransactionManager(datasource); return txm; } 

Then you can use it as

 @Transactional //this will use the first datasource because it is @primary 

or

 @Transactional("tm2") 

That should be enough. See Example and details in the link above.

Answer 10

MySqlBDConfig.java

 @Configuration @EnableTransactionManagement @EnableJpaRepositories(basePackages = "PACKAGE OF YOUR CRUDS USING MYSQL DATABASE",entityManagerFactoryRef = "mysqlEmFactory" ,transactionManagerRef = "mysqlTransactionManager") public class MySqlBDConfig{ @Autowired private Environment env; @Bean(name="mysqlProperities") @ConfigurationProperties(prefix="spring.mysql") public DataSourceProperties mysqlProperities(){ return new DataSourceProperties(); } @Bean(name="mysqlDataSource") public DataSource interfaceDS(@Qualifier("mysqlProperities")DataSourceProperties dataSourceProperties){ return dataSourceProperties.initializeDataSourceBuilder().build(); } @Primary @Bean(name="mysqlEmFactory") public LocalContainerEntityManagerFactoryBean mysqlEmFactory(@Qualifier("mysqlDataSource")DataSource mysqlDataSource,EntityManagerFactoryBuilder builder){ return builder.dataSource(mysqlDataSource).packages("PACKAGE OF YOUR MODELS").build(); } @Bean(name="mysqlTransactionManager") public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEmFactory")EntityManagerFactory factory){ return new JpaTransactionManager(factory); } } 

H2DBConfig.java

 @Configuration @EnableTransactionManagement @EnableJpaRepositories(basePackages = "PACKAGE OF YOUR CRUDS USING MYSQL DATABASE",entityManagerFactoryRef = "dsEmFactory" ,transactionManagerRef = "dsTransactionManager") public class H2DBConfig{ @Autowired private Environment env; @Bean(name="dsProperities") @ConfigurationProperties(prefix="spring.h2") public DataSourceProperties dsProperities(){ return new DataSourceProperties(); } @Bean(name="dsDataSource") public DataSource dsDataSource(@Qualifier("dsProperities")DataSourceProperties dataSourceProperties){ return dataSourceProperties.initializeDataSourceBuilder().build(); } @Bean(name="dsEmFactory") public LocalContainerEntityManagerFactoryBean dsEmFactory(@Qualifier("dsDataSource")DataSource dsDataSource,EntityManagerFactoryBuilder builder){ LocalContainerEntityManagerFactoryBean em = builder.dataSource(dsDataSource).packages("PACKAGE OF YOUR MODELS").build(); HibernateJpaVendorAdapter ven = new HibernateJpaVendorAdapter(); em.setJpaVendorAdapter(ven); HashMap<String, Object> prop = new HashMap<>(); prop.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect")); prop.put("hibernate.show_sql", env.getProperty("spring.jpa.show-sql")); em.setJpaPropertyMap(prop); em.afterPropertiesSet(); return em; } @Bean(name="dsTransactionManager") public PlatformTransactionManager dsTransactionManager(@Qualifier("dsEmFactory")EntityManagerFactory factory){ return new JpaTransactionManager(factory); } } 

application.properties

 #---mysql DATASOURCE--- spring.mysql.driverClassName = com.mysql.jdbc.Driver spring.mysql.url = jdbc:mysql://127.0.0.1:3306/test spring.mysql.username = root spring.mysql.password = root #---------------------- #---H2 DATASOURCE---- spring.h2.driverClassName = org.h2.Driver spring.h2.url = jdbc:h2:file:~/test spring.h2.username = root spring.h2.password = root #--------------------------- #------JPA----- spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect spring.jpa.show-sql=true spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults = false spring.jpa.hibernate.ddl-auto = update spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true 

Application.java

 @SpringBootApplication public class Application { public static void main(String[] args) { ApplicationContext ac=SpringApplication.run(KeopsSageInvoiceApplication.class, args); UserMysqlDao userRepository = ac.getBean(UserMysqlDao.class) //for exemple save a new user using your repository userRepository.save(new UserMysql()); } }