Loading... ## 前言 最近一个需求需要使用到多数据源,记录一下踩到的坑。 ## 环境 - SpringBoot 2.2.1.RELEASE - QueryDSL 5.0.0 ## 多数据源配置 ```java @Configuration public class DataSourceConfiguration { @Primary @Bean @ConfigurationProperties(prefix = "spring.datasource.primary") public DataSource primaryDataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties(prefix = "spring.datasource.secondary") public DataSource secondaryDataSource() { return DataSourceBuilder.create().build(); } } ``` ```java @Configuration @EnableTransactionManagement(proxyTargetClass = true) @EnableJpaRepositories( repositoryFactoryBeanClass = BaseRepositoryFactory.class, entityManagerFactoryRef = "entityManagerFactoryPrimary", transactionManagerRef = "transactionManagerPrimary", basePackages = {"com.xxx.xxx.xxx.repository.primary"}) public class PrimaryConfig { private static final String DEFAULT_IMPLICIT_STRATEGY = "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy"; private DataSource primaryDataSource; private JpaProperties jpaProperties; private HibernateProperties hibernateProperties; public PrimaryConfig(@Qualifier("primaryDataSource") DataSource primaryDataSource, JpaProperties jpaProperties, HibernateProperties hibernateProperties) { this.primaryDataSource = primaryDataSource; this.jpaProperties = jpaProperties; this.hibernateProperties = hibernateProperties; } private Map<String, Object> getVendorProperties() { return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings()); } @Primary @Bean(name = "entityManagerPrimary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject())); // 这种创建出来会有缓存问题 // return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()) // .createEntityManager(SynchronizationType.SYNCHRONIZED); } @Primary @Bean(name = "entityManagerFactoryPrimary") public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary( EntityManagerFactoryBuilder builder) { Map<String, Object> vendorProperties = getVendorProperties(); // vendorProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect"); return builder .dataSource(primaryDataSource) .packages("com.xxx.xxx.xxx.entity.primary") //设置实体类所在位置 .persistenceUnit("primaryPersistenceUnit") .properties(getVendorProperties()) .build(); } @Primary @Bean(name = "transactionManagerPrimary") public PlatformTransactionManager transactionManagerPrimary( EntityManagerFactoryBuilder builder) { return new JpaTransactionManager( Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject())); } @Bean public JPAQueryFactory queryFactory( @Qualifier("entityManagerPrimary") EntityManager entityManager) { return new JPAQueryFactory(entityManager); } } ``` ```java @Configuration @EnableTransactionManagement(proxyTargetClass = true) @EnableJpaRepositories( repositoryFactoryBeanClass = BaseRepositoryFactory.class, entityManagerFactoryRef = "entityManagerFactorySecondary", transactionManagerRef = "transactionManagerSecondary", basePackages = {"com.xxx.xxx.xxx.repository.secondary"}) //设置Repository所在位置 public class SecondaryConfig { private static final String DEFAULT_PHYSICAL_STRATEGY = "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy"; @Autowired @Qualifier("secondaryDataSource") private DataSource secondaryDataSource; @Autowired private JpaProperties jpaProperties; @Autowired private HibernateProperties hibernateProperties; private Map<String, Object> getVendorProperties() { return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings()); } @Bean(name = "entityManagerSecondary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject())); } @Bean(name = "entityManagerFactorySecondary") public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary( EntityManagerFactoryBuilder builder) { Map<String, Object> vendorProperties = getVendorProperties(); vendorProperties.put("hibernate.hbm2ddl.auto", "none"); // 在这里设置方言 vendorProperties.put("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect"); return builder .dataSource(secondaryDataSource) //设置实体类所在位置 .packages("com.xxx.xxx.xxx.entity.secondary") .persistenceUnit("secondaryPersistenceUnit") .properties(vendorProperties) .build(); } @Bean(name = "transactionManagerSecondary") PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager( Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject())); } @Bean public JPAQueryFactory queryFactorySecondary( @Qualifier("entityManagerSecondary") EntityManager entityManager) { return new JPAQueryFactory(entityManager); } } ``` #### 配置文件 ```yaml spring: datasource: primary: jdbc-url: jdbc:postgresql://1.1.1.1:5432/xxxx username: postgres password: xxxxxx driver-class-name: org.postgresql.Driver secondary: username: sa password: xxxxxx jdbc-url: jdbc:sqlserver://1.1.1.1:1433;databasename=xxxx driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver jpa: properties: hibernate: hbm2ddl: auto: update # primary-dialect: org.hibernate.dialect.PostgreSQLDialect # secondary-dialect: org.hibernate.dialect.SQLServer2008Dialect ``` ## 遇到的坑 1. 方言配置不生效 安装查到的资料配置JPA方言,但启动后控制台输出SQL Server 数据库的方言也是PostgreSQLDialect,说明没有走日志,同时自动识别也没识别出来。最后实在配置类里面手动写入的,不知道是不是我没配置对。相关代码: ```yaml @Bean(name = "entityManagerFactorySecondary") public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary( EntityManagerFactoryBuilder builder) { Map<String, Object> vendorProperties = getVendorProperties(); vendorProperties.put("hibernate.hbm2ddl.auto", "none"); // 在这里设置方言 vendorProperties.put("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect"); return builder .dataSource(secondaryDataSource) //设置实体类所在位置 .packages("com.xxx.xxx.xxx.entity.secondary") .persistenceUnit("secondaryPersistenceUnit") .properties(vendorProperties) .build(); } ``` 2. 解决了方言问题发现又出现使用 QueryDSL 进行更新删除操作报错的问题,报错如下: ```java Executing an update/delete query ``` 经过排查,发现是缺少事务,通过在创建entityManager时增加参数 `SynchronizationType.SYNCHRONIZED` 解决。相关代码: ```java @Primary @Bean(name = "entityManagerPrimary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()) .createEntityManager(SynchronizationType.SYNCHRONIZED); } ``` 3. 使用 QueryDSL 查询缓存不更新,查询出来的结果始终是缓存数据,通过`entityManager.clear()` 后能查到新数据,但不可每次查询都去手动更新缓存吧,通过Google,看到了 https://springboot.io/t/topic/3892 这个帖子。按照帖子说的方式去创建 `entityManager`,果然能够自动更新缓存了。相关代码: ```java @Primary @Bean(name = "entityManagerPrimary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject())); // 这种创建出来会有缓存问题 // return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()) // .createEntityManager(SynchronizationType.SYNCHRONIZED); } ``` Last modification:December 30th, 2021 at 12:41 pm © 允许规范转载 Support If you think my article is useful to you, please feel free to appreciate ×Close Appreciate the author Sweeping payments
非常感谢你分享这篇文章,我从中学到了很多新的知识。
赞一个OωO