Archive for the ‘ibatis’ Category

Query for Hash Map using iBatis (MyBatis)

September 27, 2012

An iBatis, oops MyBatis post. Donno why still am tend to call it iBatis only. Anyway just a small post to show how we can query data to fill a hashmap. Directly into code.

<resultMap id="hashMapResult" class="java.util.HashMap">
<result property="key" column="managerName"/>
<result property="value" column="count"/>
</resultMap>

<select id="mCount" resultMap="hashMapResult">
<![CDATA[
select managerName, count(reportees) AS count
from mgr_employee
group by managerName;
]]>
</select>

You can call this from java using the simple command below :

Map<String,Long> mCountMap = getSqlMapClientTemplate().queryForMap("mCount", "", "key", "value");

That’s it guys. I am experimenting this for having objects other than String as Values. I will update the post as soon as that is done. Happy Coding Guys πŸ™‚

Inserting / Updating NULL values in iBatis

March 1, 2012

Inserting or Updating nulls in iBatis should be done in a crewked way. One obvious fix is to put the isNotNull tag before the fields which can be null. But this will make the sqlmap file cumbersome or hard to read. For eg:, the below insert query will throw error, if you are trying to insert a null value for, say, infoId.

<insert id=”insertInformation” parameterClass=”Information”>
insert into info (Key, Name, InfoId)
values (#key#, #name#, #infoId#)
</insert>

We can put isNotNull tage like this :

<insert id=”insertInformation” parameterClass=”Information”>
insert into info (Key, Name, InfoId)
values (#key#, #name#
<isNotNull property=”infoId”>
, #infoId#
</isNotNull>
)
</insert>

Now doesn’t that became too much. So here comes the ultimate fix. You just need to put the Oracle datatype of the column separated by a colon in the statment like this :

#infoId:VARCHAR#

The complete sql tag will become like this :

<insert id=”insertInformation” parameterClass=”Information”>
insert into info (Key, Name, InfoId)
values (#key#, #name#, #infoId:VARCHAR#)
</insert>

Similarly the update statement also can be modified like this :

<update id=”updateInformation” parameterClass=”Information”>
update Info set
Key = #key#,
Name = #name#,
infoId = #infoId:VARCHAR#
where
id = #id#
</update>

Reusing Conditions in iBatis

February 15, 2010

One of the major outcome when the software industry got matured is the concept of reusage. Anything can be reused, components, methods, anything. Similarly I just came across the feature in iBatis where we can reuse the conditions in the where clause. Its very simple and reduce the size of the sqlmap file.

<select id=”selectValue”
resultMap=”ValueEntityResultGM” cacheModel=”data-cache”>
select * from VALUE_NUM
WHERE VALUE IS NOT NULL
<include refid=”idset” />
</select>

The include tag with attribute refid points to a condition saved with the name idset. This will be stored as below

<sql id=”idset-params-gm”>

<isNotNull prepend=”AND” property=”scaleid”>
scaleid = #scaleid#
</isNotNull>
<isNotEmpty prepend=”AND” property=”scaleid_list” open=”(” close=”)”>
<iterate conjunction=”OR” property=”scaleid_list”>
scaleid = #scaleid_list[]#
</iterate>
</isNotEmpty>
<isNotNull prepend=”AND” property=”ownerid”>
ownerid = #ownerid#
</isNotNull>

</sql>

The similar way this condition can be used in any number of queries.

When I keep on exploring iBatis, this is becoming more and more fun. Enjoy Coding. πŸ™‚

IN operator in iBatis

March 12, 2009

Another confusion will occur while working with iBatis , when we need to do something like

where id in (100,101,102)

We can simply do it as like this.

where r.status = ‘A’
<dynamic >
<isNotNull property=”Clients”>
and c.id
<iterate property=”Clients” open=”IN (” close=”)” conjunction=”,”>
#Clients[]#
</iterate>
</isNotNull>
<isNotNull property=”SMT”>
and t.smt_id
<iterate property=”SMT” open=”IN (” close=”)” conjunction=”,”>
#SMT[]#
</iterate>
</isNotNull>
</dynamic>

where SMT and Clients are lists, which can be any lists,Β  that I put into the parameter map(here am passing java.util.HashMap as parameter map).

Logging in Ibatis

March 12, 2009

It will be too useful if you enable loggin in iBatis. Developers can use it extensively for debugging. Just add these lines at log4j.xml and Bingo!!! you will start getting logs for each ibatis and sql threads inΒ  your system.

<logger name=”com.ibatis” additivity=”false”>
<level value=”debug”/>
<appender-ref ref=”PIPELINE”/>
</logger>

<logger name=”java.sql” additivity=”false”>
<level value=”debug”/>
<appender-ref ref=”PIPELINE”/>
</logger>

<logger name=”org.springframework” additivity=”false”>
<level value=”info”/>
<appender-ref ref=”PIPELINE”/>
</logger>

where PIPELINE is the appender name I declared in the log4j.xml.

Tail Note : Don’t forget to remove iBatis logging when your system moves to production. The log file size will become more than 10 MB in 10 minutes of usage. πŸ™‚

Like operator in iBatis

December 18, 2008

Its lots of confusion how to use the wildcard search in ibatis. Two ways are there.

1. Select * from risk_mst where name like #value#

here we can pass the parameter with % appended to it at java side itself. Or the alternate approach is

2. Select * from risk_mst where name like ‘%$value$%’

where value is the passed parameter


%d bloggers like this: