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/GenCodeMysql.java | 354 +++++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 322 insertions(+), 32 deletions(-)
diff --git a/src/test/java/com/lunhan/xxx/host/GenCodeMysql.java b/src/test/java/com/lunhan/xxx/host/GenCodeMysql.java
index 472669c..1ba1923 100644
--- a/src/test/java/com/lunhan/xxx/host/GenCodeMysql.java
+++ b/src/test/java/com/lunhan/xxx/host/GenCodeMysql.java
@@ -1,10 +1,7 @@
package com.lunhan.xxx.host;
import com.lunhan.xxx.common.ConstantFactory;
-import com.lunhan.xxx.common.util.ListUtil;
-import com.lunhan.xxx.common.util.LocalDateTimeUtil;
-import com.lunhan.xxx.common.util.NumericUtil;
-import com.lunhan.xxx.common.util.StringUtil;
+import com.lunhan.xxx.common.util.*;
import org.apache.commons.lang3.BooleanUtils;
import java.io.File;
@@ -12,7 +9,11 @@
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
import java.util.*;
+import java.util.Date;
+import java.util.stream.Collectors;
public class GenCodeMysql {
private static final String HOST = "113.250.189.120";
@@ -1836,30 +1837,6 @@
return result;
}
- public static List<Map<String, Object>> getListAll(Connection connect, String sql) {
- List<Map<String, Object>> result = new ArrayList<>();
- try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
- try (ResultSet rs = pStmt.executeQuery()) {
- //获取键名
- ResultSetMetaData md = rs.getMetaData();
- //获取列的数量
- int columnCount = md.getColumnCount();
- while (rs.next()) {
- Map<String, Object> column = new HashMap<>();
- for (int i = 1; i <= columnCount; i++) {
- String keyName = md.getColumnLabel(i);
- Object value = rs.getObject(i);
- column.put(keyName, value);
- }
- result.add(column);
- }
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return result;
- }
-
public static String getJavaType(String typeName) {
String result = "String";
switch (typeName) {
@@ -1960,9 +1937,8 @@
public static Map<String, String> getPK(Connection connect, String tableName) {
Map<String, String> result = new LinkedHashMap<>();
- String sql = "SHOW INDEX FROM ? WHERE Key_name='PRIMARY'";
+ String sql = "SHOW INDEX FROM " + tableName + " WHERE Key_name='PRIMARY'";
try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
- pStmt.setString(1, tableName);
try (ResultSet rs = pStmt.executeQuery()) {
while (rs.next()) {
String Key_name = rs.getString("Key_name");
@@ -1978,9 +1954,8 @@
public static Map<String, String> getIndex(Connection connect, String tableName) {
Map<String, String> result = new LinkedHashMap<>();
- String sql = "SHOW INDEX FROM ? WHERE Key_name!='PRIMARY'";
+ String sql = "SHOW INDEX FROM " + tableName + " WHERE Key_name!='PRIMARY'";
try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
- pStmt.setString(1, tableName);
try (ResultSet rs = pStmt.executeQuery()) {
while (rs.next()) {
String Key_name = rs.getString("Key_name");
@@ -1993,4 +1968,319 @@
}
return result;
}
+
+ 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) {
+ result = String.valueOf(((Timestamp) value).getTime());
+ } else if (value instanceof LocalDateTime) {
+ result = "'" + LocalDateTimeUtil.toFormatFullString((LocalDateTime) value) + "'";
+ } else if (value instanceof LocalDate) {
+ result = "'" + LocalDateTimeUtil.toFormatString((LocalDate) value) + "'";
+ } else if (value instanceof Date) {
+ result = "'" + LocalDateTimeUtil.toFormatFullString(((Date) value).getTime()) + "'";
+ } else if (value instanceof Calendar) {
+ result = "'" + CalendarUtil.toDateTimeMSStr((Calendar) value) + "'";
+ } else {
+ result = "'" + value.toString() + "'";
+ }
+ return result;
+ }
+
+ 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<>();
+ list.add("-- " + tableName + " - " + tableDesc + "\nDROP TABLE IF EXISTS \"" + tableName + "\";\n");
+
+ String sql = "CREATE TABLE `" + tableName + "` (\n";
+
+ int idx = 0;
+
+ // 遍历列，构建sql
+ for (Map<String, Object> column : listColumn) {
+ idx++;
+ String name = column.get("name").toString();
+ String type = column.get("type").toString();
+ String desc = ParameterUtil.dealNullStr(column.get("description"));
+ String isNullable = ParameterUtil.dealNullStr(column.get("isNullable"));
+
+ sql += "\t`" + name + "` " + GenCodeMysql.db2MySql(type);
+ switch (type) {
+ case "char":
+ case "varchar":
+ case "nvarchar":
+ if (Objects.nonNull(column.get("length"))) {
+ String length = column.get("length").toString();
+ sql += "(" + length + ")";
+ }
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT ''";
+ }
+ 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 + ")";
+ 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 + ")";
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT CURRENT_TIMESTAMP(3)";
+ }
+ break;
+ case "int":
+ case "tinyint":
+ case "bit":
+ case "int2":
+ case "int4":
+ case "int8":
+ case "bigint":
+ if ("1".equals(isNullable)) {
+ sql += " NULL DEFAULT NULL";
+ } else {
+ sql += " NOT NULL DEFAULT 0";
+ }
+ break;
+
+ default:
+ sql += " NULL DEFAULT NULL";
+ break;
+ }
+ sql += " COMMENT \"" + desc + "\"";
+ if (idx < listColumn.size()) {
+ sql += ",\n";
+ }
+ }
+ sql += "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='" + tableDesc + "';\n";
+ list.add(sql);
+ sql = "";
+
+ // 设置主键
+ if (!mapPK.isEmpty()) {
+ for (Map.Entry<String, String> key : mapPK.entrySet()) {
+ sql += "ALTER TABLE `" + tableName + "` ADD PRIMARY KEY(`" + key.getValue() + "`);\n";
+ }
+ }
+ 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 `" + tableName + "`(`" + index.getValue() + "`);\n";
+ list.add(sql);
+ sql = "";
+ }
+ }
+ return list;
+ }
+
+ private static String db2MySql(String type) {
+ String result = "";
+ switch (type) {
+ case "char":
+ case "nvarchar":
+ case "varchar":
+ result = "varchar";
+ break;
+
+ case "longtext":
+ case "text":
+ result = "text";
+ break;
+
+ case "decimal":
+ case "numeric":
+ result = "decimal";
+ break;
+ case "datetime":
+ case "timestamp":
+ result = "timestamp";
+ break;
+
+ case "bit":
+ case "int2":
+ case "tinyint":
+ result = "tinyint";
+ break;
+
+ case "int":
+ case "int4":
+ result = "int";
+ 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) {
+ return false;
+ }
+ }
+
+ public static List<Map<String, Object>> getListAll(Connection connect, String sql) {
+ List<Map<String, Object>> result = new ArrayList<>();
+ try (PreparedStatement pStmt = connect.prepareStatement(sql)) {
+ try (ResultSet rs = pStmt.executeQuery()) {
+ //获取键名
+ ResultSetMetaData md = rs.getMetaData();
+ //获取列的数量
+ int columnCount = md.getColumnCount();
+ while (rs.next()) {
+ Map<String, Object> column = new HashMap<>();
+ for (int i = 1; i <= columnCount; i++) {
+ String keyName = md.getColumnLabel(i);
+ Object value = rs.getObject(i);
+ column.put(keyName, value);
+ }
+ result.add(column);
+ }
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ return result;
+ }
+
+ public static List<String> insertAll(List<Map<String, Object>> list, String tableName, Integer onceCount) {
+ List<String> result = new ArrayList<>();
+ String insertSql = "INSERT INTO `" + 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";
+
+ String sql = "";
+ for (int i = 0; i < list.size(); i++) {
+ if (StringUtil.isNullOrEmpty(sql)) {
+ sql = insertSql;
+ }
+ Map<String, Object> row = list.get(i);
+
+ List<Object> listColValue = new ArrayList<>();
+ for (Map.Entry<String, Object> item : row.entrySet()) {
+ if ("update_time".equalsIgnoreCase(item.getKey())) {
+ continue;
+ }
+ listColValue.add(item.getValue());
+ }
+ sql += "\n(" + StringUtil.join(listColValue.stream().map(GenCodeMysql::getSqlValue).collect(Collectors.toList())) + "),";
+
+ if (i > 0 && i % onceCount == 0) {
+ sql = sql.substring(0, sql.length() - 1);
+
+ result.add(sql + ";");
+
+ sql = "";
+ }
+ }
+ if (StringUtil.isNotNullOrEmpty(sql)) {
+ sql = sql.substring(0, sql.length() - 1);
+
+ 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 List<String> export(Connection connect, String dbSchema, boolean isData) {
+ List<String> listTable = new ArrayList<>();
+ List<String> listData = new ArrayList<>();
+ // 获取所有表
+ Map<String, String> tables = GenCodeMysql.getTables(connect, dbSchema);
+ // 遍历所有表
+ for (Map.Entry<String, String> table : tables.entrySet()) {
+ String tabName = table.getKey();
+ String tabDesc = table.getValue();
+
+ // 获取所有列
+ List<Map<String, Object>> listCol = GenCodeMysql.getColumns(connect, tabName, dbSchema);
+
+ Map<String, String> mapPK = GenCodeMysql.getPK(connect, tabName);
+
+ Map<String, String> mapIndex = GenCodeMysql.getIndex(connect, tabName);
+
+ // 生成表的sql
+ List<String> listGenTable = GenCodeMysql.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 + " > " + GenCodeMysql.toDBValue(lastId) + "") + " ORDER BY " + orderBy + " LIMIT " + pageSize;
+ List<Map<String, Object>> list = GenCodeMysql.getListAll(connect, sql);
+ if (ListUtil.isNullOrEmpty(list)) {
+ continue;
+ }
+ count = list.size();
+ lastId = list.get(list.size() - 1).get(orderBy);
+
+ // 批量插入gauss数据库
+ List<String> insertAll = GenCodeMysql.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 GenCodeMysql.export(connect, dbSchema, Boolean.TRUE);
+ }
}
--
Gitblit v1.9.3