JAVA高效批量插入数据到数据库demo

1、批量插入数据到sqlserver数据库：10000条数数据平均耗时1800ms

public static void main(String[] args) {

        PreparedStatement ps;

        Connection con = getConnection();

        String sql = "insert into t_s_log (ID,broswer,logcontent,loglevel,note, operatetime,operatetype,userid,username,realname) values(?,?,?,?,?,?,?,?,?,?)";

        try {

            ps = con.prepareStatement(sql);

            List<TSLog> logList = getList();

            for (TSLog log : logList) {

                ps.setString(1, log.getId());

                ps.setString(2, log.getBroswer());

                ps.setString(3, log.getLogcontent());

                ps.setShort(4, log.getLoglevel());

                ps.setString(5, log.getNote());

                ps.setDate(6, new java.sql.Date(log.getOperatetime().getTime()));

                ps.setShort(7, log.getOperatetype());

                ps.setString(8, log.getUserid());

                ps.setString(9, log.getUsername());

                ps.setString(10, log.getRealname());

                ps.addBatch();

            }

            long a = System.currentTimeMillis();

            ps.executeBatch();

            System.out.println("插入 " + logList.size() + " 条数据耗时 " + (System.currentTimeMillis() - a) + " 毫秒");

            con.commit();

            ps.clearBatch();

            ps.close();

        } catch (SQLException throwables) {

            throwables.printStackTrace();

        }

    }

    public static List<TSLog> getList() {

        List<TSLog> logList = new ArrayList<>();

        for (int i = 0; i < 10000; i++) {

            TSLog log = new TSLog();

            log.setId(UUID.randomUUID().toString().replaceAll("-", ""));

            log.setBroswer("Chrome");

            log.setLogcontent("测试");

            log.setLoglevel((short) 1);

            log.setNote("测试");

            log.setOperatetime(new Date());

            log.setOperatetype((short) 1);

            log.setUserid("402885af6745c5ae016745dfdb590014");

            log.setUsername("admin");

            log.setRealname("admin");

            logList.add(log);

        }

        return logList;

    }

    public static Connection getConnection() {

        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

        String dbURL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=xxx";

        String userName = "xxx";

        String userPwd = "xxx";

        try {

            Class.forName(driverName);

            System.out.println("加载SQLServer驱动类成功!");

        } catch (ClassNotFoundException a) {

            System.out.println("加载SQLServer驱动失败!");

            a.printStackTrace();

        }

        Connection dbcon = null;

        try {

            dbcon = DriverManager.getConnection(dbURL, userName, userPwd);

            System.out.println("数据库连接成功!");

        } catch (SQLException e) {

            System.out.println("数据库连接失败!");

            e.printStackTrace();

        }

        return dbcon;

    }

2、批量插入数据到mysql数据库：10000条数数据平均耗时700ms

jdbc:mysql://127.0.0.1:3306/xxx&allowMultiQueries=true&rewriteBatchedStatements=true

public List<Object[]> initJDBCDemos() {

        List<Object[]> demos = new ArrayList<>();

        for (int i = 0; i < 10000; i++) {

            Object[] demo = new Object[11];

            demo[0] = i + "" + new Date();

            demo[1] = i + "name";

            demo[2] = i + "keyWord";

            demo[3] = new Date();

            demo[4] = BigDecimal.ONE;

            demo[5] = 1d;

            demo[6] = "1";

            demo[7] = 10;

            demo[8] = new Date();

            demo[9] = "fad@qq.com";

            demo[10] = "fad@qq.com";

            demos.add(demo);

        }

        return demos;

    }

    // jdbc批量插入

    @Test

    public void testJdbcInsert100000BatchSave() {

        List<Object[]> jeecgDemoList = initJDBCDemos();

        DruidDataSource dataSource = DynamicDBUtil.getDbSourceByDbKey("qyt-dd-sys");

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        long a = System.currentTimeMillis();

        String sql = "INSERT INTO  `demo`(  `id`, `name`,\n" +

                "\t\t`key_word`,\n" +

                "\t\t`punch_time`,\n" +

                "\t\t `salary_money`,\n" +

                "\t\t `bonus_money`,\n" +

                "\t\t `sex`, `age`, `birthday`,\n" +

                "\t\t  `email`, `content`)\n" +

                "\t\tVALUES (?,?,?,?,?,?,?,?,?,?,?)";

        jdbcTemplate.batchUpdate(sql, jeecgDemoList);

        System.out.println("处理" + jeecgDemoList.size() + "条数据耗时：" + (System.currentTimeMillis() - a) + "毫秒");

    }