数据迁移（使用DatabaseMetaData生成SQL）
前段时间涉及到一个数据迁移的工作，很简单，就是迁移一部分数据到新的数据库。为了防止人工拼接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 blocke.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));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);}private static void loadDirver() {try {Class.forName("com.ibm.db2.jcc.DB2Driver");} catch (ClassNotFoundException e) {e.printStackTrace();}}}
页:
[1]