简单的批量读取外部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();
                }
            }
        }
    }
}

 

转载于:https://www.cnblogs.com/notDog/p/4521635.html