Querying

This page contains information and reference about the following topics/questions/how to's

  • Querying
    • JPQL
    • Criteria API
    • Types
    • Dynamic (Criteria API or JPQL)
    • Static (Named Queries: JPQL)
    • Query Hints
  • Bulk Update/Delete
  1. What is the difference or new in JPA 2.0 vs. JPA 1.x
  2. How to setup a static query via JPQL (Named Query)
  3. How to setup a dynamic query via JPA Criteria API
  4. How to setup a dynamic query via JPQL
  5. Database Pagination w/JPA and Hibernate
  6. How to perfome batch updates/deletes
  7. When to use JPQL over Criteria API (Governance)

Architecture Overview

See Dynamic, typesafe queries in JPA 2.0

Interface hierarchy of query expressions Interface hierarchy of query expressions

Querying

JPA uses JPQL (Java Persistence Query Language) as an interface (unified query language) to relieve the programmer about knowing various data-base dialects.

  • See/Download Getting Started with JPA by Mike Keith for a quick JPA overview
  • JPQL (Java Persistence Query Language):
  • See JPQL
  • Very similar to plain SQL relies on Objects rather than native data-base tables

Querying Options: See Java Persistence/Querying, JPA provides 2 different interfaces for querying data, which can be broken down to the following categories:

  1. Static JPQL Query: @NamedQuery, Pre-copmiled JPQL
  2. Dynamic JPQL Query: Runtime JPQL
  3. Criteria API (JPA 2.0): Similar to jlink BusinessObject API

Best Practices & How To's

Which of the querying methods I am allowed to use?

  • You can use: NamedQuery or Criteria API
    • Why
      • When using these 2 method, JPA requires you to parameterize the input, hence avoiding some common bad security practices
  • Prohibited: we are prohibiting the use of Dynamic JPQL for security reasons
    • Why
      • To avoid un-intentional string concatenation of the query string and inputs which could result in security wholes
    • Enforcement: 
      • Unit-testing
      • QA testing
      • Code Reviews

@NamedQuery

  • a named query is just JPQL defined on your entity using the @NamedQuery annotation
  • the name you give to your named query has to unique through-out your whole application
  • a named query is compiled when the web-server load your application, hence it can NOT be changed at run-time
  • inputs passed to a named query have to be parameterized 
  • Performance: since the JPQL is pre-compiled you will achieve the best performance using this option
  • example:
    • define in domain
    • *
      ...
      @Entity
      @NamedQueries( {
              @NamedQuery(name = "findFundByPrimaryKey", 
                                      query = "select myFund from Fund myFund where myFund.fund = ?1"),
      })
      @Table(schema = "COA_DB", name = "FUND")
      public class Fund implements Serializable {
      ...
      }
      ...
      *
      
    • call in yourDAO
    • *
      ...
      /**
       * JPQL Query - findFundByPrimaryKey
       *
       */
      public Fund findFundByPrimaryKey(int startResult, int maxRows) throws DataAccessException {
          try {
              return executeQueryByNameSingleResult("findFundByPrimaryKey");
          } catch (NoResultException nre) {
              return null;
          }
      }
      ...
      *
      

