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