需求背景
最近有一需求,原本项目中由于某些原因使用嵌入式数据库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属性的值就可以了文章来源:https://www.toymoban.com/news/detail-804162.html
appParamDao.saveAllAndFlush(appParams);
代码如下:文章来源地址https://www.toymoban.com/news/detail-804162.html
@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;
}
到了这里,关于多数据源配置H2 Mysql的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!