Chapter 69. JPA Query Overview

javax.persistence.Query createQuery(String jpaql);

<T extends Object> javax.persistence.TypedQuery<T> createQuery(String jpaql, Class<T>);

Create query using native SQL

javax.persistence.Query createNativeQuery(String sql);

javax.persistence.Query createNativeQuery(String sql, Class sqlMapping);

javax.persistence.Query createNativeQuery(String sql, String sqlMapping);

Create query using Criteria API

javax.persistence.criteria.CriteriaBuilder getCriteriaBuilder();

javax.persistence.metamodel.Metamodel getMetamodel();

<T extends Object> javax.persistence.TypedQuery<T> createQuery(javax.persistence.criteria.CriteriaQuery<T>);

Create query from Named Query

javax.persistence.Query createNamedQuery(String queryName);

<T extends java.lang.Object> javax.persistence.TypedQuery<T> createNamedQuery(String queryName, Class<T>);

69.2. Query.getSingleResult()

Obtains exactly one result
TypedQuery returns type-safe result

Figure 69.1. Get a Unique Object based on Query

TypedQuery<Store> query = em.createQuery(

        "select s from Store s where s.name='Big Al''s'", Store.class);

Store store = query.getSingleResult();

select
    store0_.STORE_ID as STORE1_4_,
    store0_.name as name2_4_ 
from ORMQL_STORE store0_ 
where store0_.name='Big Al''s'

Figure 69.2. Throws NoResultException when not Found

try {

    store = em.createQuery(

       "select s from Store s where s.name='A1 Sales'", Store.class)

       .getSingleResult();

}

catch (NoResultException ex) { ... }

Figure 69.3. Throws NonUniqueResultException when multiple Found

try {

    Clerk clerk = em.createQuery(

        "select c from Clerk c where lastName='Pep'", Clerk.class)

        .getSingleResult();

}

catch (NonUniqueResultException ex) { ... }

Figure 69.4. Return List of Results Based on Query

TypedQuery<Clerk> query = em.createQuery(

    "select c from Clerk c where lastName='Pep'", Clerk.class);

List<Clerk> clerks = query.getResultList();

assertTrue("unexpected number of clerks:" + clerks.size(), clerks.size() > 1);

for(Clerk c : clerks) {

    logger.info("found clerk: {}", c);

}

select
    clerk0_.CLERK_ID as CLERK1_0_,
    clerk0_.FIRST_NAME as FIRST2_0_,
    clerk0_.HIRE_DATE as HIRE3_0_,
    clerk0_.LAST_NAME as LAST4_0_,
    clerk0_.TERM_DATE as TERM5_0_ 
from JPAQL_CLERK clerk0_ 
where clerk0_.LAST_NAME='Pep'
...
-found clerk:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }
-found clerk:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }
-found clerk:firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}

69.4. Query.getResultStream

Returns a java.util.stream.Stream
Useful when obtaining large results set, bypass paging
Allows query results to be scrolled by a processing stream

Figure 69.5. Return Stream of Results based on Query

TypedQuery<Customer> query = em.createQuery(

        "select c from Customer as c", 

        Customer.class);


Stream<Customer> s = query.getResultStream();

Map<String, Customer> resultMap = s.collect(

        Collectors.toMap(c->c.getFirstName()+c.getLastName(), c->c ));


for (Entry<String, Customer> result : resultMap.entrySet()) {

    logger.info("found={}", result);

}

int rows = resultMap.size();

assertTrue("unexpected number of customers", rows > 0);

select
    customer0_.CUSTOMER_ID as CUSTOMER1_1_,
    customer0_.FIRST_NAME as FIRST_NA2_1_,
    customer0_.LAST_NAME as LAST_NAM3_1_
from
    JPAQL_CUSTOMER customer0_

Implement row filtering in SQL where clause when possible

The java.util.stream capability contains many features that perform filtering of results -- emulating the purpose of the SQL where clause. The database where clause is a much better place to implement filters. Avoid using streams in an attempt to override core RDBMS capability when the data is available within the database.

69.5. Parameters

Runtime query parameters passed into query

Figure 69.6. Name-based Query Parameters

TypedQuery<Customer> query = em.createQuery(

        "select c from Customer c " +

        "where c.firstName=:firstName and c.lastName=:lastName",

        Customer.class);

query.setParameter("firstName", "cat");

query.setParameter("lastName", "inhat");


Customer customer = query.getSingleResult();

assertNotNull(customer);

logger.info("found customer for param names: {}", customer);

select
    customer0_.CUSTOMER_ID as CUSTOMER1_1_,
    customer0_.FIRST_NAME as FIRST2_1_,
    customer0_.LAST_NAME as LAST3_1_ 
from JPAQL_CUSTOMER customer0_ 
where customer0_.FIRST_NAME=? and customer0_.LAST_NAME=?

