GZ/nwyj/.svn/pristine/1d/1d3f52d9f188dd8718e4f7694e28e0e8bde55f61.svn-base

package com.toolkit.databak;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.sysmodel.datamodel.Parameter.DataBaseType;
import com.sysmodel.datamodel.xmlmodel.impl.SysModelImpl;
import com.toolkit.databak.vo.RsMetaDataVo;

public class GetInsertTableDll{

	private static Log	log	= LogFactory.getLog(GetInsertTableDll.class);

	public static void getInsertDll(ArrayList<String> tables) throws Exception {

		String path = System.getProperty("user.dir").replace("\\", "/") + "/WebContent/WEB-INF/";
		SysModelImpl sysmodel = SysModelImpl.getInstance(path + "DataModel.xml");
		SysPersistence persistence = PersistenceFactory.getInstance(sysmodel);
		Connection con = null;
		Statement statament = null;
		ResultSet resultset = null;
		try {
			con = persistence.getConnection();

			for (int i = 0; i < tables.size(); i++) {
				String tableName = tables.get(i).toUpperCase();
				String sql = "select * from " + tableName;
				statament = con.createStatement();
				statament.setFetchSize(100);
				resultset = statament.executeQuery(sql);
				// ---------------------------------------------------
				ResultSetMetaData rsmd = resultset.getMetaData();
				int colCount = rsmd.getColumnCount();
				// 取得每一列列名、列类型
				String[][] fields = new String[colCount][2];
				for (int col = 1; col <= colCount; col++) {
					fields[col - 1][0] = rsmd.getColumnName(col);
					fields[col - 1][1] = rsmd.getColumnTypeName(col);
				}
				ArrayList<Vector<RsMetaDataVo>> al = new ArrayList<Vector<RsMetaDataVo>>();
				while (resultset.next()) {
					Vector<RsMetaDataVo> vecCol = new Vector<RsMetaDataVo>();
					for (int k = 0; k < fields.length; k++) {
						RsMetaDataVo RsMetaDataVo = new RsMetaDataVo();
						RsMetaDataVo.setColumnName(fields[k][0]);
						RsMetaDataVo.setColumnTypeName(fields[k][1]);
						RsMetaDataVo.setColumnValue(resultset.getString(fields[k][0]));
						vecCol.add(RsMetaDataVo);
					}
					al.add(vecCol);
				}
				// -----------------------------------------------------
				log.info("--生成表 " + tableName + "insert sql......");
				log.info("DELETE FROM	" + tableName + ";");
				getSql(sysmodel.getDataSourceByCode(1).getDataBase(), tableName, al);
				log.info("--生成表 " + tableName + "insert sql end");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;

		} finally {
			resultset.close();
			statament.close();
			con.close();
		}
	}

	private static String getSql(String database, String tableName,
			ArrayList<Vector<RsMetaDataVo>> al) {
		if (!al.isEmpty()) {
			String INSERTINTOCol = getINSERTINTOCol(tableName, al.get(0));
			for (int i = 0; i < al.size(); i++) {
				Vector<RsMetaDataVo> vecCol = al.get(i);
				StringBuffer sb = new StringBuffer();
				sb.append(INSERTINTOCol);
				sb.append("(");
				for (int j = 0; j < vecCol.size(); j++) {
					RsMetaDataVo RsMetaDataVo = vecCol.get(j);
					if (RsMetaDataVo.getColumnValue() == null) {
						sb.append("null");
					} else if (RsMetaDataVo.getColumnTypeName().equals("DATETIME")) {
						if (database.equals(DataBaseType.ORACLE)) {
							sb.append("to_date(" + RsMetaDataVo.getColumnValue() + ")");
						} else {
							sb.append("'" + RsMetaDataVo.getColumnValue() + "'");
						}

					} else {
						sb.append("'" + RsMetaDataVo.getColumnValue() + "'");
					}
					// sb.append(RsMetaDataVo.getColumnValue());
					if (j != vecCol.size() - 1)
						sb.append(", ");
				}
				sb.append(");");
			}
		}
		return null;
	}

	private static String getINSERTINTOCol(String tableName, Vector<RsMetaDataVo> vecCol) {
		StringBuffer sb = new StringBuffer("INSERT INTO " + tableName + " (");
		int len = vecCol.size();
		for (int j = 0; j < len; j++) {
			RsMetaDataVo RsMetaDataVo = vecCol.get(j);
			sb.append(RsMetaDataVo.getColumnName());
			if (j != len - 1)
				sb.append(", ");
		}
		sb.append(") VALUES ");

		return sb.toString();
	}

	public static void main(String[] args) throws Exception {
		// GetInsertTableDll dll = new GetInsertTableDll();
		ArrayList<String> tables = new ArrayList<String>();
		tables.add("BM$_Menu");
		// tables.add("AH_IsertSQLDll");
		GetInsertTableDll.getInsertDll(tables);
	}

}