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

RDBMS | LIMIT | OFFSET
--- | --- | ---
postgreSQL | Yes | Yes
mySQL | Yes | Yes
Oracle 1) 2) | Yes | Yes
HSQL | Yes | Yes
MS SQL | Yes | -
DB2 | Yes | -

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

<notice> Copyright © 1999-2005 ExoLab Group, Intalio Inc., and Contributors. All rights reserved. </notice>
<notice> 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. </notice>