02.JPQL(Java持久層查詢語言)

JPQL

你有二種方式可以對資料庫進行查詢:
  1. 本地查詢(Native) - SQL
  2. JPQL
差別在於 JPQL
  1. 消除對不同 DBMS 系統查詢的方言查詢, 查詢語句由 JPA 實作者來進行轉化.
  2. 大副降低應用程式對特定 DBMS / SQL 的耦合度與修改難度.
  3. 建議以 JPQL 為主, 不足需求由 SQL 為輔足.


JPQL


The Java Persistence query language (JPQL) is used to define searches against persistent entities independent of the mechanism used to store those entities. As such, JPQL is "portable", and not constrained to any particular data store. The Java Persistence query language is an extension of the Enterprise JavaBeans query language, EJB QL, adding operations such as bulk deletes and updates, join operations, aggregates, projections, and subqueries. Furthermore, JPQL queries can be declared statically in metadata, or can be dynamically built in code. This chapter provides the full definition of the language.




設計查詢QL


你有三種方式建立查詢QL
  1. JPQL Query
  2. 命名式查詢(Named QL)
    1. Annotation
  3. 本地原生(Native)

JPQL 查詢

String jpql = "SELECT a FROM AuthorEO a";
Query query = em.createQuery(jpql);



Query API


Query API 由三個要件所構成
  1. JPQL 查詢語句
  2. 再經由 EntityManager 創建查詢 Query
  3. 操作 Query 物件



命名式查詢 - Annotation


最佳化式查詢, 在加載階段預先編譯, 來提高查詢的效率.

@NamedQueries({
    @NamedQuery(name = "Author.findAll", query = "SELECT a FROM Author a"),





原生查詢(Native Query)


複雜的查詢仍可由原生方式設計

public Query createNativeQuery( ... )





日期類型的查詢



   

Query q = em.createQuery("SELECT c FROM customer c WHERE c.nowdate >= :start AND c.nowdate <= :end");
           
q.setParameter("start", date, TemporalType.TIMESTAMP);
q.setParameter("end", date1, TemporalType.TIMESTAMP);



動態查詢







Reference



http://www.jpox.org/docs/1_2/jpa/jpql.html
http://download.oracle.com/docs/cd/E11035_01/kodo41/full/html/ejb3_langref.html