I am facing issues with native sql insert statements. Persisting as objects works fine. This may be because Hibernate does not flush the session when it is absolutely necessary. It surfaces only when we mix native queries and objects.


Consider 2 classes:

public class A {
@Id @GeneratedValue
private Integer id;

@MantToOne
private B b;
}

public class B {
@Id @GeneratedValue
private Integer id;
}

The following code persists an instance of A and B within same transaction
B b = new B();
getHibernateTemplate().save(b); // Persist as object

A a = new A();
a.setB(b);

Working
-----------
getHibernateTemplate.save(a); // Persist as object

Not working:
---------------
SQLQuery query = getSession().createSQLQuery("insert into table_a (column_b) values(?)");
query.setInteger(b.getId());
query.executeUpdate(); // Persist using native SQL query

It fails with foreign key violation constraint claiming that no record with id = xxx exists in table_b, even though i issued an insert of object 'b'. I can get this working by doing an explicit flush:

getSession().flush();
query.executeUpdate();

This happens only in Postgres and it appears like it has to do with the id generation mechanism. In SQLServer, Hibernate has to do an insert to get the id. In case of Postgres it uses sequence and so its not necessary for the insert to go through. Is this a known caveat and we have to get around it using explicit flush? Hibernate takes care of persisting it correctly if I use:

getHibernateTemplate().save(a);

A call to Session.save() doesn't mean that Hibernate issues an insert immediately. In general this only happens at when you call flush() or commit the transaction. It may also happen before a Query/Criteria is executed but that depends on which FlushMode you are using.

As you have discovered by yourself there is one exception. The insert happens immediately if the ID generation depends on inserting the object into the database. So what you are describing is expected and is also explained in the Hibernate documentation: http://www.hibernate.org/hib_docs/v3/re ... shing.html

But it does state that the order in which the SQL statements are executed is guaranteed. Correct me if I am wrong. So shouldn't it insert object 'b' before inserting object 'a'?

From the document referenced above:



In the case that doesn't work you are using a native SQL query, not Session.save().

One might argue that FlushMode.AUTO should detect that an automatic flush is needed but the documentation about when an automatic flush happens is quite vague and only mentions Query.list(). In any case, the only way to make the 'b' object to be inserted is to flush the session if that is an automatic or manual flush doesn't matter. It should not cause any harm to issue a manual flush.

Thanks. I will file a feature request. It will be good to have this flush taken care automatically.