Java 如何检查结果集是否有一行或多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2591732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if resultset has one row or more?
提问by TopCoder
How to check if resultset has one row or more with JDBC?
如何使用JDBC检查结果集是否有一行或多行？
采纳答案by Marcus Adams
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
boolean isMoreThanOneRow = rs.first() && rs.next();
You didn't ask this one, but you may need it:
你没有问这个，但你可能需要它：
boolean isEmpty = ! rs.first();
Normally, we don't need the row count because we use a WHILE loop to iterate through the result set instead of a FOR loop:
通常，我们不需要行数，因为我们使用 WHILE 循环而不是 FOR 循环来遍历结果集：
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
// retrieve and print the values for the current row
int i = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
System.out.println("ROW = " + i + " " + s + " " + f);
}
However, in some cases, you might want to window the results, and you need the record count ahead of time to display to the user something like Row 1 to 10 of 100. You can do a separate query with SELECT COUNT(*)first, to get the record count, but note that the count is only approximate, since rows can be added or removed between the time it takes to execute the two queries.
但是，在某些情况下，您可能希望对结果进行窗口化，并且您需要提前记录计数以向用户显示类似Row 1 to 10 of 100. 您可以先进行单独的查询SELECT COUNT(*)，以获取记录计数，但请注意，该计数只是近似值，因为可以在执行两个查询所需的时间内添加或删除行。
Sample from ResultSet Overview
回答by Carl Smotricz
My no-brainer suggestion: Fetch the first result row, and then try to fetch the next. If the attempt is successful, you have more than one row.
我的明智建议：获取第一个结果行，然后尝试获取下一个。如果尝试成功，则您有不止一行。
If there is more than one row and you want to process that data, you'll need to either cache the stuff from the first row, or use a scrollable result set so you can seek back to the top before going through the results.
如果有多于一行并且您想要处理该数据，则需要缓存第一行中的内容，或者使用可滚动的结果集，以便您可以在查看结果之前返回顶部。
You can also ask SQL directly for this information by doing a SELECT COUNT(*)on the rest of your query; the result will be 0, 1 or more depending on how many rows the rest of the query would return. That's pretty easy to implement but involves two queries to the DB, assuming you're going to want to read and process the actual query next.
您还可以通过SELECT COUNT(*)对查询的其余部分执行 a来直接向 SQL 询问此信息；结果将是 0、1 或更多，具体取决于查询的其余部分将返回多少行。这很容易实现，但涉及对数据库的两个查询，假设您接下来要读取和处理实际查询。
回答by mindas
There are many options, and since you don't provide more context the only thing left is to guess. My answers are sorted by complexity and performance ascending order.
有很多选择，并且由于您没有提供更多上下文，因此剩下的唯一事情就是猜测。我的答案按复杂性和性能升序排序。
- Just run
select count(1) FROM ...and get the answer. You'd have to run another query that actually selects and returns the data. - Iterate with
rs.next()and count until you're happy. Then if you still need the actual data re-run same query. - If your driver supports backwards iteration, go for
rs.next()couple of times and then rewind back withrs.previous().
- 只需运行
select count(1) FROM ...并获得答案。您必须运行另一个实际选择并返回数据的查询。 - 迭代
rs.next()并计数，直到您满意为止。然后，如果您仍然需要实际数据，请重新运行相同的查询。 - 如果您的驱动程序支持向后迭代，请执行
rs.next()几次，然后使用rs.previous().
回答by BalusC
You don't need JDBC for this. The normal idiom is to collect all results in a collection and make use of the collection methods, such as List#size().
为此，您不需要 JDBC。正常的习惯用法是将所有结果收集到一个集合中并使用收集方法，例如List#size().
List<Item> items = itemDAO.list();
if (items.isEmpty()) {
// It is empty!
if (items.size() == 1) {
// It has only one row!
} else {
// It has more than one row!
}
where the list()method look like something:
该list()方法看起来像什么：
public List<Item> list() throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Item> items = new ArrayList<Item>();
try {
connection = database.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(SQL_LIST);
while (resultSet.next()) {
Item item = new Item();
item.setId(resultSet.getLong("id"));
item.setName(resultSet.getString("name"));
// ...
items.add(item);
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
return items;
}
回答by Dinesh B
Get the Row Count using ResultSetMetaDataclass.
使用ResultSetMetaData类获取行数。
From your code u can create ResultSetMetaDatalike :
从您的代码中，您可以创建ResultSetMetaData如下：
ResultSetMetaData rsmd = resultSet.getMetaData(); //get ResultSetMetaData
rsmd.getColumnCount(); // get row count from resultsetmetadata
回答by Dipyaman Deb
public int rowCountValue(ResultSet rsValue) throws SQLException {
ResultSet rowCountValue = rsValue;
int countRow = 0;
while (rowCountValue.next()) {
countRow++;
}
return countRow;
}
回答by Garfield
If you want to make sure that there is exactlyone row, you can ensure that the first row is the last:
如果你想确保有恰好一列，可以确保第一行是最后：
ResultSet rs = stmt.executeQuery("SELECT a FROM Table1 WHERE b=10");
if (rs.isBeforeFirst() && rs.next() && rs.isFirst() && rs.isLast()) {
// Logic for where there's exactly 1 row
Long valA = rs.getLong("a");
// ...
}
else {
// More that one row or 0 rows returned.
// ..
}
回答by Pran Kumar Sarkar
This implementation allows you to check for whether result of the query is empty or not at the cost of duplicating some lines.
此实现允许您以复制某些行为代价来检查查询结果是否为空。
ResultSet result = stmt.executeQuery("SELECT * FROM Table");
if(result.next()) {
// Duplicate the code which should be pasted inside while
System.out.println(result.getInt(1));
System.out.println(result.getString(2));
while(result.next()){
System.out.println(result.getInt(1));
System.out.println(result.getString(2));
}
}else{
System.out.println("Query result is empty");
}
Drawbacks:
缺点：
- In this implementation a portion of the code will be duplicated.
- You cannot know how many lines are present in the result.
- 在这个实现中，部分代码将被复制。
- 您无法知道结果中有多少行。
