title: Spring-boot multiple databases - spring inserts data but only saves to one db
body: <p>I've really been struggling with a multiple (2) db setup in springboot. I've spent countless of hours and followed many examples and I've read the <a href="https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-use-two-entity-managers" rel="nofollow noreferrer">complete documentation</a>.<br>
  I find it hard to decide what should be added in my code because there are so many examples with different solutions around. There's Spring, Spring-boot, hibernate, jpa, beans, xml files, annotations, corrupt dependencies.<br>
  I've come up with a piece of code that doesn't give errors and saves my data. But, only the data to the primary database gets saved. The insert in the second db is happening according to the sql trace but no commit. What's going on here?<br>
  I also made a simple controller class to insert data with an http request, but it had the same results: only user in db1 gets saved, product in db2 doesn't.
  If anybody has any clue it would be greatly appreciated. It's driving me nuts!
  I realize this is just a simple piece of code, but it's test to see if I can use this approach in a bigger project.
  Thanks!</p>
  
  <p>the configuration:</p>
  
  <pre><code>import com.zaxxer.hikari.HikariDataSource;
  import org.apache.commons.dbcp.BasicDataSource;
  import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
  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.Primary;
  import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
  import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
  import com.multidb.db1.enitities.User;
  import com.multidb.db2.entities.Product;
  
  import javax.sql.DataSource;
  
  @EnableJpaRepositories
  public class DbConfiguration {
  
      @Bean
      @Primary
      @ConfigurationProperties("spring.datasource")
      public DataSourceProperties firstDataSourceProperties() {
          return new DataSourceProperties();
      }
  
      @Bean
      @Primary
      @ConfigurationProperties("spring.datasource.configuration")
      public HikariDataSource firstDataSource() {
          return firstDataSourceProperties().initializeDataSourceBuilder()
                  .type(HikariDataSource.class).build();
      }
  
      @Bean
      @ConfigurationProperties("spring.datasource2")
      public DataSourceProperties secondDataSourceProperties() {
          return new DataSourceProperties();
      }
  
      @Bean
      @ConfigurationProperties("spring.datasource2.configuration")
      public BasicDataSource secondDataSource() {
          return secondDataSourceProperties().initializeDataSourceBuilder()
                  .type(BasicDataSource.class).build();
      }
  
      //////
  
      @Primary
      @Bean
      public LocalContainerEntityManagerFactoryBean userEntityManagerFactory(
              EntityManagerFactoryBuilder builder) {
          return builder
                  .dataSource(firstDataSource())
                  .packages("db1")//User.class
                  .persistenceUnit("users")
                  .build();
      }
  
      @Bean
      public LocalContainerEntityManagerFactoryBean productEntityManagerFactory(
              EntityManagerFactoryBuilder builder) {
          return builder
                  .dataSource(secondDataSource())
                  .packages("db2")//Product.class
                  .persistenceUnit("products")
                  .build();
      }
  }
  
  </code></pre>
  
  <p>the applicaton.properties file</p>
  
  <pre><code>spring.jpa.hibernate.ddl-auto=create-drop
  spring.jpa.show-sql=true
  
  logging.level.org.hibernate.SQL=DEBUG
  logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
  
  spring.datasource.url=jdbc:mysql://localhost/db1?autoReconnect=true&amp;useSSL=false
  spring.datasource.username=user
  spring.datasource.password=pass
  spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  spring.datasource.configuration.maximum-pool-size=30
  
  spring.datasource2.url=jdbc:mysql://localhost/db2?autoReconnect=true&amp;useSSL=false
  spring.datasource2.username=user
  spring.datasource2.password=pass
  spring.datasource2.driver-class-name=com.mysql.jdbc.Driver
  spring.datasource2.max-total=30
  </code></pre>
  
  <p>a simple user class</p>
  
  <pre><code>import javax.persistence.*;
  
  @Entity
  @Table
  public class User {
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
  
      private String firstName;
  
      private String lastName;
  
  
      public User(String firstName, String lastName) {
          this.firstName = firstName;
          this.lastName = lastName;
      }
  
      public User() {
          //
      }
  
  }
  
  </code></pre>
  
  <p>and the product class</p>
  
  <pre><code>import javax.persistence.*;
  
  @Entity
  @Table
  public class Product {
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
  
      private String name;
  
      private double price;
  
      public Product(String name, double price) {
          this.name = name;
          this.price = price;
      }
  
      public Product() {
          //
      }
  }
  </code></pre>
  
  <p>a user repo</p>
  
  <pre><code>import org.springframework.data.repository.CrudRepository;
  import com.multidb.db1.enitities.User;
  import org.springframework.transaction.annotation.Transactional;
  
  @Transactional
  public interface UserRepository extends CrudRepository&lt;User, Long&gt; {
      //
  }
  </code></pre>
  
  <p>a product repo</p>
  
  <pre><code>
  import com.multidb.db2.entities.Product;
  import org.springframework.data.jpa.repository.JpaRepository;
  import org.springframework.data.repository.CrudRepository;
  
  public interface ProductRepository extends CrudRepository&lt;Product, Long&gt; {
      //
  }
  
  </code></pre>
  
  <p>a simple datainit class to write something to the db</p>
  
  <pre><code>import com.multidb.db1.repositories.UserRepository;
  import com.multidb.db1.enitities.User;
  import com.multidb.db2.entities.Product;
  import com.multidb.db2.repositories.ProductRepository;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.ApplicationArguments;
  import org.springframework.boot.ApplicationRunner;
  import org.springframework.stereotype.Component;
  
  @Component
  public class InitData implements ApplicationRunner {
  
      @Autowired
      private ProductRepository productRepository;
  
      @Autowired
      private UserRepository userRepository;
  
      @Autowired
      public InitData(){}
  
      @Override
      public void run(ApplicationArguments args) throws Exception {
          addUser("Johnny", "Depp");
          addProduct("Booze", 5.0);
      }
  
      private void addUser(String firstname, String lastname){
          User user = new User(firstname, lastname);
          userRepository.save(user);
      }
  
      private void addProduct(String name, double price){
          Product product = new Product(name, price);
          productRepository.save(product);
      }
  }
  
  </code></pre>
  
  <p>the Maven dependencies</p>
  
  <pre><code>&lt;parent&gt;
          &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
          &lt;artifactId&gt;spring-boot-starter-parent&lt;/artifactId&gt;
          &lt;version&gt;2.1.3.RELEASE&lt;/version&gt;
      &lt;/parent&gt;
  
      &lt;dependencies&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;mysql&lt;/groupId&gt;
              &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
              &lt;version&gt;5.1.46&lt;/version&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;commons-dbcp&lt;/groupId&gt;
              &lt;artifactId&gt;commons-dbcp&lt;/artifactId&gt;
              &lt;version&gt;1.2.2&lt;/version&gt;
          &lt;/dependency&gt;
      &lt;/dependencies&gt;
  </code></pre>
  
  <p>Last but not least the ouput</p>
  
  <pre><code>
    .   ____          _            __ _ _
   /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
  ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
   \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
    '  |____| .__|_| |_|_| |_\__, | / / / /
   =========|_|==============|___/=/_/_/_/
   :: Spring Boot ::        (v2.1.3.RELEASE)
  
  
  
  2019-04-02 13:20:09.084  INFO 47275 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
  2019-04-02 13:20:09.147  INFO 47275 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 56ms. Found 2 repository interfaces.
  2019-04-02 13:20:09.466  INFO 47275 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$84774c02] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
  2019-04-02 13:20:09.765  INFO 47275 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
  2019-04-02 13:20:09.795  INFO 47275 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
  2019-04-02 13:20:09.796  INFO 47275 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.16]
  2019-04-02 13:20:09.805  INFO 47275 --- [           main] o.a.catalina.core.AprLifecycleListener   : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/Users/Fdoe/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.]
  2019-04-02 13:20:09.884  INFO 47275 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
  2019-04-02 13:20:09.885  INFO 47275 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1367 ms
  2019-04-02 13:20:10.114  INFO 47275 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
  2019-04-02 13:20:10.327  INFO 47275 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
  2019-04-02 13:20:10.368  INFO 47275 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
      name: default
      ...]
  2019-04-02 13:20:10.423  INFO 47275 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.7.Final}
  2019-04-02 13:20:10.424  INFO 47275 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
  2019-04-02 13:20:10.553  INFO 47275 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
  2019-04-02 13:20:10.684  INFO 47275 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
  2019-04-02 13:20:11.209 DEBUG 47275 --- [           main] org.hibernate.SQL                        : drop table if exists hibernate_sequence
  Hibernate: drop table if exists hibernate_sequence
  2019-04-02 13:20:11.220 DEBUG 47275 --- [           main] org.hibernate.SQL                        : drop table if exists product
  Hibernate: drop table if exists product
  2019-04-02 13:20:11.222 DEBUG 47275 --- [           main] org.hibernate.SQL                        : drop table if exists user
  Hibernate: drop table if exists user
  2019-04-02 13:20:11.225 DEBUG 47275 --- [           main] org.hibernate.SQL                        : create table hibernate_sequence (next_val bigint) engine=MyISAM
  Hibernate: create table hibernate_sequence (next_val bigint) engine=MyISAM
  2019-04-02 13:20:11.228 DEBUG 47275 --- [           main] org.hibernate.SQL                        : insert into hibernate_sequence values ( 1 )
  Hibernate: insert into hibernate_sequence values ( 1 )
  2019-04-02 13:20:11.230 DEBUG 47275 --- [           main] org.hibernate.SQL                        : insert into hibernate_sequence values ( 1 )
  Hibernate: insert into hibernate_sequence values ( 1 )
  2019-04-02 13:20:11.231 DEBUG 47275 --- [           main] org.hibernate.SQL                        : create table product (id bigint not null, name varchar(255), price double precision not null, primary key (id)) engine=MyISAM
  Hibernate: create table product (id bigint not null, name varchar(255), price double precision not null, primary key (id)) engine=MyISAM
  2019-04-02 13:20:11.235 DEBUG 47275 --- [           main] org.hibernate.SQL                        : create table user (id bigint not null, first_name varchar(255), last_name varchar(255), primary key (id)) engine=MyISAM
  Hibernate: create table user (id bigint not null, first_name varchar(255), last_name varchar(255), primary key (id)) engine=MyISAM
  2019-04-02 13:20:11.239  INFO 47275 --- [           main] o.h.t.schema.internal.SchemaCreatorImpl  : HHH000476: Executing import script 'org.hibernate.tool.schema.internal.exec.ScriptSourceInputNonExistentImpl@3a7c678b'
  2019-04-02 13:20:11.241  INFO 47275 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
  2019-04-02 13:20:11.651  INFO 47275 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
  2019-04-02 13:20:11.695  WARN 47275 --- [           main] aWebConfiguration$JpaWebMvcConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
  2019-04-02 13:20:11.889  INFO 47275 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
  2019-04-02 13:20:11.891  INFO 47275 --- [           main] com.fdoe.multidb.Application            : Started Application in 3.92 seconds (JVM running for 4.308)
  2019-04-02 13:20:11.913 DEBUG 47275 --- [           main] org.hibernate.SQL                        : select next_val as id_val from hibernate_sequence for update
  Hibernate: select next_val as id_val from hibernate_sequence for update
  2019-04-02 13:20:11.928 DEBUG 47275 --- [           main] org.hibernate.SQL                        : update hibernate_sequence set next_val= ? where next_val=?
  Hibernate: update hibernate_sequence set next_val= ? where next_val=?
  2019-04-02 13:20:11.948 DEBUG 47275 --- [           main] org.hibernate.SQL                        : insert into user (first_name, last_name, id) values (?, ?, ?)
  Hibernate: insert into user (first_name, last_name, id) values (?, ?, ?)
  2019-04-02 13:20:11.950 TRACE 47275 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Johnny]
  2019-04-02 13:20:11.950 TRACE 47275 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [Depp]
  2019-04-02 13:20:11.951 TRACE 47275 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [1]
  2019-04-02 13:20:11.957 DEBUG 47275 --- [           main] org.hibernate.SQL                        : select next_val as id_val from hibernate_sequence for update
  Hibernate: select next_val as id_val from hibernate_sequence for update
  2019-04-02 13:20:11.958 DEBUG 47275 --- [           main] org.hibernate.SQL                        : update hibernate_sequence set next_val= ? where next_val=?
  Hibernate: update hibernate_sequence set next_val= ? where next_val=?
  2019-04-02 13:20:11.959 DEBUG 47275 --- [           main] org.hibernate.SQL                        : insert into product (name, price, id) values (?, ?, ?)
  Hibernate: insert into product (name, price, id) values (?, ?, ?)
  2019-04-02 13:20:11.959 TRACE 47275 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Booze]
  2019-04-02 13:20:11.960 TRACE 47275 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [DOUBLE] - [5.0]
  2019-04-02 13:20:11.960 TRACE 47275 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [2]
  
  </code></pre>
  
  <p>db1 - terminal/command line output</p>
  
  <pre><code>mysql&gt; select * from user;
  +----+------------+-----------+
  | id | first_name | last_name |
  +----+------------+-----------+
  |  1 | Johnny     | Depp      |
  +----+------------+-----------+
  1 row in set (0.00 sec)
  
  mysql&gt; 
  
  </code></pre>
  
  <p>db2 - terminal/command line output</p>
  
  <pre><code>mysql&gt; select * from product;
  Empty set (0.01 sec)
  
  mysql&gt; 
  </code></pre>