SpringBoot自定义初始化sql文件 支持多类型数据库

我在resources目录下有init.sql初始化sql语句

指定sql文件的地址

sql内容如下：

/*角色表*/
INSERT INTO #{schema}ccc_base_role (id, create_time, create_user_id, is_delete, role_name, status, update_time, update_user_id) VALUES('b89e30d81acb88448d412b7b196bf02e', '1710896575106', 'f1fe2f289b8646ced1bc0294a2bf6d09', 0, '超级管理员', 0, '1714467132605', 'f1fe2f289b8646ced1bc0294a2bf6d09');

• #{schema}用来指定数据库的视图 因为国产有些数据库新增数据时想要指定视图

java初始化代码如下：

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zhou.starter.jpacomment.service.AlterCommentService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.List;/*** @author lcz* @desc 数据初始化器* @mail [e-mail]* @date 2024/05/11*/@Component
@Slf4j
public class DataInitializerConfig {@Resourceprivate JdbcTemplate jdbcTemplate;@Autowiredprivate ResourceLoader resourceLoader;@Resourceprivate DataInitializerMapper dataInitializerMapper;@Resourceprivate AlterCommentService alterCommentService;//这里是sql文件的目录@Value("${init.file}")String initFile;//存在多个服务共同使用 就用服务名进行判断是否初始化@Value("${spring.application.name}")String serviceName;@PostConstructpublic void initData() {//判断是否执行初始化sql文件List<DataInitializer> dataInitializers = dataInitializerMapper.selectList(new QueryWrapper<DataInitializer>().eq("service_name", serviceName));if (!dataInitializers.isEmpty()) {log.info("数据库已经初始化完成");return;}try {// 读取原始文件BufferedReader reader = new BufferedReader(new InputStreamReader(resourceLoader.getResource(initFile).getInputStream(), StandardCharsets.UTF_8));String line;//获取视图 可以通过命令查询 也可以通过导入我的jpacomment库 自动获取String schema = alterCommentService.getSchema();if (StringUtils.isNotBlank(schema)) {schema += ".";}while ((line = reader.readLine()) != null) {// 替换字符串String newLine = line.replace("#{schema}", schema);//如果newLine以/*开头就是注释文件不执行if (newLine.startsWith("/*")) {continue;}// 注意：每行都是一个完整的SQL语句jdbcTemplate.execute(newLine);}log.info("文件初始化替换完成");// 关闭流reader.close();} catch (IOException e) {throw new ServerException(GlobalErrorCodeConstants.SQL_INIT_ERROR);}//设置系统初始化完成DataInitializer dataInitializer = new DataInitializer();dataInitializer.setServiceName(serviceName);dataInitializerMapper.insert(dataInitializer);log.info("{}数据库初始化信息完成", serviceName);}
}

@Data
@TableName("ccc_base_data_initializer")
@Table(name = "ccc_base_data_initializer")
@Entity
@TableComment("初始化表")
public class DataInitializer implements Serializable {@Id@TableId(type = IdType.ASSIGN_UUID)@ColumnComment("主键id")private String id;@ColumnComment("服务名")private String serviceName;
}

下载开源库 直接打包即可
https://gitee.com/lcz2000/zhou-jpacomment

            <!--数据库表注释--><dependency><groupId>com.zhou</groupId><artifactId>zhou-jpacomment</artifactId><version>1.0.0</version></dependency>