From fbc3f392552c14074e1295e4c6e74c5cb87ff8b5 Mon Sep 17 00:00:00 2001
From: liulin <lin.liu@aliyun.com>
Date: 星期六, 03 八月 2024 14:09:24 +0800
Subject: [PATCH] mysql迁移数据库到gauss
---
src/test/java/com/lunhan/xxx/host/GenCodeGauss.java | 200 +++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 156 insertions(+), 44 deletions(-)
diff --git a/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java b/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java
index 86923dd..a40a4fe 100644
--- a/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java
+++ b/src/test/java/com/lunhan/xxx/host/GenCodeGauss.java
@@ -13,6 +13,7 @@
import java.time.LocalDateTime;
import java.util.Date;
import java.util.*;
+import java.util.stream.Collectors;
public class GenCodeGauss {
private static final String HOST = "113.250.189.120";
@@ -213,12 +214,12 @@
//#endregion
//#region 编辑接口忽略字段配置
- private static final List<String> NO_UPDATE_COLUMNS = Arrays.asList(
+ private static final List<String> NO_UPDATE_COLUMNS = Arrays.asList(
"create_time", "create_user", "create_user_id", "create_user_name",
"update_time", "update_user", "update_user_id", "update_user_name",
"datachange_lasttime", "data_change_last_time", "is_delete"
);
- private static final List<String> UPDATE_TIME_COLUMNS = Arrays.asList(
+ private static final List<String> UPDATE_TIME_COLUMNS = Arrays.asList(
"update_time", "datachange_lasttime", "data_change_last_time"
);
//#endregion
@@ -242,7 +243,7 @@
* 只生成以下配置的表
*/
private static final List<String> ONLY_TABLES = Arrays.asList(
- "admin_menus"
+ "admin_menus"
);
public static void main(String[] args) {
@@ -250,7 +251,7 @@
System.out.println("conn is null.");
return;
}
- Map<String, String> tables = getTables(DB_NAME);
+ Map<String, String> tables = getTables(CONN, DB_NAME);
for (Map.Entry<String, String> table : tables.entrySet()) {
String tableName = table.getKey();
String tableDesc = table.getValue();
@@ -258,7 +259,7 @@
if (ONLY_TABLES.size() > 0 && BooleanUtils.isFalse(ONLY_TABLES.contains(tableName))) {
continue;
}
- List<Map<String, Object>> columns = getColumns(tableName, DB_NAME);
+ List<Map<String, Object>> columns = getColumns(CONN, tableName);
genPO(columns, tableDesc);
genVO(columns, tableDesc);
genCreateDto(columns, tableName, tableDesc);
@@ -1156,7 +1157,7 @@
" return ExecutedResult.failed(\"[" + tableDesc + "]不存在：\" + id);\n" +
" }\n" +
" return ExecutedResult.success(exists);\n" +
- " }\n"+
+ " }\n" +
" protected ExecutedResult<List<" + poName + ">> check4Id(List<Long> listId) {\n" +
" // 从数据库查找" + tableDesc + "\n" +
" List<" + poName + "> list = mapper.getList(listId);\n" +
@@ -1368,7 +1369,7 @@
//#endregion
- public static Map<String, String> getTables(String dbSchema) {
+ public static Map<String, String> getTables(Connection connect, String dbSchema) {
// dbSchema="public";
Map<String, String> result = new LinkedHashMap<>();
String sql = "SELECT tb.table_name, d.description AS TABLE_COMMENT" +
@@ -1376,7 +1377,7 @@
" JOIN pg_class c ON c.relname = tb.table_name" +
" LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0'" +
" WHERE tb.table_schema = 'public' ";
- try (PreparedStatement pStmt = CONN.prepareStatement(sql)) {
+ try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
//pStmt.setString(1, dbSchema);
try (ResultSet rs = pStmt.executeQuery()) {
while (rs.next()) {
@@ -1391,7 +1392,7 @@
return result;
}
- public static List<Map<String, Object>> getColumns(String tableName, String dbSchema) {
+ public static List<Map<String, Object>> getColumns(Connection connect, String tableName) {
List<Map<String, Object>> result = new ArrayList<>();
String sql = "SELECT col.table_name AS objName, col.column_name AS name, col.udt_name AS type, col.character_maximum_length AS LENGTH, des.description,\n" +
"col.numeric_precision, \n" +
@@ -1406,7 +1407,7 @@
"AND table_schema = 'public' " +
"ORDER BY " +
"ordinal_position;";
- try (PreparedStatement pStmt = CONN.prepareStatement(sql)) {
+ try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
pStmt.setString(1, tableName);
// pStmt.setString(2, "public");
try (ResultSet rs = pStmt.executeQuery()) {
@@ -1534,6 +1535,9 @@
public static String toDBValue(Object value) {
String result;
+ if (null == value) {
+ return "";
+ }
if (value instanceof Number || value instanceof Boolean) {
result = value.toString();
} else if (value instanceof Timestamp) {
@@ -1552,40 +1556,58 @@
return result;
}
- public static boolean genTable(String tableName, String tableDesc, List<Map<String, Object>> listColumn, Map<String, String> mapPK, Map<String, String> mapIndex) {
- String sql = "DROP TABLE IF EXISTS \"public\".\"" + tableName + "\";\n" +
- "CREATE TABLE \"public\".\"" + tableName + "\" (\n";
+ 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 {
+ 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");
+
// 遍历列，构建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();
- mapColumnDesc.put(name,desc);
+ mapColumnDesc.put(name, desc);
- sql += "\t\""+ name +"\" " + type;
+ sql += "\t\"" + name + "\" " + GenCodeGauss.mysqlType2Gauss(type);
switch (type) {
case "char":
case "varchar":
case "nvarchar":
- case "text":
String length = column.get("length").toString();
- sql += "(" + length+ ") NOT NULL DEFAULT NULL::character varying";
+ sql += "(" + length + ")";
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT NULL::character varying";
+ }
break;
case "numeric":
case "decimal":
String numeric_precision = column.get("numeric_precision").toString();
String numeric_scale = column.get("numeric_scale").toString();
- sql += "(" + numeric_precision+ ","+ numeric_scale +") NOT NULL DEFAULT 0";
+ sql += "(" + numeric_precision + "," + numeric_scale + ")";
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT 0";
+ }
break;
case "datetime":
case "timestamp":
String datetime_precision = column.get("datetime_precision").toString();
- sql += "(" + datetime_precision +") NOT NULL DEFAULT PG_SYSTIMESTAMP()";
+ sql += "(" + datetime_precision + ")";
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT PG_SYSTIMESTAMP()";
+ }
break;
case "int":
case "tinyint":
@@ -1593,7 +1615,12 @@
case "int2":
case "int4":
case "int8":
- sql += " NOT NULL DEFAULT 0";
+ case "bigint":
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT 0";
+ }
break;
default:
@@ -1606,39 +1633,91 @@
sql += "\n";
}
sql += ")\n" +
- ";\n" +
- "COMMENT ON TABLE \"public\".\"" + tableName + "\" IS '" + tableDesc + "';\n";
+ ";\n";
+ result = GenCodeGauss.execSql(connect, sql);
+ sql = "";
+
+ sql += "COMMENT ON TABLE \"public\".\"" + tableName + "\" IS '" + tableDesc + "';\n";
// 构建字段描述sql
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";
+ sql += "COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"" + desc.getKey() + "\" IS '" + desc.getValue().replace("\r\n", " ").replace("\n", " ") + "';\n";
}
+ result = GenCodeGauss.execSql(connect, sql);
+ sql = "";
+
// 设置主键
if (!mapPK.isEmpty()) {
for (Map.Entry<String, String> key : mapPK.entrySet()) {
- sql += "ALTER TABLE \"public\".\"" + tableName + "\" ADD CONSTRAINT \"PK_" + tableName + "\" PRIMARY KEY (\"" + key.getValue() +"\");\n";
+ sql += "ALTER TABLE \"public\".\"" + tableName + "\" ADD CONSTRAINT \"PK_" + tableName + "\" PRIMARY KEY (\"" + key.getValue() + "\");\n";
}
}
sql += "ALTER TABLE \"public\".\"" + tableName + "\" ADD CONSTRAINT \"PK_" + tableName + "\" PRIMARY KEY (\"id\");\n";
- // 触发器：自动赋值最后更新时间
+ result = GenCodeGauss.execSql(connect, sql);
+ sql = "";
+
+ // 触发器：自动赋值最后更新时间
sql += "CREATE TRIGGER \"TG_" + tableName + "\" BEFORE UPDATE OF \"update_time\" ON \"public\".\"" + tableName + "\"\n" +
"FOR EACH ROW\n" +
"EXECUTE PROCEDURE \"public\".\"update_timestamp_column\"();";
+ result = GenCodeGauss.execSql(connect, sql);
+ sql = "";
+
// 遍历索引，构建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);
+ sql = "";
}
}
- try (PreparedStatement pStmt = CONN.prepareCall(sql)) {
- int[] result = pStmt.executeBatch();
- return result != null;
+ return result;
+ }
+
+ private static String mysqlType2Gauss(String type) {
+ String result = "";
+ switch (type) {
+ case "char":
+ case "nvarchar":
+ result = "varchar";
+ break;
+
+ case "longtext":
+ result = "text";
+ break;
+
+ case "decimal":
+ result = "numeric";
+ break;
+ case "datetime":
+ result = "timestamp";
+ break;
+
+ case "bit":
+ case "tinyint":
+ result = "int2";
+ break;
+
+ case "int":
+ result = "int4";
+ break;
+
+ default:
+ result = type;
+ break;
+ }
+ return result;
+ }
+
+ public static boolean execSql(Connection connect, String sql) {
+ try(PreparedStatement pst = connect.prepareStatement(sql)) {
+ int rowCount = pst.executeUpdate();
+ return rowCount > 0;
} catch (Exception e) {
- System.out.println(e);
- return Boolean.FALSE;
+ return false;
}
}
- public static Map<String, String> getPK(String tableName) {
+ public static Map<String, String> getPK(Connection connect, String tableName) {
Map<String, String> mapPK = new HashMap<>();
// 查询主键
String pkSq = "select pg_attribute.attname as col_name,pg_constraint.conname as pk_name\n" +
@@ -1647,7 +1726,7 @@
"inner join pg_attribute on pg_attribute.attrelid=pg_class.oid and pg_attribute.attnum=pg_constraint.conkey[1]\n" +
"where pg_class.relname='" + tableName + "'\n" +
"and pg_constraint.contype='p'";
- try (PreparedStatement pStmt = CONN.prepareStatement(pkSq)) {
+ try (PreparedStatement pStmt = connect.prepareStatement(pkSq)) {
try (ResultSet rs = pStmt.executeQuery()) {
//获取键名
ResultSetMetaData md = rs.getMetaData();
@@ -1665,11 +1744,11 @@
return mapPK;
}
- public static Map<String, String> getIndex(String tableName) {
+ public static Map<String, String> getIndex(Connection connect, String tableName) {
Map<String, String> mapPK = new HashMap<>();
// 查询主键
- String pkSq = "SELECT * FROM pg_indexes WHERE tablename = '" + tableName +"';";
- try (PreparedStatement pStmt = CONN.prepareStatement(pkSq)) {
+ String pkSq = "SELECT * FROM pg_indexes WHERE tablename = '" + tableName + "';";
+ try (PreparedStatement pStmt = connect.prepareStatement(pkSq)) {
try (ResultSet rs = pStmt.executeQuery()) {
//获取键名
ResultSetMetaData md = rs.getMetaData();
@@ -1685,10 +1764,10 @@
return mapPK;
}
- public static List<Map<String, Object>> getListAll(String tableName, String dbSchema) {
+ public static List<Map<String, Object>> getListAll(Connection connect, String tableName, String dbSchema) {
List<Map<String, Object>> result = new ArrayList<>();
String sql = "SELECT * FROM " + dbSchema + "." + tableName;
- try (PreparedStatement pStmt = CONN.prepareStatement(sql)) {
+ try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
try (ResultSet rs = pStmt.executeQuery()) {
//获取键名
ResultSetMetaData md = rs.getMetaData();
@@ -1710,28 +1789,42 @@
return result;
}
- public static List<Boolean> insertAll(List<Map<String, Object>> list, String tableName, Integer onceCount) {
+ 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 + "(";
+ 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);
}
- insertSql += StringUtil.join(listColumn) + ") VALUES";
+ 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;
}
Map<String, Object> row = list.get(i);
- List<String> listValue = new ArrayList<>();
+
+ List<Object> listColValue = new ArrayList<>();
for (Map.Entry<String, Object> item : row.entrySet()) {
- listValue.add(GenCodeGauss.toDBValue(item.getValue()));
+ if ("update_time".equalsIgnoreCase(item.getKey())) {
+ continue;
+ }
+ listColValue.add(item.getValue());
}
- sql += "(" + StringUtil.join(listValue) + ")";
+ sql += "\n(" + StringUtil.join(listColValue.stream().map(c -> "?").collect(Collectors.toList())) + "),";
+ listValue.addAll(listColValue);
if (i > 0 && i % onceCount == 0) {
- try (PreparedStatement pStmt = CONN.prepareStatement(sql)) {
+ 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);
@@ -1741,6 +1834,25 @@
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();
+ }
+ }
return result;
}
}
--
Gitblit v1.9.3