Enterprise Java Development@TOPIC@
"select" defines root query objects -- all path references must start from this set
"from" defines source of root query terms
"as" (optional) identifies a variable assignment of entity in from clause
"object()" (optional) identifies what is returned for the path expressed in select clause (e.g., object(), count()) -- left over from EJBQL
no "where" clause indicates all entities are selected
Figure 3.3. Using a JPA-QL Query
TypedQuery<Customer> query = em.createQuery(
"select object(c) from Customer as c",
Customer.class);
List<Customer> results = query.getResultList();
select
customer0_.CUSTOMER_ID as CUSTOMER1_3_,
customer0_.FIRST_NAME as FIRST2_3_,
customer0_.LAST_NAME as LAST3_3_
from JPAQL_CUSTOMER customer0_
-found result:firstName=cat, lastName=inhat
-found result:firstName=thing, lastName=one
-found result:firstName=thing, lastName=twoAllows return of simple property
"c.lastName" is called a "path"
All paths based from root query terms
Single path selects return typed list of values
Figure 3.5. Using Non-Entity Query
TypedQuery<String> query = em.createQuery(
"select c.lastName from Customer c", String.class);
List<String> results = query.getResultList();
select customer0_.LAST_NAME as col_0_0_
from JPAQL_CUSTOMER customer0_
-lastName=inhat
-lastName=one
-lastName=twoQuery result is a List<String> because "c.lastName" is a String
Select specifies multiple terms
Terms are expressed thru a path expression
Terms must be based off paths from root terms in the FROM (or JOIN) clause
Figure 3.7. Using Object[] Multi-select Query
TypedQuery<Object[]> query = em.createQuery(
"select c.firstName, c.hireDate from Clerk c", Object[].class);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for(Object[] result : results) {
assertEquals("unexpected result length", 2, result.length);
String firstName = (String) result[0];
Date hireDate = (Date) result[1];
log.info("firstName=" + firstName + " hireDate=" + hireDate);
}
select
clerk0_.FIRST_NAME as col_0_0_,
clerk0_.HIRE_DATE as col_1_0_
from JPAQL_CLERK clerk0_
-firstName=Manny hireDate=1970-01-01
-firstName=Moe hireDate=1970-01-01
-firstName=Jack hireDate=1973-03-01Query defined to return elements of select in Object[]
Figure 3.8. Multi-select Query with Tuple Example
select c.firstName as firstName, c.hireDate as hireDate from Clerk c
Aliases may be assigned to select terms for named-access to results
Figure 3.9. Using Tuple Multi-select Query
TypedQuery<Tuple> query = em.createQuery(
"select c.firstName as firstName, c.hireDate as hireDate from Clerk c", Tuple.class);
List<Tuple> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for(Tuple result : results) {
assertEquals("unexpected result length", 2, result.getElements().size());
String firstName = result.get("firstName", String.class);
Date hireDate = result.get("hireDate", Date.class);
log.info("firstName=" + firstName + " hireDate=" + hireDate);
}
select
clerk0_.FIRST_NAME as col_0_0_,
clerk0_.HIRE_DATE as col_1_0_
from JPAQL_CLERK clerk0_
-firstName=Manny hireDate=1970-01-01
-firstName=Moe hireDate=1970-01-01
-firstName=Jack hireDate=1973-03-01Query defined to return instances of Tuple class
Tuples provide access using
get(index) - simular to Object[]
get(index, Class<T> resultType) - typed access by index
get(alias) - access by alias
get(alias, Class<T> resultType) - typed access by alias
getElements() - access thru collection interface
Figure 3.10. Multi-select Query with Constructor Example
select new ejava.jpa.examples.query.Receipt(s.id, s.buyerId, s.date, s.amount) from Sale s
Individual elements of select are matched up against class constructor
Figure 3.11. Example ResultClass
package ejava.jpa.examples.query;
...
public class Receipt {
private long saleId;
private long customerId;
private Date date;
private double amount;
public Receipt(long saleId, long customerId, Date date, BigDecimal amount) {
this(customerId, saleId, date, amount.doubleValue());
}
public Receipt(long saleId, long customerId, Date date, double amount) {
this.customerId = customerId;
this.saleId = saleId;
this.date = date;
this.amount = amount;
}
...
Constructed class may be simple POJO -- no need to be an entity
Instances are not managed
Suitable for use as Data Transfer Objects (DTOs)
Figure 3.12. Using Constructor Multi-select Query
TypedQuery<Receipt> query = em.createQuery(
String.format("select new %s(", Receipt.class.getName()) +
"s.id,s.buyerId,s.date, s.amount) " +
"from Sale s", Receipt.class);
List<Receipt> results = query.getResultList();
for(Receipt receipt : results) {
assertNotNull("no receipt", receipt);
log.info("receipt=" + receipt);
}
select
sale0_.SALE_ID as col_0_0_,
sale0_.BUYER_ID as col_1_0_,
sale0_.date as col_2_0_,
sale0_.amount as col_3_0_
from JPAQL_SALE sale0_
-receipt=sale=1, customer=1, date=1998-04-10 10:13:35, amount=$100.00
-receipt=sale=2, customer=2, date=1999-06-11 14:15:10, amount=$150.00Each row returned as instance of provided class
All paths based off root-level FROM (or JOIN) terms
Paths use dot (".") notation to change contexts
Paths -- used this way -- must always express a single element. Must use JOINs for paths involving collections
Paths that cross entity boundaries automatically add a join to SQL query
Figure 3.14. Using Single Element Path Expression
TypedQuery<Object[]> query = em.createQuery(
"select s.id, s.store.name from Sale s", Object[].class);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for(Object[] result : results) {
assertEquals("unexpected result length", 2, result.length);
Long id = (Long) result[0];
String name = (String) result[1];
log.info("sale.id=" + id + ", sale.store.name=" + name);
}
select
sale0_.SALE_ID as col_0_0_,
store1_.name as col_1_0_
from JPAQL_SALE sale0_,
ORMQL_STORE store1_
where sale0_.SALE_STORE=store1_.STORE_ID
-sale.id=1, sale.store.name=Big Al's
-sale.id=2, sale.store.name=Big Al'sAutomatic INNER JOIN formed between Sale and Store because of the cross-entity path
Cannot directly navigate a XxxToMany relationship without a join
Figure 3.16. Correct Collection Path Expression
select sale.date from Clerk c INNER JOIN c.sales sale
Collection ("sales") is brought in as a root term ("sale") of the query through a JOIN expression
JOINs will match entities by their defined primary/foreign keys
INNER JOIN will return only those entities where there is a match
INNER JOIN is the default
Figure 3.19. Collection Path Expression SQL Output
select sale2_.date as col_0_0_
from JPAQL_CLERK clerk0_
inner join JPAQL_SALE_CLERK_LINK sales1_
on clerk0_.CLERK_ID=sales1_.CLERK_ID
inner join JPAQL_SALE sale2_
on sales1_.SALE_ID=sale2_.SALE_ID
-found result:1998-04-10 10:13:35.0
-found result:1999-06-11 14:15:10.0
-found result:1999-06-11 14:15:10.0(Many-to-Many) Link table used during JOIN
Tables automatically joined on primary keys
Only Sales sold by our Clerks are returned
Figure 3.20. LEFT OUTER JOIN Example
select c.id, c.firstName, sale.amount from Clerk c LEFT OUTER JOIN c.sales sale
LEFT is the default for OUTER JOIN
Figure 3.21. Alternate LEFT OUTER JOIN Form
select c.id, c.firstName, sale.amount from Clerk c LEFT JOIN c.sales sale
LEFT OUTER JOIN will return root with or without related entities
Figure 3.22. LEFT OUTER JOIN Runtime SQL Output
select
clerk0_.CLERK_ID as col_0_0_,
clerk0_.FIRST_NAME as col_1_0_,
sale2_.amount as col_2_0_
from JPAQL_CLERK clerk0_
left outer join JPAQL_SALE_CLERK_LINK sales1_
on clerk0_.CLERK_ID=sales1_.CLERK_ID
left outer join JPAQL_SALE sale2_
on sales1_.SALE_ID=sale2_.SALE_ID
-clerk.id=1, clerk.firstName=Manny, amount=100.00
-clerk.id=1, clerk.firstName=Manny, amount=150.00
-clerk.id=2, clerk.firstName=Moe, amount=150.00
-clerk.id=3, clerk.firstName=Jack, amount=null(Many-to-Many) Link table used during JOIN
Tables automatically joined on primary keys
All clerks, with or without a Sale, are returned
Figure 3.23. Explicit Collection Path Example
select c from Sale s, Customer c where c.id = s.buyerId
Permits JOINs without relationship in entity model
Figure 3.24. Explicit Collection Path SQL Output
select
customer1_.CUSTOMER_ID as CUSTOMER1_3_,
customer1_.FIRST_NAME as FIRST2_3_,
customer1_.LAST_NAME as LAST3_3_
from JPAQL_SALE sale0_ cross
join JPAQL_CUSTOMER customer1_
where customer1_.CUSTOMER_ID=sale0_.BUYER_ID
-found result:firstName=cat, lastName=inhat
-found result:firstName=thing, lastName=oneReturns all Customers that are identified by a Sale
Figure 3.25. Example Query Resulting in Lazy Fetch
select s from Store s JOIN s.sales where s.name='Big Al''s'
A normal JOIN (implicit or explicit) may honor the fetch=LAZY property setting of the relation
Can be exactly what is desired
Can also cause problems or extra work if not desired
Figure 3.26. Example Entity with Lazy Fetch Declared for Relation
@Entity @Table(name="ORMQL_STORE")
public class Store {
...
@OneToMany(mappedBy="store",
cascade={CascadeType.REMOVE},
fetch=FetchType.LAZY)
private List<Sale> sales = new ArrayList<Sale>();
Sales are lazily fetched when obtaining Store
Figure 3.27. Example Lazy Fetch Problem
Store store = em2.createQuery(
"select s from Store s JOIN s.sales " +
"where s.name='Big Al''s'",
Store.class).getSingleResult();
em2.close();
try {
store.getSales().get(0).getAmount();
fail("did not trigger lazy initialization exception");
} catch (LazyInitializationException expected) {
log.info("caught expected exception:" + expected);
}
select
store0_.STORE_ID as STORE1_0_,
store0_.name as name0_
from ORMQL_STORE store0_
inner join JPAQL_SALE sales1_
on store0_.STORE_ID=sales1_.SALE_STORE
where store0_.name='Big Al''s' limit ?
-caught expected exception:org.hibernate.LazyInitializationException:
failed to lazily initialize a collection of role:
ejava.jpa.examples.query.Store.sales, no session or session was closed
Accessing the Sale properties causes a LazyInitializationException when persistence context no longer active or accessible
Note that only a single row is required to be returned from the database for a fetch=LAZY relation. Although it requires more queries to the database, it eliminates duplicate parent information for each child row and can eliminate the follow-on query all together when not accessed.
Figure 3.28. Example Eager Fetch Query
select s from Store s JOIN FETCH s.sales where s.name='Big Al''s'
A JOIN FETCH used to eager load related entities as side-effect of query
Can be used as substitute for fetch=EAGER specification on relation
Figure 3.29. Example Eager Fetch SQL Output
select
store0_.STORE_ID as STORE1_0_0_,
sales1_.SALE_ID as SALE1_1_1_,
store0_.name as name0_0_,
sales1_.amount as amount1_1_,
sales1_.BUYER_ID as BUYER3_1_1_,
sales1_.date as date1_1_,
sales1_.SALE_STORE as SALE5_1_1_,
sales1_.SALE_STORE as SALE5_0_0__,
sales1_.SALE_ID as SALE1_0__
from ORMQL_STORE store0_
inner join JPAQL_SALE sales1_ on store0_.STORE_ID=sales1_.SALE_STORE
where store0_.name='Big Al''s'Sales are eagerly fetched when obtaining Store
Note that adding JOIN FETCH to parent query causes the parent rows to be repeated for each eagerly loaded child row and eliminated by the provider. This requires fewer database queries but results in more (and redundant) data to be returned from the query.
Limits output to unique value combinations
Figure 3.31. Distinct Example Output
select
distinct customer0_.LAST_NAME as col_0_0_
from JPAQL_CUSTOMER customer0_
-found result:two
-found result:inhat
-found result:one
Found three unique last names
Figure 3.33. Another Distinct Example Output
select
distinct customer0_.FIRST_NAME as col_0_0_
from JPAQL_CUSTOMER customer0_
-found result:cat
-found result:thing
Found two unique first names