| I'm trying to move from pure jdbc to iBatis DAO and SQLMaps. Why iBatis? I have the experience and it is comfortable to be used in an environment where are lots of pre written database procedures and complex queries.
One of the reason why the move to iBatis was due to paging, the requirement is to show data from more than 4 million records, querying data for paging was taking a lot of time using result set. Next step was moving to scrollable result set, however managing the connection and the open result set, i'm afraid will be daunting to the developers, some of them new. We can surely destroy the connection once the session expires but how about the result sets we may need to open when users access other pages for which data is to be queried in millions of row table?
Ibatis to the rescue, with the PaginatedList interface and PaginatedDataList as the implementation. Using iBatis is very simple. Below is a sample to do dynamic queries with poperty name and value substitution.<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap>
<!-- Data is cached at server depending on the size -->
<cacheModel id="domain1Cache" type="LRU">
<flushInterval hours="1"/>
<property name="size" value="100" />
</cacheModel>
<!-- aliasing, -->
<typeAlias alias="domain1VO" type="com.company.domain1.domain1VO" />
<!--
Result mapping from sql to java object of domain1VO which is com.company.domain1.domain1VO.
If there is more that one column with the same name, use sql aliasing.
-->
<resultMap id="domain1Result" class="domain1VO">
<result property="domain1No" column="FIELD1"/>
<result property="referenceNo" column="FIELD2"/>
<result property="status" column="FIELD3"/>
...
</resultMap>
<!--
Each select has a unique id, the data to be used for criteria, empty if there isn't any. If
multiple string criteria, put them in a map use the parameterMap keyword instead of parameterClass
-->
<select id="finddomain1" parameterClass="com.company.domain1.domain1SearchVO"
resultMap="domain1Result" cacheModel="domain1Cache">
<!-- We are using CDATA for the following sql fragment because of the 'STATUS <> 'N'' notice
the less than, greater than symbols which are xml markers.
-->
<![CDATA[
SELECT FIELD1, FIELD2, FIELD3
FROM TABLE WHERE ( FIELD4 <> 'N' ) AND #code# IN (CODE) AND ID = #companyId#
]]>
<!--
#Code# <- substitues domain1SearchVO.Code value
-->
<!--
Below does dynamic query, depending if the property has data, create query reflecting the data.
isNotEmpty is used to substitute
<code>
if ((search.getValue() != null) && (search.getValue().length() > 0)) {
getdomain1s.append(" AND " + search.getCriteria());
getdomain1s.append(" = '");
getdomain1s.append(search.getValue());
getdomain1s.append("'");
}
</code>
$criteria$ <- this is for substituting property name. Note $ instead of # which is for property value
-->
<isNotEmpty prepend="AND" property="value" >
$criteria$ = #value#
</isNotEmpty>
<isNotEmpty prepend="AND" property="status" >
STATUS = #status#
</isNotEmpty>
ORDER BY ID
</select>
</sqlMap>
|
