java Spring 使用 EntityManager 启动多个数据源

Spring boot multiple data sources using EntityManager

提问by Bruno

I'm trying to setup a Springboot (v2.0.0.BUILD-SNAPSHOT) project with multiple datasources using this tutorial from INFOQ

我正在尝试使用 INFOQ 的本教程设置具有多个数据源的 Springboot (v2.0.0.BUILD-SNAPSHOT) 项目

https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot

https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot

But instead of JdbcTemplate i need to use multiple EntityManagers

但是我需要使用多个 EntityManagers 而不是 JdbcTemplate

Here's what i have so far

这是我到目前为止所拥有的

Application.properties

应用程序属性

spring.primary.url=jdbc:sqlserver://localhost:2433;databaseName=TEST
spring.primary.username=root
spring.primary.password=root
spring.primary.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.secondary.url=jdbc:oracle:thin:@//localhost:1521/DB
spring.secondary.username=oracle
spring.secondary.password=root
spring.secondary.driverClassName=oracle.jdbc.OracleDriver

Application.java

应用程序.java

package com.test;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

ApplicationConfiguration.java

应用程序配置文件

package com.test.config;
import javax.sql.DataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class ApplicationConfiguration {

    @Primary
    @Bean(name = "primaryDB")
    @ConfigurationProperties(prefix = "spring.primary")
    public DataSource postgresDataSource() {
        return  DataSourceBuilder.create().build();
    }

    @Bean(name = "primaryEM")
    public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
        EntityManagerFactoryBuilder builder, @Qualifier("primaryDB") DataSource ds) {
        return builder
            .dataSource(ds)
            .packages("com.test.supplier1")
            .persistenceUnit("primaryPU")
            .build();
    }

    @Bean(name = "secondaryDB")
    @ConfigurationProperties(prefix = "spring.secondary")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryEM")
    public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
        EntityManagerFactoryBuilder builder, @Qualifier("secondaryDB") DataSource ds) {
    return builder
            .dataSource(ds)
            .packages("com.test.supplier2")
            .persistenceUnit("secondaryPU")
            .build();
    }

}

GenericDAO.java

通用DAO.java

public abstract class GenericDAO<T extends Serializable> {

    private Class<T> clazz = null;

    @PersistenceContext
    protected EntityManager entityManager;

    public void setClazz(Class<T> clazzToSet) {
        this.clazz = clazzToSet;
    }

    public T findOne(Integer id) {          
        return this.entityManager.find(this.clazz, id);
    }

    public List<T> findAll() {
        return this.entityManager.createQuery("from " + this.clazz.getName()).getResultList();
    }

    @Transactional
    public void save(T entity) {
        this.entityManager.persist(setModifiedAt(entity));
    }
}

PersonDAO.java

PersonDAO.java

@Repository
@PersistenceContext(name = "primaryEM")
public class PersonDAO extends GenericDAO<Person> {
    public PersonDAO() {
        this.setClazz(Person.class);
    }
}

ProductDAO.java

产品DAO.java

@Repository
@PersistenceContext(name = "secondaryEM")
public class ProductDAO extends GenericDAO<Product> {
    public ProductDAO() {
        this.setClazz(Product.class);
    }
}

TestService.java

测试服务.java

@Service
public class TestService {

    @Autowired
    PersonDAO personDao;

    @Autowired
    ProductDAO productDao;

    // This should write to primary datasource
    public void savePerson(Person person) {
        personDao.save(person);
    }

    // This should write to secondary datasource
    public void saveProduct(Product product) {
        productDao.save(product);
    }

}

Problem is that it doesn't work. When i try to persist "Product" (secondary ds), it also try to persist to the @Primary datasource.

问题是它不起作用。当我尝试保留“产品”（辅助 ds）时，它还尝试保留到 @Primary 数据源。

How can i do this similar to the JdbcTemplate example from the article ?

我怎样才能做到类似于文章中的 JdbcTemplate 示例？

What am i doing wrong ?

我究竟做错了什么 ？

Thanks !

谢谢 ！

UPDATE (Tried @Deepak solution)

更新（尝试@Deepak 解决方案）

Try the below

试试下面的

@Repository
public class PersonDAO extends GenericDAO<Person> {
    @Autowired
    public PersonDAO(@Qualifier("primaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Person.class);
    }
}

ProductDAO

产品DAO

@Repository
public class ProductDAO extends GenericDAO<Product> {
    @Autowired
    public ProductDAO(@Qualifier("secondaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Product.class);
    }
}

Also Remove @PersistenceContext annotation from GenericDAO

同时从 GenericDAO 中删除 @PersistenceContext 注释

  .   ____          _            __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::  (v2.0.0.BUILD-SNAPSHOT)

com.test.Application                     : Starting Application on...   
com.test.Application                     : No active profile set, falling back to default profiles: default 
ConfigServletWebServerApplicationContext : Refreshing org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@69b2283a: startup date [Thu Apr 20 15:28:59 BRT 2017]; root of context hierarchy  
.s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!   
.s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!   
f.a.AutowiredAnnotationBeanPostProcessor : JSR-330 'javax.inject.Inject' annotation found and supported for autowiring  
o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8081 (http) 
o.apache.catalina.core.StandardService   : Starting service Tomcat  
org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.12    
o.a.c.c.C.[Tomcat].[localhost].[/    : Initializing Spring embedded WebApplicationContext
o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 4001 ms  

o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'dispatcherServlet' to [/]  
o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]   
o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]    
o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]  
o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]  

j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'primaryPU' 
o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [  name: primaryPU ...]    
org.hibernate.Version                    : HHH000412: Hibernate Core {5.2.9.Final}  
org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found    
o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final} 
org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect 
j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'primaryPU'    

j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'secondaryPU'   
o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [  name: secondaryPU   ...]
org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect 
j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'secondaryPU'

s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@69b2283a: startup date [Thu Apr 20 15:28:59 BRT 2017]; root of context hierarchy  
s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.error(javax.servlet.http.HttpServletRequest)    
s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)  
o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/** onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/** onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup    
s.a.ScheduledAnnotationBeanPostProcessor : No TaskScheduler/ScheduledExecutorService bean found for scheduled processing    
o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8081 (http)   
io.test.Application                      : Started Application in 76.21 seconds (JVM running for 77.544)    

org.hibernate.SQL                        : select next value for SEQ_TDAI_ID    
o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 923, SQLState: 42000  
o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00923: FROM keyword not found where expected 
--> ERROR

Seems it's building both entities with the @Primary datasource dialect (In this case "SQLServer2012Dialect").

似乎它正在使用 @Primary 数据源方言（在本例中为“ SQLServer2012Dialect”）构建两个实体。

Secondary EntityManager should be "Oracle12cDialect".

次要 EntityManager 应该是“ Oracle12cDialect”。

UPDATE (SOLUTION)

更新（解决方案）

Seems the connections are ok, only problem is the wrong dialect (seems it defaults to the @Primary DataSource dialect), so the solution is to force it on EntityManagerFactory, heres my quickfix for it:

似乎连接没问题，唯一的问题是错误的方言（似乎它默认为@Primary DataSource 方言），所以解决方案是在 EntityManagerFactory 上强制它，这是我的快速修复：

1) add correct dialects to application.propertiesfile

1) 将正确的方言添加到application.properties文件中

spring.primary.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.secondary.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect

2) Import application.properties dialect value into ApplicationConfiguration.java

2) 将 application.properties 方言值导入 ApplicationConfiguration.java

@Value("${spring.primary.hibernate.dialect}")
private String dialect;

3) Force it into EntityManagerFactory

3) 强制它进入 EntityManagerFactory

@Bean(name = "primaryEM")
public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
    EntityManagerFactoryBuilder builder, @Qualifier("primaryDB") DataSource ds) {

    Properties properties = new Properties();
    properties.setProperty("hibernate.dialect", dialect);

    LocalContainerEntityManagerFactoryBean emf = builder
        .dataSource(ds)
        .packages("com.test.supplier1")
        .persistenceUnit("primaryPU")
        .build();

    emf.setJpaProperties(properties);

    return emf;
}

Now it works.

现在它起作用了。

Is there a more elegant way of doing this ?

有没有更优雅的方法来做到这一点？

回答by Deepak

Try the below

试试下面的

@Repository
public class PersonDAO extends GenericDAO<Person> {
    @Autowired
    public PersonDAO(@Qualifier("primaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Person.class);
    }
}

ProductDAO

产品DAO

@Repository
public class ProductDAO extends GenericDAO<Product> {
    @Autowired
    public ProductDAO(@Qualifier("secondaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Product.class);
    }
}

Also Remove @PersistenceContext annotation from GenericDAO

同时从 GenericDAO 中删除 @PersistenceContext 注释

回答by Meriam

I think you should change "@PersistenceContext(name = "secondaryEM")" to "@PersistenceContext(unitName = "secondaryEM")" in order to specify the persistence unit.

我认为您应该将 "@PersistenceContext(name = "secondaryEM")" 更改为 "@PersistenceContext(unitName = "secondaryEM")" 以指定持久性单元。

回答by Berényi Lajos

This works for me:

这对我有用：

application.properties

应用程序属性

app.hibernate.primary.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
app.hibernate.secondary.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect

you can add more hibernate properties eighter to the primary, eighter to the secondary, like hibernate.hbm2ddl.auto, hibernate.show_sql, etc.

您可以将更多的 hibernate 属性添加到主服务器，添加到辅助服务器，例如 hibernate.hbm2ddl.auto、hibernate.show_sql 等。

ApplicationConfiguration.java

应用程序配置文件

@Bean("primaryhibernateproperties")
@ConfigurationProperties("app.hibernate.primary")
public Properties primaryHibernateProperties() {
    return new Properties();
}

@Bean(name = "primaryEM")
public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
    EntityManagerFactoryBuilder builder, @Qualifier("primaryDB") DataSource ds) {

    LocalContainerEntityManagerFactoryBean emf = builder
        .dataSource(ds)
        .packages("com.test.supplier1")
        .persistenceUnit("primaryPU")
        .build();

    emf.setJpaProperties(primaryHibernateProperties());

    return emf;
}
// same with secondary

GenericDAO

通用DAO

public abstract class GenericDAO<T extends Serializable> {

    private Class<T> clazz;
    private EntityManager entityManger;

    public GenericDAO(EntityManager entityManger, Class<T> clazz) {
        this.entityManger = entityManager;
        this.clazz = clazz;
    }
    // other codes
}

PersonDAO

PersonDAO

@Repository
public class PersonDAO extends GenericDAO<Person> {
    @Autowired
    public PersonDAO(@Qualifier("primaryEM") EntityManager entityManager) {
        super(entityManager, Person.class);
    }
}

ProductDAO

产品DAO

@Repository
public class ProductDAO extends GenericDAO<Product> {
    @Autowired
    public ProductDAO(@Qualifier("secondaryEM") EntityManager entityManager) {
        super(entityManager, Product.class);
    }
}