Limiting the Size of Returned Data

This topic explains how to limit the size of data sets returned from the database.

You can set the maximum number of records returned by the database at design time or at runtime.

Setting Max Records at Design Time

To set the maximum records at design time, use the @jc:sql max-rows attribute. The following method returns the first four records that match the query SELECT * FROM ITEMS.

    /**
     * @jc:sql max-rows="4" statement="SELECT * FROM ITEMS"
     */
    public Item[] allItemsArray();

Note that the max-rows attribute is a design time device only that you cannot set dynamically at runtime. The following section for information on limiting the size of returned data dynamically.

Setting Max Records at Run Time

To set the maximum number of rows dynamically at runtime, use the syntax provided by the database vendor. The following table shows the different syntax used by different database vendors. Note that you can use the substitution syntax {sql: ...} to limit the number of rows dynamically.

Vendor Method for Limiting Data Sets Sample Query Sample Dynamic Database Control Method
DB2 Use a FETCH FIRST N clause. SELECT * FROM ITEMS FETCH FIRST 4 ROWS ONLY
 
    /**
     * @jc:sql statement="SELECT * FROM ITEMS FETCH FIRST {sql: n} ROWS ONLY"
     */
    public Item[] getNItems_DB2Syntax(int n);
Oracle Use a nested query SELECT * FROM
   (SELECT * FROM ITEMS     ORDER BY ITEMNUMBER     DESC)
WHERE ROWNUM <= 4 
    /**
     * @jc:sql statement::
     * SELECT * FROM 
     *   (SELECT * FROM ITEMS ORDER BY ITEMNUMBER DESC) 
     * WHERE ROWNUM <= {sql: n} 
     * ::
     */
    public Items[] getNItems_OracleSyntax(int n);
MS SQL Server Use a TOP N clause SELECT TOP 4 * FROM ITEMS
 
    /**
     * @jc:sql statement="SELECT TOP {sql: n} * FROM ITEMS"
     */
    public Item[] getNItems_SQLServerSyntax(int n);

Related Topics

Parameter Substitution in @jc:sql Statements

@jc:sql Annotation