License     Codehaus     OpenEJB     OpenJMS     OpenORB     Tyrex     

Old releases
  General
  Release 1.3
  Release 1.3rc1
  Release 1.2

Main
  Home
  About
  Features
  Download
  Dependencies
  Reference guide
  Publications
  JavaDoc
  Maven 2 support
  Maven 2 archetypes
  DTD & Schemas
  Recent HTML changes
  News Archive
  RSS news feed
  Project Wiki

Development/Support
  Mailing Lists
  SVN/JIRA
  Contributing
  Support
  Continuous builds
  Prof. services

Related projects
  Spring ORM support
  Spring XML factories
  WS frameworks

XML
  XML

XML Code Generator
  XML Code Generator

JDO
  Introduction
  First steps
  Using JDO
  JDO Config
  Types
  JDO Mapping
  JDO FAQ
  JDO Examples
  JDO HOW-TOs
  Tips & Tricks
  Other Features
  JDO sample JAR

Tools
  Schema generator

Advanced JDO
  Caching
  OQL
  Trans. & Locks
  Design
  KeyGen
  Long Trans.
  Nested Attrs.
  Pooling Examples
  LOBs
  Best practice

DDL Generator
  Using DDL Generator
  Properties
  Ant task
  Type Mapping

More
  The Examples
  3rd Party Tools
  JDO Tests
  XML Tests
  Configuration
 
 

About
  License
  User stories
  Contributors
  Marketplace
  Status, Todo
  Changelog
  Library
  Contact
  Project Name

  



How to use a LIMIT claue with OQL


Overview
Intended Audience
Prerequisites
Steps
    Compose an OQL statement to obtain all ProductGroup instances
    Add LIMIT clause to OQL statement
    Add OFFSET clause to OQL statement
Limitations
Tips
References


Overview

Intended Audience

Anyone who wants to execute an OQL statement and limit the result size.

The example given describes the addition of LIMIT/OFFEST clauses to an existing OQL statement.

Prerequisites

You should have a valid class mapping for two Java classes Product and ProductGroup, similar to the following one:

package myapp;

public class Product 
{
    private int       _id;

    private String    _name; 

    private float     _price; 

    private ProductGroup _group;


    public int getId() { ... }

    public void setId( int anId ) { ... }

    public String getName() { ... }

    public void setName( String aName ) { ... }

    public float getPrice() { ... }

    public void setPrice( float aPrice ) { ... }

    public ProductGroup getProductGroup() { ... }

    public void setProductGroup( ProductGroup aProductGroup ) { ... }
}
               

The following fragment shows the Java class declaration for the ProductGroup class:



public class ProductGroup
{

    private int       _id;

    private String    _name;

    public int getId() { ... }

    public void setId( int id ) { ... }

    public String getName() { ... }

    public void setName( String name ) { ... }

}
                

Steps

Here is how to proceed.

Compose an OQL statement to obtain all ProductGroup instances

The following code fragment shows an OQL query to select the all ProductGroup instances.

OQLQuery query = db.getOQLQuery("select product from ProductGroup product");
query.bind(10);
OQLResults results = query.execute();
            

Add LIMIT clause to OQL statement

The following code fragment shows the same OQL query as above, to this time the LIMIT keyword is added to select the first 10 ProductGroup instances only.

OQLQuery query = db.getOQLQuery("select product from ProductGroup product LIMIT $1");
query.bind(10);
OQLResults results = query.execute();
            

Add OFFSET clause to OQL statement

Below is the same OQL query again, restricting the number of ProductGroup instances returned to 10, though this time it is specified that the ProductGroup instances 101 to 110 should be returned.

OQLQuery query = db.getOQLQuery("select product from ProductGroup as product LIMIT $1 OFFSET $2");
query.bind(10);
query.bind(100);
OQLResults results = query.execute();
            

Limitations

The following RDBMS fully/partially support LIMIT/OFFSET clauses.

RDBMSLIMITOFFSET
postgreSQLYesYes
mySQLYesYes
Oracle 1) 2)YesYes
HSQLYesYes
MS SQLYes-
DB2Yes-

1) Caster has full support for LIMIT/OFFSET clauses for Oracle Releases 8.1.6 and later.

2) For the LIMIT/OFFSET clauses to work properly the OQL query is required to include an ORDER BY clause.

Tips

-In the case a RDBMS does not support LIMIT/OFFSET clauses, a SyntaxNotSupportedException will be thrown.

References

-Castor JDO's OQL
 
   
  
   
 


Copyright © 1999-2005 ExoLab Group, Intalio Inc., and Contributors. All rights reserved.
 
Java, EJB, JDBC, JNDI, JTA, Sun, Sun Microsystems are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and in other countries. XML, XML Schema, XSLT and related standards are trademarks or registered trademarks of MIT, INRIA, Keio or others, and a product of the World Wide Web Consortium. All other product names mentioned herein are trademarks of their respective owners.