一、说明
- 公司要迁移历史数据到另一个数据库中，历史数据只能由Excel导出提供。因此写了这个小工具用于将Excel中的数据拼接为INSERT语句，用于项目初始化时一次性导入。
- Excel表头中的字段与表中字段映射关系由配置文件进行匹配，支持spEL表达式。
二、相关代码
- 主要的maven依赖
xml
代码解读
复制代码
<!-- easyexcel框架，用于读取excel中的数据 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
<!-- lombok减少代码量，业务逻辑中没有使用，可不用 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<!-- hutool一些静态工具，业务逻辑中没有使用，可不用 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.5</version>
</dependency>
- Excel2SqlUtils.java //功能入口，包含main函数
java
代码解读
复制代码
import com.alibaba.excel.EasyExcel;
import com.xxx.xx.admin.xx.manager.listener.Excel2SqlListener;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.yaml.snakeyaml.Yaml;
import org.yaml.snakeyaml.constructor.Constructor;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;
import java.util.Objects;
@Data
public class Excel2SqlUtils {
private String configYamlPath;
private String db;
private String table;
private List<Entry> entryList;
@Data
public static class Entry {
private String colName;
private String sqlField;
private String defaultValue;
}
public static void main(String[] args) throws FileNotFoundException {
// 历史数据路径，必须是excel文件。可以填写绝对路径
String excelPath = "doc/config/file/model.xlsx";
// 配置文件路径，excel表头和数据库字段映射关系。可以填写绝对路径
String configPath = "doc/config/model.yml";
// 生成的sql文件路径。可以填写绝对路径
String outPath = "doc/db/model.sql";
EasyExcel.read(excelPath, new Excel2SqlListener(configPath, outPath)).sheet().doRead();
}
/**
* 解析配置文件，获取映射关系
* @param configYamlPath 配置文件路径
*/
public static Excel2SqlUtils load(String configYamlPath) throws FileNotFoundException {
Objects.requireNonNull(configYamlPath, "config yaml path can not be empty");
Yaml yaml = new Yaml(new Constructor(Excel2SqlUtils.class));
InputStream in = new FileInputStream(configYamlPath);
Excel2SqlUtils csvMapConfig = yaml.load(in);
Objects.requireNonNull(csvMapConfig, "yaml load error");
Objects.requireNonNull(csvMapConfig.getEntryList(), "config entryList can not be empty");
csvMapConfig.getEntryList().forEach(entry -> {
Objects.requireNonNull(entry.getSqlField(), "the sql field of entryList item can not be null");
});
csvMapConfig.setConfigYamlPath(configYamlPath);
return csvMapConfig;
}
/**
* 获取默认值、置换spEL表达式的值
* @param defaultValue 默认值
*/
public static String getDefaultValue(String defaultValue) {
if (StringUtils.isBlank(defaultValue)) {
return "null";
}
if ((defaultValue).startsWith("${")) {
// 创建 SpEL 表达式解析器
ExpressionParser parser = new SpelExpressionParser();
defaultValue = defaultValue.replace("${", "").replace("}", "");
Expression expression = parser.parseExpression(defaultValue);
return String.valueOf(expression.getValue());
}
return defaultValue;
}
}
- Excel2SqlListener.java // 拼接的主要业务逻辑，这里用了easyexcel包
java
代码解读
复制代码
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.xxx.xx.admin.util.Excel2SqlUtils;
import lombok.SneakyThrows;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;
/**
* 解析Excel文件，拼接成sql语句
*/
public class Excel2SqlListener extends AnalysisEventListener<Map<Integer, String>> {
private static final int BATCH_COUNT = 1000;
List<Map<Integer, String>> list = new ArrayList<>();
String configPath;
private Excel2SqlUtils csvMapConfig;
Map<String, Integer> excelHeadMap = new HashMap<>();
// 配置文件中的字段映射
Map<String, Excel2SqlUtils.Entry> col2Entry = new LinkedHashMap<>();
StringBuilder sqlHeadBuilder = new StringBuilder();
StringBuilder sqlValueBuilder = new StringBuilder();
String outPath;
FileWriter fileWriter;
public Excel2SqlListener(String configPath, String outPath) {
this.configPath = configPath;
this.outPath = outPath;
}
/**
* 获取表头，读取excel数据之前执行一次
* @param headMap 表头信息
* @param context 上下文
*/
@SneakyThrows
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// 表头封装
for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
String key = entry.getValue();
Integer value = entry.getKey();
excelHeadMap.put(key, value);
}
// 获取配置文件中的字段映射
this.csvMapConfig = Excel2SqlUtils.load(configPath);
// 输出文件流
fileWriter = new FileWriter(outPath);
String path = csvMapConfig.getConfigYamlPath();
fileWriter.write("-- 初始化 " + path.split("/")[2].split("\\.")[0] + " 数据");
// 配置文件中的字段映射关系封装
for (Excel2SqlUtils.Entry entry : csvMapConfig.getEntryList()) {
col2Entry.put(entry.getColName(), entry);
}
// 拼接INSERT INTO语句前部分
sqlHeadBuilder.append("INSERT INTO ")
// 数据库名,是否添加看业务逻辑
// .append(sqlKeywordWrap(dbName)).append(".")
.append(sqlKeywordWrap(this.csvMapConfig.getTable()));
sqlHeadBuilder.append(" (");
for (Excel2SqlUtils.Entry fieldEntry : col2Entry.values()) {
sqlHeadBuilder.append(sqlKeywordWrap(fieldEntry.getSqlField()));
sqlHeadBuilder.append(",");
}
sqlHeadBuilder.deleteCharAt(sqlHeadBuilder.length() - 1);
sqlHeadBuilder.append(") VALUES");
}
/**
* 按照行读取excel数据，每行执行一次
* @param data 每行数据
* @param context 上下文
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
sqlValueBuilder.append("\n (");
// 根据配置文件中的字段，去获取excel文档中的值
for (Map.Entry<String, Excel2SqlUtils.Entry> configEntry : col2Entry.entrySet()) {
String keyConfig = configEntry.getKey();
Excel2SqlUtils.Entry valueConfig = configEntry.getValue();
// excel文档中的字段值
String valueExcel = data.get(excelHeadMap.get(keyConfig));
// 配置文档中的默认值
if (valueExcel == null) {
valueExcel = Excel2SqlUtils.getDefaultValue(valueConfig.getDefaultValue());
} else if (valueExcel.contains("'")) {
// 如果字符串中包含单引号，要进行转义
valueExcel = valueExcel.replace("'", "''");
}
sqlValueBuilder.append(sqlValWrap(valueExcel));
sqlValueBuilder.append(",");
}
sqlValueBuilder.deleteCharAt(sqlValueBuilder.length() - 1);
sqlValueBuilder.append("),");
// 批量INSERT，一千条封装为一组
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
sqlValueBuilder.setLength(0);
}
}
/**
* 读取excel数据结束执行一次
* @param context 上下文
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 防止最后一批数据不满1000条，需要单独处理
saveData();
System.out.println("所有数据解析完成！解析个数为：" + context.readRowHolder().getRowIndex());
System.out.println("所有数据解析完成！行号为：" + (context.readRowHolder().getRowIndex() + 1));
try {
fileWriter.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
private static String sqlValWrap(String val) {
return "'" + val + "'";
}
private static String sqlKeywordWrap(String keyword) {
return '`' + keyword.trim() + '`';
}
/**
* 输出
*/
private void saveData() {
StringBuilder sqlBuilder = new StringBuilder();
sqlValueBuilder.deleteCharAt(sqlValueBuilder.length() - 1);
sqlValueBuilder.append(";");
sqlBuilder.append(sqlHeadBuilder);
sqlBuilder.append(sqlValueBuilder);
try {
fileWriter.write("\n");
fileWriter.write(String.valueOf(sqlBuilder));
fileWriter.write("\n");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
- model.yml // 配置文件，配置excel表头和表字段的映射关系
yml
代码解读
复制代码
# 数据库名
db: cs_linux
# 表名
table: cs_user
# 数据库字段配置
entryList:
# excel中的表头列名
- colName: 职员工号
# 数据库字段名，不能为空
sqlField: personnel_id
- colName: 部门代码
sqlField: dept_id
- colName: 工作类型代码
sqlField: work_type_cd
- colName: 创建人
sqlField: created_by
defaultValue: 'system'
- colName: 创建时间
sqlField: created_at
# 默认值，当表中数据为空，或者字段不存在时，填入默认值。支持spEl表达式
defaultValue: "${T(cn.hutool.core.date.DateUtil).now()}"
- colName: 修改人
sqlField: updated_by
defaultValue: 'system'
- colName: 修改日期
sqlField: updated_at
defaultValue: "${T(cn.hutool.core.date.DateUtil).now()}"
- colName: UID
sqlField: uuid
defaultValue: "${T(java.util.UUID).randomUUID().toString()}"
三、注意
- 我这边的需求只是初始化执行一次，因此没有写很复杂的功能。可以根据自身需求进行修改。
