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

Being a Vegetarian

I am a Proud Vegetarian. I don't eat Meat or Eggs. People say its hard here in US to be one. I beg to differ. The mere fact that I am hail and healthy these 4 years is a definitive proof. Apart from being bullied and trash talked by The Meat-Eaters, There is really nothing that makes this choice of mine any more than a debatable issue at a lunch or dinner. Other things aside, I am writing this blog having watched a PETA Video. Before you click on the play button, I ask you - If you are a vegetarian : Dont watch it. If you are not : Dare to watch it till the end. If you think going veg is just a fashion, think again . Even if you just want to do it for Fashion . Do it. Go Vegetarian. And Feel better asking the waiter for a Vegetarian Entrée in your next lunch.

Using Equinox CommandProvider to make OSGi console interactive.

After fiddling with the First Bundles that "Hello World"-ed upon Activation, You want to see more interactivity in OSGi. Although Using OSGi for an interactive Command Line Application would be like this one would be, well, a callable over-kill, I am going to start with an example and Expand it in later posts. So, please Welcome CommandProvider. CommandProvider is an EQUINOX specific API for extending the Console. This basic Example illustrates how to get a command from console and do something in java and also gets your feet wet on Service Registry package com.so.examples.commandconsole; import org. eclipse .osgi.framework.console .CommandInterpreter; import org.eclipse.osgi.framework.console.CommandProvider; public class DisplayMessageCommand implements CommandProvider { public void _say(CommandInterpreter ci) { ci.print("You said:" + ci.nextArgument()); } @Override public String getHelp() { return "\tsay - repeats what you say\n"; } }

How to Make a Local (Offline) Repository in Ubuntu / Debian

If you are in a place where you dont have internet (or have a bad one) You want to download .deb packages and install them offline. Each deb file is packaged as a seperate unit but may contain dependencies (recursively). apt-get automagically solves all the dependencies and installs all that are necessary. Manually install deb files one by one resolving each dependency would be tedious. A better approach is to make your own local repository. Before you actually make a repo, You need *all* deb files. You dont practically have to mirror all of the packages from the internet, but enough to resolve all dependencies. Also, You have to make sure, you are getting debs of the correct architecture of your system (i386 etc) # 1. make a dir accessible (atleast by root) sudo mkdir /var/my-local-repo # 2. copy all the deb files to this directory. # 3. make the directory as a sudo dpkg-scanpackages /var/my-local-repo /dev/null > \ /var/my-local-repo/Packages # 4. add the local repo to sour