多数据源配置H2 Mysql

需求背景

最近有一需求，原本项目中由于某些原因使用嵌入式数据库H2，鉴于嵌入式数据库可靠性以及不方便管理等因素，需要将数据库迁移到Mysql。

环境说明

SpringBoot：3.0.2
JDK：17
H2：2.1.214
spring-boot-starter-data-jpa：3.0.2

Mysql：8.0.32

实现过程

配置调整

原配置

pom.xml

<!-- 省略其他依赖... -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
    <scope>runtime</scope>
</dependency>

dev.yml

spring:
  datasource:
    url: jdbc:h2:file:./data/cloak-ab
    driver-class-name: org.h2.Driver
    username: root
    password: root789456
  jpa:
    database: h2
    hibernate:
      ddl-auto: update
    show-sql: true
  h2:
    console:
      path: /h2-console
      enabled: true
      settings:
        web-allow-others: true
        trace: true

修改配置

修改后pom.xml

<!-- 省略其他依赖... -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.32</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
    <scope>runtime</scope>
</dependency>

修改后dev.yml

spring:
  datasource:
    url: jdbc:mysql://192.168.0.80:3306/cloak_ab?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root789456
  jpa:
  	# 此处有修改
    database: mysql
    hibernate:
      ddl-auto: update
    show-sql: true
  h2:
    console:
      path: /h2-console
      enabled: true
      settings:
        web-allow-others: true
        trace: true

主要修改点有三处：

①添加Mysql连接依赖
②数据库的连接地址和数据库驱动
③jpa使用的数据库

代码调整

新增DatasourceConfig配置类

配置Mysql主数据源，H2次数据源
DatasourceConfig.java

import lombok.Data;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DatasourceConfig {

    @Data
    @Configuration
    @ConfigurationProperties(prefix="spring.datasource")
    public static class MasterDatasourceProperties {
        private String url;
        private String driverClassName;
        private String username;
        private String password;
    }

    @Bean
    @Primary
    public DataSource dataSource(MasterDatasourceProperties masterDatasourceProperties) {
        return DataSourceBuilder.create()
                .driverClassName(masterDatasourceProperties.getDriverClassName())
                .url(masterDatasourceProperties.getUrl())
                .username(masterDatasourceProperties.getUsername())
                .password(masterDatasourceProperties.getPassword())
                .build();
    }

    @Primary
    @Bean(name = "jdbcTemplate")
    public JdbcTemplate jdbcTemplate(@Qualifier("dataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create()
                .url("jdbc:h2:file:./data/cloak-ab")
                .driverClassName("org.h2.Driver")
                .username("root")
                .password("root789456")
                .build();
    }

    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        return new JdbcTemplate(secondaryDataSource);
    }

}

Mysql主数据源添加@Primary注解，jpa使用的默认是主数据源，如此一来jpa操作的就是Mysql数据库了。

使用secondaryJdbcTemplate

	@Autowired
    private AppParamDao appParamDao;
	@Autowired
    @Qualifier("secondaryJdbcTemplate")
    protected JdbcTemplate secondaryJdbcTemplate;

	@Override
    public void h2ToMysql() {
        String sql = "select * from app_param";
        List<AppParam> appParams = secondaryJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AppParam.class));
        appParamDao.saveAllAndFlush(appParams);
    }

此时secondaryJdbcTemplate操作的就是H2数据库，而appParamDao操作的就是Mysql数据库

AppParamDao.java

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;
import java.util.Set;

@Repository
public interface AppParamDao extends CrudRepository<AppParam, Long>, JpaRepository<AppParam, Long> {
}

遇到的坑

坑1：在实体类中属性名与数据库字段名不一致

不知道为什么，总能碰到一些“阴间操作”，实体类中的属性名称与数据库中的字段名不一样，导致在使用secondaryJdbcTemplate查询的时候，实体类的属性值为null…如此一来，在执行数据插入的时候，这些字段值就为null

appParamDao.saveAllAndFlush(appParams);

如下，数据库字段名为：param_value，但是实体类属性名是：value

@EntityListeners(AuditingEntityListener.class)
@Schema(description = "App配置参数信息")
@Table( indexes = {
		@Index(name = "idx_app_param_bundle_id", columnList = "bundle_id")
})
@Entity(name = "app_param")
@Data
public class AppParam implements Serializable {
	@Serial
	private static final long serialVersionUID = 8398910406652563675L;
	// ... 省略其他属性
	/**
	* 参数值
	*/
	@Schema(description = "参数值")
	@Column(name = "param_value")
	private String value;

