java批量插入数据

java批量插入数据

1.封装jdbc工具类

mysql服务器默认是关闭批处理的，增加&rewriteBatchedStatements=true，mysql开启批处理的支持。

spring.datasource.platform=mysql
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/book?useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true&tinyInt1isBit=false&rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.jdbc.Driver

package com.vcredit.jdev.base.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class JDBCUtils {


    /**
     * @return
     * @throws Exception
     * @Description 获取数据库的连接
     */
    public static Connection getConnection() throws Exception {
       // 1.读取配置文件application.properties中的4个基本信息 
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("application-dev.properties");
     
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("spring.datasource.username");
        String password = pros.getProperty("spring.datasource.password");
        String url = pros.getProperty("spring.datasource.url");
        String driverClass = pros.getProperty("spring.datasource.driverClassName");
        // 2.加载驱动
        Class.forName(driverClass);

        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    
    /**
     *
     * @Description 关闭资源操作
     * @param conn
     * @param ps
     * @param rs
     */
    public static void closeResource(Connection conn, Statement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @Description 关闭资源操作
     * @param conn
     * @param ps
     * @param rs
     */
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

2.测试

   @Test
    public void test01() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            //设置不允许自动提交数据
            conn.setAutoCommit(false);
            String sql = "insert into t_user(mobile)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 5; i++) {
                ps.setObject(1, "175" + i);
                //1."积攒"sql
                ps.addBatch();
                //2.执行batch
                ps.executeBatch();
                //3.清空batch
                ps.clearBatch();
            }
            //提交数据
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

    }