Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Received-SPF: pass (hermes.apache.org: domain of clinton.begin@gmail.com
 designates 64.233.184.195 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
        b=Iu3NjRckHuhX8D1dOnEuovb9K8Sby9PFmAyrByGNc+PwZrhu2trirTrWkzUCSmwJEdZxOgl57p4R2Yl5v6qX2CvRiXJNHzNazrHB48OKaHhqDRcf1noeutBhycwDgS5mS49XzbnJHHtZTnaYLhyU7Fiz39irhYoyPS2n6nEAv8g=
Message-ID: <16178eb1041220072923bbf202@mail.gmail.com>
Date: Mon, 20 Dec 2004 08:29:38 -0700
From: Clinton Begin <clinton.begin@gmail.com>
Reply-To: cbegin@ibatis.com
To: ibatis-user-java@incubator.apache.org
Subject: Re: R: How to reuse a query?
Cc: Brandon Goodin <brandon.goodin@gmail.com>
In-Reply-To: 
 <JBEEJACGOCCJLNHJPLOCOEJMCJAA.fabrizio.gianneschi@gruppoatlantis.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: <2fe5ef5b04121712205cc13e6a@mail.gmail.com>
	 <JBEEJACGOCCJLNHJPLOCOEJMCJAA.fabrizio.gianneschi@gruppoatlantis.com>

You really should be doing two queries.  Write a count statement and a
query statement.  Execute one after the other.

For a high-performance system, you shouldn't be using PaginatedList at
all.  You should only store the count, the starting index and the
ending index.  Then use queryForList (String, Object, int, int) to
query each time.

SQL statement inclusion is coming in the next release to help with the
duplicated SQL and where clause.

Cheers,
Clinton


On Mon, 20 Dec 2004 16:06:51 +0100, Fabrizio Gianneschi
<fabrizio.gianneschi@gruppoatlantis.com> wrote:
> Regarding the SQL COUNT problem, I think the following could be an
> enhancement for a new iBATIS release...
> 
> -create a new queryForPaginatedList method that receive the id of the COUNT
> query ("countStatementName"), in addition to the usual parameters:
> 
> public PaginatedList queryForPaginatedList(String statementName, String
> countStatementName, <---
>        Object parameterObject, int pageSize)
>        throws SQLException
> 
> (the countStatement will be executed only the first time, of course)
> 
> -Expose a new property (e.g.: "maximumSize") in the PaginatedList interface,
> so pager tags (in the JSPs) could know how many objects they've to expect at
> maximum.
> 
> I'd like to know your opinion... actually we're forced to do workarounds to
> solve the PaginatedList count problem.
> 
> Regards,
> Fabrizio Gianneschi
> 
> -----Messaggio originale-----
> Da: Brandon Goodin [mailto:brandon.goodin@gmail.com]
> Inviato: venerdi 17 dicembre 2004 21.21
> A: Fabrizio Gianneschi
> Cc: iBATIS mailing list
> Oggetto: Re: How to reuse a query?
> 
> We have had discussion of creating sql "fragments" for reuse of common
> static and dynamic sql routines. However, the option is not available
> currently in IBatis. I'm afraid redundance is the only option at this
> point.
> 
> If you really wanted to get nuts you could use a properties file and
> place sql fragments in. Then you coudl use the ${property.key.here}
> notation in your sql maps where you want the sql statement to be used.
> 
> Another option (available in the next release) would be to place a
> dynamic tag around your SELECT columns so that in one dynamic element
> it is 'COUNT(1)' and the other is '*'. You cannot do this currently
> because of the way ibatis caches the property mappings for mapped
> statement. However, in the next release you will have the option to
> turn off the property mapping caching for a mapped statement. The
> price you pay here will be performance. I do not think this would be a
> good option if performance is important.
> 
> Brandon
> 
> On Fri, 17 Dec 2004 16:42:16 +0100, Fabrizio Gianneschi
> <fabrizio.gianneschi@gruppoatlantis.com> wrote:
> > 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
> >
> >
> 
>