How-To Query for Entities

Version: 05/04/07

Introduction
Summary

Introduction

This example demonstrates the support for JPA queries in Oracle TopLink. Queries are created in meta-data and executed in runtime.

Using EntityManager.find

The simplest way to query for a JPA entity is to use the EntityManager method find. This method takes a class and a primary key:


Employee employee = (Employee)em.find(Employee.class, "1234");

Dynamic JP QL Queries

Queries are acquired through API on the EntityManager. Queries can be created directly using JPQL


Query queryEmployees = 
	em.createQuery("SELECT e FROM Employee e");
Query queryRenameCity = 
	em.createQuery("UPDATE Address a SET a.city = 'Ottawa' WHERE a.city = 'Nepean'"

Named Queries

Most often queries are predefined and stored on the Entity Manager. This allows for queries to be defined ahead of time, recalled and used at runtime. This is done by storing the query on the entity bean using annotations.



@Entity
…
@NamedQuery(
	name="findAllEmployeesByFirstName",
	query="SELECT e FROM Employee e WHERE e.firstName = 'John'"
)
public class Employee implements Serializable

The query is then accessed and executed by creating a query with the query name used to store it



Query queryEmployeesByFirstName = em.createNamedQuery("findAllEmployeesByFirstName");
Collection employees = queryEmployessByFirstName.getResultList();

Creating a query with parameters

Defining parameters on your query allows for the reuse of a query to return different results. Parameters (prefixed in JPQL strings by semicolons) require values. If query uses parameters (prefixed in JPQL strings by semicolons) then values for them passed through setParameter method. Changing the example above to use parameters would look like:



@Entity
…
@NamedQuery(
	name="findAllEmployeesByFirstName",
	query="SELECT e FROM Employee e WHERE e.firstName = :firstname"
)
public class Employee implements Serializable {
	…
...
...
Query queryEmployeesByFirstName = em.createNamedQuery("findAllEmployeesByFirstName");
queryEmployeeByFirstName.setParameter("firstName", "John");
Collection employees = queryEmployessByFirstName.getResultList();

Query Hints

Setting a 'hint' on the query can alter implementation specific query behaviour. This allows for queries to be setup with vendor specific configuarations that are not available in the JPA specification.

The following OC4J-specific hint forces refreshing of the objects already read.



...
...
Query queryEmployeesByFirstName = em.createNamedQuery("findAllEmployeesByFirstName");
queryEmployeesByFirstName.setHint("refresh", new Boolean(true));

You can use the following TopLink JPA hints (for more details on these settings please refer to the TopLink documentation)

fetchSize Takes an Integer. Allows the user to set the fetchSize of a TopLink query.
referenceClass Takes a class. Override the target class of the query.
cacheUsage Takes an Integer. Describes how TopLink makes use of the cache for querying objects.
refresh Takes a Boolean. Set to true if the cache should be refreshed from the database.
lockMode Takes an Integer. Set for Pessimistic Locking.
expression Takes a TopLink Expression object. Used for querying using TopLink API.
timeout Takes an Integer. Sets the the query timeout in milseconds.

Query Selects

The API for returning a single result is:



...
Object obj = query.getSingleResult();
...

The API for returning multiple results is:



...
Collection employees = queryEmployees.getResultList();
...

Query Updates

The API for executing an update query is:



...
Query queryRenameCity = 
	em.createQuery("UPDATE Address a SET a.city = 'Ottawa' WHERE a.city = 'Nepean'");
int rowCount = queryRenameCity.executeUpdate();

Summary

In this document, you have learned how to use TopLink JPA to query for entities.