Hello Andrus,
Thanks for the reply. I'm looking forward to 1.2 ! I'll submit a feature
request in a moment.
To make LIMIT possible before 1.2 i've managed to make a quick hack. I
first found myself hacking the source tree and changing the orginal
sources. After a while i thought "this isn't c" and "i don't want to do
this everytime a new version is released" :-)
I've managed to write my own Adapter which cayenne now use. It extends the
PostgresAdapter (postgresql is my db) to override queryTranslatorClass(),
SelectQuery to add setLimit() and getLimit(), SelectTranslator to override
createSqlString() and add LIMIT statement to the end of the sql.
I found it relative easy to "add something" to cayenne. The hardest thing
was finding the place where the query was constructed and getting the idea
of writing a adapter.
As reference i've pasted the source code for this hack, maybe it's usefull
for someone.
LimitPostgresAdapter.java
===============================
package x;
import org.objectstyle.cayenne.dba.postgres.PostgresAdapter;
import org.objectstyle.cayenne.query.Query;
public class LimitPostgresAdapter extends PostgresAdapter {
protected Class queryTranslatorClass(Query q) {
if(q instanceof LimitSelectQuery){
return LimitSelectTranslator.class;
}
else{
return super.queryTranslatorClass(q);
}
}
}
===============================
LimitSelectTranslator.java
===============================
package x;
import org.objectstyle.cayenne.access.trans.SelectTranslator;
import org.objectstyle.cayenne.query.SelectQuery;
public class LimitSelectTranslator extends SelectTranslator {
public String createSqlString() throws Exception {
String s=super.createSqlString();
SelectQuery q=(SelectQuery) getQuery();
if(q instanceof LimitSelectQuery){
LimitSelectQuery lsq=(LimitSelectQuery)q;
int limit = lsq.getLimit();
if (limit != -1) {
s+=" LIMIT " + limit + " ";
}
}
return s;
}
}
===============================
LimitSelectQuery.java
===============================
package x;
import org.objectstyle.cayenne.query.SelectQuery;
import org.objectstyle.cayenne.map.ObjEntity;
import org.objectstyle.cayenne.exp.Expression;
public class LimitSelectQuery
extends SelectQuery {
protected int limit = -1;
public LimitSelectQuery(Class rootClass, Expression qualifier) {
super(rootClass, qualifier);
}
public void setLimit(int limit) {
this.limit = limit;
}
public int getLimit() {
return this.limit;
}
}
===============================
Cheers,
Twan Kogels
At 15:23 01-9-2004, you wrote:
>Agree, doing this at the database level should be a good optimization. As
>different DBs use a different syntax for the row limit, this is a fairly
>big change, so we should postpone this till 1.2 (please open a feature
>request!)
>
>On the other hand I want to try java.sql.Statement.setMaxRows(int).
>Cayenne currently doesn't rely on this API, and I hope at least some
>drivers implement "maxRows" support to smartly optimize the query. I don't
>have too much hope here though, looks like we still will need to generate
>correct SELECT.
>
>Andrus
>
>
>On Sep 1, 2004, at 6:28 AM, Twan Kogels wrote:
>>Hello,
>>
>>Is it possible to add a "LIMIT" statement to the end of a query which
>>SelectQuery executes? (like: select * from users where age < 25 LIMIT 50 )
>>
>>I'm currently testing my application and have a table with 20.000
>>records. I'm doing a query on this table and i sometimes get over 10.000
>>records. That's a bit too large. So i tryed setFetchLimit(50), but after
>>reading the documentation setFetchLimit doesn't limit the size of the
>>resultset, it only limits how many rows in the resultset cayenne will
>>convert. This means i'm still stuck with a resultset of 10.000 rows and
>>cayenne is only reading 50 of them :-)
>>
>>The difference between a query which results in a resultset of 10.000 or
>>a query which results in a resultset of 50 is about 2,5 seconds.
>>
>>Cheers,
>>Twan Kogels
>
This archive was generated by hypermail 2.0.0 : Thu Sep 02 2004 - 03:24:39 EDT