2008年10月6日星期一

EJB3 - JPA - Query


EJB3 - JPA - Query

Description
JPA的Query是很重要又方便的�西, 不�要注意�量不要把Query和一般的entity�理方式放一起.
因�JPA�要求persistence provider必��Query和persistence context同步, 所以�在就常看到一些因�使用Query�致使用者需要先flush才能Query的�象.
Reference
EJB3 in Action - CH10 - Using the query and JPQL to retrieve entities
Notes

1.        createNamedQuery可用JPQL也可放native SQL
2.        createNativeQuery(String sqlString)用� UPDATE 或 DELETE
3.        createNativeQuery(String sqlString, Class result-class)用�取得single entity type
4.        createNativeQuery(String sqlString, String result-setMapping)用�取得multiple entity types
  • named query可用annotation定�在entity上或在orm xml上, 只要是多�地方�使用到的query都�合用作named query
  • named query 可增加效能, 因�他��一次之後就可有效率的重�使用
  • 使用query不需要transaction, 在�有transaction的情�下使用query��取得的entity detach.
  • 一�named query的name在persistence unit中必�是唯一的, 所以要注意命名
  • 用@NamedQuery或@NamedQueries
@Entity
@NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer")
public class Programmer implements Serializable {...}
       
@Entity
@NamedQueries(
   @NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer")
)
public class Programmer implements Serializable {...}

@Entity
@NamedQueries({
   @NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer"),
   @NamedQuery(name=Programmer.JPQL_FIND_ALL_PROGRAMMER, query="from Programmer")
})
public class Programmer implements Serializable {...}
   
  • 使用named query的方式
Query q = em.createNamedQuery(Programmer.JPQL_FIND_ALL_PROGRAMMER);
  • 使用dynamic query的方式
Query q = em.createQuery("from Programmer");
  • 取得Query物件後, �特�的method
1.        setParameter(String name, Object value)�定named paramater
2.        setParameter(String name, Date value, TemporalType temporalType)�定Date型�的named parameter
3.        setParameter(String name, Calendar value, TemporalType temporalType)�定Calendar型�的named parameter
4.        setParameter(int position, Object value)�定特定位置parameter的值
5.        setParameter(int position, Date value, TemporalType temporalType)�定特定位置Date型�parameter的值
6.        setParameter(int position, Calendar value, TemporalType temporalType)�定特定位置Calendar型�parameter的值
  • 使用position的���定方式, 建�用named parameter比�好debug
private void useParametericQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select p from Programmer p where p.id >= ?100 AND p.id <= ?70");
   /* 如果指定��, 例如放65.
    * �出�java.lang.IllegalArgumentException:
    * org.hibernate.QueryParameterException:
    * could not locate named parameter [65] */
   q.setParameter(70, Long.valueOf(100));
   q.setParameter(100, Long.valueOf(50));
   List< Programmer > resultList = q.getResultList();
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getId() + ":" + programmer.getName());
   }
}
   
  • 使用named parameter的Query (建�用法)
private void useNamedParametericQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select p from Programmer p " +
                   "where p.id >= :lowerID and p.id <= :higherID");
   q.setParameter("lowerID", Long.valueOf(30));
   q.setParameter("higherID", Long.valueOf(80));
   List< Programmer > resultList = q.getResultList();
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getId() + ":" + programmer.getName());
   }
}
   
  • 使用getSingleResult取得一�entity.
1.        如果query�有entity��NoResultException, 若有多��NonUniqueResultException. 所以使用�要�定只�搜�到一�.
2.        由於NonUniqueResultException�NoResultException不�roll back transaction, 所以若有�理transaction要�得�理exception的�怎��.
private void useNoResultExceptionQuery(String pname) {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select p from Programmer p where p.name = :pname");
   q.setParameter("pname", pname);
   try {
       System.out.println(q.getSingleResult());
   } catch ( NoResultException nre ) {
       System.out.println("no result");
       nre.printStackTrace();
   } catch ( NonUniqueResultException nure ) {
       System.out.println("not unique result");
       nure.printStackTrace();
   }
}
       
  • 用getResultList取得多�entities
