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

Most Java programmers are aware of how to initialize and use an iterator utilizing the basic while loop. In most cases developers will iterate over a collection for every element in it using the follow generic code:

Iterator lIter = myList.iterator();
while(lIter.hasNext()) {
    ... // some logic
}

However it is surprising how many developers don’t know how to use the iterator in a for loop. You might wonder why would you want to use the for loop? Simply put you limit the scope of your iterator to the loop itself and it condenses the code to a single line. Rewriting the above iterator would look like:

for(Iterator lIter = myList.iterator(); lIter.hasNext(); ) {
    ... // some logic
}

There you have it, how to iterate over all the elements of a collection using an iterator and the for loop.

Recently I came across the issue in java where the split command I was executing was returning an array split on every character and not the | character as I had specified.

    String[] subStrings  = myString.split( "|" );

The reason for this functionality is that the | character represent a character delimeter. To split the string only on the | character we need to escape it. However do to the nature of the split method we need to escape twice so that the final regex string is an escaped |. This means me need the following to split on the Pipe | delimeter.

    String[] subStrings = myString.split( "\\|" );

If you wanted to split on a double pipe then you would use the following:

    String[] substrings = myString.split( "\\|\\|" );

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

When utilizing the iBATIS Framework as the persistence layer of your applications, you may get stumped as to why your LIKE statements are not being handled correctly and throws errors. When at first glance the errors have to do with other parameters in the query. The solution to this problem is to program the % character into the parameters passed into the sql statement, and to not have them hard coded into the sqlMap.

Example:

In this example we want to have a query that selects users with a last name like an inputted value. The query for this would be of the following format:

 SELECT * FROM USERS WHERE lastName LIKE "DEN%"

At first glance we may want to try to create our sqlMap statement as:

<select id="selectByName"parameterClass="String" >
    SELECT * FROM USERS WHERE lastName LIKE #value#%
</select>

This however will end up throwing errors. The correct way of creating this statement would be to leave out the % character and simply have the following statement:

<select id="selectByName"parameterClass="String" >
    SELECT * FROM USERS WHERE lastName LIKE #value#
</select>

Doing so will enable the query to function correctly where the % character gets passed in as part of the parameter. This also enables your query to be more dynamic by having the % character in any placement of the variable string.

Resources

• iBATIS – official website