Statement-type Element Attributes

The six statement-type elements take various attributes. See Mapped Statements for a table itemizing which attributes each element-type accepts. The individual attributes are described in the sections that follow.

id attribute

The required id attribute provides a name for this statement, which must be unique within this <SqlMap>.

parameterMap attribute

A Parameter Map defines an ordered list of values that match up with the "?" placeholders of a standard, parameterized query statement. The following example shows a <parameterMap> and a corresponding <statement>.

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

<statement id="insertProduct" parameterMap="insert-product-param">
  insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?);
</statement>

In the above example, the Parameter Map describes two parameters that will match, in order, two placeholders in the SQL statement. The first "?" is replaced by the value of the id property. The second is replaced with the description property.

SQLMap also supports named, inline parameters, which most developers seem to prefer. However, Parameter Maps are useful when the SQL must be kept in a standard form or when extra information needs to be provided. See Parameter Maps for futher details.

parameterClass attribute

If a parameterMap attribute is not specified, you may specify a parameterClass instead and use inline parameters. The value of the parameterClass attribute can be any existing PHP class name. The following example shows a statement using a PHP class named Product in parameterClass attribute.

<statement id="statementName" parameterClass="Product">
  insert into PRODUCT values (#id#, #description#, #price#)
</statement>

resultMap attribute

A Result Map lets you control how data is extracted from the result of a query, and how the columns are mapped to object properties. The following example shows a <resultMap> element and a corresponding <statement> element.

<resultMap id="select-product-result" class="product">
  <result property="id" column="PRD_ID"/>
  <result property="description" column="PRD_DESCRIPTION"/>
</resultMap>

<statement id="selectProduct" resultMap="select-product-result">
  select * from PRODUCT
</statement>

In the above example, the result of the SQL query will be mapped to an instance of the Product class using the "select-product-result" <resultMap>. The <resultMap> says to populate the id property from the PRD_ID column, and to populate the description property from the PRD_DESCRIPTION column.

See Result Maps for futher details.

resultClass attribute

If a resultMap is not specified, you may specify a resultClass instead. The value of the resultClass attribute can be the name of a PHP class or primitives like integer, string, or array. The class specified will be automatically mapped to the columns in the result, based on the result metadata. The following example shows a <statement> element with a resultClass attribute.

<statement id="SelectPerson" parameterClass="int" resultClass="Person">
  SELECT
  PER_ID as Id,
  PER_FIRST_NAME as FirstName,
  PER_LAST_NAME as LastName,
  PER_BIRTH_DATE as BirthDate,
  PER_WEIGHT_KG as WeightInKilograms,
  PER_HEIGHT_M as HeightInMeters
  FROM PERSON
  WHERE PER_ID = #value#
</statement>

In the above example, the Person class has properties including: Id, FirstName, LastName, BirthDate, WeightInKilograms, and HeightInMeters. Each of these corresponds with the column aliases described by the SQL select statement using the "as" keyword, a standard SQL feature. When executed, a Person object is instantiated and populated by matching the object property names to the column names from the query.

Using SQL aliases to map columns to properties saves defining a <resultMap> element, but there are limitations. There is no way to specify the types of the output columns (if needed), there is no way to automatically load related data such as complex properties.You can overcome these limitations with an explicit Result Map.

listClass attribute

In addition to providing the ability to return an TList of objects, the DataMapper supports the use of custom collection: a class that implements ArrayAccess. The following is an example of a TList (it implements ArrayAccess) class that can be used with the DataMapper.

class AccountCollection extends TList
{
    public function addRange($accounts)
    {
        foreach($accounts as $account)
            $this->add($account);
    }

    public function copyTo(TList $array)
    {
        $array->copyFrom($this);
    }
}

An ArrayAccess class can be specified for a select statement through the listClass attribute. The value of the listClass attribute is the full name of a PHP class that implements ArrayAccess. The statement should also indicate the resultClass so that the DataMapper knows how to handle the type of objects in the collection. The resultClass specified will be automatically mapped to the columns in the result, based on the result metadata. The following example shows a <statement> element with a listClass attribute.

<statement id="GetAllAccounts"
 listClass="AccountCollection"
 resultClass="Account">
   select
   Account_ID as Id,
   Account_FirstName as FirstName,
   Account_LastName as LastName,
   Account_Email as EmailAddress
   from Accounts
   order by Account_LastName, Account_FirstName
</statement>

cacheModel attribute

If you want to cache the result of a query, you can specify a Cache Model as part of the <statement> element. The following example shows a <cacheModel> element and a corresponding <statement>.

<cacheModel id="product-cache" implementation="LRU">
  <flushInterval hours="24"/>
  <flushOnExecute statement="insertProduct"/>
  <flushOnExecute statement="updateProduct"/>
  <flushOnExecute statement="deleteProduct"/>
  <property name="size" value="1000" />
</cacheModel>

<statement id="selectProductList" parameterClass="int" cacheModel="product-cache">
  select * from PRODUCT where PRD_CAT_ID = #value#
</statement>

In the above example, a cache is defined for products that uses a Least Recently Used [LRU] type and flushes every 24 hours or whenever associated update statements are executed. See Cache Models for futher details

extends attribute

When writing Sql, you often encounter duplicate fragments of SQL. SQLMap offers a simple yet powerful attribute to reuse them.

<select id="GetAllAccounts"
  resultMap="indexed-account-result">
select
  Account_ID,
  Account_FirstName,
  Account_LastName,
  Account_Email
from Accounts
</select>

<select id="GetAllAccountsOrderByName"
  extends="GetAllAccounts"
  resultMap="indexed-account-result">
    order by Account_FirstName
</select>