wood burning stoves 2.0*
The moose likes JDBC and the fly likes ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet" Watch "ResultSet" New topic
Author

ResultSet

Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
I've a problem with checking if my resultset is null. Even if it is null (no rows retrieved), it goes through the loop for ( if resultset not null).
Any clue?.
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
What's your code look like? I usually use if ( rs.next() ){...}
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Originally posted by Michael Hildner:
What's your code look like? I usually use if ( rs.next() ){...}

public static void main(String[] args) {
try {
DatabaseConnection dc = new DatabaseConnection("jconnect");
ResultSet rss = dc.getDataFromAuditReportTable("0000", "March");
System.out.println("query : " + rss);
if (rss!=null) {
System.out.println("nulltest");
}

} catch(Exception e) {System.out.println(e);}
}
}

Here, for the values I am passing, (0000 and March), there are no rows in the table. But it still prints "nulltest".
Thanks Michael.
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
Dear Mini,
Not sure what you're using to get your ResultSet, but just because it's empty does not necessarilly mean it is null. This is from the API:
executeQuery
public ResultSet executeQuery()
throws SQLException
Executes the SQL query in this PreparedStatement object and returns the result set generated by the query.
Returns:
a ResultSet object that contains the data produced by the query; never null
Throws:
SQLException - if a database access error occurs
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Originally posted by Michael Hildner:
Dear Mini,
Not sure what you're using to get your ResultSet, but just because it's empty does not necessarilly mean it is null. This is from the API:
executeQuery
public ResultSet executeQuery()
throws SQLException
Executes the SQL query in this PreparedStatement object and returns the result set generated by the query.
Returns:
a ResultSet object that contains the data produced by the query; [b]never null

Throws:
SQLException - if a database access error occurs[/B]


Thanks a lot. I've to display a message, if the resultset is empty. So, if (myResultset==null) doesn't work, what shall I do?. Put the values of the resultset into a hashtable and then check isEmpty...
Thanks again Michael.
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Originally posted by Michael Hildner:
Dear Mini,
Not sure what you're using to get your ResultSet, but just because it's empty does not necessarilly mean it is null. This is from the API:
executeQuery
public ResultSet executeQuery()
throws SQLException
Executes the SQL query in this PreparedStatement object and returns the result set generated by the query.
Returns:
a ResultSet object that contains the data produced by the query; [b]never null

Throws:
SQLException - if a database access error occurs[/B]


Thanks a lot. I've to display a message, if the resultset is empty. So, if (myResultset==null) doesn't work, what shall I do?. Put the values of the resultset into a hashtable and then check isEmpty...
Thanks again Michael.
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
Dear Mini,
Don't know if there's a real elegant way to do this, as ResultSet doesn't seem to have a hasNext() method, and the next() method actually moves to the next row.
Maybe something like:

That's kind of ugly, but can't think of anything better right now. Maybe someone else has a better solution?

Looking at the API, ResultSet does have an isLast() method. I haven't played with this yet, but maybe that would work?
[This message has been edited by Michael Hildner (edited April 04, 2001).]
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Great thanks to Michael. It worked
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
But Michael, the first row is missing. I will try this with ArrayList.
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Sorry but I am a bit confused by these posts and can't see why this would not work.

rs will NEVER be null, but it MAY never empty.
Converting a recordset to an ArrayList is unescessary if you don't REALLY need to do it.
Even if you need to be able to modify the info in the recordset. Use a SCROLABLE ResultSet and/or UPDATE_SENSITIVE one. This way you have minimal 'useless' object creation in your code.
Hope this helps.

[This message has been edited by John Bateman (edited April 04, 2001).]


SOURCE CODE should be SURROUNDED by "code" tags.
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
But Michael, the first row is missing. I will try this with ArrayList.
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
Dear Mini,
Why don't you post your code again, are you actually processing the first row?
John, I think our snippets are exactly the same, just that I had a doSomething() method that would have to be written.
[This message has been edited by Michael Hildner (edited April 04, 2001).]
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
John, thanks for the reply. I tried it. But still I can't see the first row of the resultset. I think, next() puts the cursor in the second row.
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Michael,
The method getDataFromAuditReportTable returns a resultset arResultSet doing a query select * from AuditReport. This method is inside the class DatabaseConnection . See the rest. It is very simple, and your snippet works except for the first row I try to output this resultset into a table using JSP. Thanks for your help.

try {

DatabaseConnection dc = new db.DatabaseConnection("jconnect");
System.out.println("dc : " + dc);
ResultSet arResultSet= dc.getDataFromAuditReportTable("2001","April");


if (arResultSet.next()){

while (arResultSet.next()) {
System.out.println("test5");

String Jobdesc = arResultSet.getString("job_desc");
String username = arResultSet.getString("user_name");
String dateoccur = arResultSet.getString("date_occur");
String jobstatus = arResultSet.getString("job_status");

} /*while*/

}/*if*/
else {

System.out.println("testsuccess"); }
arResultSet.close();

} catch(Exception e) {System.out.println(e);}
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
Dear Mini,
This is why you are not seeing the first row:
if (arResultSet.next()){
while (arResultSet.next()) {

The first next() moves to the first row. You don't do anything with it, the second next() moves to the second row, then you do some processing. So you have to process the first row also.
You could change you code to:

or just call a method instead of repeating code (like the doSomething() example posted earlier - note that that method does not exist, it was an example.)
I hope I'm being clear Mini, if not let me know. Your code may look like:

[This message has been edited by Michael Hildner (edited April 04, 2001).]
Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Thankssssssssssss Michael. It works....
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Hi
One thing you also have to realise is that when you get a ResultSet the cursor is always before the first record, so the first invocation of next() puts the cursor on the first record.

Mini Ramachandran
Greenhorn

Joined: Jan 11, 2001
Posts: 17
Yes. After the first next(), I just printed a System.out instead of printing it in a table row. Michael made it clear with his code. Anyway, it works now...
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
The thing I thought was ugly about that was calling the method from two different places. Luckily I hang out with people smarter than I am I think would be more elegeant to write the code as if (rs.next) do ... while (rs.next). That way there is only one method call.
Ipsita Naravane
Greenhorn

Joined: Dec 27, 2000
Posts: 29
I was about to say use do .. while instead of while:-)
 
 
subject: ResultSet