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]](images/note.png) | 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. |
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.
| Note |
|---|---|
Dynamic Mapped Statements (Section 3.9) can't use Parameter Maps. Being dynamic, the number of parameters will change and defeat the purpose of a Parameter Map. Depending on your provider, this may hinder your ability to use Dynamic Mapped Statements if your provider requires the use of some of the attributes, such as size or scale, that a <parameter> provides. |
Parameter Maps can be provided as an external element and inline. Example 3.16 shows an external Parameter Map.
Example 3.18. An external Parameter Map
<parameterMap id="parameterMapIdentifier" [class="fullyQualifiedClassName, assembly|typeAlias"] [extends="[sqlMapNamespace.]parameterMapId"]> <parameter property ="propertyName" [column="columnName"] [direction="Input|Output|InputOutput"] [dbType="databaseType"] [type="propertyCLRType"] [nullValue="nullValueReplacement"] [size="columnSize"] [precision="columnPrecision"] [scale="columnScale"] [typeHandler="fullyQualifiedClassName, assembly|typeAlias"] <parameter ... ... /> <parameter ... ... /> </parameterMap>
In Example 3.16, the parts in [brackets] are optional. The
parameterMap element only requires the id
attribute. The class attribute is optional but
recommended. The class attribute helps to validate
the incoming parameter and optimizes performance. Example 3.17 shows a
typical <parameterMap>.
Example 3.19. A typical <parameterMap> element
<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
|
The <parameterMap> element accepts three attributes:
id (required), class
(optional), and extends (optional).
The required id attribute provides a unique identifier for the <parameterMap> within this Data Map.
The optional class attribute specifies an
object class to use with this <parameterMap>. The full classname
and assembly or an alias must be specified. Any class can be
used.
| Note |
|---|---|
The parameter class must be a property object or
|
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.
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.
The property attribute of
<parameter> is the name of a field or property of the parameter object.
It may also be the name of an entry in a
IDictionary object. 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.)
The column attribute is used to define to
the name of a parameter used by a stored procedure.
The direction attribute may be used to
indicate a stored procedure parameter's direction.
Table 3.3. Parameter direction attribute values
| Value | Description |
|---|---|
| Input | input-only |
| Output | output-only |
| InputOutput | bidirectional |
The dbType attribute is used to
explicitly specify the database column type of the parameter to be set
by this property. For certain operations, some ADO.NET providers are
not able to determine the type of a column, and the type must be
specified.
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. Whereas .NET only has one Date value
type (System.DateTime), most SQL databases have
more than one. 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. |
The dbType attribute can be set to any
string value that matches a constant in the specific data type enum of
the used provider such as System.Data.SqlDbType
for Microsoft Sql Server. Section 3.6 describes the types that are
supported by the framework.
The type attribute is used to specify the
CLR 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.
Normally, the type can be derived from a property through reflection, but certain mappings that use objects such as a Map cannot provide the property type to the framework. If the attribute type is not set and the framework cannot otherwise determine the type, the type is assumed to be an Object. Section 6 details the CLR types and available aliases that have pre-built support in the framework.
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 (–9999), a
NULL will be written to the database instead of
the value.
The precision attribute is used to set
the maximum number of digits used to represent the property
value.
The scale attribute sets the number of
decimal places used to resolve the property value.
The typeHandler attribute allows the use
of a Custom Type Handler (see the Custom Type Handler section). 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 and Guids from your database for example.
If you prefer to use inline parameters instead of parameter maps, you can add extra type information inline too. The inline parameter map syntax lets you embed the property name, the property type, the column type, and a null value replacement into a parametrized SQL statement. The next four examples shows statements written with inline parameters.
Example 3.20. A <statement> using inline parameters
<statement id="insertProduct" parameterClass="Product"> insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (#id#, #description#) </statement>
The following example shows how dbTypes can be declared inline.
Example 3.21. A <statement> using an inline parameter map with a type
<statement id="insertProduct" parameterClass="Product"> insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (#id:int#, #description:VarChar#) </statement>
The next example shows how dbTypes and null value replacements can also be declared inline.
Example 3.22. A <statement> using an inline parameter map with a null value replacement
<statement id="insertProduct" parameterClass="Product"> insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (#id:int:-999999#, #description:VarChar#) </statement>
Like the DataMapper for Java, there is an alternate inline syntax that allows the specification of the property, type, dbType, and null value replacement. The following example shows that syntax in use.
Example 3.23. A <statement> using alternate inline syntax with property, type, dbType, and null value replacement
<update id="UpdateAccountViaInlineParameters" parameterClass="Account"> update Accounts set Account_FirstName = #FirstName#, Account_LastName = #LastName#, Account_Email = #EmailAddress,type=string,dbType=Varchar,nullValue=no_email@provided.com# where Account_ID = #Id# </update>
| Note |
|---|---|
When using inline parameters, you cannot specify the null value replacement without also specifying the dbType. You must specify both due to the parsing order. For round-trip transparency of null values, you must also specify database columns null value replacements in your Result Map (see Section 3.5). Inline parameter maps are handy for small jobs, but when there are a lot of type descriptors and null value replacements in a complex statement, an industrial-strength, external parameterMap can be easer. |
In practice, you will find that many statements take a single
parameter, often an Integer or a
String. Rather than wrap a single value in
another object, you can use the standard library object
(String, Integer, et
cetera) as the parameter directly. Example 3.22 shows a statement using
a standard type parameter.
Example 3.24. A <statement> using standard type parameters
<statement id="getProduct" parameterClass="System.Int32">
select * from PRODUCT where PRD_ID = #value#
</statement>Assuming PRD_ID is a numeric type, when a
call is made to this Mapped Statement, a standard Integer object can be
passed in. The #value# parameter will be replaced
with the value of the Integer instance. The name
value is simply a placeholder, you can use another
moniker of your choice. Result Maps support primitive types as results
as well.
For your convenience, primitive types are aliased by the
framework. For example, int can be used in place
of System.Integer. For a complete list, see
Section 3.6, "Supported Types for Parameter Maps and Result
Maps".
You can also pass a IDictionary
instance as a parameter object. This would usually be a
HashTable. Example 3.23 shows a <statement>
using an IDictionary for a
parameterClass.
Example 3.25. A <statement> using a Map or IDictionary for a parameterClass
<statement id="getProduct" parameterClass="System.Collections.IDictionary"> select * from PRODUCT where PRD_CAT_ID = #catId# and PRD_CODE = #code# </statement>
In Example 3.23, notice that the SQL in this Mapped Statement
looks like any other. There is no difference in how the inline
parameters are used. If a HashTable instance is
passed, it must contain keys named catId and
code. The values referenced by those keys must be
of the appropriate type for the column, just as they would be if passed
from a properties object.
For your convenience, IDictionary
types are aliased by the framework. So, map or
HashTable can be used in place of
System.Collections.Hashtable. For a complete list
of aliases, see Section 3.6, "Supported Types for Parameter Maps and
Result Maps".