cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrey Razumovsky <>
Subject Re: thoughts on "count queries"
Date Fri, 04 Sep 2009 14:41:13 GMT
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().

    public static long count(DataContext context, SelectQuery query,
DataNode node) {
        CountTranslator translator = new CountTranslator();


        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = node.getDataSource().getConnection();

            stmt = translator.createStatement();

            ResultSet rs = stmt.executeQuery();
            if ( {
                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) {
                if (con != null) {
            catch (Exception ex) {
                throw new RuntimeQuery("Cannot close connection", ex);

    static class CountTranslator extends SelectTranslator {
        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 <>

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


  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message