	/**
	 * 创建者
	 */
	@CreatedBy
	@Schema(description = "创建者")
	private String createBy;

	/**
	 * 创建日期
	 */
	@CreatedDate
	@Schema(description = "创建日期")
	@Column(updatable = false)
	private LocalDateTime createDate;

	/**
	 * 更新者
	 */
	@LastModifiedBy
	@Schema(description = "更新者")
	private String updateBy;

	/**
	 * 更新日期
	 */
	@LastModifiedDate
	@Schema(description = "更新日期")
	private LocalDateTime updateDate;

}

解决办法1：将实体类中属性名改成与数据库字段名一致

例如：

@EntityListeners(AuditingEntityListener.class)
@Schema(description = "App配置参数信息")
@Table( indexes = {
		@Index(name = "idx_app_param_bundle_id", columnList = "bundle_id")
})
@Entity(name = "app_param")
@Data
public class AppParam implements Serializable {
	@Serial
	private static final long serialVersionUID = 8398910406652563675L;
	/**
	* 参数值
	*/
	@Schema(description = "参数值")
	@Column(name = "param_value")
	private String paramValue;
}

该解决方式缺点：
①可能会导致修改的代码比较多，因为有其他使用到该字段的地方都要同时修改
②倘若某些地方使用了诸如BeanUtils的Bean拷贝工具，会导致copy的类中属性值为空，容易引发隐藏bug

解决办法2：实体类中添加与数据库字段名一致的属性

例如：

@EntityListeners(AuditingEntityListener.class)
@Schema(description = "App配置参数信息")
@Table( indexes = {
		@Index(name = "idx_app_param_bundle_id", columnList = "bundle_id")
})
@Entity(name = "app_param")
@Data
public class AppParam implements Serializable {
	@Serial
	private static final long serialVersionUID = 8398910406652563675L;
	/**
	* 参数值
	*/
	@Schema(description = "参数值")
	@Column(name = "param_value")
	private String value;

	@Transient
	@Deprecated
	private String paramValue;

	/**
	 * 创建者
	 */
	@CreatedBy
	@Schema(description = "创建者")
	private String createBy;

	/**
	 * 创建日期
	 */
	@CreatedDate
	@Schema(description = "创建日期")
	@Column(updatable = false)
	private LocalDateTime createDate;

	/**
	 * 更新者
	 */
	@LastModifiedBy
	@Schema(description = "更新者")
	private String updateBy;

	/**
	 * 更新日期
	 */
	@LastModifiedDate
	@Schema(description = "更新日期")
	private LocalDateTime updateDate;
}

必须添加@Transient注解，否则启动时jpa会报错，大概意思就是有多个属性引用相同列

Caused by: org.hibernate.DuplicateMappingException: Table [app_param] contains physical column name [param_value] referred to by multiple logical column names: [param_value], [paramValue]
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl$TableColumnNameBinding.bindPhysicalToLogical(InFlightMetadataCollectorImpl.java:1055)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl$TableColumnNameBinding.addBinding(InFlightMetadataCollectorImpl.java:1024)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.addColumnNameBinding(InFlightMetadataCollectorImpl.java:1094)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.addColumnNameBinding(InFlightMetadataCollectorImpl.java:1075)
	at org.hibernate.cfg.AnnotatedColumn.addColumnBinding(AnnotatedColumn.java:473)
	at org.hibernate.cfg.AnnotatedColumn.linkWithValue(AnnotatedColumn.java:436)
	at org.hibernate.cfg.annotations.BasicValueBinder.linkWithValue(BasicValueBinder.java:1134)
	at org.hibernate.cfg.annotations.BasicValueBinder.make(BasicValueBinder.java:1109)
	at org.hibernate.cfg.annotations.PropertyBinder.makePropertyAndValue(PropertyBinder.java:202)
	at org.hibernate.cfg.annotations.PropertyBinder.makePropertyValueAndBind(PropertyBinder.java:229)
	at org.hibernate.cfg.AnnotationBinder.bindBasic(AnnotationBinder.java:1455)
	at org.hibernate.cfg.AnnotationBinder.buildProperty(AnnotationBinder.java:1251)
	at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1112)
	at org.hibernate.cfg.annotations.EntityBinder.processIdPropertiesIfNotAlready(EntityBinder.java:935)
	at org.hibernate.cfg.annotations.EntityBinder.bindEntityClass(EntityBinder.java:273)
	at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:556)
	at org.hibernate.boot.model.source.internal.annotations.AnnotationMetadataSourceProcessorImpl.processEntityHierarchies(AnnotationMetadataSourceProcessorImpl.java:216)
	at org.hibernate.boot.model.process.spi.MetadataBuildingProcess$1.processEntityHierarchies(MetadataBuildingProcess.java:247)
	at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:290)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1350)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1421)
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:66)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:376)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:352)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1797)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1747)
	... 126 common frames omitted

