GZ/nwyj/.svn/pristine/e5/e5db02ce94b473de4a8328cae97a211693e4be69.svn-base

/***************************************************
 * Copyright 2009-8-19, -sun,All rights reserved.
 * Create date : 2009-8-19
 * Author : sun
 * 把数据库表生成的XML格式化成INSERT语句
 **************************************************/
package com.persistence.databak;

import java.io.File;
import java.io.IOException;
import java.util.Vector;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class XmlToTable{

	private final static Logger	log	= Logger.getLogger(XmlToTable.class);

	/**
	 * 根据从数据库中导出的xml文件生成INSERT语句集合 Creat sqldll.
	 * 
	 * @param filePath
	 *            the file path
	 * 
	 * @return the vector< string>
	 * 
	 * @author sun
	 * @version 2009-8-19-15:28:20
	 * 
	 *          Creat sqldll.
	 */
	public static Vector<String> creatSQLDLL(String filePath) {

		Vector<String> relultVec = new Vector<String>();
		DocumentBuilderFactory domfactory = DocumentBuilderFactory.newInstance();
		try {

			File xmlfile = new File(filePath);
			if (!xmlfile.exists()) {
				log.error("file: " + filePath + " not exist");
			}
			DocumentBuilder builder = domfactory.newDocumentBuilder();
			Document document;
			document = builder.parse(xmlfile);
			NodeList elementLst = document.getElementsByTagName("TABLES");
			Element element = (Element) elementLst.item(0);
			if (element.hasChildNodes()) {

				NodeList cLst = element.getChildNodes();
				for (int i = 0; i < cLst.getLength(); i++) {
					Node elcNode = cLst.item(i);
					// 数据库表名称
					String tbName = elcNode.getNodeName();
					if (tbName.startsWith("#"))
						continue;

					tbName = tbName.substring(0, tbName.length() - 1);
					log.info("需要恢复的表：" + tbName);
					relultVec.add(0, " DELETE FROM " + tbName);
					relultVec.addAll(getSqlByTable(document, tbName));
				}

			}

		} catch (SAXException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ParserConfigurationException e) {
			e.printStackTrace();
		}

		return relultVec;
	}

	/**
	 * 获得指定表的XML备份文件的ISERT语句集合 Gets the sql by table.
	 * 
	 * @param document
	 *            the document
	 * @param TagName
	 *            the tag name
	 * 
	 * @return the sql by table
	 * 
	 * @author sun
	 * @version 2009-8-19-14:56:46
	 * 
	 *          Gets the sql by table.
	 */
	private static Vector<String> getSqlByTable(Document document, String TagName) {

		NodeList eleLst = document.getElementsByTagName(TagName);

		ColomObj ColomObj = new ColomObj();
		/**
		 * 推算需要操作的列数据
		 */
		Vector<ColomObj> colVec = new Vector<ColomObj>();
		Element element = (Element) eleLst.item(0);
		if (element.hasChildNodes()) {
			NodeList cLst = element.getChildNodes();
			for (int i = 0; i < cLst.getLength(); i++) {
				Node elcNode = cLst.item(i);
				String colName = elcNode.getNodeName();
				if (colName.startsWith("#"))
					continue;

				ColomObj = new ColomObj();
				ColomObj.setColName(colName);
				colVec.add(ColomObj);
				// log.info("需要恢复的列：" + colName);
			}
		}

		int colVeclen = colVec.size();

		/**
		 * 生成INSERT语句到 values (
		 */
		StringBuffer prexSQL = new StringBuffer(" INSERT INTO " + TagName + " (");

		for (int i = 0; i < colVeclen; i++) {

			prexSQL.append(colVec.get(i).getColName());

			if (i == colVeclen - 1) {
				// 最后一个
				prexSQL.append(") VALUES (");
			} else {
				prexSQL.append(",");
			}
		}
		// log.info(prexSQL.toString());

		Vector<String> sqlVec = new Vector<String>();

		/**
		 * 生成数据列名称值对应的对象
		 */
		StringBuffer sqlsb = new StringBuffer();
		for (int i = 0; i < eleLst.getLength(); i++) {
			sqlsb = new StringBuffer();
			sqlsb.append(prexSQL);

			// 获得指定列的数据值
			element = (Element) eleLst.item(i);

			for (int j = 0; j < colVeclen; j++) {
				ColomObj = new ColomObj();
				String colName = colVec.get(j).getColName();
				Node eletmp = element.getElementsByTagName(colName).item(0);
				String value = null;
				if (eletmp.getFirstChild() != null) {
					value = eletmp.getFirstChild().getNodeValue();
					value = "'" + value + "'";
				}
				sqlsb.append(value);
				if (j == colVeclen - 1) {
					// 最后一个
					sqlsb.append(")");
				} else {
					sqlsb.append(",");
				}
				// log.info(colName + "对应值：" + value);
			}
			// log.info(sqlsb.toString());
			sqlVec.add(sqlsb.toString());
		}
		return sqlVec;
	}

	public static void main(String[] args) {
		try {
			Vector<String> sqlvec = creatSQLDLL("d:\\abl.xml");
			for (int i = 0; i < sqlvec.size(); i++) {
				log.info(sqlvec.get(i));
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}