Dear all

We are about to migrate a very big application from pure SQL statements to Hibernate 3.1.

We mapped a DAO to each table and in a perfect world we would use only DAOs to access the persisted data. But since there is a deadline and we cannot possibly replace all native SQL with HQL or DAO access, we are forced to leave a big part of the native SQL as it is.

The native SQL is based on the usual java.sql.* classes:
stmt = connection.prepareStatement("insert / update / select ...");
resultSet = stmt.executeQuery();
a = resultSet.getInteger(1);
b = resultSet.getString(2);
c = resultSet.getBoolean(3);
... and so on

As a result, there is mixed code, where some new lines in the tables are created via Hibernate and some via native SQL in the same transaction.

The problem is in synchronizing both avenues.

There is always only one Transaction per Session and certainly native SQL and Hibernate always use the same Conneciton.

The problem is, that the Session.get() does not find the objects which have been inserted into the tables by native SQL and vice versa: a SELECT statement does not find the lines in the DB which have been created via Session.save(). As said, all this happens in the same Transaction / Session.

The execution of Session.flush() just before the native SELECT query does not generate any hibernate-sql code and thus doesn't help.

Here my questions:
How can we synchronize these two access mechanisms in the same transaction?
Shouldn't the execution of Session.flush() before a native SELECT help?
Session.setCacheMode(IGNORE) and Session.setFlushMode(ALWAYS) does not help. Why?


Currently we are using Oracle 8.1.7 (will be changed to Oracle 10g soon).

Any help would be very sincerely appreciated.

Kindest regards

Markus

If you modified objects managed by the first level cache (i.e. session), session.flush() WILL trigger update (or inserts, anyway). If not, there's a problem somewhere.

But in fact, in there's nothing in your session. flushing it is useless, obviously.

In the other way : to force session to refresh after you, for example, insert things by native sql, the only way is to clear() it (or to evict() or refresh() you know were modified, but it's not always possible to know, I guess).