Parameter Maps and Inline Parameters

Most SQL statements are useful because we can pass them values at runtime. Someone wants a database record with the ID 42, and we need to merge that ID number into a select statement. A list of one or more parameters are passed at runtime, and each placeholder is replaced in turn. This is simple but labor intensive, since developers spend a lot of time counting symbols to make sure everything is in sync.

Note: Preceding sections briefly touched on inline parameters, which automatically map properties to named parameters. Many iBATIS developers prefer this approach. But others prefer to stick to the standard, anonymous approach to SQL parameters by using parameter maps. Sometimes people need to retain the purity of the SQL statements; other times they need the detailed specification offered by parameter maps due to database or provider-specific information that needs to be used.

Parameter Map

A Parameter Map defines an ordered list of values that match up with the placeholders of a parameterized query statement. While the attributes specified by the map still need to be in the correct order, each parameter is named. You can populate the underlying class in any order, and the Parameter Map ensures each value is passed in the correct order.

Parameter Maps can be provided as an external element and \emph{inline}. The following example shows an external Parameter Map.

<parameterMap id="parameterMapIdentifier"
  [extends="[sqlMapNamespace.]parameterMapId"]>
  <parameter
    property ="propertyName"
    [column="columnName"]
    [dbType="databaseType"]
    [type="propertyCLRType"]
    [nullValue="nullValueReplacement"]
    [size="columnSize"]
    [precision="columnPrecision"]
    [scale="columnScale"]
    [typeHandler="class.name"]
  <parameter ... ... />
  <parameter ... ... />
</parameterMap>

In the above example, the parts in [brackets] are optional. The parameterMap element only requires the id attribute. The following example shows a typical <parameterMap>.

<parameterMap id="insert-product-param" class="Product">
  <parameter property="description" />
  <parameter property="id"/>
</parameterMap>

<statement id="insertProduct" parameterMap="insert-product-param">
  insert into PRODUCT (PRD_DESCRIPTION, PRD_ID) values (?,?);
</statement>
Note: Parameter Map names are always local to the Data Map definition file where they are defined. You can refer to a Parameter Map in another Data Map definition file by prefixing the id of the Parameter Map with the namespace of the Data Map (set in the <sqlMap> root element).

<parameterMap> attributes

The <parameterMap> element accepts two attributes: id (required) and extends (optional).

id attribute

The required id attribute provides a unique identifier for the <parameterMap> within this Data Map.

extends attribute

The optional extends attribute can be set to the name of another parameterMap upon which to base this parameterMap. All properties of the super parameterMap will be included as part of this parameterMap, and values from the super parameterMap are set before any values specified by this parameterMap. The effect is similar to extending a class.

<parameter> Elements

The <parameterMap> element holds one or more parameter child elements that map object properties to placeholders in a SQL statement. The sections that follow describe each of the attributes.

property attribute

The property attribute of <parameter> is the name of a property of the parameter object. It may also be the name of an entry in an array. The name can be used more than once depending on the number of times it is needed in the statement. (In an update, you might set a column that is also part of the where clause.)

direction attribute

The direction attribute may be used to indicate a stored procedure parameter's direction.

ValueDescription
Input input-only
Output output-only
InputOutput bidirectional

column attribute

The column attribute is used to define to the name of a parameter used by a stored procedure.

dbType attribute

The dbType attribute is used to explicitly specify the database column type of the parameter to be set by this property. This attribute is normally only required if the column is nullable. Although, another reason to use the dbType attribute is to explicitly specify date types. Most SQL databases have more than one datetime type. Usually, a database has at least three different types (DATE, DATETIME, TIMESTAMP). In order for the value to map correctly, you might need to specify the column's dbType.

Note: Most providers only need the dbType specified for nullable columns. In this case, you only need to specify the type for the columns that are nullable.

type attribute

The type attribute is used to specify the type of the parameter's property. This attribute is useful when passing InputOutput and Output parameters into stored procedures. The framework uses the specified type to properly handle and set the parameter object's properties with the procedure's output values after execution.

nullValue attribute

The nullValue attribute can be set to any valid value (based on property type). The nullValue attribute is used to specify an outgoing null value replacement. What this means is that when the value is detected in the object property, a NULL will be written to the database (the opposite behavior of an inbound null value replacement). This allows you to use a magic null number in your application for types that do not support null values (such as int, double, float). When these types of properties contain a matching null value (for example, say, -9999), a NULL will be written to the database instead of the value.

Tip: For round-trip transparency of null values, you must also specify database columns null value replacements in your Result Map.

size attribute

The size attribute sets the maximum size of the data within the column.

precision attribute

The precision attribute is used to set the maximum number of digits used to represent the property value.

scale attribute

The scale attribute sets the number of decimal places used to resolve the property value.

typeHandler attribute

The typeHandler attribute allows the use of a Custom Type Handler. This allows you to extend the DataMapper's capabilities in handling types that are specific to your database provider, are not handled by your database provider, or just happen to be a part of your application design. You can create custom type handlers to deal with storing and retrieving booleans from your database for example.