前段时间涉及到一个数据迁移的工作,很简单,就是迁移一部分数据到新的数据库。为了防止人工拼接SQL时出现错位,就顺手写了一个根据数据库元数据来生成SQL语句的类。以下是一些最基本的工具方法。
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
public class DataMigrationTool {
/**
* @param args
*/
public static void main(String[] args) {
loadDirver();
String[] ids = { "109159947", "173902327", "757427151", "125380114", "715426426" };
List<String> list = exportDataSQL(ids);
URL url = DataMigrationTool.class.getResource("");
Date now = new Date();
SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd_HH-mm_ss_SSS");
String filePath = new File(url.getFile() + "exported_data_" + formater.format(now) + ".sql").getAbsolutePath();
writeSQLFile(filePath, list);
}
private static List<String> exportDataSQL(String[] ids) {
List<String> allList = new ArrayList<String>();
for (String id : ids {
allList.add("------------------------------------------------");
allList.add("-- Data for id: " + id);
allList.add("------------------------------------------------");
List<String> list = generateSQLList(id);
allList.addAll(list);
}
return allList;
}
public static void writeSQLFile(String filePath, List<String> stringList) {
System.out.println("导出流程数据(SQL语句格式)到:" + filePath);
FileWriter writer = null;
try {
writer = new FileWriter(new File(filePath));
for (String sql : stringList) {
System.out.println(sql);
writer.write(sql + "\n");
}
writer.flush();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (writer != null) {
try {
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 根据id生成导出数据的SQL
*
* @param id
* @return
*/
public static List<String> generateSQLList(String id) {
List<String> sqlList = new ArrayList<String>();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection("", "", "");
stmt = con.createStatement();
DatabaseMetaData metadata = con.getMetaData();
System.out.println(metadata.getDatabaseProductName() + " " + metadata.getDatabaseMajorVersion() + "."
+ metadata.getDatabaseMinorVersion());
String tableName = "TEST";
// /
// 导出TEST
// /
sqlList.add("-- "+tableName);
String sql = generateQuerySQL(metadata, tableName, "where ID='" + id + "'");
//System.out.println(queryWorkFlowSQL);
rs = stmt.executeQuery(sql);
String workflow_expand = "";
while (rs.next()) {
String insertSQL = generateInsertSQL(rs, tableName);
sqlList.add(insertSQL);
// System.out.println(insertSQL);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return sqlList;
}
/**
* 根据结果集和表名生成INSERT语句。
*
* @param rs
* @param tableName
* @return
* @throws SQLException
*/
private static String generateInsertSQL(ResultSet rs, String tableName) throws SQLException {
if (rs == null)
return "";
StringBuilder insertSQLBuilder = new StringBuilder();
insertSQLBuilder.append("insert into ").append(tableName).append(" (");
DatabaseMetaData metadata = rs.getStatement().getConnection().getMetaData();
insertSQLBuilder.append(getColumnsString(metadata, tableName));
insertSQLBuilder.append(") values (");
String[] cols = getColumnsArray(metadata, tableName);
int len = cols.length;
for (int i = 0; i < len; i++) {
String value = StringUtils.trimToEmpty(rs.getString(cols[i]));
if (i != len - 1) {
insertSQLBuilder.append("'").append(value).append("', ");
} else {
insertSQLBuilder.append("'").append(value).append("' ");
}
}
insertSQLBuilder.append(")");
return insertSQLBuilder.toString();
}
/**
* 根据表名数据库元数据和条件生成查询语句
*
* @param metadata
* @param tableName
* @param condition
* 查询条件 (eg:where ID=1)
* @return
* @throws SQLException
*/
private static String generateQuerySQL(DatabaseMetaData metadata, String tableName, String condition) throws SQLException {
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("select ");
queryBuilder.append(getColumnsString(metadata, tableName));
queryBuilder.append(" from ").append(tableName).append(" ").append(condition);
return queryBuilder.toString();
}
private static String getColumnsString(DatabaseMetaData metadata, String tableName) throws SQLException {
String[] cols = getColumnsArray(metadata, tableName);
return StringUtils.join(cols, ", ");
}
/**
* 根据表名和数据库元数据获得所有列的List
*
* @param metadata
* @param tableName
* @return
* @throws SQLException
*/
private static List<String> getColumnsList(DatabaseMetaData metadata, String tableName) throws SQLException {
List<String> columns = new ArrayList<String>();
ResultSet rs = metadata.getColumns(null, null, tableName, "%");
while (rs.next()) {
columns.add(rs.getString("COLUMN_NAME"));
}
return columns;
}
/**
* 根据表名和数据库元数据获得所有列的数组。
*
* @param metadata
* @param tableName
* @return
* @throws SQLException
*/
private static String[] getColumnsArray(DatabaseMetaData metadata, String tableName) throws SQLException {
List<String> list = getColumnsList(metadata, tableName);
return list.toArray(new String[list.size()]);
}
private static void loadDirver() {
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}