OpenJPA (4)

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

public Query createQuery(String jpql);

    EntityManager上的这个方法用来根据给定的JPQL创建Query。

public List getResultList();

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

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等效：

SELECT x FROM Magazine x 
SELECT x FROM Magazine AS x

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

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：

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

    以上的遍历中假设关系不是null。在SQL术语中类似于inner join。如果你希望包含为null 的关系，那么可以如下指定： 

SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' or x.publisher is null

    也可以在query中遍历集合字段，但是必须首先在from子句中定义遍历相关的identification variable，例如： 

SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe'

    IN() 类型的语法可以使用inner join关键词，例如以下两个JPQL等效： 

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为例：

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[]

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 支持一系列预定义的标量函数，例如：

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的简单例子：

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

    假设目前数据库中的数据如下:

select * from bankingaccount;
+----+-------+-------------+---------------+
| id | Class | limitAmount | balanceAmount |
+----+-------+-------------+---------------+
|  5 | CC    |        1000 |          NULL |
|  6 | DC    |        NULL |          2000 |
+----+-------+-------------+---------------+

 

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中不能同时使用两种类型的参数。以下是两个简单的例子：

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。例如：

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支持根据一个或者多个字段对查询结果进行排序，例如：

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

 

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

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。以下是一个例子：

@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可以用来高效地删除对象（不必将每个对象查询之后再进行删除，而是直接进行批量删除）。以下是个简单的例子：

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也可以用来高效地更新对象。以下是个简单的例子：

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子句中使用子查询。以下是一些例子：

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，例如：

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查询：

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、区分查询子类和检查并发修改。 以下是个简单的例子：

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，例如：

@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，例如：

@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]);
}