spring boot下基于spring data jpa配置mysql+达梦多数据源（以不同包路径方式，mysql为主数据源）

spring boot mysql 后端数据库 2023-10-20 14:10:55 280人浏览泡泡鱼

摘要

目录版本信息/主要引包信息背景概述代码示例引包部分（pom.xml）实体部分（Entity）数据库接口部分（Repository）服务接口部分（Service）配置部分（Configurat

版本信息/主要引包信息

Spring Boot：2.1.17.RELEASE
mysql驱动：8.0.21（跟随boot版本）
达梦驱动：8.1.2.192
lombok：1.18.12（跟随boot版本）

背景概述

以mysql为主数据源，达梦为第二数据源方式配置
适用于旧项目二次开发接入达梦数据库或基于通用二方/三方包做业务扩展等场景
将以不同包路径方式绑定不同数据源，以便扩展

代码示例

其中，primary为主数据源，second为第二数据源

引包部分（pom.xml）

<project xmlns="Http://Maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0modelVersion><groupId>orggroupId><artifactId>test-jpa-traartifactId><version>0.0.1-SNAPSHOTversion><packaging>jarpackaging><name>test-jpa-traname><url>http://maven.apache.orgurl><properties><project.build.sourceEncoding>UTF-8project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8project.reporting.outputEncoding><java.version>1.8java.version><spring-boot.version>2.1.17.RELEASEspring-boot.version>properties><dependencies><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-WEBartifactId>dependency><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-data-jpaartifactId>dependency><dependency><groupId>org.projectlombokgroupId><artifactId>lombokartifactId>dependency><dependency><groupId>mysqlgroupId><artifactId>mysql-connector-javaartifactId><scope>runtimescope>dependency><dependency><groupId>com.damenggroupId><artifactId>DmJdbcDriver18artifactId><version>8.1.2.192version>dependency><dependency><groupId>com.damenggroupId><artifactId>DmDialect-for-hibernate5.3artifactId><version>8.1.2.192version>dependency>dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-dependenciesartifactId><version>${spring-boot.version}version><type>pomtype><scope>importscope>dependency>dependencies>dependencyManagement><build><plugins><plugin><groupId>org.apache.maven.pluginsgroupId><artifactId>maven-compiler-pluginartifactId><configuration><source>1.8source><target>1.8target>configuration>plugin>plugins>build>project>

实体部分（Entity）

主数据源：

package org.test.data.entity.primary;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Table;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.ToString;@Data@ToString(callSuper = true)@NoArgsConstructor@AllArgsConstructor@Entity@Table(name = "test")public class Test {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Integer id;private String name;}

第二数据源：

package org.test.data.entity.second;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Table;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.ToString;@Data@ToString(callSuper = true)@NoArgsConstructor@AllArgsConstructor@Entity@Table(name = "test2")public class Test2 {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Integer id;private String name;}

数据库接口部分（Repository）

主数据源：

package org.test.dao.primary;import org.springframework.stereotype.Repository;import org.test.data.entity.primary.Test;import org.springframework.data.jpa.repository.JpaRepository;@Repositorypublic interface TestRepository extends JpaRepository<Test, Integer> {}

第二数据源：

package org.test.dao.second;import org.springframework.stereotype.Repository;import org.test.data.entity.second.Test2;import org.springframework.data.jpa.repository.JpaRepository;@Repositorypublic interface TestRepository2 extends JpaRepository<Test2, Integer> {}

服务接口部分（Service）

此处略，可按实际项目关联，服务部分可无需区分包路径

配置部分（Configuration）

此处将配置数据库配置读取、数据源、EntityManager构造工厂以及事务管理器
主数据源：

