Skip to main content

Write iBatis dynamic query that returns results only when atleast one parameter is sent

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 parameter does not contain either id or managerId, we are going to return all Person objects and a java queryForObject will throw a multiple objects found exception. How do we avoid this? One solution is to add a or condition with a ubiquitous false expression

something like this
<select id="getPerson"  resultClass="Person" parameterClass="java.util.map">
select * from PERSON where 1=0 
<dynamic prepend="or" >
<isNotNull parameter="id">
 ID = #id#
</isNotNull>
<isNotNull parameter="managerId" prepend="and" >
 EMAIL_ID = #emailId#
</isNotNull>
</dynamic>
</select>
if both parameters are null, the statement will never return any result-sets so we are good. And notice that if BOTH filters are given, it would AND and still return only one result.

Popular posts from this blog

Add jquery in Chrome console

Many a time, a page you are debugging doesnot have jquery. This simple js will add(or prompt you to overwrite) jquery to any page from chrome console.

javascript:if(!window.jQuery||confirm('Overwrite\x20current\x20version?\x20v'+jQuery.fn.jquery))(function(d,s){s=d.createElement('script');s.src='https://ajax.googleapis.com/ajax/libs/jquery/1.8/jquery.js';(d.head||d.documentElement).appendChild(s)})(document);

Javascript: Convert Strings to Binary (and representing in a nerdy way!)

I follow those GoogleDevelopers Videos. Sometime back, in one of the presentations on GoogleIO, there was this interesting string of dots at the bottom of each page of the presentation. They looked like random big and small dots. A similar bunch of dots were also on the T-shirt of a presenter was wearing in another presentation. While it seemed something in the pattern, I could not find what it was. Finally, another presenter cleared the matter that those dots are just binary representation of "GOOGLEIO" (So much for advertizing Google IO, Impressive!).

So I wanna do it. Takes me back to days of those DSP classes at school. Nerdy me had to churn some old brain cells. I remember those first programming language classes in Pascal and C when you were asked to do fibonacci series and converting a binary string to ascii codes. That *experience* came handy here: Check it out!

Text to Binarize:
For those who came to copy the javascript code to convert string to binary, Here it is…

The easiest way to install docker on Ubuntu

Docker releases come out faster than ubuntu updating thier repositories. Docker mainitains a ppa. One can add the ppa key, and then add to sources.list.d and then update sources and then install lxc-docker. OR..
curl -sSL https://get.docker.com/ubuntu/ | sudo sh