Execute native with DML through Hibernate Session

look_as1 · **Joined:** Mon Jan 23, 2006 10:04 am **Posts:** 9

Is there a posibility to execute native SQL with DML through Hibernate Session? (eg. INSERT INTO).

I figured that, this is not possible because of problems with Hibernate cache invalidation.

Is it possible using StateLessSession or other hack?

Thanks,

Lukas

LaLiLuna · **Posted:** Fri Jan 27, 2006 12:41 pm

You can get the database connection from the session factory and issue a jdbc query.

Regards Sebastian

look_as1 · **Joined:** Mon Jan 23, 2006 10:04 am **Posts:** 9

Is there also possibility to execute that statement during same transcation that runs in some Hibernate session?

EG:

sess.executeQuery(hql);
sess.save(someObject);
sess.executeNativeSQLQueryWithDML(sql);

Lukas

LaLiLuna · **Posted:** Fri Jan 27, 2006 1:10 pm

No, I do not think so. This topic was up a couple of days ago and we had the same result.

max · **Posted:** Fri Jan 27, 2006 2:26 pm

we dont have executeUpdate() functionallity yet for the hibernate's native sql query support, but you can execute all the naughty sql you want on the session.connection().

telenko · **Joined:** Fri Dec 24, 2004 3:16 am **Posts:** 5

Hello,

I am using Hibernate 3.1 in Jboss 4.0.3.

I have to execute a lot of native update queries from within a stateless BMT Session bean.

I am following the suggestions in this thread, but have the impression that the queries are not executed within the current transaction scope.

Here's my code for executing native SQL. The session has been passed as a parameter to the method where this is executed and has been bound to the current user transaction (I checked the server log).

Code:

  Connection conn = session.connection();
  PreparedStatement stmt = conn.prepareStatement(sql);
  //Set params ...
  stmt.executeUpdate();

If I don't use native SQL, then the whole application runs fine, and there are no warnings of any kind in my log files. But as soon as a native SQL query is executed by the code above, I get the following warnings in my server log:

Could someone tell me how I can execute a native SQL query using hibernate within the current JTA transaction? Because now I fear they fall outside of that scope (those warnings above really make me feel uncomfortable with my current solution).

And should I close those connections myself or will closing them myself cause them to be commited outside of the current JTA transaction?

All hints and solutions are welcome.

Thanks a lot.

Luc Feys

telenko · **Joined:** Fri Dec 24, 2004 3:16 am **Posts:** 5

Hello,

My problem is solved. Actually I didn't change a thing. I just closed the connections and all warnings are gone (except of course for the

org.hibernate.jdbc.JDBCContext: opening user JDBC connection, application must close it.

Afterwards it all seems very logic. Hibernate just provides me with a connection from the pool defined in my sessionfactory. And of course that connection is within JTA transaction scope.

I guess I was confused by the message

'Prepare called on a local tx. Use of local transactions on a jta transaction with more than one branch may result in inconsistent data in some cases of failure.'

I also read a thread that stated I should not close the connections myself, so that confused me even more.

Should have gone for the logical solution sooner.

Luc Feys