首页 > 代码库 > dom4j读取xml文档,通过JDBC写入数据库

dom4j读取xml文档,通过JDBC写入数据库

  最近一段时间,每天上班都把时间用在看文档上,动手写代码的比重大大减少。今天无意中看到公司的一个面试题,顺手拿过来做了一下,简单的几个类没想到竟然用了将近一下午的时间,其间还得不断地依靠google,最后的插入sql甚至只写了关键词insert 漏掉了into。常时间不写,手生的可怕。以后要随时看随时记随时写。

  记一下基本的知识点。

 

  

读取文档monthTotalData.xml

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <data> 3     <monthTotalData> 4         <year>1996</year> 5         <month>1</month> 6         <total>200</total> 7         <employeeId>2</employeeId> 8     </monthTotalData> 9 10     <monthTotalData>11         <year>1996</year>12         <month>2</month>13         <total>300</total>14         <employeeId>2</employeeId>15     </monthTotalData>16 17     <monthTotalData>18         <year>1996</year>19         <month>5</month>20         <total>500</total>21         <employeeId>3</employeeId>22     </monthTotalData>23 24     <monthTotalData>25         <year>1997</year>26         <month>8</month>27         <total>500</total>28         <employeeId>3</employeeId>29     </monthTotalData>30 31     <monthTotalData>32         <year>1997</year>33         <month>9</month>34         <total>600</total>35         <employeeId>2</employeeId>36     </monthTotalData>37 38     <monthTotalData>39         <year>1997</year>40         <month>10</month>41         <total>600</total>42         <employeeId>2</employeeId>43     </monthTotalData>44 45     <monthTotalData>46         <year>2009</year>47         <month>3</month>48         <total>600</total>49         <employeeId>1</employeeId>50     </monthTotalData>51 52     <monthTotalData>53         <year>2010</year>54         <month>5</month>55         <total>700</total>56         <employeeId>1</employeeId>57     </monthTotalData>58 59     <monthTotalData>60         <year>2009</year>61         <month>9</month>62         <total>500</total>63         <employeeId>2</employeeId>64     </monthTotalData>65 </data>

实体类MonthTotal.java

 1 package domain; 2  3 public class MonthTotal { 4  5     private int id; 6     private int year; 7     private int month; 8     private int total; 9     private int employee_id;10     11     public MonthTotal(){}12     13     public MonthTotal(int year, int month, int total, int employeeId) {14         super();15         this.year = year;16         this.month = month;17         this.total = total;18         employee_id = employeeId;19     }20 21     public int getId() {22         return id;23     }24     public void setId(int id) {25         this.id = id;26     }27     public int getYear() {28         return year;29     }30     public void setYear(int year) {31         this.year = year;32     }33     public int getMonth() {34         return month;35     }36     public void setMonth(int month) {37         this.month = month;38     }39     public int getTotal() {40         return total;41     }42     public void setTotal(int total) {43         this.total = total;44     }45     public int getEmployee_id() {46         return employee_id;47     }48     public void setEmployee_id(int employeeId) {49         employee_id = employeeId;50     }51     @Override52     public String toString() {53         return "Month_total [employee_id=" + employee_id + ", id=" + id54                 + ", month=" + month + ", total=" + total + ", year=" + year55                 + "]";56     }57      58 }

插入方法类InsertDB.java

 1 package insertDB; 2  3 import java.io.File; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.PreparedStatement; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import org.dom4j.Document;11 import org.dom4j.DocumentException;12 import org.dom4j.Element;13 import org.dom4j.io.SAXReader;14 15 import domain.MonthTotal;16 17 public class InsertDB {18 19     public void insert(MonthTotal month) {20         try {21             Class.forName("com.mysql.jdbc.Driver");22             Connection conn = DriverManager.getConnection(23                     "jdbc:mysql://localhost:3306/test", "root", "root");24 25             String sql = "insert into month_total (year, month, employee_id, total) values ("26                     + month.getYear()27                     + ","28                     + month.getMonth()29                     + ", "30                     + month.getEmployee_id() + ", " + month.getTotal() + ");  ";31 32             System.out.println(sql);33             PreparedStatement psmt = conn.prepareStatement(sql);34             psmt.execute();35 36         } catch (Exception e) {37             e.printStackTrace();38         }39     }40 41     public static void main(String[] args) {42         InsertDB i = new InsertDB();43         List<MonthTotal> list = i.getElementsList();44         for(MonthTotal m : list){45             i.insert(m);46         }47     }48 49     @SuppressWarnings("unchecked")50     public List<MonthTotal> getElementsList() {51         String filePath = "src/monthTotalData.xml";52         File f = new File(filePath);53         SAXReader reader = new SAXReader();54         List<MonthTotal> monthList = new ArrayList<MonthTotal>();55         try {56             Document docment = reader.read(f);57             Element root = docment.getRootElement();58 59             List<Element> months = new ArrayList<Element>();60 61             months = root.elements();62             for (Element e : months) {63                 int year = Integer.parseInt(e.elementText("year"));64                 int month = Integer.parseInt(e.elementText("month"));65                 int employeeId = Integer.parseInt(e.elementText("employeeId"));66                 int total = Integer.parseInt(e.elementText("total"));67                 MonthTotal m = new MonthTotal(year, month, total, employeeId);68 69                 monthList.add(m);70             }71 72         } catch (DocumentException e) {73             e.printStackTrace();74         }75         return monthList;76     }77 }

 

dom4j读取xml文档,通过JDBC写入数据库