1.        如果�有符合的就回�空List, 不�有exception.
2.        透�setMaxResults可�定一�最大query量
3.        透�setFirstResult可�定要�哪一��始查
private void usePaginationQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createNamedQuery(Programmer.JPQL_FIND_ALL_PROGRAMMER);
   q.setMaxResults(10);

   int resultCount = 0;
   while ( true ) {
       q.setFirstResult(resultCount);
       List< Programmer > resultList = q.getResultList();
       if ( resultList.size() == 0) {
           System.out.println("result end..");
           break;
       }
       resultCount += resultList.size();
       System.out.println("getResultList...");
       for (Programmer programmer : resultList) {
           System.out.println(programmer.getName());
       }
       System.out.println("next page...");
   }
}
       
  • �定Query的FlushMode
1.        FlushModeType.AUTO: �是��值, ��Query�行前先把EntityManager中�entity的�理commit再query.
private void useAutoCommit() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   // 如果�有begin就不��上有AUTO COMMIT的效果, 就是有 INSERT 的 statement
   em.getTransaction().begin();
   System.out.println("transaction begin");
   prepareProgrammer(em, 999); // 在�新增一�Programmer但�commit
   System.out.println("prepare programmer finished");
   Query q = em.createQuery("from Programmer");
   System.out.println("prepare Query instance");
   
   // 到 getResultList 才� auto-commit 然後 select
   // ��候 query 的�果�是�理完entity的�果
   List< Programmer > resultList = q.getResultList();
   System.out.println("getResultList");
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getName());
   }
   
   // 可是如果最後��行commit�是不�把entity的�理放�DB
   em.getTransaction().commit();
}                
           
2.        FlushModeType.COMMIT: ��mode�有�定provider要怎��做. ��Hibernate是Query的�果不�包含�理�的entity.
private void useNotAutoCommit() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   em.getTransaction().begin();
   System.out.println("transaction begin");
   prepareProgrammer(em, 999);
   System.out.println("prepare programmer finished");
   Query q = em.createQuery("from Programmer");
   q.setFlushMode(FlushModeType.COMMIT);
   System.out.println("prepare Query instance");
   // ��候query的�果不�包含新增的Programmer, 因�FlushMode改�COMMIT
   List< Programmer > resultList = q.getResultList();
   System.out.println("getResultList");
   for (Programmer programmer : resultList) {
       System.out.println(programmer.getName());
   }
}
           
  • 用Query�行update
1.        JPQL 的 update 格式
UPDATE entityName indentifierVariable
SET single_value_path_expression1 = value1, ...
single_value_path_expressionN = valueN
WHERE where_clause
           
2.        使用JPQL update
private void useUpdateQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("update Programmer p set " +
                   "p.name = 'QQ 100' where p.name = 'QQ 999'");

   // �行Query的update需要transaction
   em.getTransaction().begin();
   q.executeUpdate();
   em.getTransaction().commit();
}
           
  • 使用Query�行delete
1.        JPQL的delete格式
DELETE entityName indentifierVariable
WHERE where_clause
           
2.        使用JPQL delete
private void useDeleteQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("delete Certification c where c.name like 'SCWCD%'");

   // �有transaction�
   // javax.persistence.TransactionRequiredException: Executing an update/delete query
   em.getTransaction().begin();
   q.executeUpdate();
   em.getTransaction().commit();
}
           
  • 替entity 命名
1.        @Entity可以命名, 如果�有替entity命名, 名�就�跟entity class name一�, 注意如果�有替entity的table命名, table名�也�跟entity name一�, 所以�用@Table�替entity命名的�, 一旦改�entity class name就�出�找不到table的exception
2.        在一�persistence unit中, entity name必�是唯一的, 否�在deploy�段就�出���
3.        下面��的named query�取得Certification的�料, �然entity name取得差很多
@Entity(name="Programmer2")
@Table(name="CERTIFICATION")
@NamedQueries({
   @NamedQuery(name=Certification.JPQL_FIND_ALL_CERT, query="from Certification as c")
})
public class Certification implements Serializable { ... }            
           
  • 使用identifier variable
1.        使用JPQL��用到如
FROM Certification AS c
, 其中的 c 就是identifier variable
2.        identifier variable不能是同�persistence unit的entity name也不能是JPQL的保留字
3.        JPQL有保留字如下
SELECT, UPDATE, DELETE, FROM, WHERE, GROUP, HAVING, ORDER, BY, ASC, DESC

JOIN, OUTER, INNER, LEFT, FETCH

DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS,
UNKNOWN, EMPTY, MEMBER, OF, IS, NEW, EXISTS, ALL, ANY, SOME

AVG, MAX, MIN, SUM, COUNT, MOD, UPPER, LOWER, TRIM, POSITION, CHARACTER_LENGTH,
CHAR_LENGTH, BIT_LENGTH, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
           
  • path expression
1.        from Programmer p where p.name = 'KK' 的 p.name就是一�path expression
2.        path expression就是identifier variable和persistence或相��位�用"."�起�
3.        用"."�起�的相���可以是一��一值(one-to-one, many-to-one)或一�collection(many-to-many, one-to-many).
4.        many-to-many, one-to-many的用法�
from Programmer p where p.certs is not empty
5.        single-value中one-to-one的用法就是直接在"."之後加上�位即�single-value
6.        � 法在many-to-one的情形下使用"."取得�位值, 比方�certificationCenter.sentCertifications.programmer, 因�sentCertifications是collection, �不知道要取得的programmer是哪一�而�生��
  • where clause
1.        使用where的�候�乎所有的java literal都可用, 但是八�和十六�位的�字不行, byte[] 或 char[]也不行.
  • Conditional�operator
1.        JPQL支援的operator如下
.

+, -

+, -, *, /

=, >, >=, <=, <>,
[not] bewteen, [not] like, [not] in, is [not] null, is [not] empty, [not] member [of]

NOT, AND, OR
           
2.        使用between的���性必�是同一�型�
private void useQueryBetween() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where c.createDate between ?0 and ?1");
   Calendar date1 = Calendar.getInstance(); date1.set(1998, 0, 1);
   Calendar date2 = Calendar.getInstance(); date2.set(2000, 0, 1);
   q.setParameter(0, date1);
   q.setParameter(1, date2);
   List< Certification > list = q.getResultList();

   q = em.createQuery("select count(c) from Certification c " +
                   "where c.createDate between ?0 and ?1");
   q.setParameter(0, date1);
   q.setParameter(1, date2);
   Long count = (Long) q.getSingleResult();

   System.out.println(Long.valueOf(list.size()).equals(count));  // true
}
           
3.        使用in�可用('A','B'), 就是把要判�的值放�括�中或是在括��放�sub query�. �一�query有subquery�, �先�行subquery再�行query
private void useQueryIn() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where" +
                   " c.name in ('SCJP 0', 'SCJP 1', 'SCJP 2')");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   q = em.createQuery("from Certification c " +
               "where c.name not in ('SCJP 0', 'SCJP 1', 'SCJP 2')");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   q = em.createQuery("from Certification c " +
               "where c.programmer in (from Programmer p where p.id > 600)");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName() +
                   ":" + certification.getProgrammer().getId());
   }

   q = em.createQuery("from Certification c where " +
               "c.name in (select c.name from c where c.id > 600)");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getId() + ":" + certification.getName());
   }
}
           
4.        使用like判�一��位是否符合某�字串格式, 可用底�(_)代表一�字或用百分�(%)代表任意��的字
private void useQueryLike() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where c.name like '%_P_9_'");
   List< Certification > list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where c.name not like '%_P_9_'");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }
}                
           
5.        JPQL定�的null和empty string是不同的, 但不是所有DB都��, 使用DB前要先����清楚.
�conditional expression遇到null�果�是 null 或 unknown.
不能用is null�判�一�collection是否�空collection. is null也不�判�一�collection是否�空.
�於collection, JPQL是透�empty�判�是否�空collection.
由於SQL中��有is empty���西, 所以JPQL的is empty其�是透�JOIN的方式去找看相��的entity collection是否�空.
TRUE  AND null = UNKNOWN
FALSE AND null = FALSE
Null  AND null = UNKNOWN
TRUE  OR  null = TRUE
Null  OR  null = UNKNOWN
FALSE OR  null = UNKNOWN
     NOT null = UNKNOWN
           
