Hibernate, ResultSet and PrepareStatement

[Naveen Verma]

Dear All,

I am little confused. We know Hibernate does all the resource management implicitly. Supposing I have to execute arbitrary SQL using Hibernate. I may write the code like..

String query = "SELECT * from abc";
PreparedStatement ps = session.connection().prepareStatement(query);
ResultSet rs = ps.executeQuery();

Do I need to close 'ps' and 'rs' at my own OR it will be automatically taken care by Hibernate?

Please advice.

[Paul Sturrock]

You'll need to close the connection as you would with normal JDBC. The ResultSet and PreparedStatement should be closed with that.

[Naveen Verma]

Paul,
Many thanks for your reply.

Unfortunately, I am not convinced and it seems like a bug in Hibernate itself.

According to my understanding, if we close the session (session.close()), the
underlying connection should get closed and become unusable. However this does not seem to be the case because we can continue to do operations on PreparedStatement and Resultset even after session.close() has been called. Please run the below code for confirmation.

Session session = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Configuration configuration = new Configuration();
SessionFactory sessionFactory = configuration.configure().buildSessionFactory();
session = sessionFactory.openSession();

String query = "select * from trade";

Connection connection = session.connection();

ps = connection.prepareStatement(query);
session.close(); //Inspite of this line, the lines below are working fine!!

rs = ps.executeQuery();
int i=0;
while (rs.next()) {
i++;
}
System.out.println("rs_size="+i);

System.out.println("Done");
} catch (Exception e) {
System.out.println("Exception..."+e.getMessage());
e.printStackTrace();
}

[Paul Sturrock]

Re-read my post: don't close the session, close the connection. What Hibernate is allowing is a route to use direct JDBC via a Sesson. You need to uses it pretty much as you would without Hibernate, i.e. obtain a connection, do your work, close the connection. Closing the session implies nothing about the connection.

[Naveen Verma]

Paul,

I read your kind suggestion. However, if you could just play around with connection, ResultSet and PrepareStatement closing, you'd realise that your opinion could be ....

Well, I tried closing the conection also as said in code below. But I am still able to execute the query.
Ideally speaking, If i have closed the connection, PrepareStatement should not get execute.

Session session = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Configuration configuration = new Configuration();
SessionFactory sessionFactory = configuration.configure().buildSessionFactory();
session = sessionFactory.openSession();

String query = "select * from trade";

Connection connection = session.connection();

ps = connection.prepareStatement(query);
connection.close(); //Inspite of this line, the lines below are working fine!!

rs = ps.executeQuery();
int i=0;
while (rs.next()) {
i++;
}
System.out.println("rs_size="+i);

System.out.println("Done");
} catch (Exception e) {
System.out.println("Exception..."+e.getMessage());
e.printStackTrace();
}

Please advice.

[Paul Sturrock]

Hmm. That's interesting, and not how I understand it should work (though admittedly I'm getting this understanding from documentation, rather then playing with the code). Here's an idea: if you are worried about the Connection, why use one? You could just use createSQLQuery().
[ June 23, 2006: Message edited by: Paul Sturrock ]