Using Stored Procedure/Function with iBATIS

This is simple example for using stored function with iBATIS.

Recently I implemented number generating module using oracle stored function and iBATIS with lots of trial/error more than I expected. It's because I couldn't find a complete working example. The example in official manual from iBATIS is not sufficient , the example from the book 'iBATIS in Action' is not correct(I didn't check the corrections from the Manning site), and the search results from Goole didn't include excellent examples for me.

The following exmaple is using number table to generate number for construct new primary key for specified table.

The number table has following structure

CREATE TABLE test.PS_NUMBER (
  table_name VARCHAR2(50) NOT NULL,
  next_no NUMBER(20) NOT NULL,
  CONSTRAINT pk_number PRIMARY KEY (table_name)
);

To prevent same number is given in highly concurrent situation with Oracle database, SELECT ~ FOR UPDATE should be used.
But iBATIS dosen't provide ways to execute both select statement and update statement at the same time(as I know), I decided to use stored procedure. Because it is possible to lock the table row in case of server failure right after the first select(for update) statement is executed but before second update statement is excuted.

The stored procedure is like following

CREATE OR REPLACE FUNCTION gen_num_for_table(table_name_in IN VARCHAR2)
  RETURN NUMBER
  IS
  next_no_out NUMBER;
BEGIN
  SELECT next_no INTO next_no_out
  FROM ps_number
  WHERE table_name = table_name_in
  FOR UPDATE WAIT 3;

  UPDATE ps_number SET next_no = next_no + 1
  WHERE table_name = table_name_in;

  RETURN next_no_out;
END gen_num_for_table;

Now, for server applications the SQL Map is like following The statement part should be enclosed with curly brackets({}) and the statement is proceeded by '? = call' in case of function. In case of procedure, just start with 'call'. These are same syntax with java.sql.CallableStatement. Acually, iBATIS will execute callable statement using specified statement and parameterMap. The parameterMap should be specified surely. The order of parameter is important. In case of function, the first parameter is for return value and so should have 'OUT' mode attribute. For each parameter, jdbcType should be specified explicitly, so as to be interpreted to CallableStatment.registerOutParameter method with correct parameter and setXXX of CallableStatement.

Finally the application using above sql map is like following

public int generateNumberForTable(String tableName) throws Exception{

  java.util.Map map = new java.util.HashMap();
  map.put("table_name", tableName);

  this.sqlMapClient.queryForObject("Common.generateNumberForTable", map);

  return (Integer)map.get("next_no");
}

The input is given using map object as is expected from SQL Map definition. The important thing is that the result is not given as a return value of queryForObject method but is put in the map with the key specified in SQL Map definition.

For your information, the equivalent codes using plain JDBC would be like following. It could help you understand how the above codes would work.

public int generateNumberForTable(String tableName) throws Exception{
   Connection conn = null;
   CallableStatement cstmt = null;

   try{
      DataSource ds = this.sqlMapClient.getDataSource();
      conn = ds.getConnection();
      cstmt = conn.prepareCall("{? = call gen_num_for_table(?)}");
      cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
      cstmt.setString(2, tableName);

      cstmt.executeQuery();

      return cstmt.getInt(1);
   }catch(Exception ex){
      ex.printStackTrace(System.err);
      throw ex;
   }finally{
      if(cstmt != null){ try{ cstmt.close();}catch(Exception ex2){} }
      if(conn != null){ try{ conn.close();}catch(Exception ex2){} }
   }
}

For more readings.

• iBatis and Stored Oracle procedures/functions This is the article that I found after this writing and I think it gives you more information about iBATIS and Oracle stored procedure/function. I didn't execute personally the code in that article and I didn't understand what is 'ORACLECURSOR' type in the example code, but as a whole you can find hints or guide from the article.