private void useQueryNull() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where c.name is null");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   q = em.createQuery("from Programmer p where p.certs is empty");
   List< Programmer > programmers = q.getResultList();
   for (Programmer programmer : programmers) {
       System.out.println(programmer.getName() + ":" + programmer.getCerts().size());
   }
}
           
6.        member的用法是
entity_expression [NOT] MEMBER [OF] collection_value_path_expression
, 如下所示
private void useQueryMember() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Certification c = em.find(Certification.class, Long.valueOf(114));
   Query q = em.createQuery("from Programmer p where ?0 member of p.certs");
   q.setParameter(0, c);
   List< Programmer > list = q.getResultList();
   for (Programmer programmer : list) {
       System.out.println(programmer.getCerts().contains(c));
   }
}
           
  • 使用JPQL中字串�理的function. 因�java�理字串比放到DB才�理更快, 所以建�在JPQL�理字串.
private void useJPQLStringFunction() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Certification c where " +
                   "CONCAT('scwcd ', '14') = LOWER(c.name) ");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // substring第一�字母index就是一, 不像java�0�始
   q = em.createQuery("from Certification c where " +
               "UPPER(SUBSTRING('abcscwcd 14123', 4, 8)) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(BOTH ' ' FROM '   scwcd 14  ')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(LEADING ' ' FROM '   scwcd 14')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(TRAILING ' ' FROM 'scwcd 14  ')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where " +
               "UPPER(TRIM(BOTH 'a' FROM 'aaaascwcd 14aaaaaaaaa')) = UPPER(c.name)");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   q = em.createQuery("from Certification c where LENGTH(c.name) = 8");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

   // LOCATE第一�字index也是1
   q = em.createQuery("from Certification c where LOCATE('wcd', LOWER(c.name)) = 3");
   list = q.getResultList();
   for (Certification cert : list) {
       System.out.println(cert.getName());
   }

}            
       
  • 使用JPQL的arithmetic function
1.        有ABS(arithmetic_expression), SQRT(arithmetic_expression), MOD(num, div), SIZE(collection_value_path_expression)
private void useJPQLArithmeticFunction() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // ABS ��值
   Query q = em.createQuery("from Certification c where ABS(c.id) = 100");
   List< Certification > list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // SQRT 平方根
   q = em.createQuery("from Certification c where SQRT(c.id) = 10");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // MOD ��
   q = em.createQuery("from Certification c where c.id = MOD(1100, 1000)");
   list = q.getResultList();
   for (Certification certification : list) {
       System.out.println(certification.getName());
   }

   // SIZE 量
   q = em.createQuery("from Programmer p where SIZE(p.certs) > 2");
   List< Programmer > programmers = q.getResultList();
   for (Programmer programmer : programmers) {
       System.out.println(programmer.getName());
   }
}                
           
  • 使用JPQL的temporal function
1.        有 CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, 不����法使用CURRENT_TIME
private void useJPQLTemporalFunction() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // �DB 取DATE
   Query q = em.createQuery("select distinct CURRENT_DATE from Certification");
   System.out.println(q.getSingleResult());

   // �法使用CURRENT_TIME, �有
   //java.sql.SQLException: ORA-00904: "CURRENT_TIME": invalid identifier
   q = em.createQuery("select distinct CURRENT_TIME from Certification");
   System.out.println(q.getSingleResult());

   // �DB取得TIMESTAMP
   q = em.createQuery("select distinct CURRENT_TIMESTAMP from Certification");
   System.out.println(q.getSingleResult());
}
           
  • 使用select