package org.test.property.config;import java.util.HashMap;import javax.persistence.EntityManagerFactory;import javax.sql.DataSource;import org.springframework.beans.factory.annotation.Qualifier;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.ComponentScan.Filter;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.FilterType;import org.springframework.context.annotation.Primary;import org.springframework.data.jpa.repository.config.EnableJpaRepositories;import org.springframework.orm.jpa.JpaTransactionManager;import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;import org.springframework.transaction.PlatformTransactionManager;import org.springframework.transaction.annotation.EnableTransactionManagement;import org.springframework.util.StringUtils;import com.zaxxer.hikari.HikariDataSource;@Configuration@EnableTransactionManagement // 开启事务@EnableJpaRepositories(basePackages = { "org.test.dao.primary" },entityManagerFactoryRef = "primaryEntityManagerFactory", transactionManagerRef = "primaryTransactionManager" )public class TestPrimaryDataSourceConfig {@Primary@Bean(name = "primaryDataSourceProperties")@ConfigurationProperties("spring.datasource.primary") public DataSourceProperties dataSourceProperties() {return new DataSourceProperties();}@Primary@Bean(name = "primaryDataSource")@ConfigurationProperties(prefix = "spring.datasource.hikari.primary") // 配置// primary// 数据源所⽤的// hikari// 配置// key// 的前缀public DataSource dataSource(@Qualifier("primaryDataSourceProperties") DataSourceProperties primaryDataSourceProperties) {HikariDataSource dataSource = primaryDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();if (StringUtils.hasText(primaryDataSourceProperties.getName())) {dataSource.setPoolName(primaryDataSourceProperties.getName());}return dataSource;}@Primary@Bean(name = "primaryEntityManagerFactory")public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder,@Qualifier("primaryDataSource") DataSource primaryDataSource) {final HashMap<String, Object> hibernateProperties = new HashMap<String, Object>();hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");return builder.dataSource(primaryDataSource).properties(hibernateProperties)// primary 数据的实体所在的路径.packages("org.test.data.entity.primary")// persistenceUnit 的名字采⽤ primary.persistenceUnit("primary").build();}@Primary@Bean(name = "primaryTransactionManager")public PlatformTransactionManager transactionManager(@Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {return new JpaTransactionManager(primaryEntityManagerFactory);}}

第二数据源：

package org.test.property.config;import java.util.HashMap;import javax.persistence.EntityManagerFactory;import javax.sql.DataSource;import org.springframework.beans.factory.annotation.Qualifier;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.ComponentScan.Filter;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.FilterType;import org.springframework.data.jpa.repository.config.EnableJpaRepositories;import org.springframework.orm.jpa.JpaTransactionManager;import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;import org.springframework.transaction.PlatformTransactionManager;import org.springframework.transaction.annotation.EnableTransactionManagement;import org.springframework.util.StringUtils;import com.zaxxer.hikari.HikariDataSource;@Configuration@EnableTransactionManagement // 开启事务@EnableJpaRepositories( // 利⽤ EnableJpaRepositories 配置哪些包下⾯的 Repositories，采⽤哪个// EntityManagerFactory 和哪个 TransactionManagementbasePackages = { "org.test.dao.second" },entityManagerFactoryRef = "secondEntityManagerFactory", transactionManagerRef = "secondTransactionManager" )public class TestSecondDataSourceConfig {@Bean(name = "secondDataSourceProperties")// @Qualifier(value = "secondDataSourceProperties")@ConfigurationProperties("spring.datasource.second") // second 数据源的配置前缀采⽤// spring.datasource.secondpublic DataSourceProperties secondDataSourceProperties() {return new DataSourceProperties();}@Bean(name = "secondDataSource")// @Qualifier(value = "secondDataSource")@ConfigurationProperties(prefix = "spring.datasource.hikari.second") // 配置// second// 数据源所⽤的// hikari// 配置// key// 的前缀public DataSource dataSource() {DataSourceProperties secondDataSourceProperties = secondDataSourceProperties();HikariDataSource dataSource = secondDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();if (StringUtils.hasText(secondDataSourceProperties.getName())) {dataSource.setPoolName(secondDataSourceProperties.getName());}return dataSource;}@Bean(name = "secondEntityManagerFactory")// @Qualifier(value = "secondEntityManagerFactory")public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) {final HashMap<String, Object> hibernateProperties = new HashMap<String, Object>();hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.DmDialect");return builder.dataSource(dataSource()).properties(hibernateProperties)// second 数据的实体所在的路径.packages("org.test.data.entity.second")// persistenceUnit 的名字采⽤ second.persistenceUnit("second").build();}@Bean(name = "secondTransactionManager")// @Qualifier(value = "")public PlatformTransactionManager transactionManager(@Qualifier("secondEntityManagerFactory") EntityManagerFactory secondEntityManagerFactory) {return new JpaTransactionManager(secondEntityManagerFactory);}}

