如何用Java向MySQL批量插入多行动态多字段数据

核心问题分析 #

当前代码仅能处理quantity字段的多值批量插入，但实际需求是每行对应一组quantity、weight、rate、price数据，需要保证同索引的字段值属于同一行，再批量插入数据库。

修改步骤及代码调整 #

1. 调整Servlet的参数接收逻辑 #

前端提交的是多行字段，每个字段（weight/rate/price）都会是数组形式，需要用getParameterValues接收并转为List，保证和quantity的索引对应：

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
        String shoe_name = request.getParameter("shoe_name");            
        // 接收所有字段的数组参数
        String[] quantityArr = request.getParameterValues("quantity");
        String[] weightArr = request.getParameterValues("weight");
        String[] rateArr = request.getParameterValues("rate");
        String[] priceArr = request.getParameterValues("price");
        String status = "2";
        
        // 转为List，空值处理
        List<String> quantity = quantityArr != null ? Arrays.asList(quantityArr) : new ArrayList<>();
        List<String> weight = weightArr != null ? Arrays.asList(weightArr) : new ArrayList<>();
        List<String> rate = rateArr != null ? Arrays.asList(rateArr) : new ArrayList<>();
        List<String> price = priceArr != null ? Arrays.asList(priceArr) : new ArrayList<>();
        
        User newUser = new User(shoe_name, quantity, weight, rate, price, status);
        
        userDAO.insertUser(newUser);
        response.sendRedirect("List");
        System.out.println("Added Successfully");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

2. 修改User实体类结构 #

将单个字段weight、rate、price改为List类型，存储多行对应的值：

public class User {
    
    private int product_id;
    private String shoe_name;
    private List<String> quantity = new ArrayList<>();
    private List<String> weight = new ArrayList<>();
    private List<String> rate = new ArrayList<>();
    private List<String> price = new ArrayList<>();
    private String status = "2";

    // 构造方法更新为接收List参数
    public User(String shoe_name, List<String> quantity, List<String> weight, List<String> rate, List<String> price, String status) {
        super();
        this.shoe_name = shoe_name;
        this.quantity = quantity;
        this.weight = weight;
        this.rate = rate;
        this.price = price;
        this.status = status;
    }

    // 新增对应List的getter方法（原有setter/getter补充完整）
    public List<String> getWeight() {
        return weight;
    }

    public void setWeight(List<String> weight) {
        this.weight = weight;
    }

    public List<String> getRate() {
        return rate;
    }

    public void setRate(List<String> rate) {
        this.rate = rate;
    }

    public List<String> getPrice() {
        return price;
    }

    public void setPrice(List<String> price) {
        this.price = price;
    }

    // 原有quantity的addQty方法可保留，其他setter/getter自行补充
    public void addQty(String quantity) {
        if (this.quantity == null) {
            this.quantity = new ArrayList<>();
        }
        this.quantity.add(quantity);
    }
}

3. 调整DAO层的批量插入逻辑 #

循环时按索引遍历，保证每一行的四个字段值对应，同时要处理字段列表长度不一致的情况（取最小长度避免数组越界）：

//INSERT ITEMS
public void insertUser(User user) throws SQLException, FileNotFoundException {
    List<String> quantityList = user.getQuantity();
    List<String> weightList = user.getWeight();
    List<String> rateList = user.getRate();
    List<String> priceList = user.getPrice();
    
    // 校验核心数据是否为空
    if (quantityList == null || quantityList.isEmpty()) {
        return;
    }
    
    // 取最小列表长度，避免索引越界
    int rowCount = Math.min(Math.min(quantityList.size(), weightList.size()), 
                            Math.min(rateList.size(), priceList.size()));
    
    try (Connection connection = getConnection();
         PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) { 
        
        for (int i = 0; i < rowCount; i++) {
            preparedStatement.setString(1, user.getShoe_name());
            // 按索引取对应行的字段值
            preparedStatement.setString(2, quantityList.get(i));
            preparedStatement.setString(3, weightList.get(i));
            preparedStatement.setString(4, rateList.get(i));
            preparedStatement.setString(5, priceList.get(i));
            preparedStatement.setString(6, user.getStatus());
            preparedStatement.addBatch();
        }   
        
        System.out.println(preparedStatement);
        preparedStatement.executeBatch();
        
    } catch (SQLException e) {
        printSQLException(e);
    } catch(Exception ex) { 
        ex.printStackTrace(); 
    } 
}

关键注意事项 #

• 前端需保证每行的quantity、weight、rate、price命名一致，例如每行的weight输入框name属性都是weight，这样后端才能接收到数组。
• 插入前需校验各字段列表的长度，避免因前端提交数据不全导致的索引越界异常。
• 建议在前端做必填校验，保证每行的四个字段都有值后再提交。

内容的提问来源于stack exchange，提问作者Bambina