Criteria API

  • See Criteria API for details
  • See Hibernate's Docs Chapter 9. Criteria Queries
  • See Dynamic, typesafe queries in JPA 2.0
  • added to JPA since version 2.0
  • powerfull API to perform Dynamic Queries
  • Only used for querying purposes
  • for updates/deletes/inserts EntityManager has to be used
  • example:
    • implement in yourDAO
    • *
      ...
          public Set<Award> findCostShareAwardsByIFOPsIn(IFOPCriteria ifopCriteria) {
              List<Predicate> predicates = new ArrayList<Predicate>();
              CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
              CriteriaQuery<Award> criteriaQuery = criteriaBuilder.createQuery(Award.class);
              Root<Award> awardRoot = criteriaQuery.from(Award.class);
              //select Award Table
              criteriaQuery.select(awardRoot);
              //fetch AwardCostShare table columns all in one call
              awardRoot.fetch(Award_.awardCostShare);
              //-------------------------------------------
              //subquery
              List<Predicate> subQueryIndxPredicatesList = new ArrayList<Predicate>();
              CriteriaQuery<Object> criteriaQueryGeneric = criteriaBuilder.createQuery();
              Subquery<Object> subQueryIndx = criteriaQueryGeneric.subquery(Object.class);
              Root<Indx> indxRoot = subQueryIndx.from(Indx.class);
              subQueryIndx.distinct(true);
              subQueryIndx.select(indxRoot.get(Indx_.fund.getName()));
              if (ifopCriteria!=null && ifopCriteria.isNotBlank()) {
                  if (!ifopCriteria.isIndexBlank()) {
                      subQueryIndxPredicatesList.add(criteriaBuilder.like(indxRoot.<String>get("indxField"), ifopCriteria.getIndx().trim() ));
                  }
                  if (!ifopCriteria.isFundBlank()) {
                      subQueryIndxPredicatesList.add(criteriaBuilder.like(indxRoot.<String>get("fund"), ifopCriteria.getFund().trim() ));
                  }
                  if (!ifopCriteria.isOrganizationBlank()) {
                      subQueryIndxPredicatesList.add(criteriaBuilder.like(indxRoot.<String>get("organization"), ifopCriteria.getOrganization().trim() ));
                  }
                  if (!ifopCriteria.isProgramBlank()) {
                      subQueryIndxPredicatesList.add(criteriaBuilder.like(indxRoot.<String>get("program"), ifopCriteria.getProgram().trim()));
                  }
                  subQueryIndxPredicatesList.add(criteriaBuilder.equal(indxRoot.<String>get("mostRecentFlag"), "Y" ));
              }
              //check
              if (subQueryIndxPredicatesList.size()==0) {
                  throw new RuntimeException("no criteria");
                  return null;
              }else{
                  Predicate[] p = new Predicate[subQueryIndxPredicatesList.size()]; 
                  p = subQueryIndxPredicatesList.toArray(p); 
                  subQueryIndx.where(p);
                  predicates.add(criteriaBuilder.in(awardRoot.get(Award_.fundNumber.getName())).value(subQueryIndx));
              }
              if (predicates.size()==0) {
                  throw new RuntimeException("no criteria");
                  return null;
              }else{
                  Predicate[] p = new Predicate[predicates.size()]; 
                  p = predicates.toArray(p); 
                  criteriaQuery.where(criteriaBuilder.and(p)); 
              }
              //run
              TypedQuery<Award> query = entityManager.createQuery(criteriaQuery); 
              query.setFirstResult(DEFAULT_FIRST_RESULT_INDEX);
              query.setMaxResults(DEFAULT_MAX_RESULTS);
              List<Award> results = query.getResultList();
              return new LinkedHashSet<Award>(results);
          }
      ...
      *
      
    • Generated SQL
    • *
      ...
      SELECT
          award0_.*
          awardcosts1_.*
      FROM
          AWARD_DB.AWARD award0_
              INNER JOIN
              AWARD_DB.AWARD_COST_SHARE awardcosts1_
              ON
              award0_.SEQUENCE_NUMBER=awardcosts1_.SEQUENCE_NUMBER AND
          award0_.UCSD_AWARD_NUMBER=awardcosts1_.UCSD_AWARD_NUMBER
      WHERE
          award0_.FUND_NUMBER IN (
      SELECT
          distinct indx2_.FUND
      FROM
          COA_DB.INDX indx2_
      WHERE
          (indx2_.INDX like ?) AND
          (indx2_.FUND like ?) AND
          (indx2_.ORGANIZATION like ?) AND
          (indx2_.PROGRAM like ?) AND
          indx2_.MOST_RECENT_FLAG=?)
      ...
      *
      
  • call in yourService

When to use NamedQuery over Criteria API

  • NamedQuery: If no dynamic queries are needed, use NamedQueries for best performance
  • Criteria API: use when need to run/construct dynamic queries (use cases such as Search/Running Reports often require dynamic user input, ...)

Resultset Pagination with JPA

  • JPA provides database pagination out of the box
    • *
      query.setFirstResult(0); 
      query.setMaxResults(10);
      *
      
  • The  Skyway Spring Tools that we recommend to wrap around your DAO layer in the "Entity Management" section limits the result to 200 by default for JPQL queries
    • you still need to use the setFirstResult and setMaxResults when running queries through the CriteriaAPI
  • Read: Tuning Queries when using Pagination with JPA and Hibernate

Versioned Schemas

  • avoid Versioned Schemas if you can
  • as of JPA 2.0 there is no support for dynamically binding tables to object on the fly
    • note: JPA 2.1 (JDK7) might include dynamic entities, but nevertheless Versioned Schemas are not considered a good data-base design practice
  • What if I have to consume tables within across versioned schemas
    • If possible create a View on top of all those versioned schemas
    • if not, you have to create one Entity per Table Per Schema

MetaModel Generation

See the Entity (JPA Annotated POJO) section for an example and configration

Query Hints

Query Optimization

  • Use NamedQueries when possible
  • Use JPQL "JOIN FETCH" or its Criteria API equivalent to load the associated objects from the data-base rather than multiple SELECT queries
  • Use Partial Column Selection/Retrieval when possible
  • Advanced: See your JPA Vendor Configuration Options (Hibernate in our case)
  • See Join Fetch and Query Optimization

Bulk/Batch Updates/Deletes

  • EntityManager does not provide methods to perform batch Update/Deletes and for good reasons
  • Caution: Do not use unless necessary: If you have to perform such tasks you can use plain JPQL Update/Delete commands
    • keep in mind doing entities updated/removed via these commands are not synched with the persistence context
    • consult with your group before using batch Update/Deletes

Governance

  • Use of dynamic JPQL is prohibited, use CriteriaAPI or NamedQueries
  • Do not use/perform batch Update/Deletes

Unit Testing

Please refer to the unit-testing section

Advanced

Please refer to books or online docs for advanced topics

References