JavaFX表格增删记录后按name分组统计weight总和并显示

我开发了一个基于JavaFX的程序，数据存储在PostgreSQL数据库中。需求为：当表格新增或删除记录时，需按name列分组统计weight列的总和（例如仅统计name为"chocolate"的所有行weight之和），并将该总和显示在界面的"Sum"区域。以下是当前的数据库操作相关代码：

public ObservableList<BiscuitRecipe> getBiscuitMineIngredients(String name, Integer id_ing, Float weight, String photo) {

        try {
            String query = String.format("insert into biscuit(name, ingredientid, weight, photo) " +
                            "values('%s',' %s', '%s', '%s');",
                    name, id_ing, weight, photo);
            Statement statement = connect_to_db().createStatement();
            statement.executeUpdate(query);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return null;
    }

public class BiscuitRecipe {
    String name;
    int ing;
    float weight;

   String photo;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getIng() {
        return ing;
    }

    public void setIng(int ing) {
        this.ing = ing;
    }

    public float getWeight() {
        return weight;
    }

    public void setWeight(float weight) {
        this.weight = weight;
    }

    public String getPhoto() {
        return photo;
    }

    public void setPhoto(String photo) {
        this.photo = photo;
    }

    public BiscuitRecipe(String name, int ing, float weight, String photo) {
        this.name = name;
        this.ing = ing;
        this.weight = weight;
        this.photo = photo;
    }
}


 public void addMineRecipeBiscuit() {
        
        String name = filedNameBiscuit.getText();
        int ingredientID = comboIngredients.getValue().getId(); 
        float weight = Float.parseFloat(filedWeightBiscuit.getText());
        String photo = image;
        if (name.isEmpty()) {
            System.out.println("Empty!");
        } else {
            dbFunctions.getBiscuitMineIngredients(name, ingredientID, weight, photo);
            tableBiscuit.setItems(dbFunctions.getBiscuitIngredients());
        }

    }

解决方案 #

1. 优化插入方法，避免SQL注入 #

替换原插入方法为PreparedStatement实现，防止SQL注入问题：

// 替换原getBiscuitMineIngredients方法
public void addBiscuitIngredient(String name, Integer id_ing, Float weight, String photo) {
    String query = "insert into biscuit(name, ingredientid, weight, photo) values(?, ?, ?, ?);";
    try (Connection conn = connect_to_db();
         PreparedStatement pstmt = conn.prepareStatement(query)) {
        pstmt.setString(1, name);
        pstmt.setInt(2, id_ing);
        pstmt.setFloat(3, weight);
        pstmt.setString(4, photo);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

2. 添加按name统计weight总和的方法 #

在数据库操作类中新增统计方法，直接从数据库查询分组后的重量总和：

public float getTotalWeightByName(String name) {
    String query = "select sum(weight) from biscuit where name = ? group by name;";
    try (Connection conn = connect_to_db();
         PreparedStatement pstmt = conn.prepareStatement(query)) {
        pstmt.setString(1, name);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            return rs.getFloat(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return 0.0f; // 无匹配数据时返回0
}

3. 新增记录后更新Sum区域 #

修改addMineRecipeBiscuit方法，在插入数据并刷新表格后，调用统计方法更新界面（假设界面用sumWeightLabel显示总和）：

public void addMineRecipeBiscuit() {
    String name = filedNameBiscuit.getText();
    if (name.isEmpty()) {
        System.out.println("Empty name!");
        return;
    }
    int ingredientID = comboIngredients.getValue().getId(); 
    float weight;
    try {
        weight = Float.parseFloat(filedWeightBiscuit.getText());
    } catch (NumberFormatException e) {
        System.out.println("Invalid weight format!");
        return;
    }
    String photo = image;
    
    dbFunctions.addBiscuitIngredient(name, ingredientID, weight, photo);
    tableBiscuit.setItems(dbFunctions.getBiscuitIngredients());
    
    // 统计当前name的总重量并更新界面
    float totalWeight = dbFunctions.getTotalWeightByName(name);
    sumWeightLabel.setText(String.format("Sum: %.2f", totalWeight));
}

4. 删除记录后的统计更新 #

补充删除方法及界面操作，确保删除后同步更新总和：

// 数据库操作类中的删除方法
public void deleteBiscuitIngredient(int ingredientId, String name) {
    String query = "delete from biscuit where ingredientid = ? and name = ?;";
    try (Connection conn = connect_to_db();
         PreparedStatement pstmt = conn.prepareStatement(query)) {
        pstmt.setInt(1, ingredientId);
        pstmt.setString(2, name);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

// 界面删除按钮的事件处理方法
public void deleteSelectedRecipe() {
    BiscuitRecipe selected = tableBiscuit.getSelectionModel().getSelectedItem();
    if (selected == null) {
        System.out.println("No item selected!");
        return;
    }
    String name = selected.getName();
    dbFunctions.deleteBiscuitIngredient(selected.getIng(), name);
    tableBiscuit.setItems(dbFunctions.getBiscuitIngredients());
    
    // 更新总和显示
    float totalWeight = dbFunctions.getTotalWeightByName(name);
    sumWeightLabel.setText(String.format("Sum: %.2f", totalWeight));
}

5. 额外优化：监听表格数据变化自动更新统计 #

若希望表格数据发生任意变化时自动更新统计，可给表格的items添加监听器：

// 在界面初始化时添加监听器
tableBiscuit.itemsProperty().addListener((obs, oldList, newList) -> {
    String currentName = filedNameBiscuit.getText();
    if (!currentName.isEmpty()) {
        float totalWeight = dbFunctions.getTotalWeightByName(currentName);
        sumWeightLabel.setText(String.format("Sum: %.2f", totalWeight));
    }
});

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