Re: thoughts on "count queries"

[Andrey Razumovsky <ra...@gmail.com>]

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 {
        @Override
        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 <ro...@gmail.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 <ro...@gmail.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