Appending “WITH UR” to DB2 Hibernate query

Home / Blog / Java / Appending “WITH UR” to DB2 Hibernate query

I ran into this problem today while working at a client, and I thought I could help others out with a bit more information than I was able to find online.

The problem is that my client has a standard to have “WITH UR” appended to all SELECT queries. This wasn’t a problem with Spring JDBC, but now with the move to Hibernate, I had to find a way to provide this functionality. Keep in mind that I don’t have a choice in this standard, so I’m not arguing if appending “WITH UR” is a good approach or not.

Anyway, I came across this link,  http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32665#action_32665, which provides a workaround for this very problem. It involves creating a Hibernate interceptor that checks the query before executing, and appending “WITH UR” as appropriate. For my case, since I can assume that every SELECT query requires this suffix, the interceptor code looks as follows:

public class DB2Interceptor extends EmptyInterceptor {

	private static final long serialVersionUID = 1L;

	private static final Logger logger = Logger.getLogger(DB2Interceptor.class
			.getName());

	@Override
	public String onPrepareStatement(String str) {

		// Transform the original query into yasmin online pharmacy lower case
		String compstr = str.toLowerCase();

		// Check if we're dealing with a simple select statement
		if (compstr.matches("^select.*") && !compstr.matches(".*for update.*")) {
			if (!compstr.matches(".*with ur.*")) {
				str += " with ur ";

				if (logger.isDebugEnabled()) {
					logger.debug("Appending \"WITH UR\" to query.");
				}
			}
		}
		return str;
	}
}

Now you can wire this up in your Spring application context as such:

<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="entityInterceptor">
    		<bean class="com.company.dao.hibernate.DB2Interceptor"/>
  		</property>
...

Since this satisfies my requirement, I’m leaving the code as-is for my client; however, if you have a need of setting a specific transaction isolation value (as in the case of the link I mentioned previously), I have a better solution than his proposed Hashtable/thread approach.

Instead, create the ThreadUtils class as he has done, but make the TransactionManager class extend ThreadLocal to ensure this is specific to the calling thread. Be sure to remove the thread local after use, and you can even generate the getter/setter code in an aspect.

Since I wasn’t required to code the ThreadLocal side of things, I left that out; however, if anyway would benefit from seeing some extra code, let me know.

Showing 2 comments
  • tushar

    Very good article , really helpful ..
    can you please post TransactionManager and ThreadUtils class

  • tushar

    Hi,
    if possible can you please post the code of TransactionManager and ThreadUtils

    Thanks in advance
    tushar

Leave a Comment