Dear all

I read always that it is easily to execute native SQL with Hibernate.

Am I forced to always use Session.createSQLQuery() or can I use straight forwad java.sql.* classes like PreparedStatement.execute() ?

Or will this lead to synchronisation problems if I try to use both (native SQL and Hibernate) to insert and retrieve objects from the DB?

Would there be a way to synchronize this?

Many sincere thanks for your insights!

Markus

You can call session.connection() to get JDBC Connection and use it whatever you want. But to synchronize your operations with session, your need to explicitly call session.flush().

I made a little test, which does create an object via Session.save() and then executes an SQL query to retrieve the line in the code.

There the SQL query does find nothing! Which fundamentally means that they are out of sync.

A call to Session.flush() does not help.

Is there anything to be configured / called ??

Thanks

Markus

//////////////////////////////////////////////////////////////////////////////
TEST CODE
//////////////////////////////////////////////////////////////////////////////
public void testSynchronization()
{
TestDAO dao = create(new StructString(msg)); // create via Session.save() ...

logger.debug("Created TestBO " + bo.getData().getId() + " via Hibernate");

// flush session does not help
RMGCtx.get().getHibernateSession().flush();

String query = "Select t.data from TESTBO t where id = ?";

hqlStmt = getHibernateSession().createSQLQuery(query);
hqlStmt.setString(0, id);
hqlRs = hqlStmt.scroll();

if(hqlRs.first())
{
msg2 = hqlRs.getString(1); // it never goes in here!
}
if (!msg.equals(msg2)) throw new RMGRuntimeException("could not retrieve data via SQL");
...
}


//////////////////////////////////////////////////////////////////////////////
LOG OUTPUT
//////////////////////////////////////////////////////////////////////////////

11:47:54,747 DEBUG [dao] Changing state for TestDAO:499632 from null to created
11:47:54,903 DEBUG [TestDAO] Created TestDAO 499632 via Hibernate
11:48:32,911 DEBUG [SQL] Select t.data from TESTBO t where id = ?
11:48:32,911 INFO [STDOUT] Hibernate: Select t.data from TESTBO t where id = ?
11:48:43,649 DEBUG [SeibudTransactionHandler] rollback
11:48:43,681 ERROR [SeibudLoggingHandler] app exception
com.softwareag.belgium.seibud.rmg.error.RMGRuntimeException: could not retrieve data via SQL
at com.softwareag.belgium.seibud.rmg.bo.TestBO._testSynchronization(TestBO.java:185)
at com.softwareag.belgium.seibud.rmg.bo.TestBO.testSynchronization(TestBO.java:346)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)


//////////////////////////////////////////////////////////////////////////////
MAPPING
//////////////////////////////////////////////////////////////////////////////

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping
>
<class
name="com.softwareag.belgium.seibud.rmg.dao.TestDAO"
table="TESTBO"
>
<id
name="id"
column="ID"
>
</id>
<version
name="updateCounter"
column="uc"
type="java.lang.Integer"
unsaved-value="null"
/>
<property
name="data"
column="DATA"
>
</property>
<property
name="state"
column="state"
>
</property>
<property
name="lastUpdateTimestamp"
column="ts"
>
</property>
</class>
</hibernate-mapping>

Can you provide create method from this line:

TestDAO dao = create(new StructString(msg)); // create via Session.save()...

Aahhh, you are good at asking the right questions!

I just digged out the code of this create() method which is a chain of calls to a framework called Xooof and it proved to NOT execute save() at all!

I added a call to save() and flush() just after the creation and all works fine!

Thanks a lot for pointing out the right spot!

Markus