Ibatis Inline Parameter Maps

Today I (re)discovered a feature in the Ibatis data mapper framework which was clearly documented, but for some reason was not being used in our project. The feature is called "inline parameter maps" and combined with a wrapper bean it can clean up a lot of clutter in the code and in the SqlMaps. Please feel free to share this example with your fellow Ibatis Data Mapper 2 framework users.

Suppose you are building software to interface with an old Oracle backoffice for a big parcel delivery company. One of the Classes you are using is the "Box" class, which is a basic Java Bean:

public class Box {
  private int height;
  private int width;
  private int depth;
  private int weight;
  private String color;
  private String origin;
  private String destination;

  <span style="color:#888888;">[... getters and setters here...]</span>
}

You need to store a Box object using a stored procedure in the backoffice. The stored procedure, called "store_box", returns the location code of the Box, but you do not want to store this location code in the Box object.

The Parameter Class. To solve this problem in Ibatis, you can use an aggregating parameter object called "StoreBoxParameters" (or any name you like). This is a simple bean which aggregates the Box object you want to store, and has extra fields for the data returned by the "store_box" stored procedure:

public class StoreBoxParameters {
  private Box box;
  private String locationCode;

  public StoreBoxParameters(Box aBox){
    this.box = aBox;
  }

  <span style="color:#888888;">[... getters and setters here...]</span>
}

To store the Box object, you write an SqlMap which calls the stored procedure with a parameter map:

<parameterMap id="storeBoxParameterMap" class="com.rolfje.StoreBoxParameters">
  <parameter property="box.height" jdbcType="NUMERIC" mode="IN" />
  <parameter property="box.width" jdbcType="NUMERIC" mode="IN" />
  <parameter property="box.depth" jdbcType="NUMERIC" mode="IN" />
  <parameter property="box.weight" jdbcType="NUMERIC" mode="IN" />
  <parameter property="box.color" jdbcType="VARCHAR" mode="IN" />
  <parameter property="box.origin" jdbcType="VARCHAR" mode="IN" />
  <parameter property="box.destination" jdbcType="VARCHAR" mode="IN" />
  <parameter property="locationCode" jdbcType="VARCHAR" mode="OUT" />
</parameterMap>

<procedure id="store_box" parameterMap="storeBoxParameterMap">
  {call store_box (?,?,?,?,?,?,?,?)}
</procedure>

And in the DAO, you can now fetch the locationcode seperate from the Box object:

public String storeBox (Box aBox) {
  StoreBoxParameters boxParameters = new StoreBoxParameters(aBox);
  getSqlMapClientTemplate().update("store_box", boxParameters);
  return boxParameters.getLocationCode();
}

Please note that this solution becomes even nicer when you have to call a stored procedure with multiple IN and OUT parameters and you want to separate these your business objects.

In the solution for the first problem, you may have noticed the stored procedure call in the Ibatis SQL map being written as "{call store_box (?,?,?,?,?,?,?,?)}". This is not exactly elegant, particularly when you have a long list of parameters. When bug fixing, it is hard to see which value gets passed into which parameter.

The Inline Parameter Map. In the Ibatis data mapper documentation, I found a beautiful solution for this, called inline parameter maps. First, you delete the parameter map from the SqlMap. Then, you replace the question marks with an inline version of these fields. You should get something like this:

<procedure id="store_box" parameterClass="com.rolfje.StoreBoxParameters">
  {call store_box (
    #box.height,jdbcType=NUMERIC,mode=IN#,
    #box.width,jdbcType=NUMERIC,mode=IN#,
    #box.depth,jdbcType=NUMERIC,mode=IN#,
    #box.weight,jdbcType=NUMERIC,mode=IN#,
    #box.color,jdbcType=VARCHAR,mode=IN#,
    #box.origin,jdbcType=VARCHAR,mode=IN#,
    #box.destination,jdbcType=VARCHAR,mode=IN#,
    #locationCode,jdbcType=VARCHAR,mode=OUT#
  )}
</procedure>

This cleans up your SqlMaps and improves their readability. Note that for OUT parameters to work, the fields you map them to may not be primitives.

Happy coding.

StoreBoxParameters