SQL Maps are the core to the iBATIS Persistence Framework. These xml documents define the mappings between sql statements and your application objects. The base elements that make up the sqlmap are:

• resultMap
  resultMap elements define how returned data from select statements should be mapped to your objects. Providing relationships between object properties and columns in the result set.
• select
  The select element defines a select statement.
• insert
  The insert element defines an insert statement
• update
  The update element defines an update statement
• delete
  The delete element defines a delete statement

resultMap

The resultMap element requires 3 attributes, id, class, and groupBy. These properties define the id for identifying the result map when used in addtional statements, the Java Class this map correlates to, and the column that the results should be grouped together by in case your object has a collections of additional data. Below shows the resultMap for a basic Status Object.

The above xml snippet defines our result mapping. We have identified the mapping as statusMap that maps the results to our Status object grouping data by the id property. Inside the element we define the individual column mappings where for example the ID column of the result set maps to the id property of the Status object. All mappings assume that their are get/set methods defined for the properties.

select

The select statement also requires 2 or 3 attributes for its definition an id, parameterClass, and a resultMap. Where the parameterClass determines what object, primitive type will be used to pass in any variables and the resultMap is the mappings for the result set. If no parameters are being passed in then the parameterClass attribute can be left out. Below is a simple select all statement with no parameters:

    SELECT 
        *
    FROM PBL_RUN_STATUS

As can be seen from this basic statement, inside the select element you define the SQL statement that you want to have executed. By passing in parameters you can make the defined statement dynamic as shown below:

    SELECT 
        *
    FROM P_STATUS s
    WHERE s.NAME = #value#

In the above statement we made the query dynamic by passing in a string parameter that gets placed at the #value# text of the statement. So when called we could pass in “ABORTED” to make the sql query SELECT * FROM P_STATUS s WHERE s.NAME = “ABORTED”

insert

The insert statement functions similar to the select statement with the exception that their is no resultMap attribute as no result is returned beyond the inserted row id if the selectKey sub element is defined. The selectKey element defines how to define the id of the inserted row. Below is a sample insert statmenet for an TSQL database that uses an identity key.

    
        SELECT next_identity( 'PBL_RUN_STATUS' )
    
     INSERT INTO P_STATUS
    ( NAME, DESCRIPTION )
    VALUES
    ( #name:VARCHAR#, #description:VARCHAR# )

In this example you will note 2 things first is the selectKey sub element where we identify how to get the identifier of the inserted row, in this note how we identify the keyProperty of the object as well as its resultClass for its type. Second you will note then when using an object parameter you utilize it’s property names to access how to use their values in the statement, in this case the name and description properties are being utilized.

update / delete

The update delete clauses work the same as the insert statement excep that you do not define a selectKey sub element. Instead you simply define your id and parameter class and write your marked up sql as the contents of the element. Below are examples for the update or deletion of the Status object used throughout the examples.

    UPDATE P_STATUS
    SET
        NAME = #name:VARCHAR#,
        DESCRIPTION = #description:VARCHAR#
    WHERE
        ID = #id:int#

    DELETE 
    FROM PBL_RUN_STATUS
    WHERE 
    ID=#id:INT#

Using iBATIS as your persistence framework for you applications can greatly reduce your development time. Full details on the framework and SQL Maps can be obtained from the projects official site ibatis.apache.org.

Resources

• iBatis – official site