ExecuteDataBaseByXML_JAVA_编程开发_程序员俱乐部

package P.DataBase;
//耿宗国 写于：2011.10.30
//严禁任何形式转载，版权只归作者所有。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.VisitorSupport;
import org.omg.CORBA.PRIVATE_MEMBER;

public class ExcuteDataBase {
	private String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 加载JDBC驱动
	private String dbURL = "jdbc:sqlserver://192.168.8.105:1433; DatabaseName=EMPData"; // 连接服务器和数据库sample
	private String userName = "kaka"; // 默认用户名
	private String userPwd = "123"; // 密码

	private Connection con;
	private Statement stmt;
	private PreparedStatement ps;
	private ResultSet rs;

	// private DBConnectionManager dcm = null;
	public ExcuteDataBase(String DriverName, String DbURL, String UserName,
			String UserPwd) {
		this.driverName = DriverName;
		this.dbURL = DbURL;
		this.userName = UserName;
		this.userPwd = UserPwd;
	}

	public ExcuteDataBase() {

	}

	private void GetConnection() throws ClassNotFoundException, SQLException {
		Class.forName(driverName);
		con = DriverManager.getConnection(dbURL, userName, userPwd);
	}

	/**
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private Boolean ExecuteNoQuery(String sql) throws ClassNotFoundException,
			SQLException {
		GetConnection();
		ps = con.prepareStatement(sql);
		try {
			if (0 < ps.executeUpdate()) {
				CloseConnection();
				con.commit();
				return true;
			} else
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			con.rollback();
			return false;
		}
	}

	/**
	 * 增删改，由XML写到数据库。 说明：三个属性必须要统一即：TableName="表名"
	 * CommandType="Insert/Update/Delete" KeyName="唯一主键，不支持联合主键" KeyValue="主键值"
	 * 
	 * @param doc
	 * @return
	 * @throws DocumentException
	 */
	public Boolean ExecuteXml(String doc) throws DocumentException {
		try {
			Document document = DocumentHelper.parseText(doc);
			// 每个XML必须有CommandType属性指令集，Insert,Update,Delete
			Element el = document.getRootElement();
			String sql = "";
			for (Iterator it = el.elementIterator(); it.hasNext();) {
				Element TableNode = (Element) it.next();
				if (TableNode.attributeValue("CommandType").equals("Insert")) {
					String tableName = TableNode.attributeValue("TableName");
					String colName = "";
					String valueName = "";
					for (Iterator iel = TableNode.elementIterator(); iel
							.hasNext();) {
						Element eel = (Element) iel.next();
						colName += "," + eel.getName();
						valueName += "," + "'" + eel.getTextTrim() + "'";
					}
					sql += ";insert into " + tableName + "("
							+ colName.substring(1) + ") values("
							+ valueName.substring(1) + ")";
				}
				if (TableNode.attributeValue("CommandType").equals("Update")) {
					String KeyName = TableNode.attributeValue("KeyName");
					String KeyValue = TableNode.attributeValue("KeyValue");

					String tableName = TableNode.attributeValue("TableName");
					String colName = "";
					String valueName = "";

					sql += ";update " + tableName + " set ";
					String sqlTemp = "";
					for (Iterator iel = TableNode.elementIterator(); iel
							.hasNext();) {
						Element eel = (Element) iel.next();
						colName += "," + eel.getName();
						valueName += "," + "'" + eel.getTextTrim() + "'";
						sqlTemp += "," + eel.getName() + "=" + "'"
								+ eel.getTextTrim() + "'";
					}
					sql += sqlTemp.substring(1) + " where " + KeyName + "='"
							+ KeyValue + "'";

				}
				if (TableNode.attributeValue("CommandType").equals("Delete")) {
					String KeyName = TableNode.attributeValue("KeyName");
					String KeyValue = TableNode.attributeValue("KeyValue");

					String tableName = TableNode.attributeValue("TableName");
					sql += ";delete from " + tableName + " where " + KeyName
							+ " ='" + KeyValue + "'";
				}
			}
			sql = sql.substring(1);

			return ExecuteNoQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * ExecuteSql return XMLString
	 * 
	 * @param sql
	 * @param tableName
	 * @return Xml To String
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String ExecuteXml(String sql, String tableName)
			throws ClassNotFoundException, SQLException {
		Document document = DocumentHelper.createDocument();
		Element root = (Element) document.addElement("TableNodes");
		try {
			GetConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			while (rs.next()) {
				Element el = root.addElement("TableNode").addAttribute(
						"TableName", tableName);
				for (int i = 1; i < cols; i++) {
					Element element = el.addElement(rsmd.getColumnName(i));
					element.setText(rs.getString(i));
				}
			}
			CloseConnection();
			return root.asXML();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			CloseConnection();
			return null;
		}
	}

	private void CloseConnection() {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				rs = null;
				e.printStackTrace();
			}
		}
		if (null != ps) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				ps = null;
				e.printStackTrace();
			}
		}
		if (null != con) {
			try {
				con.close();
				// dcm.freeConnection("mysql", con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				con = null;
				e.printStackTrace();
			}
		}
	}
}

?<?xml version="1.0" encoding="gb2312"?>

<Root>

<TableNode TableName="tem01b" CommandType="Insert" KeyName="PKID" KeyValue="8">

<PKID>8</PKID>

<type>2</type>

<name>E区</name>

</TableNode>
<TableNode TableName="tem01p" CommandType="Insert" KeyName="PKID" KeyValue="8">

<PKID>8</PKID>

<type>2</type>

<name>E区</name>

</TableNode>


<TableNode TableName="tem01b" CommandType="Update" KeyName="PKID" KeyValue="8">

<PKID>8</PKID>

<type>2</type>

<name>E区</name>

</TableNode>
<TableNode TableName="tem01p" CommandType="Update" KeyName="PKID" KeyValue="8">

<PKID>8</PKID>

<type>2</type>

<name>E区</name>

</TableNode>

<TableNode TableName="tem01b" CommandType="Delete" KeyName="PKID" KeyValue="8">

<PKID>8</PKID>

<type>2</type>

<name>E区</name>

</TableNode>
<TableNode TableName="tem01p" CommandType="Delete" KeyName="PKID" KeyValue="8">

<PKID>8</PKID>

<type>2</type>

<name>E区</name>

</TableNode>
</Root>