-found customer for param names:firstName=cat, lastName=inhat

:firstName and :lastName act as placeholders for runtime query parameters
Runtime parameters supplied using placeholder names
A parameter for each placeholder must be supplied - no defaults
A placeholder must exist for each parameter supplied - no extras

Figure 69.7. Ordinal-based Parameters

query = em.createQuery(

        "select c from Customer c " +

        "where c.firstName=?1 and c.lastName like ?2", Customer.class);

query.setParameter(1, "thing");

query.setParameter(2, "%");

List<Customer> customers = query.getResultList();

assertTrue("unexpected number of customers:" + customers.size(),

        customers.size() == 2);

for(Customer c : customers) {

    logger.info("found customer for param position: {}", c);

}

    select
        customer0_.CUSTOMER_ID as CUSTOMER1_1_,
        customer0_.FIRST_NAME as FIRST2_1_,
        customer0_.LAST_NAME as LAST3_1_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME=?  and ( customer0_.LAST_NAME like ? )
-found customer for param position:firstName=thing, lastName=one
-found customer for param position:firstName=thing, lastName=two

Appended numbers (?1) assign an ordinal value
No numbers supplied (?) cause default value based on order

Figure 69.8. Date-based Parameters

Calendar hireDate = Calendar.getInstance();

hireDate.set(Calendar.YEAR, 1972);

TypedQuery<Clerk> query = em.createQuery(

        "select c from Clerk c " +

        "where c.hireDate > :date", Clerk.class);

query.setParameter("date", hireDate.getTime(), TemporalType.DATE);


Clerk clerk = query.getSingleResult();

logger.info("found clerk by date({}): {}", hireDate.getTime(), clerk);

    select
        clerk0_.CLERK_ID as CLERK1_0_,
        clerk0_.FIRST_NAME as FIRST2_0_,
        clerk0_.HIRE_DATE as HIRE3_0_,
        clerk0_.LAST_NAME as LAST4_0_,
        clerk0_.TERM_DATE as TERM5_0_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.HIRE_DATE>?
...    
-found clerk by date(Fri Oct 06 20:28:08 EDT 1972):firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}

Dates are specified as DATE, TIME, or TIMESTAMP

69.6. Paging Properties

Figure 69.9.

TypedQuery<Sale> query = em.createQuery(

        "select s from Sale s", Sale.class);

for(int i=0; i<2; i++) {

    List<Sale> sales = query.setMaxResults(10)

                            .setFirstResult(i)

                            .getResultList();

    for(Sale s: sales) {

        logger.info("found sale in page({}): {}", i, s);

        em.detach(s); //we are done with this

    }

}

    select
        sale0_.SALE_ID as SALE1_2_,
        sale0_.amount as amount2_2_,
        sale0_.BUYER_ID as BUYER3_2_,
        sale0_.date as date4_2_,
        sale0_.SALE_STORE as SALE5_2_ 
    from
        JPAQL_SALE sale0_ limit ?
...
-found sale in page(0):date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }
-found sale in page(0):date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }
    select
        sale0_.SALE_ID as SALE1_2_,
        sale0_.amount as amount2_2_,
        sale0_.BUYER_ID as BUYER3_2_,
        sale0_.date as date4_2_,
        sale0_.SALE_STORE as SALE5_2_ 
    from
        JPAQL_SALE sale0_ limit ? offset ?
...
-found sale in page(1):date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }

Offset and limits passed to database
Database provides specified subset of rows

69.7. Pessimistic Locking

Obtain a locked copy of entity -- ready for modification
Required for some concurrent interactions with database

Figure 69.10. Obtaining a Pessimistic Write Lock

//get a list of clerks to update -- locked so others cannot change

List<Clerk> clerks = em.createQuery(

        "select c from Clerk c " +

        "where c.hireDate > :date", Clerk.class)

        .setParameter("date", new GregorianCalendar(1972,Calendar.JANUARY,1).getTime())

        .setLockMode(LockModeType.PESSIMISTIC_WRITE)

        .setHint("javax.persistence.lock.timeout", 0)

        .getResultList(); 

//make changes

for (Clerk c: clerks) {

    c.setHireDate(new GregorianCalendar(1972, Calendar.FEBRUARY, 1).getTime());

}

select
    clerk0_.CLERK_ID as CLERK1_0_,
    clerk0_.FIRST_NAME as FIRST2_0_,
    clerk0_.HIRE_DATE as HIRE3_0_,
    clerk0_.LAST_NAME as LAST4_0_,
    clerk0_.TERM_DATE as TERM5_0_ 
from JPAQL_CLERK clerk0_ 
where clerk0_.HIRE_DATE>? 
for update
...

Provider adds database-specific technique for lock
Lock timeout (in msecs) can be expressed through query hint

Note

Not all databases support lock timeouts

69.8. Bulk Updates

