MDBitz Development Resources

How many times have you found yourself defining multiple find or search methods for obtaining data in your Web Applications. For example for a Users table you may have a findByName, findById, FindByEmail, and etc. Utilizing the iBATIS framework and SQL Maps we can have all these sql statements condensed into a single dynamic select statement.

To illustrate how this would be completed lets define our User table as the following:

Traditionally to create the above find methods you would create a sql statement for each query. Using iBATIS’s Dynamic SQl we are able to add them all into one statement by use of the dynamic element. To help illustrate lets start with a basic select SQL Map.

<select id="User.find" parameterClass="com.mdbitz.model.User" resultMap="userMap">
    SELECT * 
    FROM USERS
    WHERE 
        FIRST_NAME LIKE #firstName:VARCHAR#
        AND LAST_NAME LIKE #lastName:VARCHAR#
</select>

Using this simple map as a base we could extends it by adding a dynamic clause that only adds the where clauses if a property is set in the parameter object. To do this we would use the isNotNull element that outputs its content only if a property is not null. An updated dynamic map would look like:

<select id="User.find" parameterClass="com.mdbitz.model.User" resultMap="userMap">
    SELECT * 
    FROM USERS
    <dynamic prepend="WHERE">
        <isNotNull property="firstName" prepend="AND">
        FIRST_NAME LIKE #firstName:VARCHAR#
        </isNotNull>
        <isNotNull property="lastName" prepend="AND">
        LAST_NAME LIKE #lastName:VARCHAR#
        </isNotNull>
    </dynamic>
</select>

Using these dynamic statements you could extend this to do any type of find by that you need. You could query by name, phone number, email, id, or any combination of them. In addition to the property you could also use isNull, isEmpty, isNotEmpty, isGreaterThan, IsGreaterEqual, and etc. Instead of dozens of SQL Map statements for each desired search or query. Dynamic SQL allows you to condense your sql query into simpler more robust statements.

Resources

• iBATIS – official site

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.

<resultMap id="statusMap" class="com.mdbitz.Status" groupBy="id">
    <result property="id" column="ID" />
    <result property="name" column="NAME" />
    <result property="description" column="DESCRIPTION" />
</resultMap>

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 id="list" resultMap="statusMap" >
    SELECT 
        *
    FROM PBL_RUN_STATUS
</select>

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 id="findByName" parameterClass="String" resultMap="statusMap" >
    SELECT 
        *
    FROM P_STATUS s
    WHERE s.NAME = #value#
</select>

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.

<insert id="insert" parameterClass="com.mdbitz.Status">
    <selectKey resultClass="int" keyProperty="id">
        SELECT next_identity( 'PBL_RUN_STATUS' )
    </selectKey>
     INSERT INTO P_STATUS
    ( NAME, DESCRIPTION )
    VALUES
    ( #name:VARCHAR#, #description:VARCHAR# )
</insert>

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 id="update" parameterClass="com.mdbitz.Status">
    UPDATE P_STATUS
    SET
        NAME = #name:VARCHAR#,
        DESCRIPTION = #description:VARCHAR#
    WHERE
        ID = #id:int#
</update>

<delete id="delete" parameterClass="com.mdbitz.Status">
    DELETE 
    FROM PBL_RUN_STATUS
    WHERE 
    ID=#id:INT#
</delete>

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