数据迁移（使用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 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();
		}
	}
}