File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes JDBC ResultSet closing Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC ResultSet closing" Watch "JDBC ResultSet closing" New topic

JDBC ResultSet closing

Mitch Young

Joined: Dec 15, 2006
Posts: 10
Hello Java Ranch-A-Rareos!
I'm querying a mySql db to see if a user that is trying to register is already registered.
I query for the email and use the count function as you can see below: If it returns 0, the user gets registered, if 1, it returns the boolean. What is happening is that if the user is not in the db and the stmt.executeUpdate(insert); executes, when control passes back to the while test if fails because the ResultSet has been closed. Does anybody know how the ResultSet is being closed? Thanks for your help.

K Amit Gupta

Joined: Feb 22, 2011
Posts: 4

Why are you using while?
your objective is to simply check that user exists in database or not.
if your queryEmail is returning 0 it means user is not register and you want to run insert query for that user.
and after runnning insert query, why you want to run insert for same user?

and your resultSET would be TYPE_FORWARD_ONLY by default...

we can't back in result set if it is TYPE_FORWARD_ONLY ... actually your result set is not closing but it reaching END of result set.

you can use ResultSet.TYPE_SCROLL_SENSITIVE to iterate both side in result set.

Mitch Young

Joined: Dec 15, 2006
Posts: 10
Thanks so much, I removed the while loop and it all works as expected now.

I'm not trying to re-register the same user, only check when a user registers if they are already registered and forgot.

I still don't understand why the second time through the loop it was throwing a nullPointerException and a
"SQLEception: Operation not allowed after ResultSet closed" . If it is the end of the ReultSet won' t a call to next() return false and pass control out of the loop? But anyway, it seems that somehow the ResultSet was being closed throwing the nullPointerException.
Thanks again.
m coffee

Joined: Nov 10, 2010
Posts: 5
It's your stmt.executeUpdate in the middle of reading from the resultset.

One way that works is to open 2 statements, and use the second one just for the updates.
Mitch Young

Joined: Dec 15, 2006
Posts: 10
Thanks m coffee,
That makes sense, I'll do some testing with you solution.
Rob Spoor

Joined: Oct 27, 2005
Posts: 20274

First of all, even though a while loop is usually needed with ResultSets, in your case there will always be exactly one row (or an exception). That's why a simple call to without while/if is enough.

Secondly, when you execute a statement for the second time, the ResultSets belonging to that statement are usually closed. That's exactly what was happening for you.

How To Ask Questions How To Answer Questions
I agree. Here's the link:
subject: JDBC ResultSet closing
It's not a secret anymore!