I hope you can learn something from this function.as far as SQL->XML, using select * from xxxtable for xml raw, search xml support in SQL2K's help,that's ok. /**
*Document对象转换入库表可用数据
*param:
* doc Document需转换的数据
* conn Connection所需转入目标的连接对象
* table String传入库指定表
* nodename String为Document所需node name指定的数据集
*/
public static void document2DB(Document doc, Connection conn, String table,
String nodename,int id) {
if(doc==null||conn==null||table.equals("")||nodename.equals("")) return;
try {
//if(supptran) conn.setAutoCommit(false);
Statement stmt = conn.createStatement(); NodeList nodelist = doc.getElementsByTagName(nodename);
if (nodelist == null) {
Log.show("XMLHelper","document2DB() -no found record");
return;
}
Element node = null;
NamedNodeMap attrmap = null;
String sql = "", names = "", values = ""; for (int i = 0; i < nodelist.getLength(); i++) {
node = (Element)nodelist.item(i);
attrmap = node.getAttributes(); for (int j = 0; j < attrmap.getLength(); j++) {
// System.out.println("name:"+attrmap.item(j).getNodeName()+" j:"+j+" len:"+attrmap.getLength());
if(!attrmap.item(j).getNodeName().equals("Tablename")){
names += attrmap.item(j).getNodeName() +
( (j +1 != attrmap.getLength()) ? "," : ""); values += "'" + (attrmap.item(j).getNodeName().equals(SPEC_ID)?""+id:attrmap.item(j).getNodeValue()) + "'" +
( (j +1 != attrmap.getLength()) ? "," : "");
}else{
if(j+1==attrmap.getLength()){
names=names.substring(0,names.length()-1);
values=values.substring(0,values.length()-1);
}
}
}
// if(!isExistRecord(stmt,table,SPEC_ID,attrmap.getNamedItem(SPEC_ID).getNodeValue())){
sql = "INSERT INTO " + table + "(" + names + ") VALUES(" + values +
")";
Log.show("SQL:" + sql);stmt.execute(sql); //}else{
// Log.show("ERROR: can't accept ID:" + attrmap.getNamedItem(SPEC_ID).getNodeValue());
//}
names = values = "";
}
//if(supptran) {conn.commit();conn.setAutoCommit(true);}
stmt.close();stmt=null;
Log.show("NORMAL:Batch process of XMLHelper completed.");
}
catch (SQLException sqlex) {
Log.show("XMLHelper", "document2DB()", sqlex);
/*try {
if(supptran) conn.rollback();
}
catch (Exception any) {
Log.show("Rocllback fail", any);
}*/
}
}
*Document对象转换入库表可用数据
*param:
* doc Document需转换的数据
* conn Connection所需转入目标的连接对象
* table String传入库指定表
* nodename String为Document所需node name指定的数据集
*/
public static void document2DB(Document doc, Connection conn, String table,
String nodename,int id) {
if(doc==null||conn==null||table.equals("")||nodename.equals("")) return;
try {
//if(supptran) conn.setAutoCommit(false);
Statement stmt = conn.createStatement(); NodeList nodelist = doc.getElementsByTagName(nodename);
if (nodelist == null) {
Log.show("XMLHelper","document2DB() -no found record");
return;
}
Element node = null;
NamedNodeMap attrmap = null;
String sql = "", names = "", values = ""; for (int i = 0; i < nodelist.getLength(); i++) {
node = (Element)nodelist.item(i);
attrmap = node.getAttributes(); for (int j = 0; j < attrmap.getLength(); j++) {
// System.out.println("name:"+attrmap.item(j).getNodeName()+" j:"+j+" len:"+attrmap.getLength());
if(!attrmap.item(j).getNodeName().equals("Tablename")){
names += attrmap.item(j).getNodeName() +
( (j +1 != attrmap.getLength()) ? "," : ""); values += "'" + (attrmap.item(j).getNodeName().equals(SPEC_ID)?""+id:attrmap.item(j).getNodeValue()) + "'" +
( (j +1 != attrmap.getLength()) ? "," : "");
}else{
if(j+1==attrmap.getLength()){
names=names.substring(0,names.length()-1);
values=values.substring(0,values.length()-1);
}
}
}
// if(!isExistRecord(stmt,table,SPEC_ID,attrmap.getNamedItem(SPEC_ID).getNodeValue())){
sql = "INSERT INTO " + table + "(" + names + ") VALUES(" + values +
")";
Log.show("SQL:" + sql);stmt.execute(sql); //}else{
// Log.show("ERROR: can't accept ID:" + attrmap.getNamedItem(SPEC_ID).getNodeValue());
//}
names = values = "";
}
//if(supptran) {conn.commit();conn.setAutoCommit(true);}
stmt.close();stmt=null;
Log.show("NORMAL:Batch process of XMLHelper completed.");
}
catch (SQLException sqlex) {
Log.show("XMLHelper", "document2DB()", sqlex);
/*try {
if(supptran) conn.rollback();
}
catch (Exception any) {
Log.show("Rocllback fail", any);
}*/
}
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货