Change database -- not query it
Bypasses cache -- cached entities out of sync with database changes
Criteria API updates/deletes added in JPA 2.1

Figure 69.11. JPA-QL Bulk Update Example

Query update = em.createQuery(

        "update Clerk c set c.lastName=:newlast where c.lastName=:last");

update.setParameter("last", "Pep");

update.setParameter("newlast", "Peppy");

int rows = update.executeUpdate();

assertEquals("unexpected rows updated:" + rows, clerks.size(), rows);

update JPAQL_CLERK 
set LAST_NAME=? 
where LAST_NAME=?

Change directly applied to database, not the cached entity
Number of entities changed returned

Figure 69.12. Criteria API Bulk Update Example

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaUpdate<Clerk> qdef2=cb.createCriteriaUpdate(Clerk.class);


//"update Clerk c set c.lastName=:newlast where c.lastName=:last"

Root<Clerk> c2 = qdef2.from(Clerk.class);

qdef2.set("lastName", "Peppy")

     .where(cb.equal(c2.get("lastName"), "Pep"));


Query update = em.createQuery(qdef2);

int rows = update.executeUpdate();

assertEquals("unexpected rows updated:" + rows, clerks.size(), rows);

Figure 69.13. JPA-QL Bulk Delete Example

Query update = em.createQuery(

        "delete from Customer c " +

        "where c.firstName like :first AND c.lastName like :last");

int rows = update.setParameter("first", "thing")

                 .setParameter("last", "%")

                 .executeUpdate();

assertTrue("no rows updated", rows > 0);

delete from JPAQL_CUSTOMER 
where ( FIRST_NAME like ? ) and ( LAST_NAME like ? )

Bulk deletes do not trigger cascades
Entity instance exists in memory even after deleted from database

Figure 69.14. Criteria API Bulk Update Example

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaDelete<Customer> delete = cb.createCriteriaDelete(Customer.class);


//"delete from Customer c " +

//"where c.firstName like :first AND c.lastName like :last");

Root<Customer> c2 = delete.from(Customer.class);

delete.where(cb.and(

        cb.like(c.<String>get("firstName"), "thing"),

        cb.like(c.<String>get("lastName"), "%")

        ));


Query update = em.createQuery(delete);

int rows = update.executeUpdate();

assertTrue("no rows updated", rows > 0);

Figure 69.15. Refresh/Clear/Detach Stale Entit(ies)

//re-sync entity with DB changes

em.refresh(clerk);

//evict all managed entities in persistence context

em.clear();

//remove entity from persistence context

em.detach(clerk);

Keeping stale entities around will produce confusing results
"em.clear()" should be avoided except at end of transaction since un-manages everything

69.9. Named Queries

Register query with provider rather than ad-hoc
Available for JPA-QL and Native SQL -- not available with Criteria API
Can be registered using class annotations and orm.xml descriptor
LockMode and hints can be specified in declaration

Figure 69.16. Named Query Annotations Applied to (any) Entity Class

@Entity 

@Table(name="JPAQL_CUSTOMER")

@NamedQueries({

    @NamedQuery(name="Customer.getCustomersByName",

            query="select c from Customer c " +

                    "where c.firstName like :first AND c.lastName like :last"),

    @NamedQuery(name="Customer.getCustomerPurchases",

            query="select s from Sale s " +

                    "where s.buyerId=:custId")

})

public class Customer {

Figure 69.17. Using Named Query

Customer customer = 

    em.createNamedQuery("Customer.getCustomersByName", Customer.class)

      .setParameter("first", "cat")

      .setParameter("last", "inhat")

      .getResultList()

      .get(0);

assertNotNull("no customer found", customer);

Figure 69.18. Named Native Query Annotation Example

@Entity 

@Table(name="JPAQL_CUSTOMER")

@NamedNativeQueries({

    @NamedNativeQuery(name="Customer.getCustomerRows", 

            query="select * from JPAQL_CUSTOMER c " +

                  "where c.FIRST_NAME = ?1")

})

public class Customer {

Example query uses Native SQL to return all columns for table

Figure 69.19. Using Named Native Query

@SuppressWarnings("unchecked")

List<Object[]> rows = em.createNamedQuery("Customer.getCustomerRows")

        .setParameter(1, "cat")

        .getResultList();

assertEquals("unexpected customers found", 1, rows.size());

logger.info("found customer: {}", Arrays.toString(rows.get(0)));

    select * from JPAQL_CUSTOMER c 
    where c.FIRST_NAME = ?
-found customer:[1, cat, inhat]

69.10. Summary

Untyped (JPA 1.0) and Typed (JPA 2.0) Queries
Single and multiple results
Named and ordinal parameters
DATE, TIME, and TIMSTAMP parameters
Offset(firstResult) and limit(maxResults) paging
Locking
JPA-QL and (JPA 2.1) Criteria Bulk Updates
Named Queries