ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Philippe Laflamme" <philippe.lafla...@mail.mcgill.ca>
Subject RE: How to reuse a query?
Date Fri, 17 Dec 2004 20:19:21 GMT
Very common problem...

Currently, here's our solution.

In the SELECT part of the statement we test for the presence of a property
called "countQuery" like so:

<select id="myQuery" resultMap="myResultMap" parameterClass="com.MyClass">
SELECT 
  <isEqual property="countQuery" compareValue="false">
    column_a AS a,    
    column_b AS B,
    NULL AS rowCount
  </isEqual>
  <isEqual property="countQuery" compareValue="true">
    NULL AS a,    
    NULL AS B,
    COUNT(*) AS rowCount
  </isEqual>
WHERE
[...]
</select>

The resultMap looks something like this:

<resultMap id="myResultMap" class="com.AClass">
  <result property="columnA" column="a"/>
  <result property="columnB" column="b"/>
  <result property="rowCount" column="rowCount"/>
</resultMap>

Works great and it allows us not to maintain two identical (and complex)
FROM and WHERE clauses. As you may have noticed, iBatis (up to 2.0.8)
requires that _all_ columns identified in a resulMap be present in the
resultSet. This is why you need to specify "NULL AS", so that the column
appears on every call to the statement.

I haven't tried it yet, but 2.0.9 (currently in CVS) allow you to have
different columns for the same statement (you could a Map as your
resultClass). Be advised that this comes with a performance hit, since
iBatis needs to analyse your resultSet on every call...

Hope this helps,
Phil

-----Original Message-----
From: Fabrizio Gianneschi [mailto:fabrizio.gianneschi@gruppoatlantis.com] 
Sent: December 17, 2004 10:42 AM
To: iBATIS mailing list
Subject: How to reuse a query?
Importance: High


Hello,

first of all, hello everybody since I'm new on this list even I'm using
iBATIS sice a couple of months.

I'm looking for a smart trick to avoid duplicating the sql statements when
using SELECT COUNT.

For paginating needs, I've to execute two separate statements, the first one
to count the length of the result set and the second one to fetch the data.
Here's an example:

<select id="myQuery" resultClass="int" parameterClass="com.MyClass">
    SELECT *
    FROM table INNER JOIN ...
...
</select>

<select id="myQueryCount" resultMap="myMap" parameterClass="com.myClass">
    SELECT COUNT(1) as c
    FROM table INNER JOIN ... //"Same" query as before.
...
</select>

I don't like to write twice the body of the query (which could be very long
and error-prone) so I'm looking for an iBATIS feature to solve the problem
(an alias, a const...)

Something like the folliwing, supposing that iBATIS substitutes the $_..._$
with the body of the referred query:

<select id="myQuery" resultClass="int" parameterClass="com.MyClass">
    SELECT *
    FROM table INNER JOIN ...
...
</select>
<select id="myNewQueryCount" resultMap="myMap" parameterClass="com.myClass">
    SELECT COUNT(1) as c
    FROM $_myQuery_$;
...
</select>

Thanks a lot
Fabrizio




Mime
View raw message