BTW.. Recently wrote a method that counts size of list SelectQuery would
return.. Maybe someone will find it useful
public class CountHelper {
public static long count(DataContext context, SelectQuery query) {
return count(context, query, context.getParentDataDomain().
getDataNodes().iterator().next());
}
public static long count(DataContext context, SelectQuery query,
DataNode node) {
CountTranslator translator = new CountTranslator();
translator.setQuery(query);
translator.setAdapter(node.getAdapter());
translator.setEntityResolver(context.getEntityResolver());
Connection con = null;
PreparedStatement stmt = null;
try {
con = node.getDataSource().getConnection();
translator.setConnection(con);
stmt = translator.createStatement();
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return rs.getLong(1);
}
throw new RuntimeQuery("Count query returned no result");
}
catch (Exception e) {
throw new RuntimeQuery("Cannot count", e);
}
finally {
try {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
}
catch (Exception ex) {
throw new RuntimeQuery("Cannot close connection", ex);
}
}
}
static class CountTranslator extends SelectTranslator {
..verride
public String createSqlString() throws Exception {
String sql = super.createSqlString();
int index = sql.indexOf(" FROM ");
return "SELECT COUNT(*)" + sql.substring(index);
}
}
}
2009/3/7 Robert Zeigler <robert.zeigle..mail.com>
> Hm. Another difficulty w/ paginated queries is that it'll only work with
> SelectQuery, right? EJBQLQuery doesn't support it, for example...
> Although it seems to me like there's no reason that it /couldn't/ have
> support for it... unlike SQLTemplate, it's still entirely cayenne-generated
> SQL, right?
>
> Any objections to me adding a jira for paginated query support in
> EJBQLQuery and tackling this?
>
> Robert
>
> PS: Apologies for this discussion winding up on dev, rather than user... it
> was originally going to be a discussion about possible improvements to
> cayenne, but wound up more as a "user" question, and I neglected to change
> the destination.
>
> On Mar 7, 2009, at 3/712:26 AM , Andrey Razumovsky wrote:
>
> Hi Robert,
>>
>> What's the point of query counting the number of results if you're using
>> fetch limit & fetch offset? This way another SQL statement (Select
>> count(*))
>> would be neccesary.
>> I don't know about Tapestry, but recently I've done same thing for GWT-Ext
>> week ago. When I first open my table, the query and its result processor
>> (simple interface) are cached on server side is session using query's
>> cache
>> key. The query is paginated, and, to get some data client needs only to
>> send
>> the key, offset and limit. Total count is defined simply by getting
>> paginated query result List's size and is sent to client in header of
>> response. There are some caching issues still to care about, but generally
>> it works fine!
>> So I turned to paginated queries instead of fetch limits and offsets. Will
>> that fit your case?
>>
>> Andrey
>>
>> 2009/3/7 Robert Zeigler <robert.zeigle..mail.com>
>>
>> I've been working on the tapestry/cayenne integration. One thing that
>>> would be nice is to have automatic "conversion" from query to
>>> "GridDataSource" (the model backing tapestry's Grid component), so that
>>> you
>>> could simply define a query and pass that to the grid to have the results
>>> displayed, paginated, etc.
>>>
>>> I have a first pass of this working by simply executing the query to
>>> fetch
>>> the list of objects and allowing tapestry to convert the list into a
>>> GridDataSource. This works... for small lists. But certainly won't scale
>>> for anything large. GridDataSource provides the hooks required to select
>>> a
>>> "page" of data at a time. And queries now allow for setting fetch limits
>>> and offsets, which allows me to generically program this to handle many
>>> (most?) common use-cases. So, I'm 90% there.
>>>
>>> But there's still one thing lacking. The GridDataSource has to tell
>>> tapestry how many rows are available, total.
>>>
>>> For the many cases* (those that don't involve row aggregation), this can
>>> be
>>> accomplished via a simple count statement; ie, executing /nearly/ the
>>> same
>>> query... but selecting the count of the records rather than the actual
>>> properties.
>>>
>>> So I got to thinking that maybe there would be a way to take an existing
>>> query and "tweak" it to perform a counting version of its query.
>>> Thoughts
>>> on this approach?
>>>
>>>
>>> * This breaks down if the query is doing any kind of grouping. I'm not
>>> aware of a standard way to ask how many rows a particular query /would/
>>> in
>>> this case. Anybody? On mysql, one could set the fetch limit to 1, perform
>>> the query, and then perform a query for "found_rows()", but that's not
>>> applicable anywhere but mysql, so...
>>>
>>> Robert
>>>
>>>
>
-- Andrey
This archive was generated by hypermail 2.0.0 : Fri Sep 04 2009 - 10:42:13 EDT