笔记：读取sql文件，然后将数据批量插入数据库

关注

原创最新推荐文章于 2023-01-19 15:36:18 发布 511 阅读 AI 写同款· GEO 优化 ›

python批量插入数据到mysql python大批量插入mysql 阅读详情

private static List<String> loadSql(String sqlFile) throws Exception {
        List<String> sqlList = new ArrayList<String>();
        try {
            InputStream sqlFileIn = new FileInputStream(sqlFile);
            StringBuffer sqlSb = new StringBuffer();
            byte[] buff = new byte[1024];
            int byteRead = 0;
            while ((byteRead = sqlFileIn.read(buff)) != -1) {
                sqlSb.append(new String(buff, 0, byteRead));
            }
//            System.out.println( sqlSb.toString());
            // Windows 下换行是 \r\n, Linux 下是 \n
            String[] sqlArr = sqlSb.toString()
                    .split("(;\\s*\\r\\n)");
            for (int i = 0; i < sqlArr.length; i++) {
                String sql = sqlArr[i].trim();//.replaceAll("--.*", "").trim();
                if (!sql.equals("")) {
                    sqlList.add(sql);
                }
            }
            return sqlList;
        } catch (Exception ex) {
            throw new Exception(ex.getMessage());
        }
    }

    public static void main(String[] args) {
       Connection conn = null;
         Statement pst = null;
        try {
            List<String> sqlList = loadSql("E:/workspace/itest/src/main/resources/t_case.sql");
            System.out.println("size:" + sqlList.size());
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "ULIV", "123456");
            pst = conn.createStatement();
            System.out.println("sqlList.size():" + sqlList.size());
            for(int i=0;i<sqlList.size();i++){
               pst.addBatch(sqlList.get(i).toString());
               if(i !=0 && i%500 == 0){
                  pst.executeBatch();
               }
           }
         pst.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }