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
>
>
