Java 如何发现执行的 SQL 查询没有返回任何内容？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3524107/
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 find that a SQL query executed has returned nothing?
提问by LGAP
import java.net.URL;
import java.net.URLConnection;
import java.sql.*;
public class searchlink{
public static void main(String args[]) throws Exception {
//String link="http://hosted.ap.org";
Connection con=null;
Statement stmt=null;
Statement stmtR=null;
if(con==null){
SQLConnection.setURL("jdbc:sqlserver://192.168.2.53\SQL2005;user=sa;password=365media;DatabaseName=LN_ADWEEK");
con=SQLConnection.getNewConnection();
stmt=con.createStatement();
stmtR=con.createStatement();
}
ResultSet rs;
rs=stmt.executeQuery("select url from urls where url='http://www.topix.com/rty/elyria-oh'");
while(rs.next()){
String mem=rs.getString(1);
System.out.println("Result is "+mem);}
}
}
The above program prints the output if the query returns a row. If the query does not return any, the program stops without printing anything.
如果查询返回一行，上面的程序会打印输出。如果查询没有返回任何内容，程序将停止而不打印任何内容。
Instead of it getting stopped without printing anything, I want the program to identify that the query has returned nothing and print the output saying something like this " There is nothing returned after SQL query execution ".
与其在不打印任何内容的情况下停止，我希望程序识别出查询没有返回任何内容并打印输出，内容如下“SQL 查询执行后没有返回任何内容”。
How to identify using some method or variable that the query has been executed without returning any row?
如何使用某种方法或变量识别已执行查询而不返回任何行？
采纳答案by Matthew Vines
boolean hasRows = false;
while(rs.next()){
hasRows = true;
// do other stuff required.
}
if(!hasRows)
{
// do stuff when no rows present.
}
-- or --
- 或者 -
if(!rs.next())
{
// do stuff when no rows prsent.
}
else
{
do{
// do stuff required
}while(rs.next());
}
keeping in mind that the check if(!rs.next()) will advance the cursor in the result set. Don't advance it again before you get the values.
请记住，检查 if(!rs.next()) 将在结果集中推进光标。在获得值之前不要再次推进它。
回答by Eton B.
if (rs.hasNext())
{
while(rs.next())
{
String mem=rs.getString(1);
System.out.println("Result is "+mem);
}
}
else
{
System.out.println("There is nothing returned after SQL query execution ");
}
maybe~
也许~
回答by Matthew Flynn
The first (rs.next()) will tell you if any data was returned. React to that one, then loop through the rest (if there are any).
第一个 (rs.next()) 会告诉您是否返回了任何数据。对那个做出反应，然后遍历其余的（如果有的话）。
Below I extract the logic for what to do when there is a row into a separate method, and then call that after the "if" and within each "where".
下面我将当有一行时要做什么的逻辑提取到一个单独的方法中，然后在“if”之后和每个“where”中调用它。
. . .
ResultSet rs;
rs=stmt.executeQuery("select url from urls where url='http://www.topix.com/rty/elyria-oh'");
if (rs.next() {
printRow(rs);
while(rs.next()){
printRow(rs);
}
}
else {
System.out.println("no data returned");
}
}
static public printRow(ResultSet rs) {
String mem=rs.getString(1);
System.out.println("Result is "+mem);}
}
}
回答by Rick Schmid
Place a counter in your loop...
在你的循环中放置一个计数器......
int count = 0;
while ( rs.next() )
{
count++;
String mem=rs.getString(1);
System.out.println("Result is "+mem);}
.
.
.
}
then ...
然后 ...
if (count==0)
{
// show your message "There is nothing returned after SQL query execution"
}
Any call to rs.next()moves the cursor so if (rs.next() == false)would bump you one ahead and make you skip the first result if you had 2 or more or miss it entirely if you had one result.
任何rs.next()移动光标的调用if (rs.next() == false)都会使您领先一个，如果您有 2 个或更多，则跳过第一个结果，或者如果您有一个结果，则完全错过它。
Good Luck,
祝你好运，
Rick
瑞克
回答by tc.
boolean got_result = false;
while (...) {
got_result = true;
...
}
if (!got_result) {
...
}
回答by BalusC
The normal JDBC idiom is to collect the results in a collection like List<Entity>. The another normal idiom is to open resources in try-with-resourcesstatementso they get properly auto-closed. Your code is namely leaking DB resources by leaving those resources open. If you run this repeatedly in a short time, then the DB will run out of resources.
正常的 JDBC 习惯用法是将结果收集在一个集合中，如List<Entity>. 另一个正常的习惯用法是在try-with-resources语句中打开资源，以便它们正确地自动关闭。您的代码就是通过打开这些资源来泄漏数据库资源。如果您在短时间内重复运行此操作，那么数据库将耗尽资源。
Here's a kickoff example:
这是一个启动示例：
public List<Entity> list() throws SQLException {
List<Entity> entities = new ArrayList<Entity>();
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id, name, value FROM entity");
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
Entity entity = new Entity();
entity.setId(resultSet.getLong("id"));
entity.setName(resultSet.getString("name"));
entity.setValue(resultSet.getInteger("value"));
entities.add(entity);
}
}
return entities;
}
This way you can use the usual Listmethods to determine the state of the result:
这样你就可以使用通常的List方法来确定结果的状态：
List<Entity> entities = entityDAO.list();
if (entities.isEmpty()) {
// It is empty!
}
else if (entities.size() == 1) {
// It has only one row!
}
else {
// It has more than one row!
}
See also:
也可以看看：
回答by user2368055
if(!rs.isBeforeFirst())
System.out.println("no data is returned");
回答by Jeremy Monserrate
for select queries in you can do
对于选择查询，你可以做
rs.next();
int value = resultSet.getInt(1);
if (value == 0)
{
//throw error message
}
else
//