1.        select的�容可以用逗�分隔�一�以上的identifier variable 或 single-value path expression 或 aggregate functions.
private void useJPQLSelect() {
   EntityManager em = GeneralEAO.getEntityManager();
   Query q = null;

   // simple select clause
   q = em.createQuery("select p from Programmer p, Address a where a.employee.id = p.id");
   List< Programmer > simpleProgrammer = q.getResultList();
   for (Programmer programmer : simpleProgrammer) {
       System.out.println(programmer.getName());
   }

   // select distinct
   q = em.createQuery("select distinct(p) from Programmer p, " +
               "Address a where a.employee.id = p.id");
   List< Programmer > distinctProgrammer = q.getResultList();
   for (Programmer programmer : distinctProgrammer) {
       System.out.println(programmer.getName());
   }

   // select single property
   q = em.createQuery("select p.name from Programmer p where " +
               "p.payment = (select max(p.payment) from Programmer p)");
   List< String > oneProp = q.getResultList();
   for (String name : oneProp) {
       System.out.println(name);
   }

   // select two property
   q = em.createQuery("select p.name, p.payment from Programmer p " +
               "where p.payment = (select max(p.payment) from Programmer p)");
   List< Object[] > twoProp = q.getResultList();
   for (Object[] objects : twoProp) {
       String name = (String) objects[0];
       Double payment = (Double) objects[1];
       System.out.println(name + ":" + payment);
   }

   // select association entity
   q = em.createQuery("select n.owner.name, n from NoteBook n where " +
               "n.owner.payment = (select MAX(p.payment) from Programmer p)");
   List< Object[] > twoProp2 = q.getResultList();
   for (Object[] objects : twoProp2) {
       String name = (String) objects[0];
       NoteBook noteBook = (NoteBook) objects[1];
       System.out.println(name + ":" + noteBook.getMachineNumber() +
                   ":" + noteBook.getOwner().getName());
   }

   // �上好像�不能select collection的, 不����正常
   // select collection entities
   q = em.createQuery("select p.addressList from Programmer p");
   List< Address > addressList = q.getResultList();
   for (Address address : addressList) {
       System.out.println(address.getStreet());
   }

   // ��就�有�, 因�除了collection以外又加一�single-value
   try {
       q = em.createQuery("select p.name, p.addressList from Programmer p");
       List< Object > nameAddressList = q.getResultList();
       for (Object object : nameAddressList) {
           System.out.println(object);
       }
   } catch (Exception e) {
       System.out.println("select p.name, p.addressList has " +
               "exception because can't select single-value and collections." + e);
   }

   // new entity.StringObject�入的����不能是null, 而且要有�映的建�子. 型�也要一� 如下:
   // < code >
   //    public class StringObject {
   //    
   //    private String str1;
   //    private String str2;
   //    private Double double3;
   //
   //    public StringObject(String str1, String str2, Double double3) {
   // < /code >
   // ��的StringObject不用是entity也不用mapping到DB.
   q = em.createQuery("select new entity.StringObject(p.name, p.lang, p.payment)" +
               " from Programmer p");
   List< StringObject > stringObjectList = q.getResultList();
   for (StringObject stringObject : stringObjectList) {
       System.out.println(stringObject.getStr1());
       System.out.println(stringObject.getStr2());
       System.out.println(stringObject.getDouble3());
   }
   
   // Employee是abstract super class, 透�JPQL搜�可取得subclass
   List< Employee > employeeList = em.createQuery("from Employee").getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }
}                
           
  • 使用aggregate functions
AVG:   Return Double
COUNT: Return Long
MAX:   Return persistence field type
MIN:   Return persistence field type
SUM:   Return Long or Double        
       
