Spring Boot Multiple Datasource

Question

I'm quite new to spring boot and I'd like to create a multiple datasource for my project. Here is my current case. I have two packages for entity for multiple database. Let's say

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

So, currently I have two entities class

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'd like to achieve a configuration where if I create user from UserMySql, it will be saved to MySql Database, and if I create user from Userh2 it will be saved to H2 Databases. So, I also have two DBConfig, let's say MySqlDbConfig and H2DbConfig.

(com.test.model is package where I'll put my Repositories class. It 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 last, I have an UserController as endpoint to access 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's 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(""); }  } 

With this configuration my Spring boot run well, 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've googling around and haven't got a solution yet. Any ideas why this exception occurs? And is this the best way to implement multiple datasource to implement my case above? I'm open to full refactor if needed.

Thanks

Answer 1

I think you can find it usefull

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

It shows how to define multiple datasources & assign one of them as primary.

Here is a rather full example, also contains distributes transactions - if you need it.

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

What you need is to create 2 configuration classes, separate the model/repository packages etc to make the config easy.

Also, in above example, it creates the data sources manually. You can avoid this using the method on 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 these helps.