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.

The Case.
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;

  [... getters and setters here...]
}

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;
  }

  [... getters and setters here...]
}

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

3 Responses to Ibatis Inline Parameter Maps

  1. rolfje says:

    People who liked this also liked “Implicit Result Maps”, see IBATIS Data Mapper 2.0 documentation, page 33:

    http://svn.apache.org/repos/asf/ibatis/java/ibatis-2/trunk/ibatis-2-docs/en/iBATIS-SqlMaps-2_en.pdf

  2. neetu dawande says:

    The Inline Parameter Map was of great help. We were strugling with the out parameter issue for nearly half a day.

    Thanks🙂

  3. Bhavana says:

    This is helpful but please provide example for select query .. using Parameter map…Is it necessary to provide value attribute in that case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s