Intro to JDO Queries

1. Overview

In the previous article in this series, we showed how to persist Java objects to different data stores. For more details, please check Guide to Java Data Objects.

JDO supports different query languages to provide flexibility for the developer to use the query language he is most acquainted with.

2. JDO Query Languages

JDO supports the following query languages:

• JDOQL – a query language using Java syntax
• Typed JDOQL – following JDOQL syntax but providing an API to ease using the queries.
• SQL – used only for RDBMS.
• JPQL – provided by Datanucleus but not part of the JDO specifications.

3. Query API

3.1. Creating a Query

To create a query, we need to specify the language as well as a query String:

Query query = pm.newQuery(
  "javax.jdo.query.SQL",
  "select * from product_item where price < 10");

If we do not specify the language, it defaults to the JDOQL:

Query query = pm.newQuery(
  "SELECT FROM com.baeldung.jdo.query.ProductItem WHERE price < 10");

3.2. Creating a Named Query

We can also define the query and refer to it by its saved name.

To do so, we first create a ProductItem class:

@PersistenceCapable
public class ProductItem {

    @PrimaryKey
    @Persistent(valueStrategy = IdGeneratorStrategy.INCREMENT)
    int id;
    String name;
    String status;
    String description;
    double price;

    //standard getters, setters & constructors
}

Next, we add a class configuration to the META-INF/package.jdo file to define a query and name it:

<jdo>
    <package name="com.baeldung.jdo.query">
        <class name="ProductItem" detachable="true" table="product_item">
            <query name="PriceBelow10" language="javax.jdo.query.SQL">
            <![CDATA[SELECT * FROM PRODUCT_ITEM WHERE PRICE < 10]]>
            </query>
        </class>
    </package>
</jdo>

We defined a query named “PriceBelow10″.

We can use it in our code:

Query<ProductItem> query = pm.newNamedQuery(
  ProductItem.class, "PriceBelow10");
List<ProductItem> items = query.executeList();

3.3. Closing a Query

To save resources, we can close queries:

query.close();

Equivalently, we can close a specific result set by passing it as a parameter to the close() method:

query.close(ResultSet);

3.4. Compiling a Query

If we want to validate a query, we can call the compile() method:

query.compile();

If the query is not valid, then the method will throw a JDOException.

4. JDOQL

JDOQL is an object-based query language designed to provide the power of SQL language and preserve the Java object relationships in the application model.

JDOQL queries can be defined in a single-String form.

Before we dive deeper, let’s go over some basic concepts:

4.1. Candidate Class

The candidate class in the JDOQL has to be a persistable class. We’re using the full class name instead of the table name in the SQL language:

Query query = pm.newQuery("SELECT FROM com.baeldung.jdo.query.ProductItem");
List<ProductItem> r = query.executeList();

As we can see in the example above, the com.baeldung.jdo.query.ProductItem is the candidate class here.

4.2. Filter

A filter can be written in Java but must evaluate to a boolean value:

Query query = pm.newQuery("SELECT FROM com.baeldung.jdo.query.ProductItem");
query.setFilter("status == 'SoldOut'");
List<ProductItem> result = query.executeList();

4.3. Methods

JDOQL does not support all Java methods, but it supports various methods that we can call from the query and can be used in a wide range:

query.setFilter("this.name.startsWith('supported')");

For more details about the supported methods, please check this link.

4.4. Parameters

We can pass values to queries as parameters. We can either define the parameters explicitly or implicitly.

To define a parameter explicitly:

Query query = pm.newQuery(
  "SELECT FROM com.baeldung.jdo.query.ProductItem "
  + "WHERE price < threshold PARAMETERS double threshold");
List<ProductItem> result = (List<ProductItem>) query.execute(10);

This can also be achieved using the setParameters method:

Query query = pm.newQuery(
  "SELECT FROM com.baeldung.jdo.query.ProductItem "
  + "WHERE price < :threshold");
query.setParameters("double threshold");
List<ProductItem> result = (List<ProductItem>) query.execute(10);

We can do it implicitly by not defining a parameter type:

Query query = pm.newQuery(
  "SELECT FROM com.baeldung.jdo.query.ProductItem "
  + "WHERE price < :threshold");
List<ProductItem> result = (List<ProductItem>) query.execute(10);

5. JDOQL Typed

To use JDOQLTypedQueryAPI, we need to prepare the environment.

5.1. Maven Setup

<dependency>
    <groupId>org.datanucleus</groupId>
    <artifactId>datanucleus-jdo-query</artifactId>
    <version>6.0.1</version>
</dependency>

The latest versions of these dependencies are datanucleus-jdo-query.

5.2. Enabling Annotation Processing

For Eclipse, we can follow the steps below to enable annotated processing:

1. Go to Java Compiler and make sure the compiler compliance level is 1.8 or above
2. Go to Java Compiler → Annotation Processing and enable the project-specific settings and enable annotation processing
3. Go to Java Compiler → Annotation Processing → Factory Path, enable the project-specific settings and then add the following jars to the list: javax.jdo.jar, datanucleus-jdo-query.jar

The above preparation means that whenever we compile persistable classes, the annotation processor in the datanucleus-jdo-query.jar will generate a query class for each class annotated by @PersistenceCapable.

In our case, the processor generates a QProductItem class. The generated class has almost the same name as the persistable class, albeit prefixed with a Q.

5.3. Create JDOQL Typed Query:

JDOQLTypedQuery<ProductItem> tq = pm.newJDOQLTypedQuery(ProductItem.class);
QProductItem cand = QProductItem.candidate();
tq = tq.filter(cand.price.lt(10).and(cand.name.startsWith("pro")));
List<ProductItem> results = tq.executeList();

We can make use of the query class to access the candidate fields and use its available Java methods.

6. SQL

JDO supports the SQL language in case we are using an RDBMS.

Let’s create SQL query:

Query query = pm.newQuery("javax.jdo.query.SQL","select * from "
  + "product_item where price < ? and status = ?");
query.setClass(ProductItem.class);
query.setParameters(10,"InStock");
List<ProductItem> results = query.executeList();

We used the setClass() for the query to retrieve ProductItem objects when we executed the query. Otherwise, it retrieves an Object type.

7. JPQL

JDO DataNucleus provides the JPQL language.

Let’s Create a query using JPQL:

Query query = pm.newQuery("JPQL","select i from "
  + "com.baeldung.jdo.query.ProductItem i where i.price < 10"
  + " and i.status = 'InStock'");
List<ProductItem> results = (List<ProductItem>) query.execute();

The entity name here is com.baeldung.jdo.query.ProductItem. We cannot use the class name only. This is because JDO doesn’t have metadata to define an entity name like JPA. We have defined a ProductItem p, and after that, we can use p as an alias to refer to the ProductItem.

For more details about JPQL syntax, please check this link.

8. Conclusion

In this article, we showed the different query languages that are supported by JDO. We showed how to save named queries for reusing and explained the JDOQL concepts, and showed how to use SQL and JPQL with JDO.