Mixing direct SQL with HQL in the same transaction

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

Mark

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.

Also direct SQL means native SQL.
like
select id,name from EMPLOYEE

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.

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

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