• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Mixing direct SQL with HQL in the same transaction

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sagar Kar
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also direct SQL means native SQL.
like
select id,name from EMPLOYEE
 
Sagar Kar
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author and cow tipper
Posts: 5009
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
reply
    Bookmark Topic Watch Topic
  • New Topic