1.        ���function中, 除了COUNT可以放任何type以外, 其他都只能放persistence field.
private void useAggregateFunctions() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   /* 若放AVG(p.name)或SUM(p.name) �
    * javax.persistence.PersistenceException:
    * org.hibernate.exception.SQLGrammarException:
    * could not execute query */
   Query q = em.createQuery(
       "select AVG(p.id), MAX(p.id), MIN(p.id), SUM(p.id), COUNT(p), " +
                         "MAX(p.name), MIN(p.name),        COUNT(p.name) " +
                           "from Employee p");
   Object[] o = (Object[]) q.getSingleResult();
   Double idAvg = (Double) o[0];
   Long   idMax = (Long) o[1];
   Long   idMin = (Long) o[2];
   Long   idSum = (Long) o[3];
   Long   idCnt = (Long) o[4];
   String nameMax = (String) o[5];
   String nameMin = (String) o[6];
   Long   nameCnt = (Long) o[7];
   System.out.println("avg:" + idAvg);
   System.out.println("idMax:" + idMax);
   System.out.println("idMin:" + idMin);
   System.out.println("idSum:" + idSum);
   System.out.println("idCnt:" + idCnt);
   System.out.println("nameMax:" + nameMax);
   System.out.println("nameMin:" + nameMin);
   System.out.println("nameCnt:" + nameCnt);
}            
  • 使用GROUP BY�HAVING
1.        使用group by的�候只允�aggregation functions.
2.        可用having再��
private void useGroupByAndHaving() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("select c.owner.name, COUNT(c.id) from " +
               "Certification c group by c.owner.name");
   List< Object[] > resultList = q.getResultList();
   for (Object[] result : resultList) {
       String ownerName = (String) result[0];
       Long count = (Long) result[1];
       System.out.println(ownerName + " has " + count + " certs.");
   }

   q = em.createQuery("select c.owner.name, COUNT(c.id) from " +
               "Certification c group by c.owner.name having COUNT(c.id) = 4");
   resultList = q.getResultList();
   for (Object[] result : resultList) {
       String ownerName = (String) result[0];
       Long count = (Long) result[1];
       System.out.println(ownerName + " has " + count + " certs.");
   }

   q = em.createQuery("select c.owner.name, COUNT(c.id) from " +
               "Certification c where c.id < 40 group by c.owner.name " +
               "having COUNT(c.id) = 4");
   resultList = q.getResultList();
   for (Object[] result : resultList) {
       String ownerName = (String) result[0];
       Long count = (Long) result[1];
       System.out.println(ownerName + " has " + count + " certs.");
   }
}          
       
  • 使用order by排序
1.        有asc, desc��
2.        �上����就是在order by中若使用path expression而不是只有identifier就必�在select clause中加上order by的�位.
比方�order by c.createDate, ��createDate就要包含在select子句中. 用Hibernate��的�候�����限制.
private void useOrderBy() {
   EntityManager em = EntityManagerHelper.getEntityManager();
   Query q = em.createQuery("from Sales s order by s.id desc, s.performance asc");
   List< Sales > list = q.getResultList();
   for (Sales sales : list) {
       System.out.println(sales.getId() + ":" + sales.getPerformance());
   }

   /* �上���是不�的, 因�order by s.id�包括在select clause中.
    * 不�Hibernate���可以 */
   q = em.createQuery("select s.performance from Sales s order by s.id desc");
   List< Double > performanceList = q.getResultList();
   for (Double performance : performanceList) {
       System.out.println(performance);
   }
}
       
  • 使用subquery
1.        subquery用在where或having中��遇取得的�果. 注意在from子句不能用subquery
2.        subquery可搭配ANY, SOME, ALL使用, ANY�SOME意思相同
private void useSubQuery() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // normal subquery
   Query q = em.createQuery("select c.owner from Certification c " +
               "where c.owner = (from Employee e where e.id = 9)");
   List< Employee > employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getName());
   }

   // IN : �查是否在subquery中
   q = em.createQuery("select c.owner from Certification c " +
               "where c.owner in (from Employee e where e.id < 50)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getName());
   }

   // EXISTS : �查是否有�料存在
   q = em.createQuery("select c.owner from Certification c " +
               "where exists (from Employee e where e.id = c.owner.id)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getName());
   }

   // ANY : 只要�任一�相比�符合即可. 可搭配 =, >, >=, <, <=, < >
   q = em.createQuery("select c.owner from Certification c " +
               "where c.owner.id > ANY(select e.id from Employee e where e.id < 20)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }

   // SOME : 只要�任一�相比�符合即可. 可搭配 =, >, >=, <, <=, < >
   q = em.createQuery("select c.owner from Certification c " +
               "where c.owner.id > SOME(select e.id from Employee e where e.id > 700)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }

   // ALL : 必��所有的�料比�相符. 可搭配 =, >, >=, <, <=, < >
   q = em.createQuery("select c.owner from Certification c " +
           "where c.owner.id > ALL(select e.id from Employee e where e.id < 700)");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee);
   }

}        
       
  • 使用join
