I have a question regarding mixing SQL and HQL in the same transaction. Consider a table EMPLOYEE. It has 3 records. I start a transaction. I fire a HQL and delete an Employee record. I fire a direct SQL select on the EMPLOYEE table in the same transaction. How many records will I get? 2 or 3? I am not using a second level cache. Is it okay to mix SQL and HQL in the same transaction? I am not using any second level cache.
How do you define direct SQL, and what Transaction Manager are you using.
The only way they wouldn't be in the same transaction is if the transaction is a Hibernate transaction only, and the direct SQL is run through a connection you got by creating it yourself, not going through Hibernate itself.
If you use JTA and you create the connection yourself, and Hibernate which automatically joins the JTA transaction then they are both in the same transaction.
I think it is very safely assumed that you will be in the same transaction, because it would be ridiculous to create your own JDBC Connection object outside of Hibernate if you have Hibernate in your application. You would just either call session.getConnection (which is deprecated by the way), or call session.createSqlQuery().
with networkids as ( select s.default_network_id id from store s where s.id=? union select nfs.network_id as id from store s inner join network_fixed_sched nfs on s.id=nfs.store_id where s.id=:store1id union select nws.network_id as id from store s inner join network_weekly_sched nws on s.id=nws.store_id where s.id=:store1id ) select count(*) from network_detail nd, networkids ids where nd.network_id=ids.id and nd.store_id=store2id
I am unable to convert this to a HQL. But before this SQL is fired I am deleting a row using HQL. I don't want that row to be picked up in this SQL.
From your post what i understand is that mixing hql and sql won't create any problem.
Indeed, this shouldn't be a problem for the transaction manager. Remember, all HQL and JPA code just gets translated to SQL when it eventually gets sent to the database, so it's all the same animal under the covers.