How can I integrate Oracle's row level security with MyBatis?

A project I am working on uses and Oracle database with row level security. I need to be able to invoke call DBMS_APPLICATION_INFO.SET_CLIENT_INFO('userId'); before I can execute any other SQL statements. I am trying to figure out a way to implement this within MyBatis. Several ideas that I had, but were unable to make work, include the following:

Attempt 1

<select id="selectIds" parameterType="string" resultType="Integer">
      call DBMS_APPLICATION_INFO.SET_CLIENT_INFO(#{userId});
    select id from FOO
</select>

However, you can't two statements within a single JDBC call and MyBatis doesn't have support for JDBC batch statements, or at least not that I could find.

Attempt 2

<select id="s开发者_开发百科electMessageIds" parameterType="string" resultType="Integer">
    <![CDATA[
        declare
           type ID_TYP is table of AGL_ID.ID_ID%type;
           ALL_IDS ID_TYP;
        begin
           DBMS_APPLICATION_INFO.SET_CLIENT_INFO(#{userId});
           select ID bulk collect
             into ALL_IDS
             from FOO
        end;
    ]]>
</select>

However, that is as far I got because I learned that you can't return data in a procedure, only in a function, so there was no way to return the data.

Attempt 3

I've considered just creating a simple MyBatis statement that will set the client information and it will need to be called before executing statements. This seems the most promising, however, we are using Spring and database connection pooling and I am concerned about race conditions. I want to ensure that the client information won't bleed over and affect other statements because the connections will not get closed, they will get reused.

Software/Framework Version Information

Oracle 10g

MyBatis 3.0.5

Spring 3.0.5

Update

Forgot to mention that I am also using MyBatis Spring 1.0.1

---

This sounds like a perfect candidate for transactions. You can create a @Transactional service (or DAO) base class that makes the DBMS_APPLICATION function call. All your other service classes could extend the base and call the necessary SQL.

In the base class, you want to make sure that you only call the DBMS_APPLICATION function once. To do this, use the TransactionSynchronizationManager.hasResource() and bindResource() methods to bind a boolean or similar marker value to the current TX. Check this value to determine if you need to make the function call or not.

If the function call exists only for a 'unit of work' in the DB, this should be all you need. If the call exists for the duration of the connection, the base class will need too clean up in a finally block somehow.

Rather than a base class, another possibility would be to use AOP and do the function call before method invocation and the clean up as finally advice. The key here would be to make sure that your interceptor is called after Spring's TransactionInterceptor (i.e. after the tx has started).

---

One of the safest solution would be to have a specilaized DatSourceUtils

1: http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/datasource/DataSourceUtils.html and override doGetConnection(DataSource dataSource) and setClientInfo on connection

Write your own abstraction over SqlMapClientDaoSupport to pass client information.