Java 读取Text,JDBC批量插入

prepareStatement

String sql = "insert ignore into db.test_table(a,b,c,d,e) VALUES (?,?,1,1,0)";
PreparedStatement pstmt = null;
int i=0;
try {
pstmt = connection.prepareStatement(sql);
for(Integer peopleID:peopleIDList){
for(Integer gameID:gameIDList){
pstmt.setInt(1, peopleID);
pstmt.setInt(2,gameID );
pstmt.addBatch();
i++;
}
pstmt.executeBatch();
}

需使用预处理,否则不生效

批量插入静态SQL使用 Statement

	ObaseConnection connection = JdbcUtil.getConnection();
	connection.setAutoCommit(false);
	Statement prepareStatement = connection.createStatement();
	prepareStatement.addBatch(resSql);
 	prepareStatement.executeBatch();
	prepareStatement.clearBatch();
	connection.commit();

Demo

public class Main {
    public static void main(String[] args) throws SQLException {
        ObaseConnection connection = JdbcUtil.getConnection();
        connection.setAutoCommit(false);
        System.out.println(Main.class.getResource("/"));
        String replace = Main.class.getResource("/").toString().replace("file:/", "");

        /*文件目录*/
        String path = replace + "pgxxb_202112011057.sql";
//        String path = replace + "pgxxb_202112011055.sql";
//        String path = replace + "pgxxb_202112011103.sql";
        /*文件目录*/

        /*读取文件*/
        File file = new File(path);
        int count = 1;
        int failCount = 0;
        long startTime = System.currentTimeMillis();

        StringBuffer sql = new StringBuffer();
        String resSql;
        Statement prepareStatement = connection.createStatement();
        String s;
        BufferedReader br = null;
        try {
            //构造一个BufferedReader类来读取文件
            br = new BufferedReader(new FileReader(file));
            while ((s = br.readLine()) != null) {
                sql.append(s);
                if (s.endsWith(");")) {
                    resSql = sql.toString().replace("\t", "");
                    prepareStatement.addBatch(resSql);
                    try {
                        if (count % 10 == 0) {
                            System.out.println("执行  ----------------- " + count + "  -----------------  条!");
                            prepareStatement.executeBatch();
                            prepareStatement.clearBatch();
                            connection.commit();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } finally {
                        count++;
                    }
                    sql.setLength(0);
                }
            }
            prepareStatement.executeBatch();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            br.close();
            prepareStatement.close();
            connection.close();
            long endTime = System.currentTimeMillis();
            System.out.println(String.format("成功 %s 条  失败 %s 批次! 用时: %s 分!", count, failCount, (startTime - endTime) / 1000 / 60));
        }
    }

}