Query

6 Query
6．1 JPQL Queries
6．1．1Query Basics

Java代码
public Query createQuery(String jpql); 

public Query createQuery(String jpql);    EntityManager上的这个方法用来根据给定的JPQL创建Query。

Java代码
public List getResultList(); 

public List getResultList();   Query上的这个方法用来得到query的执行结果，以下是个简单的例子：

Java代码
EntityManager em = entityManagerFactory.createEntityManager();   
Query q = em.createQuery("SELECT x FROM Magazine x");  
List<Magazine> results = (List<Magazine>) q.getResultList();  
em.close(); 

EntityManager em = entityManagerFactory.createEntityManager();
Query q = em.createQuery("SELECT x FROM Magazine x");
List<Magazine> results = (List<Magazine>) q.getResultList();
em.close();    JPQL query的from子句中定义了query内部命名空间。可以将任意的标识符赋值给entities，之后就可以在query的任意位置上通过标识符引用entities。 在from子句中，关键字as是可选的。例如以下两个JPQL等效：Sql代码
SELECT x FROM Magazine x   
SELECT x FROM Magazine AS x 

SELECT x FROM Magazine x
SELECT x FROM Magazine AS x    当查询entities的时候，关键字object也是可选的，例如select x 和 SELECT OBJECT(x) 是等效的。JPQL中的关键字不是大小写敏感的，而entity、identifier和member names是大小写敏感的。以下是一些JPQL的例子：

Sql代码
SELECT x FROM Magazine x WHERE x.TITLE = 'JDJ' 
SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro' 
SELECT x FROM Magazine x WHERE x.price > 3.00 AND x.price <= 5.00  
SELECT x FROM Magazine x WHERE x.price <> 3.00  
SELECT x FROM Magazine x WHERE (x.price > 3.00 AND x.price <= 5.00) OR x.price = 7.00  
SELECT x FROM Magazine x WHERE x.price > 3.00 AND (x.price <= 5.00 OR x.price = 7.00)  
SELECT x FROM Magazine x WHERE x.price >= 3.00 AND x.price <= 5.00  
SELECT x FROM Magazine x WHERE x.price BETWEEN 3.00 AND 5.00  
SELECT 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 empty  
SELECT x FROM Magazine x WHERE x.publisher is null 
SELECT x FROM Magazine x WHERE NOT(x.price = 10.0) 

SELECT x FROM Magazine x WHERE x.TITLE = 'JDJ'
SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro'
SELECT x FROM Magazine x WHERE x.price > 3.00 AND x.price <= 5.00
SELECT x FROM Magazine x WHERE x.price <> 3.00
SELECT x FROM Magazine x WHERE (x.price > 3.00 AND x.price <= 5.00) OR x.price = 7.00
SELECT x FROM Magazine x WHERE x.price > 3.00 AND (x.price <= 5.00 OR x.price = 7.00)
SELECT x FROM Magazine x WHERE x.price >= 3.00 AND x.price <= 5.00
SELECT x FROM Magazine x WHERE x.price BETWEEN 3.00 AND 5.00
SELECT 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 empty
SELECT x FROM Magazine x WHERE x.publisher is null
SELECT x FROM Magazine x WHERE NOT(x.price = 10.0)

6．1．2 Relation Traversal
    可以通过类似Java的语法来遍历对象间的关系。例如Magazine中有个Publisher类型的属性publisher，那么可以通过以下方式编写JPQL query：

Sql代码
SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' 

SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House'    以上的遍历中假设关系不是null。在SQL术语中类似于inner join。如果你希望包含为null 的关系，那么可以如下指定： Sql代码
SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' or x.publisher is null 

SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' or x.publisher is null    也可以在query中遍历集合字段，但是必须首先在from子句中定义遍历相关的identification variable，例如： Sql代码
SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe' 

SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe'    IN() 类型的语法可以使用inner join关键词，例如以下两个JPQL等效： Sql代码
SELECT 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'  

SELECT 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' 

6．1．3 Fetch Joins
    JPQL query中可以指定一个或多个join fetch来指定哪些字段被pre-fetched，以5．6．1中的Publisher和Magazine为例：

Java代码
em = entityManagerFactory.createEntityManager();  
Query q2 = em.createQuery("SELECT x FROM Publisher x WHERE x.id = 1");  
List<Publisher> r2 = (List<Publisher>)q2.getResultList();  
em.close();  
for(Iterator<Publisher> iter = r2.iterator(); iter.hasNext(); ) {  
    System.out.println(iter.next().toString());  
} 

em = entityManagerFactory.createEntityManager();
Query q2 = em.createQuery("SELECT x FROM Publisher x WHERE x.id = 1");
List<Publisher> r2 = (List<Publisher>)q2.getResultList();
em.close();
for(Iterator<Publisher> iter = r2.iterator(); iter.hasNext(); ) {
System.out.println(iter.next().toString());
}    以上代码执行后的输出是：
    id: 1, name: publisher1, grade: null, magazines[]
Java代码
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<Publisher> r3 = (List<Publisher>)q3.getResultList();  
em.close();  
for(Iterator<Publisher> iter = r3.iterator(); iter.hasNext(); ) {  
    System.out.println(iter.next().toString());  
} 

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<Publisher> r3 = (List<Publisher>)q3.getResultList();
em.close();
for(Iterator<Publisher> iter = r3.iterator(); iter.hasNext(); ) {
System.out.println(iter.next().toString());
}    以上代码执行后的输出是：
    id: 1, name: publisher1, grade: excellent, magazines[isbn: isbn1, title: title1; isbn: isbn1, title: title1; isbn: isbn2, title: title2; isbn: isbn2, title: title2]



6．1．4 JPQL Functions
    JPQL 支持一系列预定义的标量函数，例如：

Sql代码
SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs' 
SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J' 
SELECT x FROM Magazine x WHERE TRIM(BOTH 'J' FROM x.title) = 'D' 
SELECT x FROM Magazine x WHERE LOWER(x.title) = 'jdj' 
SELECT x FROM Magazine x WHERE UPPER(x.title) = 'JAVAPRO' 
SELECT x FROM Magazine x WHERE LENGTH(x.title) = 3  
SELECT x FROM Magazine x WHERE LOCATE('D', x.title) = 2  
SELECT x FROM Magazine x WHERE ABS(x.price) >= 5.00  
SELECT x FROM Magazine x WHERE SQRT(x.price) >= 1.00  
SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0 

SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'
SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'
SELECT x FROM Magazine x WHERE TRIM(BOTH 'J' FROM x.title) = 'D'
SELECT x FROM Magazine x WHERE LOWER(x.title) = 'jdj'
SELECT x FROM Magazine x WHERE UPPER(x.title) = 'JAVAPRO'
SELECT x FROM Magazine x WHERE LENGTH(x.title) = 3
SELECT x FROM Magazine x WHERE LOCATE('D', x.title) = 2
SELECT x FROM Magazine x WHERE ABS(x.price) >= 5.00
SELECT x FROM Magazine x WHERE SQRT(x.price) >= 1.00
SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0

6．1．5 Polymorphic Queries
    JPQL from子句中的entity class不仅包含这个类本身，而且还包含这个类及其子类。以下是个关于polymorphic query的简单例子：

Java代码
@Entity 
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)  
@DiscriminatorColumn(name="Class", discriminatorType=DiscriminatorType.STRING)  
public abstract class BankingAccount {  
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    protected int id;  
}  
 
@Entity 
@DiscriminatorValue("CC")  
public class CreditCard extends BankingAccount {  
    @Basic 
    private BigInteger limitAmount;  
 
    public String toString() {  
        StringBuffer sb = new StringBuffer();  
        sb.append("credit card, limit amount: ").append(limitAmount);  
        return sb.toString();  
    }  
}  
 
@Entity 
@DiscriminatorValue("DC")  
public class DebitCard extends BankingAccount {  
    @Basic 
    private BigInteger balanceAmount;  
 
    public String toString() {  
        StringBuffer sb = new StringBuffer();  
        sb.append("debit card, balance amount: ").append(balanceAmount);  
        return sb.toString();  
    }  
} 

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="Class", discriminatorType=DiscriminatorType.STRING)
public abstract class BankingAccount {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
protected int id;
}

@Entity
@DiscriminatorValue("CC")
public class CreditCard extends BankingAccount {
@Basic
private BigInteger limitAmount;

public String toString() {
StringBuffer sb = new StringBuffer();
sb.append("credit card, limit amount: ").append(limitAmount);
return sb.toString();
}
}

@Entity
@DiscriminatorValue("DC")
public class DebitCard extends BankingAccount {
@Basic
private BigInteger balanceAmount;

public String toString() {
StringBuffer sb = new StringBuffer();
sb.append("debit card, balance amount: ").append(balanceAmount);
return sb.toString();
}
}    假设目前数据库中的数据如下:
   
Sql代码
select * from bankingaccount;  
+----+-------+-------------+---------------+  
| id | Class | limitAmount | balanceAmount |  
+----+-------+-------------+---------------+  
|  5 | CC    |        1000 |          NULL |  
|  6 | DC    |        NULL |          2000 |  
+----+-------+-------------+---------------+ 

select * from bankingaccount;
+----+-------+-------------+---------------+
| id | Class | limitAmount | balanceAmount |
+----+-------+-------------+---------------+
|  5 | CC    |        1000 |          NULL |
|  6 | DC    |        NULL |          2000 |
+----+-------+-------------+---------------+   
Java代码
EntityManager em = entityManagerFactory.createEntityManager();  
Query q1 = em.createQuery("SELECT x FROM BankingAccount x");  
List<BankingAccount> r1 = (List<BankingAccount>)q1.getResultList();  
for(Iterator<BankingAccount> iter = r1.iterator(); iter.hasNext(); ) {  
    System.out.println(iter.next().toString());  
}  
em.close(); 

EntityManager em = entityManagerFactory.createEntityManager();
Query q1 = em.createQuery("SELECT x FROM BankingAccount x");
List<BankingAccount> r1 = (List<BankingAccount>)q1.getResultList();
for(Iterator<BankingAccount> iter = r1.iterator(); iter.hasNext(); ) {
System.out.println(iter.next().toString());
}
em.close();    以上代码的执行结果如下：
    credit card, limit amount: 1000
    debit card, balance amount: 2000 


6．1．6 Query Parameters
    JPQL支持两种类型的query参数：named parameters 或 positional parameters。在一个JPQL query中不能同时使用两种类型的参数。以下是两个简单的例子：

Java代码
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<Magazine> results = (List<Magazine>) 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<Magazine> results = (List<Magazine>) 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<Magazine> results = (List<Magazine>) 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<Magazine> results = (List<Magazine>) q.getResultList();

6．1．7 Query Hints
    JPQL支持通过name/value对来指定query hints。例如：

Java代码
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(); 

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();

6．1．8 Ordering
    JPQL支持根据一个或者多个字段对查询结果进行排序，例如：

Sql代码
SELECT x FROM Magazine x order by x.title asc, x.price desc 

SELECT x FROM Magazine x order by x.title asc, x.price desc

6．1．9 Aggregates
    JPQL支持min、max、avg和count等聚集函数（也被称为列函数）。以下是一些例子：

Java代码
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'"); 

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'");

6．1．10 Named Queries
    JPQL支持通过NamedQuery 和NamedQueries 这两个annotation来声明命名query。以下是一个例子：

