1、DbUtil
package xml2table;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DbUtil {
/*
* 功能:编写一个静态方法用于与数据库建立连接 输入参数:无 返回值:数据库连接对象
*/
public static Connection getConnection() {
// 定义一个连接对象
Connection conn = null;
// 定义连接数据库的URL资源
String url = "jdbc:oracle:thin:@10.20.56.52:1521:orcl";
// 定义连接数据库的用户名称与密码
String username = "crm";
String password = "crm";
// 加载数据库连接驱动
String className = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 获取数据库的连接对象
try {
conn = DriverManager.getConnection(url, username, password);
System.out.println("数据库连接建立成功...");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 返回连接对象
return conn;
}
public static void close(Connection c)
{
if(c!=null)
{
try {
c.close();
} catch (Throwable e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement c)
{
if(c!=null)
{
try {
c.close();
} catch (Throwable e) {
e.printStackTrace();
}
}
}
}
2、xml2table
package xml2table;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Iterator;
import java.util.List;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
public class xml2table {
public static void main(String[] args){
//插入数据的sql语句
// String sql="insert into bank_insurance(flag,descrip,ContNo,ProposalPrtNo,Prem,PremText,Amnt,AmntText,AgentCode,AgentCertNo,AgentName,AgentGrpCode,AgentGrpName,AgentCom,AgentComName,ComCode,ComLocation,ComName,ComZipCode,ComPhone,ValidDate,
//ExpireDate,
//PolicyValue,
//AutoTransferAccNo,
//AppntGetAccNo,
//AppntProv,
//AppntCity,
//AppntCounty,
//AppntNativeplace,
//AppntAddress,
//AppntCustomerNo,
//AppntName,
//AppntSex,
//AppntBirthday,
//AppntIDType,
//AppntIDNo,
//AppntIdEfDate,
//AppntIdExpDate,
//AppntJobType,
//AppntJobCode,
//AppntJobName,
//AppntNationality,
//AppntStature,
//AppntWeight,
//AppntMaritalStatus,
//AppntZipCode,
//AppntMobile,
//AppntPhone,
//AppntEmail,
//AppntRelaToInsured,
//AppntEstSalary,
//AppntFamilyEstSalary,
//AppntLiveZone,
//InsuredGetAccNo,
//InsuredProv,
//InsuredCity,
//InsuredCounty,
//InsuredNativeplace,
//InsuredAddress,
//InsuredName,
//InsuredSex,
//InsuredBirthday,
//InsuredIDType,
//InsuredIDNo,
//InsuredJobType,
//InsuredJobCode,
//InsuredJobName,
//InsuredStature,
//InsuredNationality,
//InsuredWeight,
//InsuredMaritalStatus,
//InsuredZipCode,
//InsuredMobile,
//InsuredPhone,
//InsuredEmail,
//InsuredEsSalary,
//Bnf1Type,
//Bnf1Grade,
//Bnf1Name,
//Bnf1Birthday,
//Bnf1Sex,
//Bnf1IDType,
//Bnf1IDNo,
//Bnf1RelaToInsured,
//Bnf1Lot,
//Bnf2Type,
//Bnf2Grade,
//Bnf2Name,
//Bnf2Birthday,
//Bnf2Sex,
//Bnf2IDType,
//Bnf2IDNo,
//Bnf2RelaToInsured,
//Bnf2Lot,
//RiskCode1,
//RiskName1,
//MainRiskCode1,
//PolApplyDate1,
//SignDate1,
//CValiDate1,
//InsuEndDate1,
//Amnt1,
//Prem1,
//Mult1,
//PayIntv1,
//PayMode1,
//InsuYearFlag1,
//InsuYear1,
//InsuYearFlagA1,
//InsuYearA1,
//Years1,
//PayEndYearFlag1,
//PayEndYear1,
//PayEndDate1,
//CostIntv1,
//CostDate1,
//PayToDate1,
//GetYearFlag1,
//GetStartDate1,
//GetYear1,
//GetIntv1,
//GetBaCode1,
//GetBankAccNo1,
//GetAccName1,
//AutoPayFlag1,
//BonusGetMode1,
//SubFlag1,
//FullBonusGetMode1,
//Account1,
//EndYear0,
//Cash0,
//EndYear1,
//Cash1,
//EndYear2,
//Cash2,
//EndYear3,
//Cash3,
//EndYear4,
//Cash4,
//EndYear5,
//Cash5,
//EndYear6,
//Cash6,
//EndYear7,
//Cash7,
//EndYear8,
//Cash8,
//EndYear9,
//Cash9,
//EndYear10,
//Cash10,
//BonusValues1 ,
//SpecContent1,
//RiskCode2,
//RiskName2,
//MainRiskCode2,
//PolApplyDate2,
//SignDate2,
//CValiDate2,
//InsuEndDate2,
//Amnt2,
//Prem2,
//Mult2,
//PayIntv2,
//PayMode2,
//InsuYearFlag2,
//InsuYear2,
//InsuYearFlagA2,
//InsuYearA2,
//Years2,
//PayEndYearFlag2,
//PayEndYear2,
//PayEndDate2,
//CostIntv2,
//CostDate2,
//PayToDate2,
//GetYearFlag2,
//GetStartDate2,
//GetYear2,
//GetIntv2,
//GetBankCode2,
//GetBankAccNo2,
//GetAccName2,
//AutoPayFlag2,
//BonusGetMode2,
//SubFlag2,
//FullBonusGetMode2,
//Account2,
//BonusValues2,
//SpecContent2) values (?,?,?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,
//?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//String sql="insert into test1(flag, descrip, ContNo, ProposalPrtNo) values (?, ?, ?, ?)";
//String sql="insert into test01(flag, descrip)"
//+"values (?,?)";
String sql="insert into test02(flag, descrip,ContNo,ProposalPrtNo,Prem,PremText,Amnt,AmntText,AgentCode,AgentCertNo,AgentName,AgentGrpCode,AgentGrpName,AgentCom,AgentComName,ComCode,ComLocation,ComName,ComZipCode,ComPhone,ContState,ValidDate,ExpireDate,PolicyValue,AutoTransferAccNo)"
+"values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn=DbUtil.getConnection();
pstmt=conn.prepareStatement(sql);
//读取xml文件
Document doc=new SAXReader().read(new File("E:/xml2table/source.XML"));
//选择xml文件的节点
List itemList=doc.selectNodes("TranData/Head");
List itemList1=doc.selectNodes("TranData/Body");
//遍历读出的xml中的节点
// for(Iterator iter=itemList.iterator();iter.hasNext();){
Element el=(Element)itemList.iterator().next();
Element el1=(Element)itemList1.iterator().next();
//读取节点内容
String Flag=el.elementText("Flag");
String Desc= el.elementText("Desc");
String ContNo = el1.elementText("ContNo");
String ProposalPrtNo = el1.elementText("ProposalPrtNo");
String Prem = el1.elementText("Prem");
String PremText = el1.elementText("PremText");
String Amnt = el1.elementText("Amnt");
String AmntText = el1.elementText("AmntText");
String AgentCode = el1.elementText("AgentCode");
String AgentCertNo = el1.elementText("AgentCertNo");
String AgentName = el1.elementText("AgentName");
String AgentGrpCode = el1.elementText("AgentGrpCode");
String AgentGrpName = el1.elementText("AgentGrpName");
String AgentCom = el1.elementText("AgentCom");
String AgentComName = el1.elementText("AgentComName");
String ComCode = el1.elementText("ComCode");
String ComLocation = el1.elementText("ComLocation");
String ComName = el1.elementText("ComName");
String ComZipCode = el1.elementText("ComZipCode");
String ComPhone = el1.elementText("ComPhone");
String ContState = el1.elementText("ContState");
String ValidDate = el1.elementText("ValidDate");
String ExpireDate = el1.elementText("ExpireDate");
String PolicyValue = el1.elementText("PolicyValue");
String AutoTransferAccNo = el1.elementText("AutoTransferAccNo");
//String nombre = el.elementText("NOMBRE");
//遍历TURNOS节点中的内容
// List turnosList = el.elements("TURNOS");
// StringBuffer sbString=new StringBuffer();
// for(Iterator iter1=turnosList.iterator();iter1.hasNext();){
// Element turnosElt=(Element)iter1.next();
// String lu = turnosElt.elementText("LU");
// String ma = turnosElt.elementText("MA");
// String mi = turnosElt.elementText("MI");
// String ju = turnosElt.elementText("JU");
// String vi = turnosElt.elementText("VI");
// String sa = turnosElt.elementText("SA");
// String doo = turnosElt.elementText("DO");
// sbString.append(lu + "," + ma + "," + mi + "," + ju + "," + vi + "," + sa + "," + doo);
// }
//为sql语句赋值
pstmt.setString(1, Flag);
pstmt.setString(2, Desc);
pstmt.setString(3, ContNo);
pstmt.setString(4, ProposalPrtNo);
pstmt.setString(5, Prem);
pstmt.setString(6, PremText);
pstmt.setString(7, Amnt);
pstmt.setString(8, AmntText);
pstmt.setString(9, AgentCode);
pstmt.setString(10, AgentCertNo);
pstmt.setString(11, AgentName);
pstmt.setString(12, AgentGrpCode);
pstmt.setString(13, AgentGrpName);
pstmt.setString(14, AgentCom);
pstmt.setString(15, AgentComName);
pstmt.setString(16, ComCode);
pstmt.setString(17, ComLocation);
pstmt.setString(18, ComName);
pstmt.setString(19, ComZipCode);
pstmt.setString(20, ComPhone);
pstmt.setString(21, ContState);
pstmt.setString(22, ValidDate);
pstmt.setString(23, ExpireDate);
pstmt.setString(24,PolicyValue);
pstmt.setString(25,AutoTransferAccNo);
pstmt.addBatch();
// }
pstmt.executeBatch();
System.out.print("将XML导入数据库成功");
}catch(Exception e){
e.printStackTrace();
}finally{
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
}