How to check if resultset is empty

I have a ResultSet that I need to set to a tablemodel like below -

ResultSet rs = Database.getUserList(inputParm);

/* I would like to check if resultset is null and throw a JOptionPane saying that the query returned no rows. If I check for rs.next(), I cannot see the first row fetched in the jTable1. */
     
 jTable1.setModel(Database.resultSetToTableModel(rs));

Anu suggestions on how to check for an empty result set will be helpful.

Thanks
         
happylife1234Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rodnessConnect With a Mentor Commented:
I was afraid of that.  Really, the way ResultSet objects are intended to be used is a while loop and next().  From the Javadoc (http://download.oracle.com/javase/6/docs/api/java/sql/ResultSet.html)

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.


You might need to do something of the form:


// clear table model

boolean found = false;

while ( rs.next() ) {
   found = true;

   // add current rs value to model
}

if ( found == false ) {
  // do the JOptionPane
}

Open in new window

0
 
rodnessCommented:
Use ResultSet.first(), which will return true if it can select the first row, and false if there are no rows.
ResultSet rs = Database.getUserList(inputParm);

if ( rs.first() == false ) {
   JOptionPane.showMessageDialog( null, "No rows found.", "Query Results", JOptionPane.INFORMATION_MESSAGE );
}

jTable1.setModel(Database.resultSetToTableModel(rs));

Open in new window

0
 
happylife1234Author Commented:
rs.first() == false gives an "Invalid operation for forward only resultset: first" exception.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
objectsCommented:
TableModel model = Database.resultSetToTableModel(rs);
if (model.getRowCount()==0) {
   // no rows
} else {
   jTable1.setModel(model);
}
0
 
CEHJCommented:
Your 'Database' method should be designed to return null if there are no results, so you should be able to do something like:


TableModel tm = Database.resultSetToTableModel(rs);
if (tm == null) {
	// Show option pane warning
}
else {
	jTable1.setModel(tm);
}

Open in new window

0
 
objectsCommented:
> Your 'Database' method should be designed to return null if there are no results

no that would be poor design resulting in unexpected exceptions, and does not gain you anything. In fact its makes the method messy to use so is not desirable.

better to return an empty table model which you can check as I have shown above

0
 
GenroseSusaiCommented:
Try this 2 solution:

if (rs != null)  
{  
    blnGotSomeRows = false;  
    while (rs.next())  
    {  
        blnGotSomeRows = true;  
    }  
    if (blnGotSomeRows) out.println("Some rows is present");  
    else out.println("no rows!");  
}  
else  
{  
    out.println("result set is empty");  
}  

or this:-------------------------------------------------------------------------------

if (rs != null)  
{  
    if (rs.isBeforeFirst() && rs.isAfterLast())  
    {  
        out.println("result set is empty!");  
    }  
    else  
    {  
        while (rs.next())  
        {  
            //
        }  
    }  
}  
else  
{  
    out.println("rs == null");  
}  



Hope this helps you...
0
 
ioantonCommented:
>>>rs.first() == false gives an "Invalid operation for forward only resultset: first" exception.

You can also make the Database.getUserList(inputParm) method to return a scrollable ResultSet. This allows you to call the first() method without throwing any exception.
0
 
ValeriCommented:
You have to rework your "getUserList" and "resultSetToTableModel" methods!
As I suppose Database class encapsulates Database operations, so no one of this methods should return something like ResultSet, because ResultSet is from java.sql package and must be closed in getUserList mehod. by returning it you have to close this on some other method and it's poor design.
Good design is to return from getUserList somethig like ArrayList or Map or something like that, and after that to use this collection in your resultSetToTableModel method. The last method should't be in Database class at all. By returning List or Map from getUserList you can use size() method to see if it has elements or not.
0
 
ValeriCommented:
public  List<User> getUserList() {
   List<User> users = new ArrayList<User>();
   User user;
...
while ( rs.next() ) {
   user = new User();
   user.loadByRS(rs);
   users.add(user);
}
...close the result set
...close the callable statement
return users;
}
and in your

void resultSetToTableModel(list) { // it should be ListToTableModel
   if (list.size() == 0) {
    // no rows
   } else {
   // go through the list and set table model
}
}
that's the right way to do that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.