Sarath Chandra Pandurangi's Blog

In iBatis, a parameter is escaped, autoquoted and replaced automatically. So a #stringParam# will be auto autoquoted and then replaced. Its also escaped, meaning symbols like ' and % are escaped. This causes a problem when you have to do wildcard searches. With a like operator. Say, You have to look up employees by first name. The SQL would look like select * from emp where first_name = 'sarath' and the iBatis Query would be simply <select id="getEmpByFName"> select * from emp where first_name = #value# </select> On the same lines, A wildcard search of firstname would be (in SQL) select * from emp where first_name like '%sar%' But, if you just make an iBatis select like: <select id="getEmpByFName"> select * from emp where first_name like #value# </select> and make it concatinate with "%" before passing it to queryForList(), You will see nothing in results. This is because iBatis will escape % too. ...

iBatis allows building dynamic query conditionally based on properties sent to the named SQL. Suppose We have a Person implementation where the search page allows search by id or by email id (assuming both are unique). We could write two queries <select id="getPersonById" resultClass="Person" > select * from PERSON where ID = #value# </select> <select id="getPersonByManagerId" resultClass="Person" > select * from PERSON where EMAIL_ID = #value# </select> OR we could make a dynamic Query like the following <select id="getPerson" resultClass="Person" parameterClass="java.util.map"> select * from PERSON <dynamic prepend="where" > <isNotNull parameter="id"> ID = #id# </isNotNull> <isNotNull parameter="managerId"> EMAIL_ID = #emailId# </isNotNull> </dynamic> </select> However there is one pitfall. If the paramete...