How to find that a SQL query executed has returned nothing?

Question

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 ".

How to identify using some method or variable that the query has been executed without returning any row?

Answer 1

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~

Answer 2

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).

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".

   . . .


   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);}
  }   

}

Answer 3

bool 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.

Answer 4

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 intirely if you had one result.

Good Luck,

Rick

Answer 5

boolean got_result = false;
while (...) {
  got_result = true;
  ...
}
if (!got_result) {
  ...
}

Answer 6

The normal JDBC idiom is to collect the results in a collection like List<Entity>. The another normal idiom is to properly close resources in finally. Your code is leaking DB resources. If you run this repeatedly in a short time, then the DB will run out of resources.

Here's a kickoff example:

public List<Entity> list() throws SQLException {
    // Declare resources.
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<Entity> entities = new ArrayList<Entity>();

    try {
        // Acquire resources.
        connection = database.getConnection();
        statement = connection.createStatement("SELECT id, name, value FROM entity");
        resultSet = statement.executeQuery();

        // Gather data.
        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);
        }
    } finally {
        // Close resources in reversed order.
        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 data.
    return entities;
}

This way you can use the usual List methods to determine the state of the result:

List<Entity> entities = entityDAO.list();

if (entities.isEmpty()) {
    // It is empty!
if (entities.size() == 1) {
    // It has only one row!
} else {
    // It has more than one row!
}

See also:

• Answer with another method examples expecting zero-or-one row, zero-or-many rows
• DAO tutorial - basic kickoff tutorial how to write JDBC the proper way