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

OpenJPA (4)

繼續閱讀

httpd dead but subsys locked;No space left on device:Couldn't create accept loc

apache (httpd)不支援中文路徑問題先解除安裝yum安裝的httpd再用源碼安裝，重裝httpd再安裝支援中文的插件遇到問題

搭建httpd服務

windows下配置Apache的vhost初次接觸，強烈歡迎拍磚，指出錯誤

Apache與PHP環境下配置本地虛拟主機

Linux 7 中配置Apache服務，及禁止ip通路，删除apache廣告頁面。

Apache配置檔案中的deny和allow的使用

Apache 配置預設編碼

伺服器配置——Apache

Apache靜态檔案通路配置（書封伺服器）

apache httpd 配置

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Apache配置SSLApache配置SSL

Windows下配置Apache的SSL服務

Apache2.4.x 配置檔案詳解Apache配置需要了解如下：開始講解：

配置apache支援PHP（win7）