ExecuteDataBaseByXML

1、executedatabasebyxmlimport java.sql.drivermanager; import java.sql.preparstatement; import java.sql.reltset; import java.sql.resultsetmetadata; import java.sql.sqlexception; import java.sql.statement; import java.util.iterator; import org.dom4j.atibute; import org.dom4j.document; import org.dom4j.do

2、cumentexception; import org.dom4j.documenthelper; import org.dom4j.element; import org.dom4j.visitorsupport; import org.omg.corba.private_member; public class exedatabase private string drivername = "com.microsoft.sqerver.jdbc.sqlserverdriver" / 加载jdbc驱动 private string dburl = "jdbc:s

3、qlserver:/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 dbconnectionmanage

4、r 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 vo getconnection() throws classnotfoundexception, sqlexception cla

5、ss.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

6、if (0 ps.ecuteup() closeconnection(); mit(); return true; else return false; ch (sqlexception e) / todo auto-generated catch block e.printstacktrace(); con.rollback(); return false; * 增删改，由xml写到数据库。 解释：三个属性必需要统一即：tablename="表名" * commandtype="insert/update/delete" keyname="唯

7、一主键，不支持联合主键" 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(); str

8、ing 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 name = "" stri

9、ng valuename = "" for (iterator iel = tablenode.elementiterator(); iel .hasnext();) element eel = (element) iel.next(); colname += "," + eel.getname(); valuename += "," + "'" + eel.gettexttrim() + "'" sql += "insert into " + tablena

10、me + "(" + colname.substring(1) + ") values(" + valuename.substring(1) + ")" if (tablenode.attributevalue("commandtype").equals("update") string keyname = tablenode.attributevalue("keyname"); string keyvalue = tablenode.attributevalue("

11、;keyvalue"); string tablename = tablenode.attributevalue("tablename"); string colname = "" string valuename = "" sql += "update " + tablename + " " string sqltemp = "" for (iterator iel = tablenode.elementiterator(); iel .hasnext();) e

12、lement eel = (element) iel.next(); colname += "," + eel.getname(); valuename += "," + "'" + eel.gettexttrim() + "'" sqltemp += "," + eel.getname() + "=" + "'" + eel.gettexttrim() + "'" sql += sqltemp.subs

13、tring(1) + " where " + keyname + "='" + keyvalue + "'" if (tablenode.attributevalue("commandtype").equals("delete") string keyname = tablenode.attributevalue("keyname"); string keyvalue = tablenode.attributevalue("keyvalue"

14、;); 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

15、 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(); ele

16、ment root = (element) document.aelement("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

17、( "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 v

18、oid 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.connection("mysql", con); catch (sqlexception e) / todo auto-generated catch block con = null; e.printstacktrace(); ?xml version="1.0" eoding="gb2312"? root tablenode tablename="tem01b" commandtype=&