java批处理，读csv文件，写入数据库

  转自：http://cxck.itpub.net/post/28897/302636

 

下面这个例子，是从tab分隔的csv文件里读取数据，把数据写入hostname上的DBNAME的USER_MASTER里。

 

import java.io.*;

import java.sql.*;

 

import oracle.jdbc.*;

 

public class Batch1 {

         private static String sDBConnect = "jdbc:oracle:thin:@hostname:1521:DBNAME";

         private static String sDBUser = "user";

         private static String sDBPassword = "password";

         private static String sPath = "C:Batch";

         private static String sFileName = "csvfile.txt";//file name divide by t

         private static int iMax=10000;//max count

         private static int iCol=4;//column number

        

         public static void main(String[] args) {

          do_Batch_User_Master();

          System.out.println("Over.");

    }

 

  public static void do_Batch_User_Master(){

           String [][]values=new String[iMax][iCol];

           int i=do_readfile(values);

           do_Batch_User_Master(values,i);

  }

 

  public static int do_readfile(String [][]values){

           int i = 0;

           String value[];

           try {

          FileReader in = new FileReader(sPath+sFileName);

          BufferedReader br = new BufferedReader(in);

          String line;

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

              value = (line+"t0").split("t");

              if (do_check_have(value,values,i)) continue;

             

              values[i] = value;

              i++;

          }

          br.close();

          in.close();

      } catch (IOException e) {

          System.out.println(e);

      }

         return i;

  }

 

  public static boolean do_check_have(String []value,String [][]values,int icnt){

           boolean bret = false;

          

           for ( int i=0;i < icnt ;i++ )

                 {

                   if(values[i][0].equals(value[0])){//double checked

                            bret = true;

                            break;

                   }

           }

          

           return bret;

  }

     

  public static void do_Batch_User_Master(String [][]values,int icnt){

           Connection con = null;

           PreparedStatement pstm = null;

           Statement       stm = null;

           ResultSet       rst = null;

           StringBuffer sb =null;

          

             try {

               DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

               con = DriverManager.getConnection(sDBConnect,sDBUser,sDBPassword);

        

               // disable autocommit

               con.setAutoCommit(false);

                               

               sb = new StringBuffer();

               sb.append("INSERT INTO USER_MASTER (USER_ID,PASSWORD,NAME,DEL_FLG) ");

               sb.append("VALUES (?,?,?,?)");

               // PrepareStatement

               pstm = con.prepareStatement( sb.toString());

                    

               for( int i=0;i<icnt;i++ )

                     {    

                   pstm.setString(1,values[i][0]);//USER_ID

                  pstm.setString(2,values[i][1]);//PASSWORD

                   pstm.setString(3,values[i][2]);//NAME

                   pstm.setString(4,values[i][3]);//DEL_FLG

                   pstm.addBatch(); 

               }

                   

               // execute SQL

               pstm.executeBatch();

 

               // commit

               con.commit();

             } catch (SQLException ex) {

                 System.out.println(ex.getMessage());

                 try {

                                   con.rollback();

                          } catch (SQLException e) {

                                   // TODO Auto-generated catch block

                                   e.printStackTrace();

                          }

              } finally {

                if ( pstm != null ) {

                  try {

                                   pstm.close();

                          } catch (SQLException e) {

                                   // TODO Auto-generated catch block

                                   e.printStackTrace();

                          }

                }

                if ( con != null ) {

                  try {

                                   con.close();

                          } catch (SQLException e) {

                                   // TODO Auto-generated catch block

                                   e.printStackTrace();

                          }

                }

              }

  }

 

}