SQL文件转Liquibase Changelog.xml的实现
项目实践中使用了Liquibase对数据库版本进行管理,每次创建表和表结构的改变,都需要编写相应的Changelog.xml文件,如果需求涉及的表很多,那直接不要太酸爽。而SQL是有一定规范的文件,Changelog.xml也一样,这样就尝试想做一个转换的工具。
思路
将SQL文件格式化成结构化对象
然后对应结构化Change对象
闲话少说上代码
依赖
两个包
jsqlparser:把sql文件格式化成结构化对象
liquibase-maven-plugin:结构化Change对象生成xml
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.2</version>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>4.6.2</version>
</dependency>
代码实现
package com.lyu.generator;
import liquibase.change.Change;
import liquibase.change.ColumnConfig;
import liquibase.change.ConstraintsConfig;
import liquibase.change.core.AddPrimaryKeyChange;
import liquibase.change.core.CreateTableChange;
import liquibase.change.core.SetColumnRemarksChange;
import liquibase.change.core.SetTableRemarksChange;
import liquibase.changelog.ChangeSet;
import liquibase.changelog.DatabaseChangeLog;
import liquibase.serializer.core.xml.XMLChangeLogSerializer;
import net.sf.jsqlparser.parser.CCJSqlParser;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.parser.ParseException;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.comment.Comment;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
import java.util.stream.Collectors;
/**
* @Author: Lyu
* @Description: sql文件转换Liquibase Changelog.xml
* @Date: Created in 11:01 2022/8/12
* @Modified By:
*/
public class GeneratorChangeLogXmlUtils {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入SQL的路径,具体到文件,回车结束");
String inputPath = scanner.nextLine();
System.out.println("请输入作者,回车结束");
String author = scanner.nextLine();
System.out.println("请输入生成的xml存放路径,回车结束");
String outputPath = scanner.nextLine();
// execute("D:\\szewec_code\\lei-hr\\src\\main\\resourcesliguibaseltest.sql","author","D:\\2019_szewec");
execute(inputPath, author, outputPath);
scanner.close();
}
/**
* 执行方法
*
* @param inputPath 输入SQL路径
* @param author 作者
* @param outputPath 输出xml路径
*/
private static void execute(String inputPath, String author, String outputPath) {
if (!inputPath.endsWith(".sql")) {
throw new IllegalStateException("文件格式不正确,不是SQL文件");
}
File file = new File(inputPath);
String fileName = file.getName();
try {
DatabaseChangeLog databaseChangeLog = genaratorChangeLog(file, author);
// write output
changeLogXmlWrite(databaseChangeLog, inputPath, outputPath, fileName);
} catch (IOException | ParseException e) {
e.printStackTrace();
}
}
/**
* 生成changelog
*
* @param file SQL文件
* @param author 作者
* @return DatabaseChangeLog
*/
private static DatabaseChangeLog genaratorChangeLog(File file, String author) throws IOException, ParseException {
// 读SQL
CCJSqlParser ccjSqlParser = CCJSqlParserUtil.newParser(new FileInputStream(file));
// 解析成statements
List<Statement> statements = ccjSqlParser.Statements().getStatements();
// ChangeLog
DatabaseChangeLog databaseChangeLog = new DatabaseChangeLog();
List<ChangeSet> changeSets = new ArrayList<>();
for (Statement statement : statements) {
System.out.println("parse statement :" + statement.toString());
if (statement instanceof CreateTable) {
// 创建changeSet
ChangeSet changeSet = getInstanceChangeSet(null, author, databaseChangeLog);
// create table statement
CreateTable createTable = (CreateTable) statement;
CreateTableChange createTableChange = new CreateTableChange();
createTableChange.setTableName(createTable.getTable().getName().replace("\"", "").replace("\'", ""));
createTableChange.setSchemaName(createTable.getTable().getSchemaName().replace("\"", "").replace("\'", ""));
// 处理column
List<ColumnConfig> columnConfigs = createTable.getColumnDefinitions().stream().map(m -> {
ColumnConfig columnConfig = new ColumnConfig();
columnConfig.setName(m.getColumnName().replace("\"", "").replace("\'", ""));
columnConfig.setType(m.getColDataType().toString());
if (CollectionUtils.isNotEmpty(m.getColumnSpecs())) {
ConstraintsConfig constraintsConfig = null;
// primary key
if (hasAttr(m.getColumnSpecs(), "primary") && hasAttr(m.getColumnSpecs(), "key")) {
if (constraintsConfig == null) {
constraintsConfig = new ConstraintsConfig();
}
constraintsConfig.setPrimaryKey(true);
columnConfig.setConstraints(constraintsConfig);
}
// not null
if (hasAttr(m.getColumnSpecs(), "not") && hasAttr(m.getColumnSpecs(), "null")) {
if (constraintsConfig == null) {
constraintsConfig = new ConstraintsConfig();
}
constraintsConfig.setNullable(false);
columnConfig.setConstraints(constraintsConfig);
}
}
return columnConfig;
}).collect(Collectors.toList());
createTableChange.setColumns(columnConfigs);
changeSet.addChange(createTableChange);
// 判断是否有primary key,默认设置第一列
if (!isPrimaryKeyCheck(columnConfigs)) {
AddPrimaryKeyChange addPrimaryKeyChange = new AddPrimaryKeyChange();
addPrimaryKeyChange.setColumnNames(columnConfigs.get(0).getName());
addPrimaryKeyChange.setConstraintName("pk_".concat(createTableChange.getTableName()));
addPrimaryKeyChange.setTableName(createTableChange.getTableName());
changeSet.addChange(addPrimaryKeyChange);
}
changeSets.add(changeSet);
} else if (statement instanceof Comment) {
Comment comment = (Comment) statement;
SetTableRemarksChange setTableRemarksChange = null;
SetColumnRemarksChange setColumnRemarksChange = null;
String tableName;
// 设置表comment
if (comment.getTable() != null && StringUtils.isNotBlank(comment.getTable().getName())) {
setTableRemarksChange = new SetTableRemarksChange();
setTableRemarksChange.setTableName(comment.getTable().getName().replace("\"", "").replace("\'", ""));
setTableRemarksChange.setRemarks(comment.getComment().toString().replace("\"", "").replace("\'", ""));
tableName = setTableRemarksChange.getTableName();
} else {
// 设置column comment
setColumnRemarksChange = new SetColumnRemarksChange();
setColumnRemarksChange.setTableName(comment.getTable().getName().replace("\"", "").replace("\'", ""));
setColumnRemarksChange.setColumnName(comment.getComment().toString().replace("\"", "").replace("\'", ""));
tableName = setColumnRemarksChange.getTableName();
}
// 存在create 相同table加入相同的changeSet,否则新建
if (isEqualsTable(changeSets, tableName)) {
if (setTableRemarksChange != null) {
addExistChange(changeSets, setTableRemarksChange, tableName);
}
if (setColumnRemarksChange != null) {
addExistChange(changeSets, setColumnRemarksChange, tableName);
}
} else {
ChangeSet changeSet = getInstanceChangeSet(null, author, databaseChangeLog);
changeSet.addChange(setTableRemarksChange);
changeSet.addChange(setColumnRemarksChange);
changeSets.add(changeSet);
}
}
}
changeSets.forEach(databaseChangeLog::addChangeSet);
return databaseChangeLog;
}
/**
* 写change文件
* @param databaseChangeLog databaseChangeLog
* @param inputPath 输入路径
* @param outputPath 输出路径
* @param fileName 文件名
*/
private static void changeLogXmlWrite(DatabaseChangeLog databaseChangeLog, String inputPath, String outputPath, String fileName) throws IOException {
// write
XMLChangeLogSerializer xmlChangeLogSerializer = new XMLChangeLogSerializer();
String outName = StringUtils.isBlank(outputPath) ? inputPath.replace(fileName, "").concat(".xml") : outputPath + "\\" + fileName.substring(0, fileName.indexOf(".")).concat(".xml");
FileOutputStream fileOutputStream = new FileOutputStream(new File(outName));
xmlChangeLogSerializer.write(databaseChangeLog.getChangeSets(), fileOutputStream);
}
/**
* 创建ChangeSet对象
*
* @param id key
* @param author 作者
* @param databaseChangeLog databaseChangeLog
* @return ChangeSet
*/
private static ChangeSet getInstanceChangeSet(String id, String author, DatabaseChangeLog databaseChangeLog) {
String key = StringUtils.isBlank(id) ? String.valueOf(System.currentTimeMillis()) : id;
return new ChangeSet(key, author, false, false, "", null, null, true, null, databaseChangeLog);
}
/**
* 相同表归入一个changeSet
* @param changeSets 集合
* @param setTableRemarksChange remark
* @param tableName 表名
*/
private static void addExistChange(List<ChangeSet> changeSets, Change setTableRemarksChange, String tableName) {
if (CollectionUtils.isEmpty(changeSets) || StringUtils.isBlank(tableName)) {
return;
}
ChangeSet temp = null;
Iterator iterator = changeSets.iterator();
while (iterator.hasNext()) {
ChangeSet changeSet = (ChangeSet) iterator.next();
for (Change change : changeSet.getChanges()) {
if (change instanceof CreateTableChange) {
CreateTableChange createTableChange = (CreateTableChange) change;
if (tableName.toLowerCase().equals(createTableChange.getTableName().toLowerCase())) {
temp = getInstanceChangeSet(changeSet.getId(), changeSet.getAuthor(), changeSet.getChangeLog());
for (Change changeSetChange : changeSet.getChanges()) {
temp.addChange(changeSetChange);
}
iterator.remove();
break;
}
}
}
}
if (temp != null) {
temp.addChange(setTableRemarksChange);
changeSets.add(temp);
}
}
/**
* 判断是否相同table
* @param changeSets 集合
* @param tableName 表名
* @return 布尔值
*/
private static boolean isEqualsTable(List<ChangeSet> changeSets, String tableName) {
if (CollectionUtils.isEmpty(changeSets) || StringUtils.isBlank(tableName)) {
return false;
}
for (ChangeSet changeSet : changeSets) {
for (Change change : changeSet.getChanges()) {
if (change instanceof CreateTableChange) {
CreateTableChange createTableChange = (CreateTableChange) change;
if (tableName.toLowerCase().equals(createTableChange.getTableName().toLowerCase())) {
return true;
}
}
}
}
return false;
}
/**
* 处理 columnSpecs
*
* @param columnSpecs columnSpecs
* @param attr 目标字符
* @return 布尔值
*/
private static boolean hasAttr(List<String> columnSpecs, String attr) {
return columnSpecs.stream().anyMatch(item -> item.toLowerCase().endsWith(attr));
}
/**
* 判断是否存在 primary key
*
* @param columnConfigs columns
* @return 布尔值
*/
private static boolean isPrimaryKeyCheck(List<ColumnConfig> columnConfigs) {
return columnConfigs.stream().anyMatch(x -> x.getConstraints() != null && x.getConstraints().isPrimaryKey() != null && x.getConstraints().isPrimaryKey());
}
}
使用任何你熟悉的方式打包成jar,做成批处理bat脚本
%cd%
java -Dfile.encoding=utf-8 -jar GeneratorChangeLogXml.jar
pause
执行效果
最后
以上就是将SQL文件转换成Liquibase Changelog.xml的一种尝试。