License     Codehaus     OpenEJB     OpenJMS     OpenORB     Tyrex     
 

Main
  Home
  About
  Features
  Download
  Maven 2 support
  API
  DTD & Schemas
  Recent changes
  RSS news feed

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

Related projects
  Spring ORM support
  Spring XML factories

XML
  Using XML
  XML Mapping
  XML FAQ
  XML HOW-TOs
  Custom Handlers
  Best practice

XML Code Generator
  Code Generator
  Properties
  Custom bindings
  Ant task
  Schema Support
  Example

JDO
  Introduction
  Using JDO
  JDO Config
  Types
  JDO Mapping
  JDO FAQ
  JDO Examples
  JDO HOW-TOs
  Other Features
  JDO sample JAR

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

More
  Presentations
  The Examples
  3rd Party Tools
  JDO Tests
  XML Tests
  Configuration
  Tips & Tricks
  Full JavaDoc
  CastorWiki
 
 

About
  License
  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.