jdbc批量插入10万/100万条数据

川长思鸟来 2023-06-01 12:44 1阅读 0赞

jdbc批量插入10万/100万条数据

package arthur.dy.lee.mybatisplusdemo.jdbc;
    
    /** * @auther arthur.dy.lee * @since 2019/4/20 20:06 */
    
    import lombok.extern.slf4j.Slf4j;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    @Slf4j
    public class InsertJobBatch extends Thread { 
    
        public static void main(String[] args) throws Exception { 
    
            for (int i = 1; i <= 10; i++) { 
                new InsertJobBatch().start();
            }
    
        }
    
        @Override
        public void run() { 
            String url = "jdbc:mysql://localhost:3306/xxl_job?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false";
            String name = "com.mysql.jdbc.Driver";
            String user = "root";
            String password = "root";
            Connection conn = null;
            try { 
                Class.forName(name);//指定连接类型
                conn = DriverManager.getConnection(url, user, password);//获取连接
                if (conn != null) { 
                    System.out.println("获取连接成功");
                    insert(conn);
                }
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    
        public static void insert(Connection conn) { 
            // 开始时间
            Long begin = System.currentTimeMillis();
            // sql前缀
            String prefix = "INSERT INTO xxl_job_info (job_group,job_cron,job_desc,add_time,update_time,author,alarm_email,"
                    + "executor_route_strategy,executor_handler,executor_param,executor_block_strategy,executor_timeout,"
                    + "executor_fail_retry_count,glue_type,glue_source,glue_remark,glue_updatetime,child_jobid,trigger_status,"
                    + "trigger_last_time,trigger_next_time) VALUES ";
            try { 
                // 保存sql后缀
                StringBuffer sqlVal = new StringBuffer();
                // 设置事务为非自动提交
                conn.setAutoCommit(false);
                // 比起st，pst会更好些
    
                DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
                // 外层循环，总提交事务次数
                for (int i = 1; i <= 1; i++) { 
                    sqlVal = new StringBuffer();
                    // 第j次提交步长
                    for (int j = 1; j <= 10; j++) { 
                        //int id = i*j;
                        //Sequence sequence = new Sequence();
                        //int id = (int) sequence.nextId();
                        int jobGroup = 3;
                        String jobCron = "*/20 * * * * ?";
                        String jobDesc = "批量测试-" + i;
                        Date addTime = new Date();
                        String nowTime = format.format(addTime);
    
                        String author = "lee";
                        String alarmEmail = "arthur@1.com";
                        String executorRouteStrategy = "CONSISTENT_HASH";  //FIRST, CONSISTENT_HASH
                        String executorHandler = "financeHandler";
                        String executor_param = "paramArthur";
                        String executorBlockStrategy = "DISCARD_LATER"; //DISCARD_LATER, COVER_EARLY , SERIAL_EXECUTION
                        int executorTimeout = 30;
                        String executorFailRetryCount = "3";
                        String glueType = "BEAN";
                        String glueSource = "";
                        String glueRemark = "GLUE代码初始化";
                        String glueUpdatetime = nowTime;
                        String childJobid = "";
                        int triggerStatus = 1;
                        Long triggerLastTime = 0L;
                        Long triggerNextTime = 0L;
                        sqlVal.append("(");
                        //sqlVal.append(id).append(",");
                        sqlVal.append(jobGroup).append(",");
                        sqlVal.append("'" + jobCron + "',");
                        sqlVal.append("'" + jobDesc + "',");
                        sqlVal.append("'" + nowTime + "',");
                        sqlVal.append("'" + nowTime + "',");
                        sqlVal.append("'" + author + "',");
                        sqlVal.append("'" + alarmEmail + "',");
                        sqlVal.append("'" + executorRouteStrategy + "',");
                        sqlVal.append("'" + executorHandler + "',");
                        sqlVal.append("'" + executor_param + "',");
                        sqlVal.append("'" + executorBlockStrategy + "',");
                        sqlVal.append(executorTimeout).append(",");
                        sqlVal.append(executorFailRetryCount).append(",");
                        sqlVal.append("'" + glueType + "',");
                        sqlVal.append("'" + glueSource + "',");
                        sqlVal.append("'" + glueRemark + "',");
                        sqlVal.append("'" + glueUpdatetime + "',");
                        sqlVal.append("'" + childJobid + "',");
                        sqlVal.append(triggerStatus).append(",");
                        sqlVal.append(triggerLastTime).append(",");
                        sqlVal.append(triggerNextTime);
                        sqlVal.append("),");
                    }
                    // 构建完整SQL
                    String sql = prefix + sqlVal.substring(0, sqlVal.length() - 1);
                    // 添加执行SQL
                    PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql);
                    pst.addBatch(sql);
                    // 执行操作
                    pst.executeBatch();
                    // 提交事务
                    conn.commit();
                    pst.close();
                }
    
                conn.close();
            } catch (SQLException e) { 
    
                e.printStackTrace();
            }
            // 结束时间
            Long end = System.currentTimeMillis();
            // 耗时
            System.out.println("数据插入花费时间 : " + (end - begin) / 1000 + " s");
            System.out.println("插入完成");
        }
    }