Friday, December 22, 2006

Using a Stored Procedure to Insert Records with Spring & Hibernate

It took a little research to discover how to integrate a stored procedure for inserting records into my application. However, once I found the right piece of information, it became easy. Here is what I am using:

   <class name="com.codebits.vo.SuperSimpleRecord" table="super_simple">
       <id name="id" type="string" length="32" unsaved-value="null">
           <generator class="uuid.hex" />
       <property name="name" type="string" length="100" not-null="true" />
       <property name="count" type="integer" not-null="false" />
       <property name="created" type="date" not-null="true" insert="false" />
       <sql-insert callable="true" check="none">
         {call insert_record(?, ?, ?)}

Notice the check attribute? That's what took me awhile to find, I think it was introduced in Hibernate 3.2. Just for completeness, here is my stored procedure:

create or replace procedure insert_record(
  in_name in varchar2, in_count in number, in_id in varchar2
) as begin
  insert into super_simple
  values(in_id, in_name, in_count, sysdate);
end insert_record;

Per-User Connection Pooling for Spring, Hibernate and Oracle VPD (Virtual Private Database)

There are several forum messages about how to handle per-user connection pooling but none of them clearly state what works. It is quite simple. Here is part of my applicationContext.xml.

<!-- this datasource must hande per-user connection pooling -->
<bean id="targetDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="url" value="jdbc:oracle:thin:@pooz:1521:ACC"/>

<!-- this object wraps the original datasource allows change of user when needed -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
  <property name="targetDataSource" ref="targetDataSource"/>
  <property name="username"><value>unknown</value></property>
  <property name="password"><value>unknown</value></property>

<!-- this bean does real work, inject the user adapter so it can change the user credentials -->
<bean id="create" class="com.codebits.dao.hibernate.actions.Create">
  <property name="sessionFactory" ref="sessionFactory" />
  <property name="userAdapter" ref="dataSource" />

The Java code is also simple, just before you grab the Hibernate session, do the following:

  userAdapter.setCredentialsForCurrentThread("test", "test");

Thursday, December 21, 2006

Solving the "Second-level cache is not enabled" Exception

I ran into the "Second-level cache is not enabled" error recently even though I had the hibernate.cache.use_query_cache and hibernate.cache.use_second_level_cache parameters defined as true. The issue was resolved when I also defined the hibernate.cache.provider_class as org.hibernate.cache.EhCacheProvider.