跨库迁徙指定表
跨库迁移指定表
程序如下：
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.log4j.Logger;
import org.apache.log4j.xml.DOMConfigurator;
/**
* 根据表名进行两个数据库的迁移
*
* @author 大鹏
*
*/
@SuppressWarnings("unchecked")
public class TableTransferTool {
private static final Logger logger = Logger.getLogger(TableTransferTool.class);
private static Connection connFrom = null;// 源数据源(查询数据源)
private static Connection connTo = null;// 目标数据源(更新数据源)
static {
DOMConfigurator.configureAndWatch("config/log4j.xml");
}
/**
* 根据源数据库和目标数据库的配置，初始化数据源
*/
public static void initDB(Properties propFrom, Properties propTo) throws ClassNotFoundException,
SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
connFrom = DriverManager.getConnection(propFrom.getProperty("url"), propFrom.getProperty("username"),
propFrom.getProperty("password"));
connTo = DriverManager.getConnection(propTo.getProperty("url"), propTo.getProperty("username"),
propTo.getProperty("password"));
}
/**
* 从源数据库生成指定表的创建SQL
*/
public static List<String> createInsertSql(String tableName) throws SQLException {
logger.info(tableName);
List<String> resultList = new LinkedList<String>();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = connFrom.createStatement();
rs = stmt.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
String sqlHead = "INSERT INTO " + tableName + " (";
int j;
for (j = 0; j < numberOfColumns; j++) {
// 获取指定列的名称
sqlHead += rsmd.getColumnName(j + 1) + ", ";
}
sqlHead = sqlHead.substring(0, sqlHead.length() - 2);
sqlHead += ") ";
int i = 0;
while (rs.next()) {
i++;
String sqlValues = "VALUES (";
for (j = 0; j < numberOfColumns; j++) {
String columnValue = rs.getString(rsmd.getColumnName(j + 1));
// 获取指定列的数据库特定的类型名称
String columnTypeName = rsmd.getColumnTypeName(j + 1);
if (columnValue != null) {
if ("VARCHAR2".equals(columnTypeName) || "NUMBER".equals(columnTypeName)
|| "CHAR".equals(columnTypeName)) {
columnValue = "'" + columnValue + "'";
}
else if ("DATE".equals(columnTypeName)) {
columnValue = columnValue.substring(0, columnValue.length() - 2);
columnValue = "TO_DATE('" + columnValue + "', 'YYYY-MM-DD HH24:MI:SS')";
}
}
sqlValues += columnValue + ", ";
}
sqlValues = sqlValues.substring(0, sqlValues.length() - 2);
sqlValues += ")";
logger.debug(sqlHead + sqlValues + ";");
resultList.add(sqlHead + sqlValues);
}
logger.info("生产库共有条数：" + i);
}
finally {
rs.close();
stmt.close();
}
return resultList;
}
/**
* 更新目标数据库指定表的数据
*/
public static void insertTable(List<String> insertSqlList, String tableName) throws SQLException {
Statement stmt = null;
// 关闭自动提交
connTo.setAutoCommit(false);
try {
stmt = connTo.createStatement();
int delNum = stmt.executeUpdate("DELETE FROM " + tableName);
for (String tempSql : insertSqlList) {
stmt.addBatch(tempSql);
}
int[] successNums = stmt.executeBatch();
connTo.commit();
connTo.setAutoCommit(true);
logger.info("开发库删除原有数据条数：" + delNum);
int insertNum = 0;
for (int num : successNums) {
insertNum += num;
}
logger.info("开发库新插入条数：" + insertNum);
logger.info("*********************************");
}
finally {
stmt.close();
}
}
/**
* 得到源数据库和目标数据库的用户名密码等相关配置
*/
public static Map getProperties() throws Exception {
Map resultMap = new HashMap();
// 获取数据库的配置信息
Properties config = new Properties();
config.load(ClassLoader.getSystemResourceAsStream("config/config.properties"));
// 获得连接配置
Properties propFrom = DesUtil.getConfig(config, config.getProperty("DBSource"));
Properties propTo = DesUtil.getConfig(config, config.getProperty("DBTarget"));
String tableGroups = config.getProperty("readTableGroup");
tableGroups = config.getProperty(tableGroups);
resultMap.put("DBFrom", propFrom);
resultMap.put("DBTo", propTo);
resultMap.put("tableGroups", tableGroups);
return resultMap;
}
public static void closeConnection() throws SQLException {
connFrom.close();
connTo.close();
}
public static void main(String[] args) {
try {
logger.info("************程序开始*************");
Map propMap = getProperties();
Properties propFrom = (Properties) propMap.get("DBFrom");
Properties propTo = (Properties) propMap.get("DBTo");
initDB(propFrom, propTo);
String tableGroups = (String) propMap.get("tableGroups");
logger.info("************更新数据库参数表：*************");
for (String tableName : tableGroups.split(",")) {
logger.info("************" + tableName + "*************");
}
logger.info("*********************************");
for (String tableName : tableGroups.split(",")) {
List<String> insertSqlList = createInsertSql(tableName);
insertTable(insertSqlList, tableName);
}
closeConnection();
logger.info("************程序结束*************");
}
catch (Exception e) {
e.printStackTrace();
}
}
}
config.properties文件如下：
des.isEncrypt=true key=config/key.obj readTableGroup=tableGroupA DBSource=DBFrom DBTarget=DBFrom3 ##源数据库(测试库) driver.DBFrom2= oracle.jdbc.driver.OracleDriver url.DBFrom2=jdbc:oracle:thin:@132.35.81.59:1521:essdemo username.DBFrom2=top_sysmanm password.DBFrom2=DE57F36EC8F7AC335ECA1DA0B62E0229 ##目标数据库(开发库) driver.DBTo=oracle.jdbc.driver.OracleDriver url.DBTo=jdbc:oracle:thin:@132.35.81.59:1521:essdemo username.DBTo=ucenter password.DBTo=F9EE1D8DA9829260 #组A：终端配置表 #组B：权限配置表 tableGroupA=TD_R_TERMINAL_PROPTY,TD_R_TERMINAL_COLOR,TD_R_TERMINAL_TYPE,TD_R_TERMINAL_SUBTYPE,TD_R_TERMINAL_FACTORY,TD_R_TERMINAL_BRAND,TD_R_TERMINAL_MODEL,TD_R_TERMINAL_INTPROXY,TD_R_MACHINE_TYPE tableGroupB=TD_M_FUNCRIGHT,TD_S_GUIMENU,TD_S_MODFILE tableGroupC=TD_B_PRVBRAND tableGroupD=TD_R_TERMINAL_SUBTYPE