>You really should be doing two queries. Write a count statement and a
>query statement. Execute one after the other.
I agree... but I'd like that iBATIS does it for me (and in the same
transaction, if possible). That's why I suggest a second
queryForPaginatedList/queryForList method :)
>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.
I agree, that's what we're doing now.
Is this advice due the huge memory allocated when navigating all over the
result pages? I see the PaginatedList implements the ValueListHandler
pattern and allocates page by page...
This pops in my mind a monster-parameterized-version (:p) of PaginatedList
where you could choose:
-to maintain all the pages in memory or not
-to maintain a set of pages in memory or not (like a window: the current,
the last and the next ones).
-...
-to maintain nothing in memory
>SQL statement inclusion is coming in the next release to help with the
>duplicated SQL and where clause.
Good, I'll wait...
Regards,
Fabrizio
On Mon, 20 Dec 2004 16:06:51 +0100, Fabrizio Gianneschi
<[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
> 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
> <[EMAIL PROTECTED]> 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
> >
> >
>
>
