java实现跨服务器实现同步表结构，数据
- 不同服务器之间的数据库中的表结构，数据进行同步操作，测试类，可直接运行，部分不同类型库之间的转换，还未完全完善，可能存在字段类型转换失败，导致建表失败，同步数据支持百万千万的数据量
package com.test.web.service;
/**
*
* @Date: 2020/2/28 14:20
* @Version: 1.0
*/
public class TestMysqlToOracle {
public static final String dirver = "com.mysql.jdbc.Driver";
public static final String useranem="root";
public static final String password="root";
public static final String type = "mysql";
public static final String url="jdbc:mysql://XXXXXX/test";
public static final String dirverR = "oracle.jdbc.OracleDriver";
public static final String useranemR="root";
public static final String passwordR="root";
public static final String urlR = "jdbc:oracle:thin:@localhost:1522:orcl";
public static final String typeR = "oracle";
public static void main(String[] args) throws Exception{
Connection conn = null;
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
StringBuffer sql = new StringBuffer();
Class.forName(dirver);
conn = DriverManager.getConnection(url, useranem, password);
DatabaseMetaData databaseMetaData = conn.getMetaData();
ResultSet tables = databaseMetaData.getTables(null, null, "%", null);
while (tables.next()) {
new TestG().sync(tables.getString("TABLE_NAME"));
}
}
@Transactional(rollbackFor = Exception.class)
public Map<String, Object> sync(String tableName) throws Exception {
Map<String, Object> map = new HashMap<>();
long start = System.currentTimeMillis();
// 生成建表sql
Map<String, Object> sql = sql(tableName);
Map<String, Object> columnMap = (Map<String, Object>) sql.get("map");
Map<String, Object> sqlMap = (Map<String, Object>) sql.get("sql");
String createTableSQL = (String) sqlMap.get("sql");
System.out.println("-------获取表结构成功，建表sql生成成功-----");
Boolean tableExit = false;
createTable(dirverR, urlR, useranemR, passwordR , createTableSQL, tableName);
return map;
}
// 获取建表sql
public Map<String, Object> sql(String tableName) throws Exception {
Map<String, Object> map = new HashMap<>();
Map<String, Object> sqlMap = new HashMap<>();
Connection conn = null;
DatabaseMetaData metaData = null;
ResultSet rs = null;
ResultSet pt = null;
String sql = null;
boolean isExit = false;
List<String> primaryKeyList = new ArrayList<>();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
Class.forName(dirver);
conn = DriverManager.getConnection(url, useranem, password);
String catalog = conn.getCatalog(); // catalog 是数据库名
System.out.println("---------连接成功，数据库：" + catalog);
metaData = conn.getMetaData();
// 获取表
rs = metaData.getColumns(null, null, tableName, null);
Map<String, Object> dataMap = new HashMap<>();
List<HashMap<String, Object>> rows = new ArrayList<HashMap<String, Object>>();
// 获取信息
while (rs.next()) {
HashMap<String, Object> row = new HashMap<String, Object>();
dataMap.put("TABLE_NAME", tableName);
row.put("COLUMN_NAME", rs.getString("COLUMN_NAME")); //字段名
row.put("TYPE_NAME", rs.getString("TYPE_NAME")); //字段类型
if ("DATETIME".equals(rs.getString("TYPE_NAME"))) {
row.put("COLUMN_SIZE", Integer.valueOf(0)); //如果事dataTime类型修改为0，调试返回时19，创表失败
} else {
row.put("COLUMN_SIZE", rs.getInt("COLUMN_SIZE"));
}
map.put(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));
row.put("NULLABLE", rs.getInt("NULLABLE") == 0 ? "NOT NULL" : " "); //可否为null
rows.add(row);
}
//主键
pt = metaData.getPrimaryKeys(conn.getCatalog(), null, tableName);
while (pt.next()) {
primaryKeyList.add( pt.getString("COLUMN_NAME"));
}
dataMap.put("PRIMARYS",primaryKeyList); //获取主键
dataMap.put("rows", rows);
sql = getSql(dataMap, tableName, typeR);
sqlMap.put("sql", sql);
Map<String, Object> all = new HashMap<>();
all.put("map", map);
all.put("sql", sqlMap);
return all;
} finally {
if (null != pt) {
pt.close();
}
if (null != rs) {
rs.close();
}
if (null != conn) {
conn.close();
}
}
}
//拼接建表sql
public String getSql(Map<String, Object> map, String tableName, String typeR) {
StringBuffer sb = new StringBuffer();
List<String> PRIMARYS =(List<String>) map.get("PRIMARYS");
sb.append("CREATE TABLE ").append(map.get("TABLE_NAME")).append(" (").append("\n");
List<HashMap<String, Object>> rows = (List<HashMap<String, Object>>) map.get("rows");
for (Map<String, Object> rowMap : rows) {
if ("mysql".equals(typeR)){
sb.append("`").append(rowMap.get("COLUMN_NAME") + "` ");
}
else {
sb.append(" ").append(rowMap.get("COLUMN_NAME") + " ");
}
sb.append(caseVale(rowMap.get("TYPE_NAME"), typeR));
// sql server 除了varchar类型，其他都加大小
if ("sql_server".equals(typeR)) {
if ((caseVale(rowMap.get("TYPE_NAME"), typeR)).equals("VARCHAR")) {
sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
}
// sql server 不存在float和double,转换成decimal
if ((caseVale(rowMap.get("TYPE_NAME"), typeR)).equals("DECIMAL")) {
sb.append("(12,4)");
}
}
// mysql , dateTime和时间戳类型不需要大小
else if ("mysql".equals(typeR)) {
if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DATETIME") || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("TIMESTAMP(6)")
|| caseVale(rowMap.get("TYPE_NAME"), typeR).equals("LONGTEXT") || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("TEXT")) {
// sb.append("(0)");
} else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("CHAR(1)")) {
} else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE") || caseVale(rowMap.get("TYPE_NAME"), typeR).equals("FLOAT")) {
sb.append("(12,4)");
} else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("BIT")) {
sb.append("(1)");
}
else {
sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
}
} else {
// 如果是oracle的date类型，不需要加大小
if (!caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DATE") && !caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE")
&& !caseVale(rowMap.get("TYPE_NAME"), typeR).equals("FLOAT")) {
if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("DOUBLE")) {
sb.append("(12,4)");
} else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("CHAR")) {
sb.append("(1)");
} else if (caseVale(rowMap.get("TYPE_NAME"), typeR).equals("VARCHAR2")) {
sb.append("(255)");
}
else {
sb.append("(" + rowMap.get("COLUMN_SIZE") + ")");
}
}
}
sb.append((rowMap.get("NULLABLE") == "" ? "" : " " + rowMap.get("NULLABLE")) + ",");
sb.append("\n");
}
if (PRIMARYS.size() > 0) {
sb.append(" PRIMARY KEY (");
}
for (int i = 0; i < PRIMARYS.size(); i++) {
sb.append(PRIMARYS.get(i) + ",");
}
sb.deleteCharAt(sb.lastIndexOf(","));
if (PRIMARYS.size() > 0) {
sb.append(")\n");
}
sb.append(")");
System.out.println("-----------建表语句\n" + sb.toString());
return sb.toString();
}
//两方不同数据库，需要对类型进行转换
public String caseVale(Object typeName, String type) {
String typeClound = null;
if ("mysql".equals(type)) {
switch (((String) typeName).toUpperCase()) {
case "NUMBER":
typeClound = "BIGINT";
break;
case "DATE":
typeClound = "DATETIME";
break;
case "VARCHAR2":
typeClound = "VARCHAR";
break;
case "TIMESTAMP(6)":
typeClound = "DATETIME";
break;
case "TIMESTAMP":
typeClound = "DATETIME";
break;
default:
typeClound = (String) ((String) typeName).toUpperCase();
break;
}
} else if ("oracle".equals(type)) {
switch (((String) typeName).toUpperCase()) {
case "BIGINT":
typeClound = "NUMBER";
break;
case "DATETIME":
typeClound = "DATE";
break;
case "BIT":
typeClound = "CHAR";
break;
case "TIMESTAMP(6)":
typeClound = "DATE";
break;
case "TIMESTAMP":
typeClound = "DATE";
break;
case "INT":
typeClound = "NUMBER";
break;
case "FLOAT":
typeClound = "NUMBER";
break;
case "DOUBLE":
typeClound = "NUMBER";
break;
case "TEXT":
typeClound = "VARCHAR2";
break;
case "SMALLINT":
typeClound = "NUMBER";
break;
default:
typeClound = ((String) typeName).toUpperCase();
break;
}
} else {
switch (((String) typeName).toUpperCase()) {
case "NUMBER":
typeClound = "BIGINT";
break;
case "DATE":
typeClound = "DATETIME";
break;
case "CHAR":
typeClound = "BIT";
break;
case "VARCHAR2":
typeClound = "VARCHAR";
break;
case "TIMESTAMP(6)":
typeClound = "DATETIME";
break;
case "FLOAT":
typeClound = "DECIMAL";
break;
case "DOUBLE":
typeClound = "DECIMAL";
break;
case "LONGTEXT":
typeClound = "TEXT";
break;
case "BLOB":
typeClound = "IMAGE";
break;
default:
typeClound = ((String) typeName).toUpperCase();
break;
}
}
return typeClound;
}
//连接对方系统进行建表
public boolean createTable(String dirverR, String urlR, String useranemR, String passwordR, String sql, String tableName) throws Exception {
Connection remoteConn = null;
PreparedStatement preparedStatement = null;
Boolean bl = false;
try {
Class.forName(dirverR);
remoteConn = DriverManager.getConnection(urlR, useranemR, passwordR);
System.out.println("-------连接对方系统成功-------");
if (!typeR.equals("oracle")) {
if (exitsTable(dirverR, urlR, useranemR, passwordR, tableName)) {
return true;
} else {
preparedStatement = remoteConn.prepareStatement(sql);
preparedStatement.execute();
return exitsTable(dirverR, urlR, useranemR, passwordR, tableName);
}
} else {
if (exitsTable(dirverR, urlR, useranemR, passwordR, tableName)) {
return true;
} else {
preparedStatement = remoteConn.prepareStatement(sql);
preparedStatement.execute();
return exitsTable(dirverR, urlR, useranemR, passwordR, tableName);
}
}
} finally {
if (preparedStatement != null) preparedStatement.close();
if (remoteConn != null) remoteConn.close();
}
}
//判断对方表是否存在
public boolean exitsTable(String dirverR, String urlR, String useranemR, String passwordR, String tableName) throws Exception {
Connection remoteConn = null;
ResultSet rs = null;
try {
Class.forName(dirverR);
remoteConn = DriverManager.getConnection(urlR, useranemR, passwordR);
rs = remoteConn.getMetaData().getTables(null, null, tableName.toUpperCase(), null);
if (rs.next()) {
return true;
} else {
return false;
}
} finally {
if (null != rs) rs.close();
if (null != remoteConn) remoteConn.close();
}
}
//获取insert模板
public Map<String, Object> sqlTemplet(List<Map<String, Object>> list, String tableName) {
List<String> fieldList = new ArrayList<>();
StringBuffer sb = new StringBuffer();
Map<String, Object> map = new HashMap<>();
sb.append("insert into ").append(tableName).append(" (");
for (Map.Entry<String, Object> entry : list.get(0).entrySet()) {
sb.append(entry.getKey()).append(",");
fieldList.add(entry.getKey());
}
sb.deleteCharAt(sb.lastIndexOf(",")).append(") values (");
for (Map.Entry<String, Object> entry : list.get(0).entrySet()) {
sb.append("?").append(",");
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append(")\n");
System.out.println(sb.toString());
map.put("fieldList", fieldList);
map.put("sqlTemplet", sb.toString());
return map;
}
}
本文由 创作，采用 知识共享署名4.0 国际许可协议进行许可。本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名。最后编辑时间为: 2020/11/04 02:36