java - 如何在netbeans中使用总和大小写查询

Question

我使用下面的查询:

    String qry1 = "SELECT SUM(*) FROM class_cse where Student_ID='" + StudentUserBean.getUserId() + "'";
    try {
        ResultSet rs, rs1;
        rs = db2.stmt.executeQuery(qry1);
        while (rs.next()) {
            String count = rs.getString("SUM(*)");
            jTextField6.setText(count);
        }
    } catch (SQLException ex) {
        Logger.getLogger(AdminInfo.class.getName()).log(Level.SEVERE, null, ex);
    }

现在我使用这个查询

    String qry1=select sum(case when maths = 'ABSENT' then 1 else 0 end) + 
            sum(case when ca = 'ABSENT' then 1 else 0 end) + 
            sum(case when cn = 'ABSENT' then 1 else 0 end) 
     from attendance_table;

此查询的总和在 jTextField5 中设置。你能告诉我如何使用这个查询吗？

Answer 1

你有两个选择。第一个是按位置获取结果，而不是按列名称:

String sum = rs.getString(1);

或者:

int sum = rs.getInt(1);

第二个是向结果添加别名，例如我的结果:

String qry1=select sum(case when maths = 'ABSENT' then 1 else 0 end) + 
            sum(case when ca = 'ABSENT' then 1 else 0 end) + 
            sum(case when cn = 'ABSENT' then 1 else 0 end) myresult 
     from attendance_table;

并使用它:

String count = rs.getString("myresult");