title: Spring Boot Multiple Datasource
body: <p>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</p>
  
  <pre><code>com.test.entity.db.mysql ; for entities that belong to MySql
  com.test.entity.db.h2 ; for entities that belong to H2 Databases
  </code></pre>
  
  <p>So, currently I have two entities class</p>
  
  <p>UserMySql.java</p>
  
  <pre><code>@Entity
  @Table(name="usermysql")
  public class UserMysql{
  
      @Id
      @GeneratedValue
      public int id;
  
      public String name;
  
  }
  </code></pre>
  
  <p>UserH2.java</p>
  
  <pre><code>@Entity
  @Table(name="userh2")
  public class Userh2 {
  
      @Id
      @GeneratedValue
      public int id;
  
      public String name;
  }
  </code></pre>
  
  <p>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. </p>
  
  <p>(com.test.model is package where I'll put my Repositories class. It will be defined below)</p>
  
  <p>MySqlDbConfig.java</p>
  
  <pre><code>@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();
  }   
  
  }
  </code></pre>
  
  <p>H2DbConfig.java</p>
  
  <pre><code>@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();
  }
  }
  </code></pre>
  
  <p>My application.properties file</p>
  
  <pre><code>#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
  </code></pre>
  
  <p>And then for crud I have UserMySqlDao and UserH2Dao</p>
  
  <p>UserMySqlDao.java</p>
  
  <pre><code>@Transactional 
  @Repository
  public interface UserMysqlDao extends CrudRepository&lt;UserMysql, Integer&gt;{
  
      public UserMysql findByName(String name);
  }
  </code></pre>
  
  <p>UserH2Dao.java</p>
  
  <pre><code>@Transactional
  @Repositories
  public interface UserH2Dao extends CrudRepository&lt;Userh2, Integer&gt;{
  
      public Userh2 findByName(String name);
  }
  </code></pre>
  
  <p>And for last, I have an UserController as endpoint to access my service</p>
  
  <p>UserController.java</p>
  
  <pre><code>@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();
      }
  }   
  }
  </code></pre>
  
  <p>Application.java</p>
  
  <pre><code>@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("");
  }
  
  }
  </code></pre>
  
  <p>With this configuration my Spring boot run well, but when I access </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks</p>