iBatis and Stored Oracle procedures/functions

By Andrius Miasnikovas

September 1, 2008

This one took me a while to get it right the first time. I won’t go into details of configuring iBatis datasources and such, and will go straight to putting some queries in the sqlMap file. Just let me note that I’m using iBatis 2.3 for these examples. I’ll start off with a procedure call.

<procedure id="getUserRoles" parameterMap="myParamMap">
    { call SCHEMA.GET_USERS_ROLES(?, ?) }
</procedure>

This one is pretty self-explanatory, just defining a procedure to be called. Notice the questionmarks in the SQL, don’t put the usual #variable# style annotation here. Also instead of parameterClass I use parameterMap here, which means I’ll have to define a parameter map for this query or it won’t work.

<parameterMap id="myParamMap" class="java.util.Map">
    <parameter property="username" mode="IN" />
    <parameter property="roles" jdbcType="ORACLECURSOR" mode="OUT" />
</parameterMap>

Here I explain to iBatis what kind of parameters I want to pass to the query and whether IN, OUT or INOUT mode should be used. It is important to specify correct jdbcType and javaType for the parameters otherwise when calling the procedure iBatis will spit out a very annoying Exception. Now for the JAVA part.

HashMap params = new HashMap();
params.put("username", username);
getSqlMapClient().queryForObject("getUserRoles", params);

Object o = params.get("roles");
if(o instanceof ResultSet)
{
    ResultSet res = (ResultSet) o;
    while(res.next())
        System.out.println(res.getInt("ROLE_NR"));
    res.close();
}

As you can see the procedure call is pretty much like any other SELECT query, but you don’t need to check the return value. All the OUT parameters will be stored in the Map object that you passed. In my case I defined a roles parameter as ORACLECURSOR which translates into ResultSet object. It’s not shown in this code snippet, but don’t forget to use startTransaction() and endTransaction() if you’re managing your own transactions. Also if the stored procedure is modifying the data you’ll most likely need commitTransaction() too, but iBatis transaction management is a different story.

And now for some stored function calling.

<procedure id="exampleFunction" parameterMap="funcMap">
    { ? = call SCHEMA.MY_FUNCTION(?, ?) }
</procedure>

The first thing you might notice is that it still says __, but it’s no a typo, iBatis doesn’t have a __ tag (at least to my knowledge). Note how because it’s a function it has a questionmark at the beginning of the query to specify the return variable.

<parameterMap id="funcMap" class="java.util.Map">
  <parameter property="result" jdbcType="NUMERIC" mode="OUT"/>
  <parameter property="P1" />
  <parameter property="P2" javaType="java.util.Date" jdbcType="DATE" mode="OUT" />
</parameterMap>

Here I defined a parameter map which is pretty much like the one used for the procedure call except for the extra parameter result that is used to get the return value, but formally registered as an OUT parameter and not a return value.

HashMap params = new HashMap();
params.put("P1", param);
getSqlMapClient().queryForObject("exampleFunction", params);

Object o = params.get("result");
if(o instanceof Long)
{
    long res = (Long)o;
    System.out.println(res);
}

The actual call to the stored function is exactly the same as in the case of procedure. You just have to correctly configure the parameter mapping so that the return value will be available as an extra output parameter. In fact correctly configuring the parameter map was the place where I spent most of my time trying to figure out how to do it. In my case when using Oracle and retrieving an integer you can not jdbcType it INTEGER, NUMBER or whatever, it only worked when I put NUMERIC as a parameter JDBC type. That’s why I put so much stress on the parameter configuration. I wasted quite some time getting the annoying “java.sql.SQLException: Invalid column type” error. So, I guess that’s about it.