Enterprise Java Development@TOPIC@
Three fundamental query types within JPA
JPA Query Language (JPA) - entity/property/relationship-based
Native SQL - table/column-based
Criteria API - entity/property/relationship-based using Java classes
Access to the *entity* model using a SQL-like text query language
Queries expressed using entities, properties, and relationships
Pros
More concise (than other query forms)
Familiar to SQL users
Abstracts query away from table, column, primary key, and relationship mapping
Can be defined within XML deployment descriptors
Produces portable SQL
Cons
Not (overly) type-safe
No help from Java compiler in constructing query expression
Don't find out most errors until runtime
Figure 68.1. Building a JPA Query using JPA-QL
String jpaqlString =
"select c from Customer c " +
"where c.firstName = :firstName " +
"order by c.lastName ASC";
//use query string to build typed JPA-QL query
TypedQuery<Customer> query = em
.createQuery(jpaqlString,Customer.class);
"c" is part of root query
"c" represents rows from Customer entity table(s)
"c.firstName and c.lastName" are property paths off root term
":firstName" is parameter placeholder
"Customer.class" type parameter allows for a type-safe return result
Figure 68.2. Executing a JPA Query (built from JPA-QL)
//at this point we are query-type agnostic
List<Customer> customers = query
.setParameter("firstName", "thing")
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
select
customer0_.CUSTOMER_ID as CUSTOMER1_3_,
customer0_.FIRST_NAME as FIRST2_3_,
customer0_.LAST_NAME as LAST3_3_
from JPAQL_CUSTOMER customer0_
where customer0_.FIRST_NAME=?
order by customer0_.LAST_NAME ASC
-result=[firstName=thing, lastName=one, firstName=thing, lastName=two]Placeholder is replaced by runtime parameter
Zero-or-more results are requested
Entities returned are managed
Figure 68.3. Condensing the JPA-QL Query
List<Customer> customers = em.createQuery(
"select c from Customer c " +
"where c.firstName = :firstName " +
"order by c.lastName ASC",
Customer.class)
.setParameter("firstName", "thing")
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
Access to power of working with native SQL
Pros
Provides full access to native SQL power
Provides full access to database-vendor SQL extensions
Easy to see when native SQL is being used within application -- target for portability review
Ability to produce managed entity as result of query
Easy use ad-hoc of SQL UNIONs across tables
Cons
Portability of SQL not addressed by JPA
Not type-safe
No help from Java compiler in constructing query expression
Don't find out most errors until runtime
Figure 68.4. Building a JPA Query using Native SQL
Table table = Customer.class.getAnnotation(Table.class);
String sqlString =
"select c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME " +
String.format("from %s c ", table.name()) +
"where c.FIRST_NAME = ? " +
"order by c.LAST_NAME ASC";
//use query string to build query
Query query = em.createNativeQuery(sqlString,Customer.class);
"c" represents rows in table
specific columns (or *) are return for each row
"?" marks a positional parameter -- non-portable to use named parameters in native SQL queries
TypedQuery<T>s not supported in native SQL queries because of a conflict with legacy JPA 1.0 API
Figure 68.5. Executing a JPA Query (built from Native SQL)
//at this point we are query-type agnostic (mostly)
@SuppressWarnings("unchecked")
List<Customer> customers = query
.setParameter(1, "thing")
.getResultList();
log.info("result=" + customers);
assertEquals("unexpected number of results", 2, customers.size());
select
c.CUSTOMER_ID,
c.FIRST_NAME,
c.LAST_NAME
from JPAQL_CUSTOMER c
where c.FIRST_NAME = ?
order by c.LAST_NAME ASC
-result=[firstName=thing, lastName=one, firstName=thing, lastName=two]Query execution similar to other query types
User-provided SQL executed
Legacy JPA 1.0 Native SQL query syntax already used the signature of passing in a Class for createNativeQuery(). In this context, it was an entity class that contained JPA mappings for the query -- not the returned entity type. This prevented createNativeQuery() from being updated to return a typed result in JPA 2.0.
Figure 68.6. Condensing the SQL Query
@SuppressWarnings("unchecked")
List<Customer> customers = em.createNativeQuery(
"select c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME " +
"from JPAQL_CUSTOMER c " +
"where c.FIRST_NAME = ? " +
"order by c.LAST_NAME ASC",
Customer.class)
.setParameter(1, "thing")
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
Allow query to return mixture of managed entities and values
DAOs can use value results to plugin transient aggregate properties in parent entity without pulling entire child entities back from database
e.g., total sales for clerk
Figure 68.7. NativeQuery with SqlResultSetMapping
@SuppressWarnings("unchecked")
List<Object[]> results = em.createNativeQuery(
"select clerk.CLERK_ID, "
+ "clerk.FIRST_NAME, "
+ "clerk.LAST_NAME, "
+ "clerk.HIRE_DATE, "
+ "clerk.TERM_DATE, "
+ "sum(sales.amount) total_sales "
+ "from JPAQL_CLERK clerk "
+ "left outer join JPAQL_SALE_CLERK_LINK slink on clerk.CLERK_ID=slink.CLERK_ID "
+ "left outer join JPAQL_SALE sales on sales.SALE_ID=slink.SALE_ID "
+ "group by clerk.CLERK_ID, "
+ "clerk.FIRST_NAME, "
+ "clerk.LAST_NAME, "
+ "clerk.HIRE_DATE, "
+ "clerk.TERM_DATE "
+ "order by total_sales DESC",
"Clerk.clerkSalesResult")
.getResultList();
@Entity @Table(name="JPAQL_CLERK")
@SqlResultSetMappings({
@SqlResultSetMapping(name = "Clerk.clerkSalesResult",
entities={ @EntityResult(entityClass = Clerk.class )},
columns={@ColumnResult(name = "total_sales")}
)
})
public class Clerk {
Figure 68.8. Example NativeQuery with SqlResultSetMapping Output
for (Object[] result: results) {
Clerk clerk = (Clerk) result[0];
BigDecimal totalSales = (BigDecimal) result[1];
log.info(String.format("%s, $ %s", clerk.getFirstName(), totalSales));
}
-Manny, $ 250.00 -Moe, $ 150.00 -Jack, $ null
Figure 68.9. NamedNativeQuery with SqlResultSetMapping
@Entity @Table(name="JPAQL_CLERK")
@NamedNativeQueries({
@NamedNativeQuery(name = "Clerk.clerkSales", query =
"select clerk.CLERK_ID, "
+ "clerk.FIRST_NAME, "
+ "clerk.LAST_NAME, "
+ "clerk.HIRE_DATE, "
+ "clerk.TERM_DATE, "
+ "sum(sales.amount) total_sales "
+ "from JPAQL_CLERK clerk "
+ "left outer join JPAQL_SALE_CLERK_LINK slink on clerk.CLERK_ID=slink.CLERK_ID "
+ "left outer join JPAQL_SALE sales on sales.SALE_ID=slink.SALE_ID "
+ "group by clerk.CLERK_ID, "
+ "clerk.FIRST_NAME, "
+ "clerk.LAST_NAME, "
+ "clerk.HIRE_DATE, "
+ "clerk.TERM_DATE "
+ "order by total_sales DESC",
resultSetMapping="Clerk.clerkSalesResult")
})
@SqlResultSetMappings({
@SqlResultSetMapping(name = "Clerk.clerkSalesResult",
entities={ @EntityResult(entityClass = Clerk.class )},
columns={@ColumnResult(name = "total_sales")}
)
})
public class Clerk {
Figure 68.10. Example NamedNativeQuery with SqlResultSetMapping Usage
List<Object[]> results = em.createNamedQuery("Clerk.clerkSales").getResultList();
Somewhat parallel capability to JPAQL
Build overall query using Java types (demonstrated here with "string accessors")
Pros
Structure of query is type-safe
Allows object-level manipulation of the query versus manipulation of a query string
Useful when building query structure based on runtime properties
Cons
Complex -- looses familiarity with SQL
Cannot be expressed in XML deployment descriptor
Access to properties not type-safe (addressed by Canonical Metamodel)
Figure 68.11. Building a JPA Query using Criteria API
select c from Customer c where c.firstName = :firstName order by c.lastName ASC
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c)
.where(cb.equal(c.get("firstName"),
cb.parameter(String.class,"firstName")))
.orderBy(cb.asc(c.get("lastName")));
//build query from criteria definition
TypedQuery<Customer> query = em.createQuery(qdef);
"CriteriaBuilder" used as starting point to build objects within the query tree
"CriteriaQuery<T>" used to hold the definition of query
"Root<T>" used to reference root level query terms
"CriteriaBuilder.from()" used to designate the entity that represents root query term
Result used to create path references for query body
"CriteriaBuilder.select()" officially lists the objects returned from query
"CriteriaBuilder.where()" builds a decision predicate of which entities to include
"CriteriaBuilder.equal()" builds an equals predicate for the where clause
"Root<T>.get()" returns the property referenced in path expression
"CriteriaBuilder.parameter()" builds a parameter placeholder within query. Useful with @Temporal date comparisons
Figure 68.12. Executing a JPA Query using Criteria API
//at this point we are query-type agnostic
List<Customer> customers = query
.setParameter("firstName", "thing")
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
select
customer0_.CUSTOMER_ID as CUSTOMER1_3_,
customer0_.FIRST_NAME as FIRST2_3_,
customer0_.LAST_NAME as LAST3_3_
from JPAQL_CUSTOMER customer0_
where customer0_.FIRST_NAME=?
order by customer0_.LAST_NAME asc
-result=[firstName=thing, lastName=one, firstName=thing, lastName=two]]Query execution identical to JPA-QL case
Figure 68.13. Condensing the Criteria API Query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
List<Customer> customers = em.createQuery(qdef.select(c)
.where(cb.equal(c.get("firstName"), "thing"))
.orderBy(cb.asc(c.get("lastName"))))
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
Previous Criteria API examples were string label based -- not type safe
Criteria API provides means for stronger typing
Strong typing permits automatic detection of model and query differences
Provides access to the persistent model backing each entity and its properties
Figure 68.14. Accessing JPA Metamodel
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
EntityType<Customer> c_ = c.getModel();
logger.info(String.format("%7s, %10s:%-30s",
c_.getPersistenceType(),
c_.getName(),
c_.getJavaType()));
for (Attribute<? super Customer, ?> p: c_.getAttributes()) {
logger.info(String.format("%7s, %10s:%-30s",
p.getPersistentAttributeType(),
p.getName(),
p.getJavaType()));
}
- ENTITY, Customer:class ejava.jpa.examples.query.Customer - BASIC, firstName:class java.lang.String - BASIC, id:long - BASIC, lastName:class java.lang.String
JPA Metamodel provides access to
Entity structure
Entity database mapping
Pros
Access properties in (a more) type-safe manner
Cons
Complex
No compiler warning of entity type re-factoring
Figure 68.15. Building Query with JPA Metamodel
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
EntityType<Customer> c_ = c.getModel();
qdef.select(c) //we are returning a single root object
.where(cb.equal(
c.get(c_.getSingularAttribute("firstName", String.class)),
cb.parameter(String.class,"firstName")))
.orderBy(cb.asc(c.get(c_.getSingularAttribute("lastName", String.class))));
TypedQuery<Customer> query = em.createQuery(qdef);
Access to properties within entities done through type-safe accessors
Figure 68.16. Executing Query with JPA Metamodel
//at this point we are query-type agnostic
List<Customer> customers = query
.setParameter("firstName", "thing")
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
select
customer0_.CUSTOMER_ID as CUSTOMER1_3_,
customer0_.FIRST_NAME as FIRST2_3_,
customer0_.LAST_NAME as LAST3_3_
from JPAQL_CUSTOMER customer0_
where customer0_.FIRST_NAME=?
order by customer0_.LAST_NAME asc
-result=[firstName=thing, lastName=one, firstName=thing, lastName=two]Results identical to previous approaches
Figure 68.17. Condensing the JPA Metamodel-based Query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
EntityType<Customer> c_ = c.getModel();
List<Customer> customers = em.createQuery(qdef.select(c)
.where(cb.equal(
c.get(c_.getSingularAttribute("firstName", String.class)), "thing"))
.orderBy(cb.asc(c.get(c_.getSingularAttribute("lastName", String.class)))))
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
Complexities of metamodel can be simplified using metamodel classes
Pros
Easy, type-safe access to entity model
Java compiler can alert of mismatch between query and entity model
Cons
Requires either manual construct or auto-generation of separate metamodel class
Figure 68.18. Example Canonical Metamodel
package ejava.jpa.examples.query;
import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;
@StaticMetamodel(Customer.class)
public abstract class Customer_ {
public static volatile SingularAttribute<Customer, Long> id;
public static volatile SingularAttribute<Customer, String> lastName;
public static volatile SingularAttribute<Customer, String> firstName;
}
Construct or generate a canonical metamodel class to provide type-safe, easy access to properties
Figure 68.19. Building Query with Canonical Metamodel
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c) //we are returning a single root object
.where(cb.equal(
c.get(Customer_.firstName),
cb.parameter(String.class,"firstName")))
.orderBy(cb.asc(c.get(Customer_.lastName)));
TypedQuery<Customer> query = em.createQuery(qdef);
Use canonical metamodel class to provide type-safe, easy access to properties ("Customer_.firstName")
Figure 68.20. Executing Query with Canonical Metamodel
//at this point we are query-type agnostic
List<Customer> customers = query
.setParameter("firstName", "thing")
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
select
customer0_.CUSTOMER_ID as CUSTOMER1_3_,
customer0_.FIRST_NAME as FIRST2_3_,
customer0_.LAST_NAME as LAST3_3_
from JPAQL_CUSTOMER customer0_
where customer0_.FIRST_NAME=?
order by customer0_.LAST_NAME asc
-result=[firstName=thing, lastName=one, firstName=thing, lastName=two]Result is identical to previous approaches
Figure 68.21. Condensing the Canonical Metamodel-based Query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
Root<Customer> c = qdef.from(Customer.class);
List<Customer> customers = em.createQuery(qdef.select(c)
.where(cb.equal(c.get(Customer_.firstName),"thing"))
.orderBy(cb.asc(c.get(Customer_.lastName))))
.getResultList();
logger.info("result={}" + customers);
assertEquals("unexpected number of results", 2, customers.size());
More work to get here but clean, result
Type-safe - queries will not compile if entity changes
Canonical Metamodel classes can be manually authoried or generated
Figure 68.22. Maven Dependency Can Generate Canonical Metamodel Classes
<!-- generates JPA metadata classes -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-jpamodelgen</artifactId>
<version>${hibernate-entitymanager.version}</version>
<scope>provided</scope>
</dependency>
Figure 68.23. Generated Source placed in target/generated-sources/annotations
`-- target
|-- generated-sources
`-- annotations
`-- ejava
`-- jpa
`-- examples
`-- query
|-- Clerk_.java
|-- Customer_.java
|-- Sale_.java
`-- Store_.javaFigure 68.24. Maven Plugin adds Generated Source to IDE Build Path
<!-- add generated JPA metamodel classes to classpath -->
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<version>1.9.1</version>
<executions>
<execution>
<id>add-metamodel-classes</id>
<phase>process-sources</phase>
<goals>
<goal>add-source</goal>
</goals>
<configuration>
<sources>
<source>target/generated-sources/annotations</source>
</sources>
</configuration>
</execution>
</executions>
</plugin>
Three basic forms for query expression
SqlResultSetMapping
JPAQL
Native SQL
Criteria API
String-based Accessors
Metamodel Accessors
Canonical Metamodel Accessors
Entity model provides portability
JPAQL
Criteria API
Native SQL provides direct access to
full power of SQL
full access to database-specific extensions
Criteria API provides type-safe construct of query structure
JPA Metamodel provides type-safe access to entity properties
JPA Canonical Metamodel provides type-safe access to model-specific entity properties
Produces compilation error when query our of sync with entity model
Provides convenient access to model-specific properties