Java读取Excel万条数据使用JDBC批处理插入数据库_小小得愿望-博客

Excel文件中有10万条数据需要导入数据库中，先将Excel文件中数据读取出来，再通过jdbc批量插入。Java读取、写入、备份Excel文件请参考这篇。

 1. 首先导入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

 2. 获取驱动和释放资源工具类

import java.sql.*;

public class JDBCUtils {

    private static  String url = "jdbc:mysql://localhost:3306/lottery_user?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false";
    private static  String username = "root";
    private static  String password = "xxxx";
    private static  String driverName = "com.mysql.jdbc.Driver";

    /**
     * 获取连接对象
     * @return 连接对象
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            // 1. 注册驱动
            Class.forName(driverName);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }


    /**
     * 释放资源
     * @param connection 连接对象
     * @param statement 预编译执行对象
     * @param resultSet 结果集
     */
    public static void releaseResources(Connection connection, PreparedStatement statement, ResultSet resultSet){
        // 释放资源
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 3. 将读取到Excel文件的数据写入数据库

public void makeCode(){
    
    //sql语句
    String sql = "insert into user (user_name) values (?)";

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    JnQrcodePrize prize = new JnQrcodePrize();
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        rs = null;

        //读取excel文件获取到数据集合
        List<String> column = ExcelUtils.getFileColumn();

        for (int i = 1; i <= column.size(); i++) {

            //设置占位符参数值
            ps.setLong(1,column.get(i-1));

            //添加批处理
            ps.addBatch();

            if(i % 1000 == 0){

                //当i整除1000时执行批处理
                ps.executeBatch();

                //清空
                ps.clearBatch();

            }
        }

        //将剩余不足1000条执行
        ps.executeBatch();

    }catch (Exception e){
        e.printStackTrace();
    }finally {

        //释放资源
        JDBCUtils.releaseResources(conn,ps,rs);

    }
}

4. ExcelUtils工具类：读取Excel数据工具类 

依赖：
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {
    //文件路径
    public static final String filePath = "C:\\Users\\admin\\Desktop\\01.xls";

    public static List<String> getFileColumn(){
        List<String> info = new ArrayList<>();
        InputStream io = null;
        Workbook readwb = null;
        try {
            File file = new File(filePath);
            io = new FileInputStream(file);

            //获取工作簿
            readwb = Workbook.getWorkbook(io);

            //获取sheet1, Sheet1: 0 ; Sheet2:1 ; Sheet3:2
            Sheet readsheet = readwb.getSheet(0);

            //获取表格列数
            //int rsColumns = readsheet.getColumns();

            //获取表格行数
            int rsRows = readsheet.getRows();

            for (int i = 0; i < rsRows; i++) {               //从第一行开始
                Cell cell_name = readsheet.getCell(2, i);    //第三列第i+1行
                String contents = cell_name.getContents();   //第i+1行第三列的值
                info.add(contents);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(io!= null){
                    io.close();
                }
                if(readwb != null){
                    readwb.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return info;
    }
}