iBATIS Dynamic SQL - iBATIS

What is Dynamic SQL?

Dynamic SQL is an important and powerful feature of iBATIS. You need to change the WHERE clause as per the requirement of your parameter object's state. In this scenario , iBATIS provides a set of dynamic SQL tags in combination with mapped statements to increase the reusability and flexibility of the SQL.

Let’s apply the logic in .XML file with some additional tags. Here’s an example where the SELECT statement would work in two ways −

  • If you pass an ID, then it would return all the records corresponding to that ID.
  • Otherwise, it would return all the records where employee ID is set to NULL.

If you want to check a condition using the <isNotEmpty> tag as follows. Here a condition would be added only when a passed property is not empty.

Select an id and/or the first name of an Employee if you want a query then your SELECT statement would be as follows −

Dynamic SQL Example

Here’s an example describes how you can write a SELECT statement with dynamic SQL. Consider, we have the following EMPLOYEE table in MySQL −

Let’s assume this table has only one record as follows −

Employee POJO Class

You need to have an Employee class in Employee.java to perform read operation.

Employee.xml File

To define SQL mapping statement using iBATIS, we would add the following modified <select> tag in Employee.xml and inside this tag definition, we would define an "id" which will be used in IbatisReadDy.java for executing Dynamic SQL SELECT query on database.

The above SELECT statement would work in two ways −

  • If you pass an ID, then it returns records corresponding to that ID Otherwise, it returns all the records.

IbatisReadDy.java File

This file has application level logic to read conditional records from the Employee table −

Compilation and Run

Following steps are used to compile and run the above mentioned software. It is mandatory to set PATH and CLASSPATH appropriately before proceeding for compilation and execution.

  • Create Employee.xml as shown above.
  • Create Employee.java as shown above and compile it.
  • Create IbatisReadDy.java as shown above and compile it.
  • Execute IbatisReadDy binary to run the program.

After the execution the above code you would get the following result, and a record would be read from the EMPLOYEE table.

Try the above example by passing null as smc.queryForList("Employee.findByID", null).

iBATIS OGNL Expressions

iBATIS includes powerful OGNL based expressions to avoid most of the other elements.

  • if Statement
  • choose, when, otherwise Statement
  • where Statement
  • foreach Statement

The if Statement

Have a part of a where clause conditionally in dynamic SQL. Here’s example −

If statement provides an optional text search type of functionality otherwise if you say no title, then all active Blogs are returned. Incase if you do pass in a title, then it will search for a title with the given like condition.

Here’s how to include multiple if conditions −

The choose, when, and otherwise Statements

iBATIS provides a choose element which is similar to Java's switch statement. You can select only one case among many options.

Below mentioned example look for only by title if one is provided, then only by author if one is provided. If nothing is provided, then it returns only featured blogs −

The where Statement

If you observe the previous examples to check whether the conditions are met. You would end up with an SQL that looks like this −

If this condition fails but iBATIS has a simple solution with one simple change, everything works fine −

The where element inserts a WHERE only when the containing tags return any content. Furthermore, if that content begins with AND or OR, it knows to strip it off.

The foreach Statement

The foreach element provides you to specify a collection and declare item and index variables that can be used inside the body of the element.

You can specify opening and closing strings, and add a separator to place in between iterations. You can build an INcondition as follows −

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

iBATIS Topics