hibernate-devel

[Hibernate-devel] New feature discussion (max query result list size)

[Gavin_King/Cirrus%<CI...@ci...>]

I just posted the following as a feature request. Its something I would
very much like to see just after 1.0

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

It would be very useful to allow the application to limit the size of a
query result list. The three obvious ways to do this would be:

1. provide overloaded versions of find() and iterate() that take an int
argument

2. allow a LIMIT clause in the query language (like MySQL)

3. for advanced usage of queries, provide a Query object with a
setMaxResults().

approach (1) has the disadvantage of causing an explosion of methods on the
Session interface - and a further possible explosion if we ever decide to
extend this functionality to allowing offsets, fetch sizes, etc....

approach(2) leaves slightly open the question of how to bind a dynamic
value to the LIMIT value .... the best way to do this would be with a "?"
placeholder that looks just like the JDBC in parameters. Also this approach
complexifies the query language which is somewhat undesirable.

approach (3) requires a single new prepareQuery() method of the Session
interface. However, it also requires a change in the was users think about
queries. On the _very_ positive side it allows easy and elegant extension
to setting other things like fetch sizes. Incidently, this is the standard
model used by persistence layers.

Given the preceding discussion, I would be ruling out (1) in favor of (2)
or (3) - which are not mutually exclusive, actually.

Thoughts?

Re: [Hibernate-devel] New feature discussion (max query result list size)

[<no...@ex...>]

if you're going to change the language, their are at least 2 other options :

(a) oracle uses "WHERE ROWNUM<?"

(b) informix uses "SELECT FIRST ?"

I have no idea if there is a ANSI standard for this :-)


----- Original Message -----
From: <Gavin_King/Cirrus%CI...@ci...>
To: <hib...@li...>
Sent: Wednesday, June 19, 2002 10:06 AM
Subject: [Hibernate-devel] New feature discussion (max query result list
size)


> I just posted the following as a feature request. Its something I would
> very much like to see just after 1.0
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> It would be very useful to allow the application to limit the size of a
> query result list. The three obvious ways to do this would be:
>
> 1. provide overloaded versions of find() and iterate() that take an int
> argument
>
> 2. allow a LIMIT clause in the query language (like MySQL)
>
> 3. for advanced usage of queries, provide a Query object with a
> setMaxResults().
>
> approach (1) has the disadvantage of causing an explosion of methods on
the
> Session interface - and a further possible explosion if we ever decide to
> extend this functionality to allowing offsets, fetch sizes, etc....
>
> approach(2) leaves slightly open the question of how to bind a dynamic
> value to the LIMIT value .... the best way to do this would be with a "?"
> placeholder that looks just like the JDBC in parameters. Also this
approach
> complexifies the query language which is somewhat undesirable.
>
> approach (3) requires a single new prepareQuery() method of the Session
> interface. However, it also requires a change in the was users think about
> queries. On the _very_ positive side it allows easy and elegant extension
> to setting other things like fetch sizes. Incidently, this is the standard
> model used by persistence layers.
>
> Given the preceding discussion, I would be ruling out (1) in favor of (2)
> or (3) - which are not mutually exclusive, actually.
>
> Thoughts?
>
>
> --------------------------------------------------------------------------
--
>                    Bringing you mounds of caffeinated joy
>                    >>>     http://thinkgeek.com/sf    <<<
>
> _______________________________________________
> Hibernate-devel mailing list
> Hib...@li...
> https://lists.sourceforge.net/lists/listinfo/hibernate-devel
>

RE: [Hibernate-devel] New feature discussion (max query result list size)

[Anton v. S. <an...@ap...>]

> if you're going to change the language, their are at least 2
> other options :
>
> (a) oracle uses "WHERE ROWNUM<?"
>
> (b) informix uses "SELECT FIRST ?"

FWIW, Sybase/Microsoft T-SQL uses "SET ROWCOUNT n" to limit returned rows in
subsequent queries.  (later more Microsoft-specific SQL dialects may do it
differently.)

RE: [Hibernate-devel] New feature discussion (max query result list size)

[Anton v. S. <an...@ap...>]

> > 3. for advanced usage of queries, provide a Query object with a
> > setMaxResults().

This sounds best to me.

I don't think it's a bad thing to start "breaking down" the Session
interface like this, for the reasons you say:

> On the _very_ positive side it allows easy and elegant extension
> to setting other things like fetch sizes. Incidently, this is
> the standard model used by persistence layers.

...which arises from general good design principles, e.g. small
special-purposes interfaces being generally better and more cleanly
extensible than large general-purpose interfaces.

I took a brief look at ODMG OQL to see whether that had a query-limiting
mechanism in the language, but I didn't find it(which doesn't mean it's not
there).  However, it does seem to me as though this requirement is
orthogonal to the business of producing a query, and doesn't necessarily
belong in the query language.  Putting it in the language might be nice, but
that would be icing on the cake, IMO.

BTW, I remembered that Microsoft Access uses "SELECT TOP n ...", so we're up
to 5 systems that have it in the query language.

However, Hibernate is a little different in that it assumes that its clients
are always Java code, whereas these other query languages have to deal with
being called from all sorts of clients, and can't easily expose a consistent
object API to all its possible clients.  Hibernate doesn't have that
restriction, so it's less important to have a feature like this in the query
language itself.

Anton