Java代码
@Entity 
@NamedQueries({  
    @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<Magazine> results = (List<Magazine>) q.getResultList();  
 
Query q = em.createNamedQuery("magsByTitle");  
q.setParameter("titleParam", "JDJ");  
List<Magazine> results = (List<Magazine>) q.getResultList(); 

@Entity
@NamedQueries({
    @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<Magazine> results = (List<Magazine>) q.getResultList();

Query q = em.createNamedQuery("magsByTitle");
q.setParameter("titleParam", "JDJ");
List<Magazine> results = (List<Magazine>) q.getResultList();

6．1．11 Delete By Query
    Query可以用来高效地删除对象（不必将每个对象查询之后再进行删除，而是直接进行批量删除）。以下是个简单的例子：

Java代码
Query q = em.createQuery("DELETE FROM Subscription s WHERE s.subscriptionDate < :today");  
q.setParameter("today", new Date());  
int deleted = q.executeUpdate(); 

Query q = em.createQuery("DELETE FROM Subscription s WHERE s.subscriptionDate < :today");
q.setParameter("today", new Date());
int deleted = q.executeUpdate();    executeUpdate方法的返回值是删除对象的个数。 


6．1．12 Update By Query
    跟delete类似，Query也可以用来高效地更新对象。以下是个简单的例子：

Java代码
Query q = em.createQuery("UPDATE Subscription s SET s.paid = :paid WHERE s.subscriptionDate < :today");  
q.setParameter("today", new Date());  
q.setParameter("paid", true);  
int updated = q.executeUpdate();  

Query q = em.createQuery("UPDATE Subscription s SET s.paid = :paid WHERE s.subscriptionDate < :today");
q.setParameter("today", new Date());
q.setParameter("paid", true);
int updated = q.executeUpdate(); 

6．1．13 Subquery
    目前JPA规范支持在WHERE子句和HAVING子句中使用子查询，未来可能会支持FROM子句中使用子查询。以下是一些例子：

Sql代码
SELECT 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 < (SELECT AVG(p.revenue) FROM Publisher p)  
SELECT mag FROM Magazine mag WHERE (SELECT COUNT(art) FROM mag.articles art) > 10 

SELECT 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 < (SELECT AVG(p.revenue) FROM Publisher p)
SELECT mag FROM Magazine mag WHERE (SELECT COUNT(art) FROM mag.articles art) > 10

6．1．14 JPQL Constructor Expressions
    在SELECT子句中可以通过使用constructor来返回一个或多个实例。指定的类不必是entity class，例如：

Sql代码
SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price) FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00 

SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price) FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00


6．2 SQL Queries
    JPA支持通过Query接口的以下两个方法使用SQL查询：

Java代码
public Query createNativeQuery(String sqlString, Class resultClass);  
public Query createNativeQuery(String sqlString, String resultSetMapping); 

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代码
Query query = em.createNativeQuery("SELECT isbn, title, price, vers FROM Magazine WHERE price > 5 AND price < 10", Magazine.class);  
List<Magazine> results = (List<Magazine>) query.getResultList();  
for (Magazine mag : results)  
    processMagazine(mag); 

Query query = em.createNativeQuery("SELECT isbn, title, price, vers FROM Magazine WHERE price > 5 AND price < 10", Magazine.class);
List<Magazine> results = (List<Magazine>) query.getResultList();
for (Magazine mag : results)
    processMagazine(mag); 
   在指定resultSetMapping的情况下，resultSetMapping引用一个定义好的SqlResultSetMapping，例如：

Java代码
@Entity 
@SqlResultSetMapping(name="srsm1",  
    entities={@EntityResult(entityClass=Publisher.class),  
              @EntityResult(entityClass=Magazine.class)}  
)  
public class Publisher {  
    …  
}  
 
EntityManager em = entityManagerFactory.createEntityManager();  
Query q13 = em.createNativeQuery("SELECT p.id, p.name, m.isbn, m.title FROM Publisher AS p, Magazine as m WHERE p.id = 1 AND p.id = m.publisherId", "srsm1");  
List r13 = q13.getResultList();  
em.close();  
for(Iterator iter = r13.iterator(); iter.hasNext(); ) {  
    Object objects[] = (Object[])iter.next();  
    System.out.println("publisher: " + objects[0] + ", magazine: " + objects[1]);  
} 

@Entity
@SqlResultSetMapping(name="srsm1",
entities={@EntityResult(entityClass=Publisher.class),
      @EntityResult(entityClass=Magazine.class)}
)
public class Publisher {
    …
}

EntityManager em = entityManagerFactory.createEntityManager();
Query q13 = em.createNativeQuery("SELECT p.id, p.name, m.isbn, m.title FROM Publisher AS p, Magazine as m WHERE p.id = 1 AND p.id = m.publisherId", "srsm1");
List r13 = q13.getResultList();
em.close();
for(Iterator iter = r13.iterator(); iter.hasNext(); ) {
Object objects[] = (Object[])iter.next();
System.out.println("publisher: " + objects[0] + ", magazine: " + objects[1]);
} 
   当查询结果不仅包含entity，而且包含value type的时候，可以在SqlResultSetMapping中指定@ColumnResult，例如：

Java代码
@Entity 
@SqlResultSetMapping(name="srsm2",  
    entities={@EntityResult(entityClass=Publisher.class)},  
        columns={@ColumnResult(name="count")}  
)  
public class Publisher {  
    …  
}  
 
EntityManager em = entityManagerFactory.createEntityManager();  
Query q14 = em.createNativeQuery("SELECT p.id, count(*) AS count FROM Publisher AS p LEFT JOIN Magazine as m ON p.id = m.publisherId GROUP BY p.id", "srsm2");  
List r14 = q14.getResultList();  
em.close();  
for(Iterator iter = r14.iterator(); iter.hasNext(); ) {  
    Object objects[] = (Object[])iter.next();  
    System.out.println("publisher: " + objects[0] + ", count: " + objects[1]);  
}