java将xml文件导入mysql数据库_java实现将xml数据插入到oracle数据库的表中

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

}

}

}