OpenJPQL详细解读.doc

2008-03-11OpenJPA关键字: jpa 6 Query61 JPQL Queries611Query BasicsJava代码 1. publicQuerycreateQuery(Stringjpql);public Query createQuery(String jpql); EntityManager上的这个方法用来根据给定的JPQL创建Query。Java代码 1. publicListgetResultList();public List getResultList(); Query上的这个方法用来得到query的执行结果，以下是个简单的例子：Java代码 1. EntityManagerem=entityManagerFactory.createEntityManager(); 2. Queryq=em.createQuery(SELECTxFROMMagazinex); 3. Listresults=(List)q.getResultList(); 4. em.close();EntityManager em = entityManagerFactory.createEntityManager(); Query q = em.createQuery(SELECT x FROM Magazine x);List results = (List) q.getResultList();em.close(); JPQL query的from子句中定义了query内部命名空间。可以将任意的标识符赋值给entities，之后就可以在query的任意位置上通过标识符引用entities。 在from子句中，关键字as是可选的。例如以下两个JPQL等效： Sql代码 1. SELECTxFROMMagazinex 2. SELECTxFROMMagazineASxSELECT x FROM Magazine x SELECT x FROM Magazine AS x 当查询entities的时候，关键字object也是可选的，例如select x 和 SELECT OBJECT(x) 是等效的。JPQL中的关键字不是大小写敏感的，而entity、identifier和member names是大小写敏感的。以下是一些JPQL的例子：Sql代码 1. SELECTxFROMMagazinexWHEREx.TITLE=JDJ2. SELECTxFROMMagazinexWHEREx.title=JDJORx.title=JavaPro3. SELECTxFROMMagazinexWHEREx.price3.00ANDx.price=5.00 4. SELECTxFROMMagazinexWHEREx.price3.00 5. SELECTxFROMMagazinexWHERE(x.price3.00ANDx.price3.00AND(x.price=3.00ANDx.price 3.00 AND x.price = 5.00SELECT x FROM Magazine x WHERE x.price 3.00SELECT x FROM Magazine x WHERE (x.price 3.00 AND x.price 3.00 AND (x.price = 3.00 AND x.price = 5.00SELECT x FROM Magazine x WHERE x.price BETWEEN 3.00 AND 5.00SELECT x FROM Magazine x WHERE x.title LIKE J%SELECT x FROM Magazine x WHERE x.title LIKE J_SELECT x FROM Magazine x WHERE x.title IN (JDJ, JavaPro, IT Insider)SELECT x FROM Magazine x WHERE x.articles is emptySELECT x FROM Magazine x WHERE x.publisher is nullSELECT x FROM Magazine x WHERE NOT(x.price = 10.0)612 Relation Traversal 可以通过类似Java的语法来遍历对象间的关系。例如Magazine中有个Publisher类型的属性publisher，那么可以通过以下方式编写JPQL query： Sql代码 1. SELECTxFROMMagazinexWHERE=RandomHouseSELECT x FROM Magazine x WHERE = Random House 以上的遍历中假设关系不是null。在SQL术语中类似于inner join。如果你希望包含为null 的关系，那么可以如下指定： Sql代码 1. SELECTxFROMMagazinexWHERE=RandomHouseorx.publisherisnullSELECT x FROM Magazine x WHERE = Random House or x.publisher is null 也可以在query中遍历集合字段，但是必须首先在from子句中定义遍历相关的identification variable，例如： Sql代码 1. SELECTxFROMMagazinex,IN(x.articles)yWHEREy.authorName=JohnDoeSELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = John Doe IN() 类型的语法可以使用inner join关键词，例如以下两个JPQL等效： Sql代码 1. SELECTxFROMMagazinex,IN(x.articles)yWHEREy.authorName=JohnDoe 2. SELECTxFROMMagazinexinnerjoinx.articlesyWHEREy.authorName=JohnDoeSELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = John Doe SELECT x FROM Magazine x inner join x.articles y WHERE y.authorName = John Doe 613 Fetch Joins JPQL query中可以指定一个或多个join fetch来指定哪些字段被pre-fetched，以561中的Publisher和Magazine为例： Java代码 1. em=entityManagerFactory.createEntityManager(); 2. Queryq2=em.createQuery(SELECTxFROMPublisherxWHEREx.id=1); 3. Listr2=(List)q2.getResultList(); 4. em.close(); 5. for(Iteratoriter=r2.iterator();iter.hasNext();) 6. System.out.println(iter.next().toString(); 7. em = entityManagerFactory.createEntityManager();Query q2 = em.createQuery(SELECT x FROM Publisher x WHERE x.id = 1);List r2 = (List)q2.getResultList();em.close();for(Iterator iter = r2.iterator(); iter.hasNext(); ) System.out.println(iter.next().toString(); 以上代码执行后的输出是： id: 1, name: publisher1, grade: null, magazines Java代码 1. em=entityManagerFactory.createEntityManager(); 2. Queryq3=em.createQuery(SELECTxFROMPublisherxjoinfetchx.gradejoinfetchx.magazinesWHEREx.id=1); 3. Listr3=(List)q3.getResultList(); 4. em.close(); 5. for(Iteratoriter=r3.iterator();iter.hasNext();) 6. System.out.println(iter.next().toString(); 7. em = entityManagerFactory.createEntityManager();Query q3 = em.createQuery(SELECT x FROM Publisher x join fetch x.grade join fetch x.magazines WHERE x.id = 1);List r3 = (List)q3.getResultList();em.close();for(Iterator iter = r3.iterator(); iter.hasNext(); ) System.out.println(iter.next().toString(); 以上代码执行后的输出是： id: 1, name: publisher1, grade: excellent, magazinesisbn: isbn1, title: title1; isbn: isbn1, title: title1; isbn: isbn2, title: title2; isbn: isbn2, title: title2 614 JPQL Functions JPQL 支持一系列预定义的标量函数，例如： Sql代码 1. SELECTxFROMMagazinexWHERECONCAT(x.title,s)=JDJs2. SELECTxFROMMagazinexWHERESUBSTRING(x.title,1,1)=J3. SELECTxFROMMagazinexWHERETRIM(BOTHJFROMx.title)=D4. SELECTxFROMMagazinexWHERELOWER(x.title)=jdj5. SELECTxFROMMagazinexWHEREUPPER(x.title)=JAVAPRO6. SELECTxFROMMagazinexWHERELENGTH(x.title)=3 7. SELECTxFROMMagazinexWHERELOCATE(D,x.title)=2 8. SELECTxFROMMagazinexWHEREABS(x.price)=5.00 9. SELECTxFROMMagazinexWHERESQRT(x.price)=1.00 10. SELECTxFROMMagazinexWHEREMOD(x.price,10)=0SELECT x FROM Magazine x WHERE CONCAT(x.title, s) = JDJsSELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = JSELECT x FROM Magazine x WHERE TRIM(BOTH J FROM x.title) = DSELECT x FROM Magazine x WHERE LOWER(x.title) = jdjSELECT x FROM Magazine x WHERE UPPER(x.title) = JAVAPROSELECT x FROM Magazine x WHERE LENGTH(x.title) = 3SELECT x FROM Magazine x WHERE LOCATE(D, x.title) = 2SELECT x FROM Magazine x WHERE ABS(x.price) = 5.00SELECT x FROM Magazine x WHERE SQRT(x.price) = 1.00SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0615 Polymorphic Queries JPQL from子句中的entity class不仅包含这个类本身，而且还包含这个类及其子类。以下是个关于polymorphic query的简单例子： Java代码 1. Entity2. Inheritance(strategy=InheritanceType.SINGLE_TABLE) 3. DiscriminatorColumn(name=Class,discriminatorType=DiscriminatorType.STRING) 4. publicabstractclassBankingAccount 5. Id6. GeneratedValue(strategy=GenerationType.IDENTITY) 7. protectedintid; 8. 9. 10. Entity11. DiscriminatorValue(CC) 12. publicclassCreditCardextendsBankingAccount 13. Basic14. privateBigIntegerlimitAmount; 15. 16. publicStringtoString() 17. StringBuffersb=newStringBuffer(); 18. sb.append(creditcard,limitamount:).append(limitAmount); 19. returnsb.toString(); 20. 21. 22. 23. Entity24. DiscriminatorValue(DC) 25. publicclassDebitCardextendsBankingAccount 26. Basic27. privateBigIntegerbalanceAmount; 28. 29. publicStringtoString() 30. StringBuffersb=newStringBuffer(); 31. sb.append(debitcard,balanceamount:).append(balanceAmount); 32. returnsb.toString(); 33. 34. EntityInheritance(strategy=InheritanceType.SINGLE_TABLE)DiscriminatorColumn(name=Class, discriminatorType=DiscriminatorType.STRING)public abstract class BankingAccount IdGeneratedValue(strategy = GenerationType.IDENTITY)protected int id;EntityDiscriminatorValue(CC)public class CreditCard extends BankingAccount Basicprivate BigInteger limitAmount;public String toString() StringBuffer sb = new StringBuffer();sb.append(credit card, limit amount: ).append(limitAmount);return sb.toString();EntityDiscriminatorValue(DC)public class DebitCard extends BankingAccount Basicprivate BigInteger balanceAmount;public String toString() StringBuffer sb = new StringBuffer();sb.append(debit card, balance amount: ).append(balanceAmount);return sb.toString(); 假设目前数据库中的数据如下: Sql代码 1. select*frombankingaccount; 2. +-+-+-+-+ 3. |id|Class|limitAmount|balanceAmount| 4. +-+-+-+-+ 5. |5|CC|1000|NULL| 6. |6|DC|NULL|2000| 7. +-+-+-+-+select * from bankingaccount;+-+-+-+-+| id | Class | limitAmount | balanceAmount |+-+-+-+-+| 5 | CC | 1000 | NULL | 6 | DC | NULL | 2000 |+-+-+-+-+Java代码 1. EntityManagerem=entityManagerFactory.createEntityManager(); 2. Queryq1=em.createQuery(SELECTxFROMBankingAccountx); 3. Listr1=(List)q1.getResultList(); 4. for(Iteratoriter=r1.iterator();iter.hasNext();) 5. System.out.println(iter.next().toString(); 6. 7. em.close();EntityManager em = entityManagerFactory.createEntityManager();Query q1 = em.createQuery(SELECT x FROM BankingAccount x);List r1 = (List)q1.getResultList();for(Iterator iter = r1.iterator(); iter.hasNext(); ) System.out.println(iter.next().toString();em.close(); 以上代码的执行结果如下： credit card, limit amount: 1000 debit card, balance amount: 2000 616 Query Parameters JPQL支持两种类型的query参数：named parameters 或 positional parameters。在一个JPQL query中不能同时使用两种类型的参数。以下是两个简单的例子： Java代码 1. Queryq=em.createQuery(SELECTxFROMMagazinexWHEREx.title=?1andx.price?2); 2. q.setParameter(1,JDJ).setParameter(2,5.0); 3. Listresults=(List)q.getResultList(); 4. 5. Queryq=em.createQuery(SELECTxFROMMagazinexWHEREx.title=:titleParamandx.price:priceParam); 6. q.setParameter(titleParam,JDJ).setParameter(priceParam,5.0); 7. Listresults=(List)q.getResultList();Query q = em.createQuery(SELECT x FROM Magazine x WHERE x.title = ?1 and x.price ?2);q.setParameter(1, JDJ).setParameter(2, 5.0);List results = (List) q.getResultList();Query q = em.createQuery(SELECT x FROM Magazine x WHERE x.title = :titleParam and x.price :priceParam);q.setParameter(titleParam, JDJ).setParameter(priceParam, 5.0);List results = (List) q.getResultList();617 Query Hints JPQL支持通过name/value对来指定query hints。例如： Java代码 1. Queryq=em.createQuery(selectmfromMagazinemwhere.); 2. q.setHint(openjpa.hint.OptimizeResultCount,newInteger(2); 3. q.setHint(openjpa.FetchPlan.ReadLockMode,WRITE); 4. Listr=q.getResultList();Query q = em.createQuery(select m from Magazine m where . );q.setHint(openjpa.hint.OptimizeResultCount, new Integer(2);q.setHint(openjpa.FetchPlan.ReadLockMode,WRITE);List r = q.getResultList();618 Ordering JPQL支持根据一个或者多个字段对查询结果进行排序，例如：Sql代码 1. SELECTxFROMMagazinexorderbyx.titleasc,x.pricedescSELECT x FROM Magazine x order by x.title asc, x.price desc619 Aggregates JPQL支持min、max、avg和count等聚集函数（也被称为列函数）。以下是一些例子：Java代码 1. Queryq=em.createQuery(SELECTAVG(x.price)FROMMagazinex); 2. Queryq=em.createQuery(SELECTSUM(DISTINCTx.price)FROMMagazinex); 3. Queryq=em.createQuery(SELECTMAX(x.price)FROMMagazinexWHEREx.title=JDJ);Query q = em.createQuery(SELECT AVG(x.price) FROM Magazine x);Query q = em.createQuery(SELECT SUM(DISTINCT x.price) FROM Magazine x);Query q = em.createQuery(SELECT MAX(x.price) FROM Magazine x WHERE x.title = JDJ);6110 Named Queries JPQL支持通过NamedQuery 和NamedQueries 这两个annotation来声明命名query。以下是一个例子：Java代码 1. Entity2. NamedQueries( 3. NamedQuery(name=magsOverPrice, 4. query=SELECTxFROMMagazinexWHEREx.price?1), 5. NamedQuery(name=magsByTitle, 6. query=SELECTxFROMMagazinexWHEREx.title=:titleParam) 7. ) 8. publicclassMagazine 9. . 10. 11. 12. Queryq=em.createNamedQuery(magsOverPrice); 13. q.setParameter(1,5.0f); 14. Listresults=(List)q.getResultList(); 15. 16. Queryq=em.createNamedQuery(magsByTitle); 17. q.setParameter(titleParam,JDJ); 18. Listresults=(List)q.getResultList();EntityNamedQueries( NamedQuery(name=magsOverPrice, query=SELECT x FROM Magazine x WHERE x.price ?1), NamedQuery(name=magsByTitle, query=SELECT x FROM Magazine x WHERE x.title = :titleParam)public class Magazine .Query q = em.createNamedQuery(magsOverPrice);q.setParameter(1, 5.0f);List results = (List) q.getResultList();Query q = em.createNamedQuery(magsByTitle);q.setParameter(titleParam, JDJ);List results = (List) q.getResultList();6111 Delete By Query Query可以用来高效地删除对象（不必将每个对象查询之后再进行删除，而是直接进行批量删除）。以下是个简单的例子： Java代码 1. Queryq=em.createQuery(DELETEFROMSubscriptionsWHEREs.subscriptionDate:today); 2. q.setParameter(today,newDate(); 3. intdeleted=q.executeUpdate();Query q = em.createQuery(DELETE FROM Subscription s WHERE s.subscriptionDate :today);q.setParameter(today, new Date();int deleted = q.executeUpdate(); executeUpdate方法的返回值是删除对象的个数。 6112 Update By Query 跟delete类似，Query也可以用来高效地更新对象。以下是个简单的例子： Java代码 1. Queryq=em.createQuery(UPDATESubscriptionsSETs.paid=:paidWHEREs.subscriptionDate:today); 2. q.setParameter(today,newDate(); 3. q.setParameter(paid,true); 4. intupdated=q.executeUpdate();Query q = em.createQuery(UPDATE Subscription s SET s.paid = :paid WHERE s.subscriptionDate =ALL(SELECTa.salaryFROMAuthoraWHEREa.magazine=auth.magazine) 3. SELECTgoodPublisherFROMPublishergoodPublisherWHEREgoodPublisher.revenue10SELECT DISTINCT auth FROM Author auth WHERE EXISTS (SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse)SELECT auth FROM Author auth WHERE auth.salary = ALL(SELECT a.salary FROM Author a WHERE a.magazine = auth.magazine)SELECT goodPublisher FROM Publisher goodPublisher WHERE goodPublisher.revenue 106114 JPQL Constructor Expressions 在SELECT子句中可以通过使用constructor来返回一个或多个实例。指定的类不必是entity class，例如：Sql代码 1. SELECTNEWpany.PublisherInfo(pub.id,pub.revenue,mag.price)FROMPublisherpubJOINpub.magazinesmagWHEREmag.price5.00SELECT NEW pany.PublisherInfo(pub.id, pub.revenue, mag.price) FROM Publisher pub JOIN pub.magazines mag WHERE mag.price 5.0062 SQL Queries JPA支持通过Query接口的以下两个方法使用SQL查询：Java代码 1. publicQuerycreateNativeQuery(StringsqlString,ClassresultClass); 2. publicQuerycreateNativeQuery(StringsqlString,StringresultSetMapping);public Query createNativeQuery(String sqlString, Class resultClass);public Query createNativeQuery(String sqlString, String resultSetMapping); OpenJPA也支持使用存储过程。OpenJPA假定任何不以SELECT开头的SQL为存储过程的调用。 在指定resultClass的情况下，sqlString必须查询指定resultClass的 primary key 列、 discriminator 列 (如果存在) 和version column (如果存在)。JPA使用以上三个列的信息来判断object identity、区分查询子类和检查并发修改。 以下是个简单的例子： Java代码 1. Queryquery=em.createNativeQuery(SELECTisbn,title,price,versFROMMagazineWHEREprice5ANDprice10,Magazine.class); 2. Listresults=(List)query.getResultList(); 3. for(Magazinemag:results) 4. processMagazine(mag);Query query = em.createNativeQuery(SELECT isbn, title, price, vers FROM Magazine WHERE price 5 AND price 10, Magazine.class);List results = (List) query.getResultList();for (Magazine mag : results) processMagazine(mag); 在指定resultSetMapping的情况下，resultSetMap