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;
    }
}