配置文件部分（application.properties）

如需更为yml，可自行转换。
其中数据库连接池为观察效果用，可根据实际自行配置

####################################################jpa setting##############################################JPA Configuration:  # Show or not log for each sql queryspring.jpa.show-sql=truespring.jpa.generate-ddl=true# Hibernate ddl auto (create, create-drop, update)spring.jpa.hibernate.ddl-auto=update#spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect  spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy# 主数据源配置spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.primary.url=spring.datasource.primary.username=spring.datasource.primary.passWord=# 主数据源连接池配置（主要为观察多数据源配置效果，可按实际配置或不配置）# spring.datasource.hikari.primary.pool-name=jpa-hikari-pool-primary# spring.datasource.hikari.primary.max-lifetime=900000# spring.datasource.hikari.primary.maximum-pool-size=8spring.datasource.second.driver-class-name=dm.jdbc.driver.DmDriverspring.datasource.second.url=spring.datasource.second.username=spring.datasource.second.password=# 第二数据源连接池配置（主要为观察多数据源配置效果，可按实际配置或不配置）# spring.datasource.hikari.second.pool-name=jpa-hikari-pool-second# spring.datasource.hikari.second.max-lifetime=500000# spring.datasource.hikari.second.maximum-pool-size=6

Controller部分（测试）

此为测试效果用，不推荐直接在controller层调用dao层写法。

package org.test.controller;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import org.test.data.entity.primary.Test;import org.test.service.TestService;@RestController@RequestMapping("/test")public class TestController {@Autowiredprivate TestRepository testRepository;@Autowiredprivate TestRepository2 testRepository2;@GetMappingpublic String test(String name) {testRepository.save(new Test(null, name));testRepository2.save(new Test2(null, name));return "ok";}}

至此，便完成全部配置！

可能碰见的问题

1. 报语法不匹配/不支持问题“org.hibernate.HibernateException：对DialectResolutionInfo的访问在’hibernate.dialect’未设置时不能为空”

在EntityManager工厂配置LocalContainerEntityManagerFactoryBean中添加方言指定：

hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.DmDialect");

详情可查看**配置部分（Configuration）**中TestSecondDataSourceConfig相关代码。

参考文章：使用多个数据源时，Spring boot Hibernate错误“当“Hibernate.dialent”未设置时，对方言解析信息的访问不能为null” - 一点教程

2. 报“仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值”问题

在版本8.1.1.49中，可通过将主键字段序列设置为@GeneratedValue(strategy = GenerationType.AUTO)解决
在版本8.1.2.192中，该问题已被解决。注意，新版本驱动更改了artifactId，详情如下：
旧：

<dependency><groupId>com.damenggroupId><artifactId>Dm8JdbcDriver18artifactId><version>8.1.1.49version>dependency><dependency><groupId>com.damenggroupId><artifactId>DmDialect-for-hibernate5.3artifactId><version>8.1.1.49version>dependency>

新：

<dependency><groupId>com.damenggroupId><artifactId>DmJdbcDriver18artifactId><version>8.1.2.192version>dependency><dependency><groupId>com.damenggroupId><artifactId>DmDialect-for-hibernate5.3artifactId><version>8.1.2.192version>dependency>