From 6b7b2214ef457a953381226dc42354d237d6b295 Mon Sep 17 00:00:00 2001
From: liulin <lin.liu@aliyun.com>
Date: 星期二, 13 八月 2024 00:39:26 +0800
Subject: [PATCH] 导出mysql
---
src/test/java/com/lunhan/xxx/host/GenCodeGauss.java | 248 +++++++++++++++++++++++++++++++++---------------
1 files changed, 169 insertions(+), 79 deletions(-)
diff --git a/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java b/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java
index dc3f35a..8b089c9 100644
--- a/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java
+++ b/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java
@@ -23,7 +23,7 @@
private static final String DB_NAME = "water_basic";
private static final String AUTHOR = "lin.liu";
- private static Connection CONN = null;
+ public static Connection CONN = null;
private static final String DRIVER = "org.postgresql.Driver";
private static final String CONNECTION_STR = "jdbc:postgresql://" + HOST + ":" + PORT + "/" + DB_NAME;
@@ -144,7 +144,9 @@
"import com.lunhan.xxx.entity.dto.*;",
"import com.lunhan.xxx.entity.enums.*;",
"import com.lunhan.xxx.repository.BasicMapperImpl;",
- "import org.springframework.stereotype.Repository;"
+ "import org.springframework.stereotype.Repository;",
+ "",
+ "import java.util.List;"
};
//endregion
@@ -243,7 +245,7 @@
* 只生成以下配置的表
*/
private static final List<String> ONLY_TABLES = Arrays.asList(
- "admin_menus"
+
);
public static void main(String[] args) {
@@ -332,16 +334,18 @@
listColumn.add("(type = IdType.ASSIGN_ID)");
}
}
- String columnAnnotation = "\t@TableField(\"" + name + "\"";
- //忽略最后修改时间这一列的新增和编辑
+ if (!"id".equals(name)) {
+ String columnAnnotation = "\t@TableField(\"" + name + "\"";
+ //忽略最后修改时间这一列的新增和编辑
// if (UPDATE_TIME_COLUMNS.stream().anyMatch(c -> c.equalsIgnoreCase(name))) {
// columnAnnotation += ", insertable = false, updatable = false";
// }
+ listColumn.add(columnAnnotation + ")");
+ }
if ("datachange_lasttime".equalsIgnoreCase(name)) {
propName = "dataChangeLastTime";
columnNameUpper = "DataChangeLastTime";
}
- listColumn.add(columnAnnotation + ")");
//追加列
listColumn.add(String.format("\tprivate %s %s;", javaType, propName));
@@ -359,7 +363,7 @@
}
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -422,7 +426,7 @@
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -487,7 +491,7 @@
}
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -572,7 +576,7 @@
content.append(poName + ";\n");
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -582,7 +586,7 @@
content.append(daoName);
content.append(" extends BasicMapperImpl<");
content.append(modelName + GenCodeGauss.SuffixSet.PO);
- content.append("> {");
+ content.append(", " + mapperName + "> {");
content.append(ConstantFactory.STR_NEWLINE);
content.append("\t" + daoName + "(" + mapperName + " mapper) {\n");
content.append("\t\tsuper(mapper);\n");
@@ -638,15 +642,15 @@
"\t\t\t\t\tswitch (orderBy) {\n" +
"\t\t\t\t\t\t// 主键\n" +
"\t\t\t\t\t\tcase ID:\n" +
- "\t\t\t\t\t\t\tqueryWrapper.orderByAsc(" + poName + "::getId);\n" +
+ "\t\t\t\t\t\t\tqueryWrapper.orderByDesc(" + poName + "::getId);\n" +
"\t\t\t\t\t\t\tbreak;\n" +
"\t\t\t\t\t\t// 数据创建时间\n" +
"\t\t\t\t\t\tcase CREATE_TIME:\n" +
- "\t\t\t\t\t\t\tqueryWrapper.orderByAsc(" + poName + "::getCreateTime);\n" +
+ "\t\t\t\t\t\t\tqueryWrapper.orderByDesc(" + poName + "::getCreateTime);\n" +
"\t\t\t\t\t\t\tbreak;\n" +
"\t\t\t\t\t\t// 最后更新时间\n" +
"\t\t\t\t\t\tcase UPDATE_TIME:\n" +
- "\t\t\t\t\t\t\tqueryWrapper.orderByAsc(" + poName + "::getUpdateTime);\n" +
+ "\t\t\t\t\t\t\tqueryWrapper.orderByDesc(" + poName + "::getUpdateTime);\n" +
"\t\t\t\t\t\t\tbreak;\n" +
"\t\t\t\t\t}\n" +
"\t\t\t\t}\n" +
@@ -654,9 +658,28 @@
"\t\t} else {\n" +
"\t\t\tqueryWrapper.orderByDesc(" + poName + "::getId);\n" +
"\t\t}\n" +
- "\t\tPage<" + poName + "> pageResult = DB.selectPage(new Page<>(search.getPage(), search.getLimit()), queryWrapper);\n" +
+ "\t\tPage<" + poName + "> pageResult = super.selectPage(new Page<>(search.getPage(), search.getLimit()), queryWrapper);\n" +
"\t\treturn new PagerResult<>(pageResult.getSize(), pageResult.getPages(), pageResult.getTotal(), pageResult.getRecords());\n" +
- "\t}");
+ "\t}\n" +
+ "\n" +
+ "\tpublic Boolean add(" + poName + " item) {\n" +
+ "\t\tint rowCount = super.insert(item);\n" +
+ "\t\treturn rowCount == 1;\n" +
+ "\t}\n" +
+ "\n" +
+ "\tpublic Boolean addNotIncrement(" + poName + " item) {\n" +
+ "\t\tint rowCount = super.insert(item);\n" +
+ "\t\treturn rowCount == 1;\n" +
+ "\t}\n" +
+ "\n" +
+ "\tpublic " + poName + " getById(Long id) {\n" +
+ "\t\treturn super.get(id);\n" +
+ "\t}\n" +
+ "\n" +
+ "\tpublic List<" + poName + "> getListById(List<Long> listId) {\n" +
+ "\t\treturn super.getList(listId);\n" +
+ "\t}\n"
+ );
content.append("}");
if (new File(GenCodeGauss.OutSet.MAPPER_IMPL + daoName + ".java").exists()) {
@@ -745,7 +768,7 @@
}
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -838,7 +861,7 @@
}
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -912,7 +935,7 @@
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
);
content.append(ConstantFactory.STR_NEWLINE);
content.append("@Mapper");
@@ -987,7 +1010,7 @@
content.append(responseDTOName + ";\n");
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -1007,7 +1030,7 @@
" // 设置记录创建时间\n" +
" item.setCreateTime(LocalDateTimeUtil.nowTimeStamp());\n" +
" // 是否删除(逻辑删除)初始值\n" +
- " //item.setIsDelete(EYesOrNo.NO.getValue());\n" +
+ " item.setIsDelete(EYesOrNo.NO.getValue());\n" +
"\n" +
" int rowCount = mapper.insert(item);\n" +
" if (rowCount != 1) {\n" +
@@ -1247,7 +1270,7 @@
content.append(responseDTOName + ";\n");
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc)
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc)
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -1366,7 +1389,7 @@
content.append(ConstantFactory.STR_NEWLINE);
content.append(ConstantFactory.STR_NEWLINE);
content.append(SET_CLASS_DESC_TPL
- .replaceAll("\\{\\#\\=desc\\}", tableDesc + " mapper")
+ .replaceAll("\\{\\#\\=desc\\}", tableDesc == null ? modelName : tableDesc + " mapper")
.replaceAll("\\{\\#\\=author\\}", AUTHOR)
.replaceAll("\\{\\#\\=date\\}", LocalDateTimeUtil.todayStr().replaceAll("\\-", "/"))
);
@@ -1571,32 +1594,35 @@
return result;
}
- public static boolean genTable(Connection connect, String tableName, String tableDesc, List<Map<String, Object>> listColumn, Map<String, String> mapPK, Map<String, String> mapIndex) throws SQLException {
+ public static List<String> genTable(String tableName, String tableDesc, List<Map<String, Object>> listColumn, Map<String, String> mapPK, Map<String, String> mapIndex) {
+ List<String> list = new ArrayList<>();
+
String sql = "CREATE TABLE \"public\".\"" + tableName + "\" (\n";
Map<String, String> mapColumnDesc = new HashMap<>();
int idx = 0;
- boolean result = Boolean.FALSE;
- result = GenCodeGauss.execSql(connect, "DROP TABLE IF EXISTS \"public\".\"" + tableName + "\";\n");
+ list.add("-- " + tableName + " - " + tableDesc + "\nDROP TABLE IF EXISTS \"public\".\"" + tableName + "\";\n");
// 遍历列，构建sql
for (Map<String, Object> column : listColumn) {
idx++;
String name = column.get("name").toString();
String type = column.get("type").toString();
- String desc = column.get("description").toString();
- String isNullable = column.get("isNullable").toString();
+ Object desc = column.get("description");
+ Object isNullable = column.get("isNullable");
- mapColumnDesc.put(name, desc);
+ mapColumnDesc.put(name, ParameterUtil.dealNullStr(desc));
- sql += "\t\"" + name + "\" " + GenCodeGauss.mysqlType2Gauss(type);
+ sql += "\t\"" + name + "\" " + GenCodeGauss.dbType2Gauss(type);
switch (type) {
case "char":
case "varchar":
case "nvarchar":
- String length = column.get("length").toString();
- sql += "(" + length + ")";
+ if (Objects.nonNull(column.get("length"))) {
+ String length = column.get("length").toString();
+ sql += "(" + length + ")";
+ }
if ("1".equals(isNullable)) {
sql += " NULL DEFAULT NULL";
} else {
@@ -1647,9 +1673,8 @@
}
sql += "\n";
}
- sql += ")\n" +
- ";\n";
- result = GenCodeGauss.execSql(connect, sql);
+ sql += ");\n";
+ list.add(sql);
sql = "";
sql += "COMMENT ON TABLE \"public\".\"" + tableName + "\" IS '" + tableDesc + "';\n";
@@ -1657,7 +1682,7 @@
for (Map.Entry<String, String> desc : mapColumnDesc.entrySet()) {
sql += "COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"" + desc.getKey() + "\" IS '" + desc.getValue().replace("\r\n", " ").replace("\n", " ") + "';\n";
}
- result = GenCodeGauss.execSql(connect, sql);
+ list.add("-- 字段描述\n" + sql);
sql = "";
// 设置主键
@@ -1667,28 +1692,28 @@
}
}
sql += "ALTER TABLE \"public\".\"" + tableName + "\" ADD CONSTRAINT \"PK_" + tableName + "\" PRIMARY KEY (\"id\");\n";
- result = GenCodeGauss.execSql(connect, sql);
+ list.add("-- 设置主键\n" + sql);
sql = "";
// 触发器：自动赋值最后更新时间
- sql += "CREATE TRIGGER \"TG_" + tableName + "\" BEFORE INSERT OR UPDATE OF \"update_time\" ON \"public\".\"" + tableName + "\"\n" +
+ sql += "CREATE TRIGGER \"TG_" + tableName + "\" BEFORE INSERT OR UPDATE ON \"public\".\"" + tableName + "\"\n" +
"FOR EACH ROW\n" +
"EXECUTE PROCEDURE \"public\".\"update_timestamp_column\"();";
- result = GenCodeGauss.execSql(connect, sql);
+ list.add("-- 触发器：自动赋值最后更新时间\n" + sql);
sql = "";
-
+ list.add("-- 创建索引\n");
// 遍历索引，构建sql
for (Map.Entry<String, String> index : mapIndex.entrySet()) {
if (!mapPK.containsKey(index.getKey())) {
sql += "CREATE INDEX \"" + index.getKey() + "\" ON \"public\".\"test_info\"(\"" + index.getValue() + "\");\n";
- result = GenCodeGauss.execSql(connect, sql);
+ list.add(sql);
sql = "";
}
}
- return result;
+ return list;
}
- private static String mysqlType2Gauss(String type) {
+ private static String dbType2Gauss(String type) {
String result = "";
switch (type) {
case "char":
@@ -1779,9 +1804,8 @@
return mapPK;
}
- public static List<Map<String, Object>> getListAll(Connection connect, String tableName, String dbSchema) {
+ public static List<Map<String, Object>> getListAll(Connection connect, String sql) {
List<Map<String, Object>> result = new ArrayList<>();
- String sql = "SELECT * FROM " + dbSchema + "." + tableName;
try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
try (ResultSet rs = pStmt.executeQuery()) {
//获取键名
@@ -1804,20 +1828,19 @@
return result;
}
- public static List<Boolean> insertAll(Connection connect, List<Map<String, Object>> list, String tableName, Integer onceCount) {
- List<Boolean> result = new ArrayList<>();
- String insertSql = "INSERT INTO public." + tableName + "(\"";
+ public static List<String> insertAll(List<Map<String, Object>> list, String tableName, Integer onceCount) {
+ List<String> result = new ArrayList<>();
+ String insertSql = "INSERT INTO public.\"" + tableName + "\"(\"";
List<String> listColumn = new ArrayList<>();
for (String column : list.get(0).keySet()) {
if ("update_time".equalsIgnoreCase(column)) {
continue;
}
- listColumn.add(column);
+ listColumn.add("\"" + column + "\"");
}
insertSql += StringUtil.join(listColumn, "\",\"") + "\") VALUES";
String sql = "";
- List<Object> listValue = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
if (StringUtil.isNullOrEmpty(sql)) {
sql = insertSql;
@@ -1831,43 +1854,110 @@
}
listColValue.add(item.getValue());
}
- sql += "\n(" + StringUtil.join(listColValue.stream().map(c -> "?").collect(Collectors.toList())) + "),";
- listValue.addAll(listColValue);
+ sql += "\n(" + StringUtil.join(listColValue.stream().map(GenCodeGauss::getSqlValue).collect(Collectors.toList())) + "),";
+
if (i > 0 && i % onceCount == 0) {
sql = sql.substring(0, sql.length() - 1);
- try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
- for (int j = 0; j < listValue.size(); j++) {
- pStmt.setObject(j + 1, listValue.get(j));
- }
- listValue = new ArrayList<>();
- boolean insertList = pStmt.execute();
- result.add(insertList);
- System.out.println("插入数据" + Math.max(i - onceCount, 0) + " - " + i + "，结果：" + insertList);
- } catch (SQLException e) {
- e.printStackTrace();
- }
+
+ result.add(sql + ";");
+
sql = "";
}
}
if (StringUtil.isNotNullOrEmpty(sql)) {
sql = sql.substring(0, sql.length() - 1);
- try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
- for (int j = 0; j < listValue.size(); j++) {
- Object value = listValue.get(j);
- if (Objects.equals("", value)) {
- // TODO 神奇的gauss，''会判定为null，如果有非空约束，会插入失败
- value = " ";
- }
- pStmt.setObject(j + 1, value);
- }
- listValue = new ArrayList<>();
- boolean insertList = pStmt.execute();
- result.add(insertList);
- System.out.println("插入数据" + (list.size() - list.size() % onceCount) + " - " + list.size() + "，结果：" + insertList);
- } catch (SQLException e) {
- e.printStackTrace();
- }
+
+ result.add(sql + ";");
}
return result;
}
+ private static String getSqlValue(Object value) {
+ if (Objects.isNull(value)) {
+ return "null";
+ }
+ if (value instanceof String) {
+ return "'" + value + "'";
+ }
+ if (value instanceof Date) {
+ return "'" + LocalDateTimeUtil.toFormatFullString(((Date)value).getTime()) + "'";
+ }
+ if (value instanceof LocalDate) {
+ return "'" + LocalDateTimeUtil.toFormatString((LocalDate)value) + "'";
+ }
+ if (value instanceof LocalDateTime) {
+ return "'" + LocalDateTimeUtil.toFormatFullString((LocalDateTime)value) + "'";
+ }
+ if (value instanceof Calendar) {
+ return "'" + CalendarUtil.toDateTimeMSStr((Calendar)value) + "'";
+ }
+ return value.toString();
+ }
+
+ public static String getAutoUpdateTimeFunction() {
+ return "CREATE OR REPLACE FUNCTION \"public\".\"update_timestamp_column\"()\n" +
+ " RETURNS \"pg_catalog\".\"trigger\" AS $BODY$\n" +
+ "BEGIN\n" +
+ "NEW.update_time := current_timestamp;\n" +
+ "RETURN NEW;\n" +
+ "END;\n" +
+ "$BODY$\n" +
+ " LANGUAGE plpgsql VOLATILE\n" +
+ " COST 100";
+ }
+
+ public static List<String> export(Connection connect, String dbSchema, boolean isData, boolean isAutoUpdateTimeFunc) {
+ List<String> listTable = new ArrayList<>();
+ List<String> listData = new ArrayList<>();
+ if (isAutoUpdateTimeFunc) {
+ listTable.add(GenCodeGauss.getAutoUpdateTimeFunction());
+ }
+ // 获取所有表
+ Map<String, String> tables = GenCodeGauss.getTables(connect, dbSchema);
+ // 遍历所有表
+ for (Map.Entry<String, String> table : tables.entrySet()) {
+ String tabName = table.getKey();
+ String tabDesc = table.getValue();
+
+ // 获取所有列
+ List<Map<String, Object>> listCol = GenCodeGauss.getColumns(connect, tabName);
+
+ Map<String, String> mapPK = GenCodeGauss.getPK(connect, tabName);
+
+ Map<String, String> mapIndex = GenCodeGauss.getIndex(connect, tabName);
+
+ // 生成表的sql
+ List<String> listGenTable = GenCodeGauss.genTable(tabName, tabDesc, listCol, mapPK, mapIndex);
+ listTable.addAll(listGenTable);
+
+ if (isData) {
+ // 获取mysql表所有数据
+ String sqlSelect = "SELECT * FROM " + table.getKey();
+ Integer count = 1;
+ Integer pageSize = ConstantFactory.NUM500;
+ String orderBy = "id";
+ Object lastId = null;
+ while (count > 0) {
+ count = 0;
+ String sql = sqlSelect + (Objects.isNull(lastId) ? "" : " WHERE " + orderBy + " > " + GenCodeGauss.toDBValue(lastId) + "") + " ORDER BY " + orderBy + " LIMIT " + pageSize;
+ List<Map<String, Object>> list = GenCodeGauss.getListAll(connect, sql);
+ if (ListUtil.isNullOrEmpty(list)) {
+ continue;
+ }
+ count = list.size();
+ lastId = list.get(list.size() - 1).get(orderBy);
+
+ // 批量插入gauss数据库
+ List<String> insertAll = GenCodeGauss.insertAll(list, table.getKey(), ConstantFactory.NUM50);
+ listData.add(StringUtil.join(insertAll, "\n"));
+ }
+ }
+ }
+ if (ListUtil.isNotNullOrEmpty(listData)) {
+ listTable.addAll(listData);
+ }
+ return listTable;
+ }
+ public static List<String> export(Connection connect, String dbSchema) {
+ return GenCodeGauss.export(connect, dbSchema, Boolean.TRUE, Boolean.TRUE);
+ }
}
--
Gitblit v1.9.3