JDBC:如何从结果集中检索SQL COUNT函数的结果? [英] JDBC: How to retrieve the result of SQL COUNT function from the result set?
问题描述
通常,当我们想从表中存在的数据库中检索一个值时,我们调用ResultSet的适当方法,并将其传递给我们要检索的列名.
Normally when we want to retrieve a value from our database which is present in the table, we call the appropriate method of ResultSet and pass it the column name which we want to retrieve.
ResultSet rs= stmt.executeQuery("select name from db.persons where school ='"+sch+"'");
int count= rs.getString("person_name");
但是,当我们想获取特定列中的行(或字段)计数时(我们使用SQL COUNT函数),但是我们如何检索结果.我应该在以下代码中的rs.getInt()方法中传递什么参数?
But when we want to get a count of rows (or fields) in a particular column (we use the SQL COUNT function) but how do we retrieve the result. What argument should I pass in the rs.getInt() method in the following piece of code?
ResultSet rs= stmt.executeQuery("select count(name) from db.persons where school ='"+sch+"'");
int count= rs.getInt( ????? );
推荐答案
为该列命名:
ResultSet rs= stmt.executeQuery("select count(name) AS count_name from db.persons where school ='"+sch+"'");
if (rs.next()) {
int count= rs.getInt("count_name");
}
您还可以传递基于1的列索引号(以防您不想修改查询).检查 ResultSet#getInt(int columnIndex)
:
You could also pass the number of the index of the column (in case you don't want to modify your query) which is 1 based. Check ResultSet#getInt(int columnIndex)
:
ResultSet rs= stmt.executeQuery("select count(name) from db.persons where school ='"+sch+"'");
if (rs.next()) {
int count= rs.getInt(1);
}
除此之外,如果您使用声明与PreparedStatement之间的区别.您的代码如下所示:
Apart from this, it would be better if you use a PreparedStatement
to execute your queries, it has many advantages over plain Statement
as explained here: Difference between Statement and PreparedStatement. Your code would look like:
String sql = "select count(name) AS count_name from db.persons where school = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, sch);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
int count = rs.getInt("count_name");
}
这篇关于JDBC:如何从结果集中检索SQL COUNT函数的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!