我想在mysql中计算一列，并将结果放在一个文本框中

Question

我有一个名为MemType的列的mysql数据库，这一列将显示一个成员是否处于活动状态。并非所有成员都活跃。我想统计活动成员并将结果显示在textField中。我可以做点数，但不知道结果如何回来。我的代码folows：

@FXML 
private void CountActionPerformed(ActionEvent event) { 
    String user = "root"; 
    String password = ""; 

    String ResultSet = null; // This is to ensure that ResultSet is empty. 

    try { 
     Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/club", user, password); 
     System.out.println("Connection Successful "); 

     // Create statement. 
     Statement myStmt = myConn.createStatement();  

     ResultSet myRs = myStmt.executeQuery("SELECT COUNT(MemType)\n " 
      + "FROM members \n " 
      + "WHERE MemType = 'Active ' "); 

     while (myRs.next()) { 
      CountRel.setText(myRs.getString("MemType")); 
     } 
    } catch (Exception e) { 
    } 
} 

Answer 1

，您可以给列一个这样的别名：

ResultSet myRs = myStmt.executeQuery("SELECT COUNT(MemType) as MemType " 
     + "FROM members " 
     + "WHERE MemType = 'Active ' "); 

这应该是你所需要的所有更改，你将只有一个结果（所有计数有效行）

Answer 2

结果是一个单列和单行的表。您可以使用列索引检索结果：

try { 
    try (Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/club", user, password)) { 
     System.out.println("Connection Successful "); 

     // Create statement. 
     Statement myStmt = myConn.createStatement();  

     ResultSet myRs = myStmt.executeQuery("SELECT COUNT(*) " 
      + "FROM members " 
      + "WHERE MemType = 'Active '"); 

     if (myRs.next()) { 
      CountRel.setText(Integer.toString(myRs.getInt(1))); 
     } else { 
      throw new Exception("error while counting"); 
     } 

    } 
} catch (Exception e) { 
}