By Programmer For Programmer

Background

So at my current job at jamasoftware.com, our Contour application supports the three major databases MySQL, SQL Server, and Oracle (sorry PostgreSQL fans… maybe soon).  Recently I needed to run UPDATE statements with JOINs, and had to learn the different syntax for each of these databases.  I thought I would share all of them side-by-side, so that you can easily see the differences:

Our Simplified Schema:

Table: ORGANIZATION
id int primary key

Table: PROJECT
id int primary key
organizationId int foreign key ORGANIZATION(id)

Table: DOCUMENT
id int primary key
projectId int foreign key ORGANIZATION(id)
organizationId int null

The Task

Update all DOCUMENT records with the organizationId of their corresponding project

The Solution

MySQL:
UPDATE DOCUMENT d
    INNER JOIN PROJECT p ON d.projectId = p.id
SET d.organizationId = p.organizationId
WHERE d.organizationId IS NULL;

SQL Server:
UPDATE d
SET d.organizationId = p.organizationId
FROM DOCUMENT d
    INNER JOIN PROJECT p ON d.projectId = p.id
WHERE d.organizationId IS NULL

Oracle:
UPDATE (SELECT d.organizationId AS docOrgId, p.organizationId AS projectOrgId
        FROM DOCUMENT d
            INNER JOIN PROJECT p ON d.projectId = p.id
        WHERE d.organizationId IS NULL) v
set v.docOrgId = v.projectOrgId

Here’s the situation:

• Auto-flush is OFF (either FlushMode.COMMIT or FlushMode.MANUAL)
• You save() or saveOrUpdate() an object with Hibernate
• Your object is not using IDENTITY for its id
• Later (possible unrelated) code tries to get the saved object back by id

If you found this post, you probably know that no object is returned with the given id.  This is because the session has not been flushed.

So just flush the session before retrieval, right?  Well yes, but not so fast.  Most people, including myself, don’t want to have a big “session.flush()” in the middle of service layer code.

My solution was to register a “save” and “save-update” event listener with Hibernate, and process the insert immediately after.

Register lisenters (using Spring):

<bean id="sessionFactory">
    <property name="eventListeners">
        <map>
            <entry key="save">
                <bean class="com.jamasoftware.contour.util.hibernate.ExecuteImmediateSaveEventListener"/>
            </entry>
            <entry key="save-update">
                <bean class="com.jamasoftware.contour.util.hibernate.ExecuteImmediateSaveOrUpdateEventListener"/>
            </entry>
        </map>
    </property>
</bean>

Listeners:

public class ExecuteImmediateSaveEventListener extends DefaultSaveEventListener {

	private static final long serialVersionUID = 1L;

	protected Serializable performSaveOrReplicate(
			Object entity,
			EntityKey key,
			EntityPersister persister,
			boolean useIdentityColumn,
			Object anything,
			EventSource source,
			boolean requiresImmediateIdAccess) {
		Serializable id = super.performSaveOrReplicate(
				entity,
				key,
				persister,
				useIdentityColumn,
				anything,
				source,
				requiresImmediateIdAccess);
		if (!useIdentityColumn) {
			immediatelyInsert(source);
		}
		return id;
	}

	private void immediatelyInsert(EventSource source) {
		source.getActionQueue().executeInserts();
	}

}

public class ExecuteImmediateSaveOrUpdateEventListener extends DefaultSaveOrUpdateEventListener {

	private static final long serialVersionUID = 1L;

	protected Serializable performSaveOrReplicate(
			Object entity,
			EntityKey key,
			EntityPersister persister,
			boolean useIdentityColumn,
			Object anything,
			EventSource source,
			boolean requiresImmediateIdAccess) {
		Serializable id = super.performSaveOrReplicate(
				entity,
				key,
				persister,
				useIdentityColumn,
				anything,
				source,
				requiresImmediateIdAccess);
		if (!useIdentityColumn) {
			immediatelyInsert(source);
		}
		return id;
	}

	private void immediatelyInsert(EventSource source) {
		source.getActionQueue().executeInserts();
	}

}

This code was tested with Hibernate 3.3.1.

This problem was especially annoying to me because I work with an application that can be deployed on many databases. MySQL and SQL Server, for example, allow IDENTITY columns, which Hibernate will immediately execute INSERT statements for since it needs to in order to get their id. But Oracle doesn’t work this way, and the execution of the INSERT statements goes onto the ActionQueue, since it uses a SEQUENCE. What happens then, is that you end up with code that works for one database that doesn’t work for another.

Using the above listeners, my code now acts the same whether using IDENTITY columns or not.