用transient关键字标记的成员变量不参与序列化过程
将不需要序列化的属性前添加关键字transient，序列化对象的时候，这个属性就不会序列化到指定的位置

然后再插入数据时手动将paramValue的值set给value

@Override
public void h2ToMysql() {
	String sql = "select * from app_param";
	List<AppParam> appParams = secondaryJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AppParam.class));
	for (AppParam appParam : appParams) {
		appParam.setValue(appParam.getParamValue());
	}
	appParamDao.saveAllAndFlush(appParams);
}

坑2：与jap审计功能冲突

如上，可以看到 AppParam 实体类上有添加注解：

@EntityListeners(AuditingEntityListener.class)

该注解通常与：@CreatedBy、@CreatedDate、@LastModifiedBy、@LastModifiedDate注解一起使用，即在插入数据时自动设置创建时间、创建者；在更新数据时，自动设置更新时间、更新者，可以减少很多重复代码。

当然，使用审计功能，要在启动类上添加启用审计功能的注解：@EnableJpaAuditing和实现默认的AuditorAware

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

DefaultAuditorAware .java

@Configuration
public class DefaultAuditorAware implements AuditorAware<String> {

    @Override
    public Optional<String> getCurrentAuditor() {
        SysUser sysUser = SecurityUtils.getSysUser();
        return Optional.ofNullable(sysUser).map(SysUser::getUserNo);
    }
}

此类的作用是告诉jpa获取当前用户的方式，这里使用的SecurityUtils是结合了Spring Security的工具类。各位看官可以自行实现

问题：在执行数据插入代码时，审计功能自动设置创建者、创建时间；但此时这种效果并不是我们想看见的…因为旧数据已经存在的值，我们不想它发生变化

appParamDao.saveAllAndFlush(appParams);

解决办法：自定义CustomAuditingEntityListener

CustomAuditingEntityListener.java

import jakarta.persistence.PrePersist;
import jakarta.persistence.PreUpdate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@Configurable
public class CustomAuditingEntityListener {

	// 标识是否需要jpa的审计
    public static Boolean custom = false;

    @Autowired
    private AuditingEntityListener auditingEntityListener;

    @PrePersist
    public void touchForCreate(Object target) {
        if (!custom) {
            auditingEntityListener.touchForCreate(target);
        }
    }

    @PreUpdate
    public void touchForUpdate(Object target) {
        if (!custom) {
            auditingEntityListener.touchForUpdate(target);
        }
    }
}

实体类AppParam.java
将AuditingEntityListener换成CustomAuditingEntityListener

@EntityListeners(CustomAuditingEntityListener.class)
@Schema(description = "App配置参数信息")
@Table( indexes = {
		@Index(name = "idx_app_param_bundle_id", columnList = "bundle_id")
})
@Entity(name = "app_param")
@Data
public class AppParam implements Serializable {
	@Serial
	private static final long serialVersionUID = 8398910406652563675L;

	/**
	 * 参数值
	 */
	@Schema(description = "参数值")
	@Column(name = "param_value")
	private String value;

	@Transient
	@Deprecated
	private String paramValue;

	/**
	 * 创建者
	 */
	@CreatedBy
	@Schema(description = "创建者")
	private String createBy;

	/**
	 * 创建日期
	 */
	@CreatedDate
	@Schema(description = "创建日期")
	@Column(updatable = false)
	private LocalDateTime createDate;

	/**
	 * 更新者
	 */
	@LastModifiedBy
	@Schema(description = "更新者")
	private String updateBy;

	/**
	 * 更新日期
	 */
	@LastModifiedDate
	@Schema(description = "更新日期")
	private LocalDateTime updateDate;
}

此时只要在执行插入数据之前设置CustomAuditingEntityListener中custom属性的值就可以了

appParamDao.saveAllAndFlush(appParams);

代码如下：

@Override
public void h2ToMysql() {
	String sql = "select * from app_param";
	List<AppParam> appParams = secondaryJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AppParam.class));
	for (AppParam appParam : appParams) {
	    appParam.setValue(appParam.getParamValue());
	}
	CustomAuditingEntityListener.custom = true;
	appParamDao.saveAllAndFlush(appParams);
	CustomAuditingEntityListener.custom = false;
}