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 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 −
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 −
You need to have an Employee class in Employee.java to perform read operation.
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 −
This file has application level logic to read conditional records from the Employee table −
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.
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 includes powerful OGNL based expressions to avoid most of the other elements.
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 −
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 −
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 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 −
iBATIS Related Interview Questions
|XML Interview Questions||Core Java Interview Questions|
|SQL Database Interview Questions||JSP Interview Questions|
|Ruby on Rails Interview Questions||Java-Google Web Toolkit (GWT) Interview Questions|
|Hibernate Interview Questions||MVC Framework Interview Questions|
|Java collections framework Interview Questions||Data Structure & Algorithms Interview Questions|
|NHibernate Interview Questions||Spring MVC Framework Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.