目录
- 版本信息/主要引包信息
- 背景概述
- 代码示例
- 可能碰见的问题
- 1. 报语法不匹配/不支持问题“org.hibernate.HibernateException:对DialectResolutionInfo的访问在'hibernate.dialect'未设置时不能为空”
- 2. 报“仅当指定列列表,且SET IDENTITY_INSERT为ON时,才能对自增列赋值”问题
- 3. 启动时报“org.hibernate.engine.jdbc.spi.TypeInfo : HHH000362: Unable to retrieve type info result set : dm.jdbc.driver.DMException: 第 1 行, 第 270 列[AUTO_INCREMENT]附近出现错误”问题
- 参考文章
关键词:mysql 达梦/dameng jpa 多数据源
版本信息/主要引包信息
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.0</modelVersion>
<groupId>org</groupId>
<artifactId>test-jpa-tra</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>test-jpa-tra</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<spring-boot.version>2.1.17.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- 数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmJdbcDriver18</artifactId>
<version>8.1.2.192</version>
</dependency>
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmDialect-for-hibernate5.3</artifactId>
<version>8.1.2.192</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</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;
@Repository
public 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;
@Repository
public 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 数据源的 dataSource 配置
*
* @return primary 数据源配置
*/
@Primary
@Bean(name = "primaryDataSourceProperties")
@ConfigurationProperties("spring.datasource.primary")
public DataSourceProperties dataSourceProperties() {
return new DataSourceProperties();
}
/**
* 可以选择不同的数据源,这⾥使⽤ HikariDataSource,创建数据源
*
* @param primaryDataSourceProperties
* 数据源配置
* @return primary 数据源
*/
@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 数据源的 entityManagerFactory 命名为
* primaryEntityManagerFactory,⽤来对实体进⾏⼀些操作
*
* @param builder
* 构建器
* @param primaryDataSource
* primary 数据源
* @return primary 实体管理工厂
*/
@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 数据源的事务管理者,命名为 primaryTransactionManager 依赖
* primaryEntityManagerFactory
*
* @param primaryEntityManagerFactory
* primary 实体管理工厂
* @return primary 事务管理者
*/
@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 和哪个 TransactionManagement
basePackages = { "org.test.dao.second" },
entityManagerFactoryRef = "secondEntityManagerFactory",
transactionManagerRef = "secondTransactionManager"
)
public class TestSecondDataSourceConfig {
/**
* 指定 second 数据源的 dataSource 配置
*
* @return second 数据源配置
*/
@Bean(name = "secondDataSourceProperties")
// @Qualifier(value = "secondDataSourceProperties")
@ConfigurationProperties("spring.datasource.second") // second 数据源的配置前缀采⽤
// spring.datasource.second
public DataSourceProperties secondDataSourceProperties() {
return new DataSourceProperties();
}
/**
* 可以选择不同的数据源,这⾥使⽤ HikariDataSource,创建数据源
*
* @param secondDataSourceProperties
* 数据源配置
* @return second 数据源
*/
@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;
}
/**
* 配置 second 数据源的 entityManagerFactory 命名为
* secondEntityManagerFactory,⽤来对实体进⾏⼀些操作
*
* @param builder
* 构建器
* @param secondDataSource
* second 数据源
* @return second 实体管理工厂
*/
@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();
}
/**
* 配置 second 数据源的事务管理者,命名为 secondTransactionManager 依赖
* secondEntityManagerFactory
*
* @param secondEntityManagerFactory
* second 实体管理工厂
* @return second 事务管理者
*/
@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 query
spring.jpa.show-sql=true
spring.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.Driver
spring.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=8
spring.datasource.second.driver-class-name=dm.jdbc.driver.DmDriver
spring.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 {
@Autowired
private TestRepository testRepository;
@Autowired
private TestRepository2 testRepository2;
@GetMapping
public 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.dameng</groupId>
<artifactId>Dm8JdbcDriver18</artifactId>
<version>8.1.1.49</version>
</dependency>
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmDialect-for-hibernate5.3</artifactId>
<version>8.1.1.49</version>
</dependency>
新:
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmJdbcDriver18</artifactId>
<version>8.1.2.192</version>
</dependency>
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmDialect-for-hibernate5.3</artifactId>
<version>8.1.2.192</version>
</dependency>
3. 启动时报“org.hibernate.engine.jdbc.spi.TypeInfo : HHH000362: Unable to retrieve type info result set : dm.jdbc.driver.DMException: 第 1 行, 第 270 列[AUTO_INCREMENT]附近出现错误”问题
解决方法如问题2解决方法2即可。
参考文章
Spring Data JPA 之 多数据源配置_jpa 多数据源_曾小二的秃头之路的博客-CSDN博客
使用多个数据源时,Spring boot Hibernate错误“当“Hibernate.dialent”未设置时,对方言解析信息的访问不能为null” - 一点教程
spring boot mysql8 迁移到达梦报错[AUTO_INCREMENT]附近出现错误_wdd668的博客-CSDN博客
dm.jdbc.driver.DMException: 第 1 行, 第 270 列[AUTO_INCREMENT]附近出现错误:语法分析出错 | 达梦技术社区