Java Persistence/Querying
From Wikibooks, the open-content textbooks collection
Contents |
[edit] Querying
Querying is a fundamental part of persistence. Being able to persist something is not very useful without being able to query it back. There are many querying languages and frameworks; the most common query language is SQL used in relational databases. JPA uses the Java Persistence Querying Language (JPQL), which is based on the SQL language and evolved from the EJB Query Language (EJBQL). It basically provides the SQL syntax at the object level instead of at the data level.
Other querying languages and frameworks include:
- SQL
- EJBQL
- JDOQL
- Query By Example (QBE)
- TopLink Expressions
- Hibernate Criteria
- Object Query Language (OQL)
JPQL is similar in syntax to SQL and can be defined through its BNF definition.
JPA provides querying through the Query interface, and the @NamedQuery and @NamedNativeQuery annotations and the <named-query> and <named-native-query> XML elements.
JPA provides several querying mechanisms:
[edit] Named Queries
There are two main types of queries in JPA, named queries and dynamic queries. A named query is used for a static query that will be used many times in the application. The advantage of a named query is that it can be defined once, in one place, and reused in the application. Most JPA providers also pre-parse/compile named queries, so they are more optimized than dynamic queries which typically must be parsed/compiled every time they are executed. Since named queries are part of the persistence meta-data they can also be optimized or overridden in the orm.xml without changing the application code.
Named queries are defined through the @NamedQuery and @NamedQueries annotations, or <named-query> XML element. Named queries are accessed through the EntityManager.createNamedQuery API, and executed through the Query interface.
Named queries can be defined on any annotated class, but are typically defined on the Entity that they query for. The name of the named query must be unique for the entire persistence unit, they name is not local to the Entity. In the orm.xml named queries can be defined either on the <entity-mappings> or on any <entity>.
Named queries are typically parametrized, so they can be executed with different parameter values. Parameters are defined in JPQL using the :<name> syntax for named parameters, or the ? syntax for positional parameters.
A collection of query hints can also be provided to a named query. Query hints can be used to optimize or to provide special configuration to a query. Query hints are specific to the JPA provider. Query hints are defined through the @QueryHint annotation or query-hint XML element.
[edit] Example named query annotation
@NamedQuery( name="findAllEmployeesInCity", query="Select emp from Employee emp where emp.address.city = :city" hints={@QueryHint(name="acme.jpa.batch", value="emp.address")} ) public class Employee { ... }
[edit] Example named query XML
<entity-mappings> <entity name="Employee" class="org.acme.Employee" access="FIELD"> <named-query name="findAllEmployeesInCity"> <query>Select emp from Employee emp where emp.address.city = :city</query> <hint name="acme.jpa.batch" value="emp.address"/> </named-query> <attributes> <id name="id"/> </attributes> </entity> </entity-mappings>
[edit] Example named query execution
EntityManager em = getEntityManager(); Query query = em.createNamedQuery("findAllEmployeesInCity"); query.setParameter("city", "Ottawa"); List<Employee> employees = query.getResultList(); ...
[edit] Dynamic Queries
Dynamic queries are normally used when the query depends on the context. For example, depending on which items in the query form were filled in, the query may have different parameters. Dynamic queries are also useful for uncommon queries, or prototyping. Because JPQL is a string based language, dynamic queries using JPQL typically involve string concatenation. Some JPA providers provide more dynamic query languages, and in JPA 2.0 a Criteria API will be provided to make dynamic queries easier.
Dynamic queries can use parameters, and query hints the same as named queries.
Dynamic queries are accessed through the EntityManager.createQuery API, and executed through the Query interface.
[edit] Example dynamic query execution
EntityManager em = getEntityManager(); Query query = em.createQuery("Select emp from Employee emp where emp.address.city = :city"); query.setParameter("city", "Ottawa"); query.setHint("acme.jpa.batch", "emp.address"); List<Employee> employees = query.getResultList(); ...
[edit] Criteria API (JPA 2.0)
JPA 2.0 defines a query criteria API in an attempt to simplify dynamic query creation. With JPQL dynamic queries would require performing string concatenations to build queries dynamically from web forms or dynamic content. JPQL is also not checked until runtime, making typos more common. The criteria API using a set of Java interfaces to allow queries to be dynamically constructed, and reduces runtime typos with compile time checking.
Criteria queries are accessed through the EntityManager.getCriteriaBuilder() API, and executed through the normal Query API.
Criteria queries can use parameters, and query hints the same as named queries.
[edit] Example dynamic query execution
EntityManager em = getEntityManager(); CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaQuery<Employee> query = qb.createQuery(Employee.class); Root<Employee> employee = query.from(Employee.class); query.where(qb.equal(employee.get("firstName"), "Bob")); List<Employee> result = em.createQuery(query).getResultList(); ...
[edit] Parameters
Parameters are defined in JPQL using the :<param> syntax, i.e. "Select e from Employee e where e.id = :id". The parameter values are set on the Query using the Query.setParameter API.
Parameters can also be defined using the ?, mainly for native SQL queries. You can also use ?<int>. These are ordered parameters, not named parameters and are set using the Query API Query.setParameter. The int is the index of the parameter in the SQL. Some JPA providers also allow the :<param> syntax for native queries.
For temporal parameters (Date, Calendar) you can also pass the temporal type, depending on if you want the Date, Time or Timestamp from the value.
Parameters are normally basic values, but you can also reference object's if comparing on their Id, i.e. "Select e from Employee e where e.address = :address", can take the Address object as a parameter. The parameter values are always at the object level when comparing to a mapped attribute, for example if comparing a mapped enum the enum value is used, not the database value.
[edit] Query Results
[edit] Common Queries
[edit] Inverse ManyToMany, all employees for a given project
To query all employees for a given project where the employee project relationship is a ManyToMany.
If the relationship is bi-directional you could use:
SELECT project.employees FROM Project project WHERE project.name = :name
If it is uni-directional you could use:
SELECT employee FROM Employee employee, Project project WHERE project.name = :name AND project member of employee.projects
or,
SELECT employee FROM Employee employee JOIN employee.projects project WHERE project.name = :name
[edit] How to simulate casting to a subclass
To query all employees who have a large project with a budget greater than 1,00,000 where the employee only has a relationship to Project, not to the LargeProject subclass. JPA 1.0 JPQL does not define a cast operation (JPA 2.0 may define this), so querying on an attribute of a subclass is not obvious. This can be done indirectly however, if you add a secondary join to the subclass to the query.
SELECT employee FROM Employee employee JOIN employee.projects project, LargeProject lproject WHERE project = lproject AND lproject.budget > 1000000
[edit] Advanced
[edit] Update and Delete Queries
[edit] Flush Mode
Within a transaction context in JPA, changes made to the managed objects are normally not flushed (written) to the database until commit. So if a query were executed against the database directly, it would not see the changes made within the transaction, as these changes are only made in memory within the Java. This can cause issues if new objects have been persisted, or objects have been removed or changed, as the application may expect the query to return these results. Because of this JPA requires that the JPA provider performs a flush of all changes to the database before any query operation. This however can cause issues if the application is not expecting that a flush as a side effect of a query operation. If the application changes are not yet in a state to be flushed, a flush may not be desired. Flushing also can be expensive and causes the database transaction, and database locks are other resources to be held for the duration of the transaction, which can effect performance and concurrency.
JPA allows the flush mode for a query to be configured using the FlushModeType enum and the Query.setFlushMode() API. The flush mode is either AUTO the default which means flush before every query execution, or COMMIT which means only flush on commit. The flush mode can also be set on an EntityManager using the EntityManager.setFlushMode() API, to affect all queries executed with the EntityManager. The EntityManager.flush() API can be called directly on the EntityManager anytime that a flush is desired.
Some JPA providers also let the flush mode be configured through persistence unit properties, or offer alternatives to flushing, such as performing the query against the in memory objects.
[edit] Pagination, Max/First Results
A common requirement is to allow the user to page through a large query result. Typically a web user is given the first page of n results after a query execution, and can click next to go to the next page, or previous to go back.
If you are not concerned about performance, or the results are not too big, the easiest way to implement this is to query all of the results, then access the sub-list from the result list to populate your page. However, you will then have to re-query the entire results on every page request.
One simple solution is to store the query results in a stateful SessionBean or an http session. This means the initial query make take a while, but paging will be fast. Some JPA providers also support the caching of query results, so you can cache the results in your JPA providers cache and just re-execute the query to obtain the cached results.
If the query result is quite large, then another solution may be required. JPA provides the Query API setFirstResult, setMaxResults to allow paging through a large query result. The maxResults can also be used as a safeguard to avoid letting users execute queries that return too many objects.
How these query properties are implemented depends on the JPA provider and database. JDBC allows the maxResults to be set, and most JDBC drivers support this, so it will normally work for most JPA providers and most databases. Support for firstResult can be less guaranteed to be efficient, as it normally requires database specific SQL. There is no standard SQL for pagination, so whether if this is supported depends on your database, and your JPA providers support.
When performing pagination, it is also important to order the result. If the query does not order the result, then each subsequent query could potentially return the results in a different order, and give a different page. Also if rows are insert/deleted in between the queries, the results can be slightly different.
[edit] Example using firstResult, maxResults
Query query = em.createQuery("Select e from Employee e order by e.id"); query.setFirstResult(100); query.setMaxResults(200); List<Employee> page = query.getResultList();
An alternative to using firstResult is to filter the first result in the where clause based on the order by and the value from the previous page.
[edit] Example using maxResults and order by
Query query = em.createQuery("Select e from Employee e where e.id > :lastId order by e.id"); query.setParameter("lastId", prevousPage.get(prevousPage.size()-1).getId()); query.setMaxResults(100); List<Employee> nextPage = query.getResultList();
Another alternative is to only query the Ids, and store this result in a stateful SessionBean or an http session. Then query for the set of Ids for each page.
[edit] Example using Ids and IN
Query query = em.createQuery("Select e.id from Employee e"); List<Long> ids= query.getResultList(); Query pageQuery = em.createQuery("Select e from Employee e where e.id in :ids"); pageQuery.setParameter("ids", ids.subList(100, 200)); List<Employee> page = pageQuery.getResultList();
Pagination can also be used for server processes, or batch jobs. On the server, it is normally used to avoid using too much memory upfront, and allow processing each batch one at a time. Any of these techniques can be used, also some JPA providers support returning a database cursor for the query results that allows scrolling through the results.
-
- TopLink / EclipseLink : Support streams and scrollable cursors through the query hints
"eclipselink.cursor.scrollable"and"eclipselink.cursor", andCursoredStreamandScrollableCursorclasses.
- TopLink / EclipseLink : Support streams and scrollable cursors through the query hints
[edit] Native SQL Queries
Typically queries in JPA are defined through JPQL. JPQL allows the queries to be defined in terms of the object model, instead of the data model. Since developers are programming in Java using the object model, this is normally more intuitive. This also allows for data abstraction and database schema and database platform independence. JPQL supports much of the SQL syntax, but some aspects of SQL, or specific database extensions or functions may not be possible through JPQL, so native SQL queries are sometimes required. Also some developers have more experience with SQL than JPQL, so may prefer to use SQL queries. Native queries can also be used for calling some types of stored procedures or executing DML or DDL operations.
Native queries are defined through the @NamedNativeQuery and @NamedNativeQueries annotations, or <named-native-query> XML element. Native queries can also be defined dynamically using the EntityManager.createNativeQuery() API.
A native query can be for a query for instances of a class, a query for raw data, an update or DML or DDL operation, or a query for a complex query result. If the query is for a class, the resultClass attribute of the query must be set. If the query result is complex, a Result Set Mapping can be used.
Native queries can be parameterized, so they can be executed with different parameter values. Parameters are defined in SQL using the ? syntax for positional parameters, JPA does not require native queries support named parameters, but some JPA providers may.
A collection of query hints can also be provided to a native query. Query hints can be used to optimize or to provide special configuration to a query. Query hints are specific to the JPA provider. Query hints are defined through the @QueryHint annotation or query-hint XML element.
[edit] Example native named query annotation
@NamedNativeQuery( name="findAllEmployeesInCity", query="SELECT E.* from EMP E, ADDRESS A WHERE E.EMP_ID = A.EMP_ID AND A.CITY = ?", resultClass=Employee.class ) public class Employee { ... }
[edit] Example native named query XML
<entity-mappings> <entity name="Employee" class="org.acme.Employee" access="FIELD"> <named-native-query name="findAllEmployeesInCity" result-class="org.acme.Employee"> <query>SELECT E.* from EMP E, ADDRESS A WHERE E.EMP_ID = A.EMP_ID AND A.CITY = ?</query> </named-native-query> <attributes> <id name="id"/> </attributes> </entity> </entity-mappings>
[edit] Example native named query execution
EntityManager em = getEntityManager(); Query query = em.createNamedQuery("findAllEmployeesInCity"); query.setParameter(0, "Ottawa"); List<Employee> employees = query.getResultList(); ...
[edit] Example dynamic native query execution
EntityManager em = getEntityManager(); Query query = em.createNativeQuery("SELECT E.* from EMP E, ADDRESS A WHERE E.EMP_ID = A.EMP_ID AND A.CITY = ?", Employee.class); query.setParameter(0, "Ottawa"); List<Employee> employees = query.getResultList(); ...
