우편번호 파일. / 테이블

zipcode.xls

zipcode2.xls

 

package excelReader;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;

import java.io.File;

import jxl.Cell;
import jxl.Workbook;
import jxl.Sheet;

 

public class Ex {
 
 String url = "jdbc:oracle:thin:@localhost:1522:java";
 String user = "yoshikix";
 String passwd = "yoshikix";
 Connection conn;
 
 public Ex() {
//    System.out.println("Ex 실행");
 }
 
 public boolean dbConn() {
  boolean flag = true;

  try {
          Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection (url, user, passwd);

  } catch ( Exception e ) {
          flag = false;
          System.out.println(e.getMessage());
  }
    
  return flag;
 }

   /**
    * Excel File Read/DB Insert
    *
    */
   public void readXls(){
    try{
     Workbook workbook = Workbook.getWorkbook(new File("c:/zipcode2.xls"));
   
     PreparedStatement pstmt = null;
   //  for(int a=0 ; a< workbook.getNumberOfSheets() ; a++){
       int a=0;
    
      Sheet sheet = workbook.getSheet(a);

      int colCnt = sheet.getColumns();
      int rowCnt = sheet.getRows();
    
      StringBuffer sb = new StringBuffer();
    
      sb.append("Insert into zipcode Values(?,?,?,?,?,?,?,?)");
     
     
    
      pstmt = conn.prepareStatement(sb.toString());
    
      for(int n=0 ; n<rowCnt ; n++){
         pstmt.setString(1,sheet.getCell(0,n).getContents());
         pstmt.setString(2,sheet.getCell(1,n).getContents());
         pstmt.setString(3,sheet.getCell(2,n).getContents());
         pstmt.setString(4,sheet.getCell(3,n).getContents());
         pstmt.setString(5,sheet.getCell(4,n).getContents());
         pstmt.setString(6,sheet.getCell(5,n).getContents());
         pstmt.setString(7,sheet.getCell(6,n).getContents());
         pstmt.setString(8,sheet.getCell(7,n).getContents());
         
         pstmt.addBatch();
      }
      pstmt.executeBatch();
      System.out.println("Batch_작업 끝");
     //}
   
     conn.close();
           pstmt.close();
         
    }catch(Exception e){
     e.printStackTrace();
     //System.out.println("error -- " + e.getMessage());
    }
   }
 

   public static void main(String args[]) {
      
       Ex ex = new Ex();

       if(ex.dbConn()) {
        System.out.println("DB 연결 성공");
      
        /* Excel File Read & DB Insert */
        ex.readXls();
              
       }else{
        System.err.println("DB 연결 실패");
       }
     
   }
}

// Zipcode 테이블 스키마.

이름	널?	유형
ZIPCODE		VARCHAR2(7)
SIDO		VARCHAR2(4)
GUGUN		VARCHAR2(15)
DONG		VARCHAR2(26)
RI		VARCHAR2(18)
BLDG		VARCHAR2(40)
BUNJI		VARCHAR2(17)
SEQ		VARCHAR2(5)