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

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.

Answer 2

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-resources statement so 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.

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 List methods to determine the state of the result:

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:

• 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

Answer 3

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 4

if(!rs.isBeforeFirst())
   System.out.println("no data is returned");

Answer 5

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 6

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.

Good Luck,

Rick

Answer 7

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

Answer 8

for select queries in you can do

rs.next();
int value = resultSet.getInt(1);
if (value == 0)
{
        //throw error message
}
else
        //