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/Mysql2Gauss.java | 69 ++++++++++++++++++----------------
1 files changed, 37 insertions(+), 32 deletions(-)
diff --git a/src/test/java/com/lunhan/xxx/host/Mysql2Gauss.java b/src/test/java/com/lunhan/xxx/host/Mysql2Gauss.java
index dbd0ac2..24ea4cf 100644
--- a/src/test/java/com/lunhan/xxx/host/Mysql2Gauss.java
+++ b/src/test/java/com/lunhan/xxx/host/Mysql2Gauss.java
@@ -1,7 +1,6 @@
package com.lunhan.xxx.host;
import com.lunhan.xxx.common.util.ListUtil;
-import com.lunhan.xxx.common.util.SerializeUtil;
import java.sql.Connection;
import java.sql.DriverManager;
@@ -11,47 +10,47 @@
import java.util.Objects;
public class Mysql2Gauss {
- private static final String Mysql_HOST = "113.250.189.120";
- private static final String Mysql_PORT = "23306";
- private static final String Mysql_USER = "lunhan";
- private static final String Mysql_PASSWORD = "lunhan123";
- private static final String Mysql_DB_NAME = "water_basic";
- private static final String Mysql_DRIVER = "com.mysql.cj.jdbc.Driver";
- private static final String Mysql_CONNECTION_STR = "jdbc:mysql://" + Mysql_HOST + ":" + Mysql_PORT + "/" + Mysql_DB_NAME + "?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false";
+ private static final String FROM_HOST = "113.250.189.120";
+ private static final String FROM_PORT = "23306";
+ private static final String FROM_USER = "lunhan";
+ private static final String FROM_PASSWORD = "lunhan123";
+ private static final String FROM_DB_NAME = "water_basic";
+ private static final String FROM_DRIVER = "com.mysql.cj.jdbc.Driver";
+ private static final String FROM_CONNECTION_STR = "jdbc:mysql://" + FROM_HOST + ":" + FROM_PORT + "/" + FROM_DB_NAME + "?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false";
- private static final String Gauss_HOST = "113.250.189.120";
- private static final String Gauss_PORT = "57654";
- private static final String Gauss_USER = "lunhan";
- private static final String Gauss_PASSWORD = "lunhan.20240330";
- private static final String Gauss_DB_NAME = "water_basic";
- private static final String Gauss_DRIVER = "org.postgresql.Driver";
- private static final String Gauss_CONNECTION_STR = "jdbc:postgresql://" + Gauss_HOST + ":" + Gauss_PORT + "/" + Gauss_DB_NAME;
+ private static final String TO_HOST = "113.250.189.120";
+ private static final String TO_PORT = "57654";
+ private static final String TO_USER = "lunhan";
+ private static final String TO_PASSWORD = "lunhan.20240330";
+ private static final String TO_DB_NAME = "water_basic";
+ private static final String TO_DRIVER = "org.postgresql.Driver";
+ private static final String TO_CONNECTION_STR = "jdbc:postgresql://" + TO_HOST + ":" + TO_PORT + "/" + TO_DB_NAME;
- private static Connection CONN_Mysql = null;
- private static Connection CONN_Gauss = null;
+ private static Connection CONN_FROM = null;
+ private static Connection CONN_TO = null;
static {
//加载驱动
try {
- Class.forName(Mysql_DRIVER);
+ Class.forName(FROM_DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//获得连接
try {
- CONN_Mysql = DriverManager.getConnection(Mysql_CONNECTION_STR, Mysql_USER, Mysql_PASSWORD);
+ CONN_FROM = DriverManager.getConnection(FROM_CONNECTION_STR, FROM_USER, FROM_PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
//加载驱动
try {
- Class.forName(Gauss_DRIVER);
+ Class.forName(TO_DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//获得连接
try {
- CONN_Gauss = DriverManager.getConnection(Gauss_CONNECTION_STR, Gauss_USER, Gauss_PASSWORD);
+ CONN_TO = DriverManager.getConnection(TO_CONNECTION_STR, TO_USER, TO_PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
@@ -59,7 +58,7 @@
public static void main(String[] args) throws SQLException {
// 创建触发器需要的函数
- GenCodeGauss.execSql(CONN_Gauss, "CREATE OR REPLACE FUNCTION \"public\".\"update_timestamp_column\"()\n" +
+ GenCodeGauss.execSql(CONN_TO, "CREATE OR REPLACE FUNCTION \"public\".\"update_timestamp_column\"()\n" +
" RETURNS \"pg_catalog\".\"trigger\" AS $BODY$\n" +
"BEGIN\n" +
"NEW.update_time := current_timestamp;\n" +
@@ -70,23 +69,26 @@
" COST 100");
// 获取mysql所有的表名称
- Map<String, String> tables = GenCodeMysql.getTables(CONN_Mysql, Mysql_DB_NAME);
+ Map<String, String> tables = GenCodeMysql.getTables(CONN_FROM, FROM_DB_NAME);
// 遍历表名，获取表结构
for (Map.Entry<String, String> table : tables.entrySet()) {
// 获取mysql表结构
- List<Map<String, Object>> columns = GenCodeMysql.getColumns(CONN_Mysql, table.getKey(), Mysql_DB_NAME);
+ List<Map<String, Object>> columns = GenCodeMysql.getColumns(CONN_FROM, table.getKey(), FROM_DB_NAME);
// 获取mysql表的主键
- Map<String, String> mapPK = GenCodeMysql.getPK(CONN_Mysql, table.getKey());
+ Map<String, String> mapPK = GenCodeMysql.getPK(CONN_FROM, table.getKey());
// 获取mysql表的索引
- Map<String, String> mapIndex = GenCodeMysql.getIndex(CONN_Mysql, table.getKey());
+ Map<String, String> mapIndex = GenCodeMysql.getIndex(CONN_FROM, table.getKey());
System.out.println("从mysql查询表结构：" + table.getKey());
// 调用gauss创建表结构
- boolean genTale = GenCodeGauss.genTable(CONN_Gauss, table.getKey(), table.getValue(), columns, mapPK, mapIndex);
- System.out.println("向gauss数据库创建表结果：" + genTale);
+ List<String> listGenTable = GenCodeGauss.genTable(table.getKey(), table.getValue(), columns, mapPK, mapIndex);
+ for (String sql : listGenTable) {
+ boolean result = GenCodeGauss.execSql(CONN_TO, sql);
+ System.out.println(sql + ", 执行结果：" + result);
+ }
// 获取mysql表所有数据
- String sqlSelect = "SELECT * FROM " + Mysql_DB_NAME + "." + table.getKey();
+ String sqlSelect = "SELECT * FROM " + FROM_DB_NAME + "." + table.getKey();
Integer count = 1;
Integer pageSize = 500;
String orderBy = "id";
@@ -94,7 +96,7 @@
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 = GenCodeMysql.getListAll(CONN_Mysql, sql);
+ List<Map<String, Object>> list = GenCodeMysql.getListAll(CONN_FROM, sql);
if (ListUtil.isNullOrEmpty(list)) {
continue;
}
@@ -103,8 +105,11 @@
System.out.println("从mysql获取到数据：" + count);
// 批量插入gauss数据库
- List<Boolean> insertAll = GenCodeGauss.insertAll(CONN_Gauss, list, table.getKey(), pageSize);
- System.out.println("向gauss数据库插入数据结果：" + SerializeUtil.toJson(insertAll));
+ List<String> insertAll = GenCodeGauss.insertAll(list, table.getKey(), pageSize);
+ for (String addData : insertAll) {
+ boolean result = GenCodeGauss.execSql(CONN_TO, addData);
+ System.out.println(addData + ", 执行结果：" + result);
+ }
}
}
}
--
Gitblit v1.9.3