I have a problem with autoflushing on native SQL queries. The scenario is:

1. I use a complex sql with a lot of joined tables
2. I change some of the associated objects from the result (but not the returned types.
3. I do the same sql as in step 1

The problem is that the autoflush before the sql in step 3 only flushes changes in objects of the class returned by the sql, NOT all other objects used in the query.

If I change the query in step 3 to HQL it works as I would expect and flushes all objects included in the query.

Is there any way that I can make the autoflushing before a SQL query flush all objects of the classes included in the query, just as the HQL query does?

Hibernate version: 3.1.3

The SQL query: (flushes only changed Tjeneste objects)
SQLQuery sqlQuery = session.createSQLQuery
("SELECT /*+ use_nl(a av) */ {t.*}"
+"FROM TJENESTE t, "
+"TJENESTEVERSION tv, "
+"ALLOKERING a, "
+"ALLOKERINGSVERSION av "
+"WHERE t.TJENESTE_ID = tv.TJENESTE_ID "
+"AND av.plan_id = 0 "
+"AND av.slettet = 0 "
+"AND av.allokering_id = a.allokering_id "
+"AND a.medarbejder_id =:medarb "
+"AND tv.tjeneste_id = av.tjeneste_id "
+"AND tv.fra_tid >= :fraTid "
+"AND tv.til_tid <= :tilTid"
);

The HQL query: (flushes changed objects of classes Tjeneste, TjenesteVersion, AllokeringsVersion, Allokering)

("SELECT av.tjeneste "
+" FROM"
+" TjenesteVersion tv,"
+" AllokeringsVersion av"
+" WHERE "
+" av.plan.id = 0"
+" and av.slettet = 0"
+" and av.allokering.medarbejder.id = :medarb"
+" and tv.tjeneste = av.tjeneste"
+" and tv.plan.id = 0"
+" and tv.fraTid >= :fraTid"
+" and tv.tilTid <= :tilTid"


Regards,
Frank

Hi

I have noticed the same problem, it seems that sql queries does not flush the affected entities, where hibernate queries does.

This seem wrong. How are you supposed to use sql queries if you cant be sure that the database is consistent when you execute the query ?

I hope someone have a solution to this problem, since it would severly impact the usability of the SQLQuery.


Best Regards
Timm

It says in the hibernate documentation (section 10.10 Flushing the session), that

"However, Hibernate does guarantee that the Query.list(..) will never return stale data; nor will they return the wrong data."

This seems not to be the case with native SQL queries. Can this be a bug?

Regards,
Frank

if you use named query with <synchronize> then it works.

We don't currently automagically flush since we don't know which tables you are actually referring to in the SQL.

We could probably have it to default flush out the entities you actually refer to via .addEntity, and then let you override with the <synchronize> tag that you can use today.

Feel free to put it in jira as a feature request.

Hi Max

Maybe you could add the possibility of adding querySpaces to the query. So that you can tell hibernate wich tables to flush in addition to the table of the result type.

I would suggest something like the following in org.hibernate.impl.SQLQueryImpl



This makes hibernate check for, and flush, the spaces added.
I do not know if this breaks code other places in hibernate, but i do know that it fixes the problem statet by Frank. And it seems to work since i am able to run all my projects test with this addition to hibernate.