1.        theta-join : �然是�有constraint的�位, 但因�在意�上是有��的, 所以也拿�作select.
2.        relationship join : 就是用entity�的��join. 可以把完全吻合的entity都找出�, 若相��的entity�空就不算match
3.        inner join的�法是 : [INNER] JOIN join_association_path_expression [AS] identification_variable
4.        outer join和inner join不一�的就是不一定�match所有�件, 就是即使相��的entity�空也算match.
5.        inner [outer] join fetch 可取得原本��lazy的相��性
private void useJoin() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // theta-join
   Query q = em.createQuery("select ae.name from " +
           "AnotherEmployee ae, Certification c where ae.id = c.owner.id group by ae.name");
   List< String > nameList = q.getResultList();
   for (String name : nameList) {
       System.out.println(name);
   }

   // inner join . "inner"　is optional.
   q = em.createQuery("select e from Employee e inner join e.noteBook");
   List< Employee > employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() +
               " noteBook is null: " + (employee.getNoteBook() == null));
   }

   // inner join. "inner" and "as" is optional
   q = em.createQuery("select n from Employee e inner join e.noteBook as n");
   List< NoteBook > noteBookList = q.getResultList();
   for (NoteBook noteBook : noteBookList) {
       System.out.println(noteBook.getOwner().getId());
   }

   // outer join.
   q = em.createQuery("select e from Employee e left outer join e.noteBook");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() +
               " noteBook is null: " + (employee.getNoteBook() == null) );
   }

   /* both join �有java.lang.IllegalArgumentException:
    * org.hibernate.hql.ast.QuerySyntaxException:
    * unexpected token: both near line 1, column 33
    * [select e from entity.Employee e both join e.noteBook] */
   q = em.createQuery("select e from Employee e both join e.noteBook");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() + " noteBook is null: " +
               (employee.getNoteBook() == null) );
   }

   /**
    * right outer join 有 java.lang.UnsupportedOperationException:
    * join type not supported by OracleJoinFragment (use Oracle9Dialect)
    */
   q = em.createQuery("select e from Employee e right outer join e.noteBook");
   employeeList = q.getResultList();
   for (Employee employee : employeeList) {
       System.out.println(employee.getId() + " noteBook is null: " +
               (employee.getNoteBook() == null) );
   }

   /**
    * 用join fetch可直接透�Query取得lazy的相�型�
    * reference: http://edocs.bea.com/kodo/docs41/full/html/ejb3_langref.html
    *                       #ejb3_langref_fetch_joins
    */
   q = em.createQuery("select e from Employee e inner join fetch e.certs");
   employeeList = q.getResultList();
   em.clear(); // ��才可以知的detach
   for (Employee employee : employeeList) {
       System.out.println(employee.getCerts());
   }

}
       
  • 使用update和delete
1.        update, delete一定要在transaction中
2.        update, delete也可以加��
3.        建�把update, delete�立於平常的操作, 因�Query是否�persistence context是由provider�定, JPA��定要同步.
    private void useUpdateDelete() {
       EntityManager em = EntityManagerHelper.getEntityManager();
       Query q = em.createQuery("update Programmer p set p.lang = :lang");
       q.setParameter("lang", "Java");
       
       /**
        * �transaction�有javax.persistence.TransactionRequiredException:
        * Executing an update/delete query
        */
       em.getTransaction().begin();
       System.out.println("update..." + q.executeUpdate() + " records");
       em.getTransaction().commit();
       
       q = em.createQuery("delete NoteBook n where n.id < :lowID");
       q.setParameter("lowID", Long.valueOf(200));
       em.getTransaction().begin();
       System.out.println("delete..." + q.executeUpdate() + " records");
       em.getTransaction().commit();
   }            
       
  • 使用Native SQL
