Java inserts xml data into an oracle database table

Java inserts xml data into an oracle database table

1. DbUtilpackage xml2table; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException; public class DbUtil {/** function: Write a static method to establish a Connection with the database. input parameter: no return value: database Connection object */public static Connection getConnection () {// define a Connection object Connection conn = null; // define the URL resource String url = "jdbc: oracle: thin: @ 10.20.56.52: 1521: orcl "; // define the user name and password for database connection String username = "cr M "; String password =" crm "; // load the database connection driver String className =" oracle. jdbc. driver. oracleDriver "; try {Class. forName (className);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // obtain the database connection object try {conn = DriverManager. getConnection (url, username, password); System. out. println ("database connection established successfully... ");} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTr Ace () ;}// return the Connection object 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. xml2tablepackage 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. extends entexception; import org. dom4j. export enthelper; import org. dom4j. element; import org. dom4j. node; import org. dom4j. io. SAXReader; pub Lic class xml2table {public static void main (String [] args) {// SQL statement for data insertion // String SQL = "insert into bank_insurance (flag, descrip, ContNo, ProposalPrtNo, prem, PremText, Amnt, AmntText, AgentCode, plaintext, AgentName, agentcodcode, agentcodname, AgentCom, AgentComName, ComCode, ComLocation, ComName, ComZipCode, ComPhone, ValidDate, // ExpireDate, // PolicyValue, // AutoTransferAccNo, // AppntGetAccNo, // AppntProv, // AppntCi Ty, // AppntCounty, // AppntNativeplace, // AppntAddress, // AppntCustomerNo, // AppntName, // AppntSex, // AppntBirthday, // AppntIDType, // AppntIDNo, // response, // response, // AppntJobType, // AppntJobCode, // AppntJobName, // AppntNationality, // AppntStature, // AppntWeight, // AppntMaritalStatus, // AppntZipCode, // AppntMobile, // AppntPhone, // AppntEmail, // AppntRelaToInsured, // AppntEstSalary, // AppntFamilyEstSalary, // PpntLiveZone, // InsuredGetAccNo, // InsuredProv, // InsuredCity, // InsuredCounty, // region, // InsuredAddress, // InsuredName, // InsuredSex, // region, // response, // response, // InsuredJobType, // InsuredJobCode, // response, // InsuredStature, // response, // InsuredWeight, // InsuredMaritalStatus, // InsuredZipCode, // InsuredMobile, // InsuredPhone, // InsuredEmail, // InsuredEsSalary, // B Nf1Type, // Bnf1Grade, // Bnf1Name, // Bnf1Birthday, // Bnf1Sex, // Bnf1IDType, // Bnf1IDNo, // second, // Bnf1Lot, // Bnf2Type, // Bnf2Grade, // Bnf2Name, // second, // Bnf2Sex, // Bnf2IDType, // Bnf2IDNo, // second, // Bnf2Lot, // RiskCode1, // RiskName1, // MainRiskCode1, // PolApplyDate1, // SignDate1, // CValiDate1, // InsuEndDate1, // Amnt1, // Prem1, // Mult1, // PayIntv1, // PayMode1, // InsuYearFlag1, // InsuYear1, // InsuY EarFlagA1, // InsuYearA1, // Years1, // region, // PayEndYear1, // PayEndDate1, // CostIntv1, // CostDate1, // PayToDate1, // GetYearFlag1, // GetStartDate1, // GetYear1, // GetIntv1, // GetBaCode1, // success, // GetAccName1, // AutoPayFlag1, // BonusGetMode1, // SubFlag1, // response, // Account1, // EndYear0, // Cash0, // EndYear1, // Cash1, // EndYear2, // Cash2, // EndYear3, // Cash3, // EndYear4, // Cash4, // EndYear5, // Cash5 ,// EndYear6, // Cash6, // EndYear7, // Cash7, // EndYear8, // Cash8, // EndYear9, // Cash9, // EndYear10, // Cash10, // register, // SpecContent1, // RiskCode2, // RiskName2, // MainRiskCode2, // PolApplyDate2, // SignDate2, // CValiDate2, // InsuEndDate2, // Amnt2, // Prem2, // Mult2, // PayIntv2, // PayMode2, // Hangzhou, // InsuYear2, // InsuYearFlagA2, // InsuYearA2, // Years2, // PayEndYearFlag2, // PayEndYear2, // PayEndDate2, // CostIntv2, // CostD Ate2, // PayToDate2, // response, // 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, delimiter, Prem, PremText, Amnt, AmntText, AgentCode, delimiter, AgentName, 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); // read the xml file Document doc = new SAXReader (). read (new File ("E:/xml2table/source. XML "); // select the node List itemList = doc of the xml file. selectNodes ("TranData/Head"); List itemList1 = doc. selectNodes ("TranData/Body"); // traverses the nodes in the read xml // for (Iterator iter = itemList. iterator (); iter. hasNext ();) {Element el = (Element) itemList. iterator (). next (); Element el1 = (Element) itemList1.iterator (). next (); // read the node content 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 plaintext = el1.elementText ("plaintext"); String AgentName = el1.elementText ("AgentName"); String agentkgcode = plaintext ("agentkgcode"); String agentkgname = el1.elementText ("agentkgname "); 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 comment = el1.elementText ("comment "); // String nombre = el. elementText ("NOMBRE"); // traverses the content in the TURNOS node // 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); // assign pstmt to the SQL statement. 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, agentkgcode); pstmt. setString (13, agentkgname); 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.exe cuteBatch (); System. out. print ("succeeded in importing XML into the database");} catch (Exception e) {e. printStackTrace ();} finally {DbUtil. close (pstmt); DbUtil. close (conn );}}}