Solved

How to check if resultset is empty

Posted on 2010-11-19
10
1,089 Views
Last Modified: 2013-11-23
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
         
0
Comment
Question by:happylife1234
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 7

Expert Comment

by:rodness
ID: 34176941
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
 

Author Comment

by:happylife1234
ID: 34177077
rs.first() == false gives an "Invalid operation for forward only resultset: first" exception.
0
 
LVL 7

Accepted Solution

by:
rodness earned 500 total points
ID: 34177103
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 92

Expert Comment

by:objects
ID: 34177159
TableModel model = Database.resultSetToTableModel(rs);
if (model.getRowCount()==0) {
   // no rows
} else {
   jTable1.setModel(model);
}
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34177444
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
 
LVL 92

Expert Comment

by:objects
ID: 34177502
> 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
 
LVL 1

Expert Comment

by:GenroseSusai
ID: 34177514
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
 
LVL 9

Expert Comment

by:ioanton
ID: 34183430
>>>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
 
LVL 16

Expert Comment

by:Valeri
ID: 34183574
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
 
LVL 16

Expert Comment

by:Valeri
ID: 34183626
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
diffSum example 4 50
by zero exception 10 52
JAVA API design with micro service cloud in mind 1 47
Fast way to search item into Java Array (Rhino compatible) 2 31
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question