如何将COUNT查询结果赋值给small_count变量（JLabel组件）

Hey there! Let's get that COUNT query result properly displayed in your small_count JLabel. The main issue in your current code is how you're trying to retrieve the COUNT value from the ResultSet—let's break this down step by step.

What's Wrong in the Original Code? #

Your query SELECT COUNT(SIZE) FROM uniform WHERE SIZE='S' computes the count, but it doesn't assign a column alias to the result. When you try rs.getString("COUNT"), the ResultSet doesn't recognize that name because the computed column doesn't have a label matching "COUNT".

Solution 1: Use a Column Alias (Most Readable) #

Add an alias to your COUNT expression, then reference that alias in the ResultSet. We'll also use getInt() instead of getString() since COUNT returns an integer (safer for numeric values):

private void small() {
    try {
        // Add an alias (count_s) to the COUNT result
        String q = "SELECT COUNT(SIZE) AS count_s FROM uniform WHERE SIZE='S'";
        pst = con.prepareStatement(q); // No need for explicit casting if con is Connection
        rs = pst.executeQuery();
        
        if (rs.next()) {
            // Get the integer value and convert to string for the JLabel
            int count = rs.getInt("count_s");
            small_count.setText(String.valueOf(count));
        } else {
            // Handle edge case (though COUNT always returns a number, this adds safety)
            small_count.setText("0");
        }
    } catch (Exception e) {
        JOptionPane.showConfirmDialog(null, e.getMessage());
    } finally {
        // Always close resources to avoid memory leaks
        try {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Solution 2: Use Column Index (Shorter, Less Readable) #

If you don't want to use an alias, you can reference the column by its position (since it's the first and only column in the result set):

if (rs.next()) {
    int count = rs.getInt(1); // 1 refers to the first column in the result
    small_count.setText(String.valueOf(count));
}

Bonus: Use Try-With-Resources (Best Practice) #

To automatically close your PreparedStatement and ResultSet (no need for a messy finally block), use try-with-resources. This ensures resources are closed even if an exception occurs:

private void small() {
    try (PreparedStatement pst = con.prepareStatement("SELECT COUNT(SIZE) AS count_s FROM uniform WHERE SIZE='S'");
         ResultSet rs = pst.executeQuery()) {
        
        if (rs.next()) {
            int count = rs.getInt("count_s");
            small_count.setText(String.valueOf(count));
        } else {
            small_count.setText("0");
        }
    } catch (Exception e) {
        JOptionPane.showConfirmDialog(null, e.getMessage());
    }
}

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