你的位置:首页 > Java教程

[Java教程]简单的批量读取外部insert文并插入DB


package com.tongxiang.item.base.dao;import java.io.BufferedReader;import java.io.File;import java.io.FileNotFoundException;import java.io.FileReader;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.util.Arrays;import org.apache.commons.lang.StringUtils;public class ImportFromSql{    private static final String URL = "jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:orcl";    private static final String USER = "user";    private static final String PASSWORD = "password";    private static Connection CONN = null;    static  {    try    {      Class.forName("oracle.jdbc.driver.OracleDriver");      CONN = DriverManager.getConnection(URL, USER, PASSWORD);    }    catch (SQLException e)    {      e.printStackTrace();    }    catch (Exception e)    {      e.printStackTrace();    }  }    public static void main(String[] args) throws Exception  {    try    {      // insert 文件数据格式      // C:\\TEST.sql      // --------------------------      // insert into (...) values (...);      // insert into (...) values (...);      // insert into (...) values (...);      // --------------------------      String sqlFile = "C:\\TEST.sql";            // 每次批处理数目      int batchCnt = 100;            insertSqlBacth(CONN, sqlFile, batchCnt);    }    finally    {      CONN.close();    }  }    /**   * 传入连接来执行 SQL 脚本文件,这样可与其外的数据库操作同处一个事物中   *   * @param 数据库连接   * @param 文件路径   * @param 每次批处理数   *   */  public static void insertSqlBacth(Connection conn, String sqlFile,      int batchCnt) throws Exception  {    Statement stmt = null;    //从给定位置获取文件    File file = new File(sqlFile);    BufferedReader reader = null;    try    {      reader = new BufferedReader(new FileReader(file));      //每次读取文件的缓存      String temp = null;      int i = 0;      int mod = 0;      stmt = conn.createStatement();      while ((temp = reader.readLine()) != null)      {        i++;        stmt.addBatch(StringUtils.chomp(temp.trim(), ";"));        mod = i % batchCnt;        if (mod == 0)        {          int[] rows = stmt.executeBatch();          conn.commit();          System.out.println("Row count:" + Arrays.toString(rows));          stmt.close();          stmt = conn.createStatement();        }      }      if (mod != 0)      {        int[] rows = stmt.executeBatch();        conn.commit();        System.out.println("Row count:" + Arrays.toString(rows));        stmt.close();      }    }    catch (FileNotFoundException e)    {      e.printStackTrace();    }    catch (IOException e)    {      e.printStackTrace();    }    finally    {      //关闭文件流      if (reader != null)      {        try        {          reader.close();        }        catch (IOException e)        {          e.printStackTrace();        }      }    }  }}