RE: Limiting a number of rows returned

[Scott Howlett]

I implemented this functionality for PostgreSQL.

I subclassed SqlGeneratorDefaultImpl to provide the functionality and
then pointed to my subclass in OJB.properties.

My OJB.properties entry is:

SqlGeneratorClass=PostgreSqlStatementGenerator


My subclass code is:

import org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl;
import org.apache.ojb.broker.metadata.ClassDescriptor;
import org.apache.ojb.broker.platforms.Platform;
import org.apache.ojb.broker.query.Query;

public class PostgreSqlStatementGenerator extends
SqlGeneratorDefaultImpl {

    public SqlStatementGenerator(Platform pf) {
        super(pf);
    }

    public String getPreparedSelectStatement(
        Query query,
        ClassDescriptor cld) {
        String result = super.getPreparedSelectStatement(query, cld);
        return addOffsetLimit(query, result);
    }

    public String getSelectStatementDep(Query query, ClassDescriptor
cld) {
        String result = super.getSelectStatementDep(query, cld);
        return addOffsetLimit(query, result);
    }

    private String addOffsetLimit(Query q, String stmt) {
        int startIndex = q.getStartAtIndex();
        int endIndex = q.getEndAtIndex();
        if (endIndex > 0) {
            if (startIndex < 0 || startIndex >= endIndex) {
                startIndex = 0;
            }
            stmt += " LIMIT " + (endIndex - startIndex);
        }
        if (startIndex > 0) {
            stmt += " OFFSET " + startIndex;
        }
        return stmt;
    }

}


Hope that helps,
Scott Howlett


-----Original Message-----
From: Max Vesely [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 26, 2003 10:27 PM
To: [EMAIL PROTECTED]
Subject: Limiting a number of rows returned


Hi,
 
   Does anyone know how to limit a number of rows returned from database
using OJB?  
 
Most of the database engines implement custom keywords to limit a number
of rows for select statement (top, limit, rownum () < .....), OJB Query
interface has methods in it to set EndAt and StartAt indexes as well as
the size of resultset returned but setting them doesn't really help. The
database engine I'm using is HSQLDB.
 
Thank, you in advance.
 
Max.

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]