package com.nanjing.water;
|
|
import com.nanjing.water.common.util.ListUtil;
|
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.SQLException;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Objects;
|
|
public class Mysql2Gauss {
|
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 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_FROM = null;
|
private static Connection CONN_TO = null;
|
|
static {
|
//加载驱动
|
try {
|
Class.forName(FROM_DRIVER);
|
} catch (ClassNotFoundException e) {
|
e.printStackTrace();
|
}
|
//获得连接
|
try {
|
CONN_FROM = DriverManager.getConnection(FROM_CONNECTION_STR, FROM_USER, FROM_PASSWORD);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
//加载驱动
|
try {
|
Class.forName(TO_DRIVER);
|
} catch (ClassNotFoundException e) {
|
e.printStackTrace();
|
}
|
//获得连接
|
try {
|
CONN_TO = DriverManager.getConnection(TO_CONNECTION_STR, TO_USER, TO_PASSWORD);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public static void main(String[] args) throws SQLException {
|
// 创建触发器需要的函数
|
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" +
|
"RETURN NEW;\n" +
|
"END;\n" +
|
"$BODY$\n" +
|
" LANGUAGE plpgsql VOLATILE\n" +
|
" COST 100");
|
|
// 获取mysql所有的表名称
|
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_FROM, table.getKey(), FROM_DB_NAME);
|
// 获取mysql表的主键
|
Map<String, String> mapPK = GenCodeMysql.getPK(CONN_FROM, table.getKey());
|
// 获取mysql表的索引
|
Map<String, String> mapIndex = GenCodeMysql.getIndex(CONN_FROM, table.getKey());
|
|
System.out.println("从mysql查询表结构：" + table.getKey());
|
// 调用gauss创建表结构
|
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 " + FROM_DB_NAME + "." + table.getKey();
|
Integer count = 1;
|
Integer pageSize = 500;
|
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 = GenCodeMysql.getListAll(CONN_FROM, sql);
|
if (ListUtil.isNullOrEmpty(list)) {
|
continue;
|
}
|
count = list.size();
|
lastId = list.get(list.size() - 1).get(orderBy);
|
|
System.out.println("从mysql获取到数据：" + count);
|
// 批量插入gauss数据库
|
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);
|
}
|
}
|
}
|
}
|
}
|
