Any problems in using hibernate's session.connection() to run native sql

Question

In my project, they use Hibernate's session the below mentioned way and then save entity objects with in a transaction.

Session session = HibernateUtil.getCurrentSession();
session.beginTransaction();

Employee employee = new Employee() ;

employee.setName("someName") ;
employee.setEmailId ("someId")
employee.setID (100000) ;

session.saveOrUpdate(employee) ;

session.getTransaction().commit();

Now for few functionality I decided to run native SQL. Below is the way i used to run native sql. I wanted to run the queries with-in a transaction and so i decided to write the code the below way

String query = "select name from master_employee where id=?"

Session session = HibernateUtil.getCurrentSession();
session.beginTransaction();

Connection connection = session.connection();
PreparedStatement psStmt = connection.prepareStatement(query);
psStmt.setInt(1,id) ;

ResultSet resultSet = psStmt.executeQuery();
// here i will take data from this result set
psStmt.close();
resultSet.close() ;

// I am not closing the connection object since am taking it from hibernate session
// rather i commit hibernate's transaction
session.getTransaction().commit();

Is this the right way ?? will the transaction's still be managed ?? taking the connection object from session cannot be managed in to the transaction ????

Please tell if there any problems in using this way ??? thanks

Answer 1

Yes, there are no problems here.

However, it would be much easier to run a native query using Session.createSQLQuery():

Session session = HibernateUtil.getCurrentSession(); 
session.beginTransaction(); 

String name = (String) session
    .createSQLQuery("select name from master_employee where id = ?")
    .setInteger(1, id)
    .uniqueResult();

session.getTransaction().commit();

See also:

• Chapter 18. Native SQL