aspose file tools*
The moose likes Object Relational Mapping and the fly likes Mixing direct SQL with HQL in the same transaction Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Mixing direct SQL with HQL in the same transaction" Watch "Mixing direct SQL with HQL in the same transaction" New topic
Author

Mixing direct SQL with HQL in the same transaction

Sagar Kar
Greenhorn

Joined: Sep 03, 2004
Posts: 25
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.


Warm regards,<br />Sagar Kar.<br />SCJP 1.4, SCBCD 1.3
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

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().

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Sagar Kar
Greenhorn

Joined: Sep 03, 2004
Posts: 25
Thank you Mark for you prompt reply.
I am using transaction properties

<prop key="hibernate.transaction.factory_class">
org.hibernate.transaction.CMTTransactionFactory
</prop>
<propkey="hibernate.transaction.manager_lookup_class">
org.hibernate.transaction.WebSphereExtendedJTATransactionLookup
</prop>

Also I am using Spring's AOP to demarcate transactions.
From your post what i understand is that mixing hql and sql won't create any problem.
Sagar Kar
Greenhorn

Joined: Sep 03, 2004
Posts: 25
Also direct SQL means native SQL.
like
select id,name from EMPLOYEE
Sagar Kar
Greenhorn

Joined: Sep 03, 2004
Posts: 25
My SQL is

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.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Again, how is that SQL run, is it through a SQQuery object, is the jdbc Connection from hibernate, or are you just using Connection c = new JDBDCOnnection()......

But you are within a JTA transaction, which means you are demarcating transactions at the service layer, and that encompasses everything within, so Hibernate will just join the JTA Transaction.

Mark
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

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.

-Cameron McKenzie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Mixing direct SQL with HQL in the same transaction