使用maven构建简单的Java工程，采用JDBC批量插入数据

使用maven构建Java工程，使用简单的JDBC批量插入数据，基本步骤如下：

1.创建maven项目

2.添加依赖坐标

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.12</version>
    </dependency>
</dependencies>

3. 编写代码

package com.kwg.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author wengQ
 * @Title:
 * @Description:
 * @date 2020-07-06 09:04
 */
public class TestJdbcConnection {

    public static void main(String[] args) {
        //Connection连接对象
        Connection con = null;
        //jdbc驱动
        String driver = "com.mysql.jdbc.Driver";
        //数据库URL地址
        String url = "jdbc:mysql://localhost:3306/demo?&useSSL=false&serverTimezone=UTC";
        //连接数据库的用户名及密码
        String userName = "root";
        String password = "root";
        //SQL执行对象
        PreparedStatement preparedStatement = null;
        try {
            //开始时间
            Long startTime = System.currentTimeMillis();
            //注册JDBC驱动程序
            Class.forName(driver);
            //建立连接
            con = DriverManager.getConnection(url, userName, password);

            //准备SQL
            //String sql = "insert into tb_user values(?,?)";
            String sql = "INSERT INTO `demo`.`ord_order_items` (`id`, `version`, `integration_amount`, `level`, `merchant_icon`, " +
                    "`merchant_name`, `order_id`, `order_sn`, `product_category_id`, `product_id`, `product_name`, `product_pic`, " +
                    "`product_price`, `product_quantity`, `product_sn`, `promotion_product_id`, `real_integration_amount`, `real_price_amount`," +
                    " `create_by`, `create_time`, `modify_by`, `modify_time`, `delivery_type`, `promotion_id`, `sku`, `coupon_id`, `referrer_id`, " +
                    "`promotion_amount`, `promotion_integral`, `buy_product_id`, `coupon_amount`, `integral_amount`, `referrer_phone`, `referrer_name`," +
                    " `areaCompany_name`, `channel_name`, `referrer_areaCompany_name`, `referrer_channel_name`, `settlement_price`, `sku_desc`," +
                    " `product_price_Integral`, `expired_time`, `point_detail_id`, `point_type`, `points`, `product_category_name`, `confirm_time`, " +
                    "`refund_amount`, `refund_integration`, `refund_time`, `return_bonus_point`, `return_quantity`, `distribute_channel_id`," +
                    " `order_coupon_amount`, `settle_method`, `points_channel_id`, `settle_id`, `settle_name`) " +
                    "VALUES (?, '0', NULL, 'WHITE', '659fae46-d126-455b-a316-270adf3bf12e', 'allin商家6', '7bfc29842c13402baca8692cf2eaab02', '2020041410000072', '23', 'e8f6daf617e04dfcbabeca65fe7d893e', '库存为1', '6536a6a5-0c0e-4f30-9f22-4fca2d474f3d', '0.01', '1', '0000592', NULL, '1', '0.01', NULL, '1586855714777', NULL, '1586855714777', 'ORDINARY', NULL, 'c27e1476e3e8412a91dcde446ec71297', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'allin区域公司B', '一合会员', NULL, NULL, '0.01', NULL, '1', NULL, NULL, NULL, NULL, '存储设备', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'TRANSACTION_PRICE', NULL, 'f156da2b83ea4dfdbe80cab82c076361', '1');\n";
            /**
             * 取消自动提交
             *
             * 没有setAutoCommit(false)；那么对于每一条insert语句，都会产生一条log
             * 写入磁盘，所以虽然设置了批量插入，但其效果就像单条插入一样，导致插入速度十分缓慢
             */
            con.setAutoCommit(false);
            //预编译SQL
            preparedStatement = con.prepareStatement(sql);

            //循环插入100000万条数据
            for (int i = 1; i <= 100000; i++) {
                //preparedStatement.setString(2, "测试批量插入数据" + i);
                preparedStatement.setString(1, "" + i);
                preparedStatement.addBatch();
                // 1w条记录插入一次
                if (i % 10000 == 0) {
                    preparedStatement.executeBatch();
                    con.commit();
                }
            }
            // 最后插入不足1w条的数据
            preparedStatement.executeBatch();
            con.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("插入10万条数据共耗时 ： " + ((endTime - startTime) / 1000) + "秒");

        } catch (ClassNotFoundException e) {
            System.out.println("数据库驱动没有安装");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

4.测试运行

执行时间跟自己本地的网络有关，一般在10秒到20秒内可以执行完成.