1.        �量不要用Native SQL, 因�不portable.
2.        和delete, update一�, 由於��定要和persistence context同步, 因此最好把Query操作和一般的entity操作隔��.
3.        透�JPQL和Native SQL取得的Query在使用上�有不同. 在使用named query上也�有不同, 只是native sql的named query�有要求provider一定支援
4.        JPA不支援store procedure
private void useNativeSQL() {
   EntityManager em = EntityManagerHelper.getEntityManager();

   // simple native sql
   Query q = em.createNativeQuery("select e.EMPLOYEE_ID, e.EMPLOYEE_TYPE, " +
               "e.NAME from EMPLOYEE e");
   List< Object[] > objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       BigDecimal id = (BigDecimal) objAry[0];
       String type = (String) objAry[1];
       String name = (String) objAry[2];
       System.out.println(id + ":" + type + ":" + name);
   }

   // native sql with parameters
   q = em.createNativeQuery("select e.EMPLOYEE_ID, e.EMPLOYEE_TYPE, e.NAME " +
               "from EMPLOYEE e where e.EMPLOYEE_ID = :id");
   q.setParameter("id", Long.valueOf(1));
   objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       BigDecimal id = (BigDecimal) objAry[0];
       String type = (String) objAry[1];
       String name = (String) objAry[2];
       System.out.println(id + ":" + type + ":" + name);
   }

   // 指定所�的class, 注意�然SQL只�要select EMPLOYEE_ID, 透�Query�可取出整�owner.
   // 不���方式�有一次只能指定一�entity的��
   q = em.createNativeQuery("select NOTE_BOOK_ID, SERIAL_NO, EMPLOYEE_ID " +
               "from NOTE_BOOK where NOTE_BOOK_ID = 200", NoteBook.class);
   List< NoteBook > noteBookList = q.getResultList();
   for (NoteBook noteBook : noteBookList) {
       System.out.println(noteBook.getId() + ":" + noteBook.getSerialNO() +
               ":" + noteBook.getOwner());
   }

   /**
    * 透�指定SqlResultSetMapping即可同�用native sql取得��entity
    *  @SqlResultSetMapping(name="allNoteBooksAndCertifications",
    *      entities={
    *          @EntityResult(entityClass=NoteBook.class),
    *          @EntityResult(entityClass=Certification.class)})
    */
   q = em.createNativeQuery("select * from NOTE_BOOK n, CERTIFICATION c " +
           "where n.EMPLOYEE_ID = c.EMPLOYEE_ID and c.CERTIFICATION_ID = 202"
           , "allNoteBooksAndCertifications");
   objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       NoteBook noteBook = (NoteBook) objAry[0];
       Certification cert = (Certification) objAry[1];
       System.out.println(noteBook);
       System.out.println(cert);
   }

   /**
    * 透�在NoteBook上�定named query
    * @NamedNativeQueries({
    *      @NamedNativeQuery(name="all204NoteBook",
    *          query="select * from NOTE_BOOK where NOTE_BOOK_ID = 204",
    *          resultClass=NoteBook.class),
    *      @NamedNativeQuery(name="204NoteBooksAnd202Certifications",
    *          query="select * from NOTE_BOOK n, CERTIFICATION c " +
    *              "where n.EMPLOYEE_ID = c.EMPLOYEE_ID" +
    *              " and c.CERTIFICATION_ID = 202",
    *          resultSetMapping="allNoteBooksAndCertifications")
    * })
    */
   q = em.createNamedQuery("all204NoteBook");
   noteBookList = q.getResultList();
   for (NoteBook noteBook : noteBookList) {
       System.out.println(noteBook);
   }
   q = em.createNamedQuery("204NoteBooksAnd202Certifications");
   objAryList = q.getResultList();
   for (Object[] objAry : objAryList) {
       NoteBook noteBook = (NoteBook) objAry[0];
       Certification cert = (Certification) objAry[1];
       System.out.println(noteBook);
       System.out.println(cert);
   }

}            
       
发帖者 时间：

没有评论:

发表评论

订阅： 博文评论 (Atom)