[XML 파싱 실습2] XML 파일을 DB에 저장하기

#1. 기본 개념

DomParser이용해서 xml파일을 읽어와 DB에 저장하는 간단한 실습

 

#2. 실습 시나리오

1) XML 파일 읽기

2) 변환 하고자하는 형태의 VO 객체에 저장하고

3) DB에 저장

 

#3. Library

1. parser

jaxen-1.1.4.jar

xercesImpl.jar

xml-apis.jar

 

2. postgres jdbc driver

postgresql-9.2-1002.jdbc4.jar

download link : http://jdbc.postgresql.org/download.html

 

#4. Directory 구조

 

 

#5. 코드

ConnectDB.java

 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectDB {  String sqlURL  = "jdbc:postgresql://localhost:5432/postgres";     String user = "postgres";     String password = "postgres";     static Connection conn = null;      public ConnectDB(){      try {       System.out.println("try : get connection");       Class.forName("org.postgresql.Driver");          conn = DriverManager.getConnection(sqlURL, user, password);          System.out.println("success !");      } catch (SQLException e) {} catch (ClassNotFoundException e) {    // TODO Auto-generated catch block    e.printStackTrace();   }  }    public static Connection getConnection(){   return conn;  } }

 

XmlDataVO.java

 

public class XmlDataVO {  private int u_id;  private String u_name;  private String u_address;  private String u_phone;    public XmlDataVO(int u_id, String u_name, String u_address, String u_phone) {   super();   this.u_id = u_id;   this.u_name = u_name;   this.u_address = u_address;   this.u_phone = u_phone;  }    public int getU_id() {   return u_id;  }  public void setU_id(int u_id) {   this.u_id = u_id;  }  public String getU_name() {   return u_name;  }  public void setU_name(String u_name) {   this.u_name = u_name;  }  public String getU_address() {   return u_address;  }  public void setU_address(String u_address) {   this.u_address = u_address;  }  public String getU_phone() {   return u_phone;  }  public void setU_phone(String u_phone) {   this.u_phone = u_phone;  } }

 

XmlParser.java

 

import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.xml.sax.SAXException; public class XmlParser {  private DocumentBuilderFactory documentBuilderFactory;  private DocumentBuilder documentBuilder;  private Document document;  private NodeList nodeList;  public XmlParser(File file) {   DomParser(file);  }  public void DomParser(File file){         try {      documentBuilderFactory = DocumentBuilderFactory.newInstance();      documentBuilder = documentBuilderFactory.newDocumentBuilder();        document = documentBuilder.parse(file);     } catch (ParserConfigurationException e) {      // TODO Auto-generated catch block      e.printStackTrace();     } catch (SAXException e) {      // TODO Auto-generated catch block      e.printStackTrace();     } catch (IOException e) {      // TODO Auto-generated catch block      e.printStackTrace();     }  }     public List<XmlDataVO> parse(String tagName){     List<XmlDataVO> listOfData = new ArrayList<XmlDataVO>();     nodeList = document.getElementsByTagName(tagName);     for(int i = 0; i < nodeList.getLength() ; i ++){      Element element = (Element) nodeList.item(i);      int u_id = Integer.parseInt(this.getTagValue("id",element));      String u_name = this.getTagValue("name", element);      String u_address = this.getTagValue("address",element);      String u_phone = this.getTagValue("phone",element);      listOfData.add(new XmlDataVO(u_id, u_name, u_address, u_phone));     }       return listOfData;  }  private String getTagValue(String tagName, Element element) {   NodeList nodeList = element.getElementsByTagName(tagName).item(0).getChildNodes();   Node node = nodeList.item(0);   return node.getNodeValue();  } }

 

GetResult.java

 

 

import java.io.File; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.List; public class GetResult {      public static void saveDB(List<XmlDataVO> xmlList){     }    public static void main(String[] args) {   // TODO Auto-generated method stub   File file = new File("userInfo.xml");   XmlParser xmlParser = new XmlParser(file);   List<XmlDataVO> tmp = xmlParser.parse("userInfo");      ConnectDB connectDB = new ConnectDB();   Connection conn = ConnectDB.getConnection();   String sql = "insert into users values(?,?,?,?)";   try {    for(int i=0; i<tmp.size() ; i++){     PreparedStatement stmt = conn.prepareStatement(sql);     stmt.setInt(1, tmp.get(i).getU_id());     stmt.setString(2, tmp.get(i).getU_name());     stmt.setString(3, tmp.get(i).getU_address());     stmt.setString(4, tmp.get(i).getU_phone());     stmt.executeUpdate();     System.out.println("sucess to save");    }   } catch (SQLException e) {    // TODO Auto-generated catch block    e.printStackTrace();   }  }   }

userInfo.xml

 

<?xml version="1.0" encoding="UTF-8"?> <userlist>  <userInfo>   <id>1</id>   <name>bok</name>   <address>수원</address>   <phone>010</phone>  </userInfo>  <userInfo>   <id>2</id>   <name>praveen</name>   <address>인도</address>   <phone>011</phone>  </userInfo>  <userInfo>   <id>3</id>   <name>teaha</name>   <address>서울</address>   <phone>010</phone>  </userInfo>  <userInfo>   <id>4</id>   <name>kim</name>   <address>정자동</address>   <phone>011</phone>  </userInfo> </userlist>

 

 

#6. 결과화면

 

 

 

 

##.

배운것

- postgres db연결 시 jdbc driver 라이브러리 등록 필수

- DomParser는 R/W용

- SAXParser는 R용, 빠름

- OSM도 Java로 Task 처리할때는 parser사용해서 하기때문에 쓸데없이 코드가 길어지기때문에 요즘에는 xquery를 사용함.