JDBC : 외부 파일 읽어서 오라클 데이터베이스로 집어넣기

 외부 파일 "C:\\oracle\\ORACLE_T3.sql" 을 읽어와서 데이터베이스에 집어넣기

 

 1,135-806,서울,강남구,개포1동,경남아파트,,
2,135-807,서울,강남구,개포1동,우성3차아파트,1,6
3,135-806,서울,강남구,개포1동,우성9차아파트,901,902
4,135-770,서울,강남구,개포1동,주공아파트,1,16
5,135-805,서울,강남구,개포1동,주공아파트,17,40
6,135-966,서울,강남구,개포1동,주공아파트,41,85
7,135-807,서울,강남구,개포1동,주공아파트,86,103
8,135-805,서울,강남구,개포1동,주공아파트,104,125
9,135-807,서울,강남구,개포1동,현대1차아파트,101,106
10,135-805,서울,강남구,개포1동,,565,
11,135-806,서울,강남구,개포1동,,649,651
12,135-807,서울,강남구,개포1동,,652,653
13,135-810,서울,강남구,개포1동,,660,
14,135-241,서울,강남구,개포1동,,,
15,135-800,서울,강남구,개포2동,우성8차아파트,801,803
16,135-992,서울,강남구,개포2동,주공아파트,501,506
17,135-993,서울,강남구,개포2동,주공아파트,601,609
18,135-993,서울,강남구,개포2동,주공아파트,701,

.

.

.

.

 

칼럼간의 구분은 ","(쉼표) 로 구분!

 

 

 

 

 

import java.io.BufferedReader;

import java.io.FileReader;

import java.io.IOException;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

//jdbc : preparedStatement(insert) 우편번호 집어넣기

import java.util.StringTokenizer;

 

public class zipFor {

        public static void main(String[] args) {

              

               BufferedReader br = null;     //csv파일 읽기

               Connection conn = null;

               PreparedStatement pstmt = null;      

 

              

               try {

                       Class.forName("oracle.jdbc.driver.OracleDriver");

                       conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");

                      

                       br = new BufferedReader(new FileReader("C:\\oracle\\ORACLE_T3.sql"));

                      

                  String query = "insert into ZIPCODE3 values(?, ?, ?, ?, ?, ?, ?, ?)";

                       pstmt = conn.prepareStatement(query);

                      

                      

                       String postData = null;                      

           

            while ((postData = br.readLine()) != null) { //csv에서 받아온 파일을 배열에 저장

                 String dongs[] = postData.split(",");

                      if (dongs.length == 5){

                            pstmt.setString(6, " ");

                            pstmt.setString(7, " ");

                            pstmt.setString(8, " ");

                            for (int i = 0 ; i < 5 ; i++){

                                   pstmt.setString(i+1, dongs[i]);

                            }

                      } else if(dongs.length == 6 ){

                            pstmt.setString(7, " ");

                            pstmt.setString(8, " ");

                            for (int i = 0 ; i < 6 ; i++){

                                   pstmt.setString(i+1, dongs[i]);

                            }

                      } else if (dongs.length == 7 ){

                            pstmt.setString(8, " ");

                            for (int i = 0 ; i < 7 ; i++){

                                   pstmt.setString(i+1, dongs[i]);

                            }

                      } else{

                            for (int i = 0 ; i < 8 ; i++){

                                   pstmt.setString(i+1, dongs[i]);

                            }

                      }

                      pstmt.executeUpdate();                              

               }

                

                      

//        while ((postData = br.readLine()) != null) { //csv에서 받아온 파일을 배열에 저장

//                            StringTokenizer dong = new StringTokenizer(postData,",");

//                            int i = 1;

//                            while(dong.hasMoreTokens()){

//                                    pstmt.setString(i,(String)dong.nextToken());

//                                    i++;

//                                    pstmt.executeUpdate();

//                            }                                                

//               }

                      

                      

               } catch (ClassNotFoundException e) {

                       e.printStackTrace();

               } catch (SQLException e) {

                       e.printStackTrace();

               } catch (IOException e){

                      

               } catch (ArrayIndexOutOfBoundsException e){

                       e.printStackTrace();

                       System.out.println("ArrayIndexOutOfBoundsException 발생");

               }

               finally{

                       if(br != null) try{br.close();} catch(IOException e){};

                       if(pstmt != null) try{ pstmt.close();} catch(SQLException e){};

                       if(conn != null) try{ conn.close();} catch(SQLException e){};

               }

 

        }

}

 

 

 

 

 

강사님 Ver

import java.sql.Statement;

import java.io.BufferedReader;

import java.io.FileNotFoundException;

import java.io.FileReader;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

 

 

public class ImportEx01 {

 

 

        public static void main(String[] args) {

               BufferedReader br = null;

               Connection conn = null;

               Statement stmt = null;

              

               try {

                       br = new BufferedReader(new FileReader("C:\\oracle\\ORACLE_T3.sql"));

                       String line = null;

                      

                       int count = 1;

                       Class.forName("oracle.jdbc.driver.OracleDriver");

                       conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");

                       stmt = conn.createStatement();

                      

                      

                       while((line = br.readLine()) != null){

                              String[] words = line.trim().split(",");

                             

                              String seq = words[0];

                              String zipcode = words[1];

                              String sido = words[2];

                              String gugun = words[3];

                              String dong = words[4];

                              String ri = " ";

                              String st_bunji = " ";

                              String ed_bunji = " ";

                             

                              if (words.length == 6){

                                      ri = words[5];

                                      st_bunji = " ";

                                      ed_bunji = " ";

                              }else if (words.length == 7){

                                      ri = words[5];

                                      st_bunji = words[6];

                                      ed_bunji = " ";

                              }else if (words.length == 8){

                                      ri = words[5];

                                      st_bunji = words[6];

                                      ed_bunji = words[7];

                              }

                             

                              String query = "insert into zipcode values(" + seq + ", '" + zipcode + "', '"+ sido +"', '"+gugun+"', '"+dong+"', '"+ri+"', '"+st_bunji+"', '"+ed_bunji+"')";

//                            String query = "insert into zipcode values(seq, zipcode, sido, gugun, dong, ri, st_bunji, ed_bunji)";

                             

                              //실제 insert가 이루어 지는 구문

                              stmt.executeUpdate(query);

                             

//                            count++;

//                            if(count == 10) break;

//                            System.out.println(query);

                             

                             

                       }

 

               } catch (FileNotFoundException e) {

                       e.printStackTrace();

               } catch (IOException e) {

                       e.printStackTrace();

               } catch (ClassNotFoundException e){

                      

               } catch (SQLException e){

                      

               }

               finally{

                       if(br != null) try{br.close();} catch (IOException e) {}

                       if(stmt != null) try{stmt.close();} catch (SQLException e) {}

                       if(conn != null) try{conn.close();} catch (SQLException e) {}

               }

               System.out.println("데이터베이스 입력이 완료되었습니다.");

 

        }

 

}