Ibatis Tutorial: Dynamic SQL

This tutorial expands on the knowledge and sample code from Ibatis Tutorial: Aggregation with groupBy and previous Ibatis tutorials.

One of the problems in dealing with database queries is that the SQL needs to change based on runtime conditions, typically user input but can also include things like security (ie making sure the user can only see what they're authorized to see), user settings and so on.

The example data model from this series are the beginning of the Party data model, which is very common in CRM and related fields. There are many variations on this model.

Such systems will inevitably have a search screen to find clients, users, employees and the like. Anyone who has done this knows that it can be, depending on your persistence framework, anything from really tedious to incredibly painful. It could involve dynamically building a SQL statement, which can have problems with getting the parameter binding done correctly, or programmatic queries (eg LINQ-to-SQL in the .Net world) to the incredibly painful world of JPA native queries, especially if done dynamically.

Ibatis has a custom solution for this kind of problem that, in my opinion, is the easiest to use, easiest to understand and most powerful of any of these solutions.

Firstly, let's lay the groundwork.

FindParty.java

package com.cforcoding;

public class FindParty {
    private String name;
    private String phone;
    private String email;

    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
}

This class will store the query criteria. In our example, we will be able to search for a party based on their name, a phone number, an email address or a combination of these.

PartyDAOImpl.java

@Override
@SuppressWarnings("unchecked")
@Transactional(propagation = Propagation.SUPPORTS)
public List findParties(FindParty find) {
    return getSqlMapClientTemplate().queryForList("findParties", find);
}

Once again the interface is assumed.

Party.xml

<select id="findParties" parameterClass="com.cforcoding.FindParty" resultMap="party">
    SELECT pt.id, party_type, name, title, given_names, last_name, date_of_birth, company_number, state_of_incorporation, ct.id contact_id, contact_type, details
    FROM party pt
    LEFT OUTER JOIN person ps ON pt.id = ps.id
    LEFT OUTER JOIN company cy ON pt.id = cy.id
    LEFT OUTER JOIN contact ct ON pt.id = ct.party_id

    <dynamic prepend="WHERE">

        <isNotNull property="name" prepend="AND">
            pt.name LIKE #name#
        </isNotNull>

        <isNotNull property="email" prepend="AND">
            EXISTS (SELECT id FROM contact WHERE party_id = pt.id AND contact_type = 'E' AND detailsLIKE #email#)
        </isNotNull>

        <isNotNull property="phone" prepend="AND">
            EXISTS (SELECT id FROM contact WHERE party_id = pt.id AND contact_type IN ('P','W','M') AND details LIKE #email#)
        </isNotNull>

    </dynamic>

</select>

Now this starts to get interesting.

The first thing to notice is that the crux of the query and the result map are the same as our selectAllParties query. We're definitely getting some value out of reuse of the result map.

There are three conditional clauses in this statement, one for each of our properties. Each of these are saying that if the specified property is not null then include the enclosed condition in the query. The enclosed text is nothing more than SQL with Ibatis parameter mappings.

It's worth noting that each conditional clause has an attribute of prepend="AND". This means that if the condition is true (and thus the cluase is included) then prepend the SQL with AND. If you've done dynamically built SQL statements, you'll know that issues such as this can be hugely problematic.

But it gets better. All of these clauses are wrapped in a <dynamic> element. This tag tells Ibatis that the first true condition will have it's prepend replaced by the dynamic prepend.

The result of this is that if all three properties are null, there will be no WHERE clause at all. If you specify any combination of one, two or three of the properties, a correct SQL statement will be generated.

Testing the Code

public static void main(String args[]) {
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    PartyDAO partyDAO = (PartyDAO) ctx.getBean("partyDAO");
    FindParty find = new FindParty();
    find.setEmail("susan%");
    List parties = partyDAO.findParties(find);
    int i = 0;
    for (Party party : parties) {
        List contacts = party.getContacts();
        System.out.printf("[%d/%d] %s: %s has %d contacts%n", ++i, parties.size(), party.getType().getDescription(), party.getName(), contacts.size());
        int j = 0;
        for (Contact contact : party.getContacts()) {
            System.out.printf("  [%d/%d] %s: %s%n", ++j, contacts.size(), contact.getType().getDescription(), contact.getValue());
        }
    }
}

Try it with any combination of criteria and it will work. This simple example does of course have some serious limitations:

  • It requires Oracle wildcard syntax. You can of course wrap present this to the user anyway you like and transform it before passing it to the query (eg by appending % automatically);
  • Oracle, by default, uses case-sensitive text matching so "SUSAN%" will return nothing in the above code. This typically isn't what user's want but efficient case-insensitive searching is beyond the scope of this post;
  • This example doesn't efficiently find matches within a column that don't start at the beginning of the column due to how indexes work. Again this isn't necessarily what you want or expect as a user but again, efficient full-text searching is beyond the scope of this post.

Conditional Clauses

Ibatis has a wide range of conditions available. From the reference documentation:

Property Description
<isPropertyAvailable> Checks if a property is available (i.e is a property of the parameter bean)
<isNotPropertyAvailable> Checks if a property is unavailable (i.e not a property of the parameter bean)
<isNull> Checks if a property is null.
<isNotNull> Checks if a property is not null.
<IsEmpty> Checks to see if the value of a Collection, String or String.valueOf() property is null or empty ("" or size() < 1).
<isNotEmpty> Checks to see if the value of a Collection, String or String.valueOf() property is not null and not empty ("" or size() < 1).

Example Usage:

<isNotEmpty prepend=”AND” property=”firstName” >
FIRST_NAME=#firstName#
</isNotEmpty>
<isEqual> Checks the equality of a property and a value, or another property.
<isNotEqual> Checks the inequality of a property and a value, or another property.
<isGreaterThan> Checks if a property is greater than a value or another property.
<isGreaterEqual> Checks if a property is greater than or equal to a value or another property.
<isLessThan> Checks if a property is less than a value or another property.
<isLessEqual> Checks if a property is less than or equal to a value or another property.

Example Usage:

<isLessEqual prepend=”AND” property=”age” compareValue=”18”>
ADOLESCENT = ‘TRUE’
</isLessEqual>
<isParameterPresent> Checks to see if the parameter object is not present (null).
<isParameterNotPresent> Checks to see if the parameter object is not present (null).

Example Usage:

<isNotParameterPresent prepend=”AND”>
EMPLOYEE_TYPE = ‘DEFAULT’
</isNotParameterPresent>

Template Reuse

Firstly, it's worth pointing out that if you specify no conditions (by putting null in all the FindParty properties or by passing null as a parameter) that this works exactly like the selectAllParties query we developed previously. As such you could combine them. Let's assume for the purpose of this example that we don't wish to do this.

Ibatis allows us to create reusable SQL fragments in an easy way. Our two queries could be replaced with:

<sql id="selectParties">
    SELECT pt.id, party_type, name, title, given_names, last_name, date_of_birth, company_number, state_of_incorporation, ct.id contact_id, contact_type, details
    FROM party pt
    LEFT OUTER JOIN person ps ON pt.id = ps.id
    LEFT OUTER JOIN company cy ON pt.id = cy.id
    LEFT OUTER JOIN contact ct ON pt.id = ct.party_id
</sql>

<select id="selectAllParties" resultMap="party">
    <include refid="selectParties"/>
</select>

<select id="findParties" parameterClass="com.cforcoding.FindParty" resultMap="party">
    <include refid="selectParties"/>

    <dynamic prepend="WHERE">

        <isNotNull property="name" prepend="AND">
            pt.name LIKE #name#
        </isNotNull>

        <isNotNull property="email" prepend="AND">
            EXISTS (SELECT id FROM contact WHERE party_id = pt.id AND contact_type = 'E' AND details LIKE #email#)
        </isNotNull>

        <isNotNull property="phone" prepend="AND">
            EXISTS (SELECT id FROM contact WHERE party_id = pt.id AND contact_type IN ('P','W','M') AND details LIKE #email#)
        </isNotNull>

    </dynamic>

</select>

It couldn't be easier.

Conclusion

As you can see, I've barely scratched the surface of what's possible. The example used is necessarily simple and it might fool you into thinking it's easy to construct a prepared statement to do the same in, say, Spring JDBC or JPA. While you can do this, it'll be harder to read, more tedious to code and more error-prone. It will also quickly turn into a quagmire of complexity as your conditional logic becomes more complex.

3 comments:

Anonymous said...

I can't stand verbose xml files anymore.

Dimitris Menounos said...

Personaly, I prefer xml configuration. Maybe I have seen so much of it that now feels natural to my eyes (with namespaces and all).

Thank you for this article, very useful for me!

Anonymous said...

Thanks for the explanation of iBatis dynamic sql.

Post a Comment