9.3.原生查詢
EJB QL中富有大量的查詢語句并且基本上能符合你的絕大多數(shù)的查詢需求.有時(shí),你想要使用特定廠商提供的數(shù)據(jù)庫上的專有能力.
實(shí)體管理服務(wù)提供了一個(gè)方法來建立原生的SQL查詢并且映射他們到你的對象上.原生查詢能反回實(shí)體,欄位值,或者兩者的組合.EntityManager接口有三種方法來建立原生查詢:一種返回標(biāo)量值,一種是返回實(shí)體類型,最后一種是定義一個(gè)復(fù)雜的結(jié)果集,它能映射到多個(gè)實(shí)體的混合和標(biāo)量值.
你可以進(jìn)行JDBC的連接通過javax.sql.DataSource,使用@Resource注入和執(zhí)行你的SQL語句.要意識到你所做的改變不會(huì)被當(dāng)前的持久化上下文所反映.
9.3.1. 標(biāo)量原生查詢
Query createNativeQuery(String sql)
這將建立一個(gè)原生查詢返回一個(gè)標(biāo)量結(jié)果.它需要一個(gè)參數(shù):你的原生SQL.它執(zhí)行并且返回結(jié)果集同EJB QL相同的形式,返回標(biāo)量值.
9.3.2.簡單的實(shí)體原生查詢
Query createNativeQuery(String sql, Class entityClass)
一個(gè)簡單的原生查詢通過一個(gè)SQL語句和隱式的映像到一個(gè)實(shí)體,映射元數(shù)據(jù)為基礎(chǔ)的一個(gè)實(shí)體.它認(rèn)為原生查詢的結(jié)果集中的欄將完全匹配實(shí)體的O/R映射.原生SQL查詢的映射實(shí)體的確定通過entityClass 參數(shù):
Query query = manager.createNativeQuery(
"SELECT p.phone_PK, p.phone_number, p.type
FROM PHONE AS p", Phone.class
);
實(shí)體的所有屬性被列出:
9.3.3.復(fù)雜的原生查詢
這個(gè)實(shí)體管理方法允許你有一個(gè)復(fù)雜的映射為原生SQL.你可以同時(shí)返回多個(gè)實(shí)體和標(biāo)量欄.mappingName 參數(shù)參考@javax.persistence.SqlResultSetMapping定義.這個(gè)批注用來定義一個(gè)怎能樣查詢原生結(jié)果的釣子到O/R模型.如果返回的欄位名與批注映射的屬性不匹配,你可以提代一個(gè)字段到欄位的映射為他們,使用@javax.persistence.FieldResult :
package javax.persistence;
public @interface SqlResultSetMapping {
String name( );
EntityResult[] entities( ) default {};
ColumnResult[] columns( ) default {};
}
public @interface EntityResult {
Class entityClass( );
FieldResult[] fields( ) default {};
String discriminatorColumn( ) default "";
}
public @interface FieldResult {
String name( );
String column( );
}
public @interface ColumnResult {
String name( );
}
讓我們做一系列的例子表示這會(huì)如何工作.
9.3.3.1. 使用多個(gè)實(shí)體的原生查詢
@Entity
@SqlResultSetMapping(name="customerAndCreditCardMapping",
entities={@EntityResult(entityClass=Customer.class),
@EntityResult(entityClass=CreditCard.class,
fields={@FieldResult(name="id",
column="CC_ID"),
@FieldResult(name="number",
column="number")}
)})
public class Customer {...}
// execution code
{
Query query = manager.createNativeQuery(
"SELECT c.id, c.firstName, cc.id As CC_ID,
cc.number" +
"FROM CUST_TABLE c, CREDIT_CARD_TABLE cc" +
"WHERE c.credit_card_id = cc.id",
"customerAndCreditCardMapping");
}
因?yàn)榻Y(jié)果集返回多個(gè)實(shí)體類型,我們必需使用一個(gè)@SqlResultSetMapping.這個(gè)批注可以被放在一個(gè)實(shí)體類或方法上.entities( )屬性用來設(shè)置@EntityResult批注組成的隊(duì)列.每一個(gè)@EntityResult注釋指定將要通過原生SQL查詢返回的實(shí)體.
@javax.persistence.FieldResult注釋用來明確查詢中與實(shí)體屬性對應(yīng)的映射欄位.@FieldResult批注的name()屬性標(biāo)識實(shí)體組件的屬性, column( ) 屬性標(biāo)識通過原生查詢返回的結(jié)果集欄位.
在這個(gè)例子中,我們需要指定@FieldResults為客戶.原生查詢?yōu)閷?shí)體引用的每一個(gè)欄位.因?yàn)槲覀冎徊樵僀reditCard 實(shí)體的ID和number欄,@FieldResult批注需要被指定.在 CreditCard的@EntityResult批注中,fields()屬性定義CreditCard 屬性每次查詢的映射.因?yàn)镃ustomer和CreditCard主鍵欄有相同的名子,SQL查詢需要辨別出他們的不同.cc.id As CC_ID這段SQL代碼演示出這種標(biāo)識.
我們也可以使用XML來表達(dá)：
<entity-mappings>
<sql-result-set-mapping name="customerAndCreditCardMapping">
<entity-result entity-class="com.titan.domain.Customer"/>
<entity-result entity-class="com.titan.domain.CreditCard"/>
<field-result name="id" column="CC_ID"/>
<field-result name="number" column="number"/>
</entity-result>
</sql-result-set-mapping>
</entity-mappings>
9.3.3.2.混合標(biāo)量和實(shí)體結(jié)果
在我們的最終例子,顯示一個(gè)實(shí)體和一個(gè)標(biāo)量值的混合.我們寫一個(gè)原生查詢,來返回一個(gè)每次巡行由多少預(yù)定組成的巡行列表.
@SqlResultSetMapping(name="reservationCount",
entities=@EntityResult(name="com.titan.domain.Cruise",
fields=@FieldResult(name="id", column="id")),
columns=@ColumnResult(name="resCount"))
@Entity
public class Cruise {...}
{
Query query = manager.createNativeQuery(
"SELECT c.id, count(Reservation.id) as resCount
FROM Cruise c LEFT JOIN Reservation ON c.id = Reservation.CRUISE_ID
GROUP BY c.id",
"reservationCount");
}
reservationCount映射的定義,原生查詢表現(xiàn)對一個(gè)巡航實(shí)體和一個(gè)所有巡航預(yù)定的數(shù)目的請求.@FieldResult批注標(biāo)識c.id欄同Cruise實(shí)體相關(guān)聯(lián).@ColumnResult批注標(biāo)識resCount欄同一個(gè)標(biāo)量值.
等價(jià)的XML文件:
<entity-mappings>
<sql-result-set-mapping name="reservationCount">
<entity-result entity-class="com.titan.domain.Cruise">
<field-result name="id" column="id"/>
</entity-result>
<column-result name="resCount"/>
</sql-result-set-mapping>
</entity-mappings>
9.4命名查詢
JAVA持久化提供了一種機(jī)制,所以在建立一個(gè)查詢時(shí),你可以預(yù)先定義EJB QL或原SQL查詢,并且引用它們通過名字.你可以先建立查詢,然后建立JAVA語言中的String類型的常量:在多種不同的情形中重復(fù)使用他們.你預(yù)先定義一個(gè)查詢,當(dāng)在后面用到的時(shí)候,可以很容易的進(jìn)行調(diào)整.@javax.persistence.NamedQuery 批注用在預(yù)定義EJB QL中:
package javax.persistence;
public @interface NamedQuery {
String name( );
String query( );
QueryHint[] hints( ) default {};
}
public @interface QueryHint {
String name( );
String value( );
}
public @interface NamedQueries {
NamedQuery[] value( );
}
當(dāng)你定義一個(gè)或多個(gè)查詢在類或包中,你可以使用@javax.persistence.NamedQueries 批注.@javax.persistence.QueryHint批注定義廠商提供的暗示.這些暗示工作方式與Query.setHint( )方法類似,它的描述在本單的前面.這是一個(gè)例:
package com.titan.domain;
import javax.persistence.*;
@NamedQueries({
@NamedQuery
(name="getAverageReservation",
query=
"SELECT AVG( r.amountPaid)
FROM Cruise As c, JOIN c.reservations r
WHERE c = :cruise"),
@NamedQuery(name="findFullyPaidCruises",
query=
"FROM Cruise cr
WHERE 0 < ALL (
SELECT res.amountPaid from cr.reservations res
)")
})
@Entity
public class Cruise {...}
在這個(gè)例子中定義了兩個(gè)EJB QL查詢在Cruise實(shí)體組件類.你可以引用這些定義在EntityManager.createNamedQuery( )方法中:
Query query = em.createNamedQuery("findFullyPaidCruises");
Query.setParameter("cruise", cruise);
等價(jià)于@NamedQuery的XML文件:
<entity-mappings>
<named-query
name="getAverageReservation">
<query>
SELECT AVG( r.amountPaid)
FROM Cruise As c JOIN c.reservations r
WHERE c = :cruise
</query>
</named-query>
</entity-mappings>
9.4.1.命名原生查詢
@javax.persistence.NamedNativeQuery 批注用于預(yù)處理原生SQL查詢:
package javax.persistence;
public @interface NamedNativeQuery {
String name( );
String query( );
Class resultClass( ) default void.class;
String resultSetMapping( ) default "";
}
public @interface NamedNativeQueries {
NamedNativeQuery[] value( );
}
resultClass() 屬性是為當(dāng)你有一個(gè)原生查詢時(shí),只返回一個(gè)實(shí)體類型.(看這章的前面"Native Queries" 節(jié)).resultSetMapping( ) 屬性解決一個(gè)預(yù)定@SqlResultSetMapping.這兩個(gè)屬性是可選的,但是你必需至少定義它們中的一個(gè).這是@NamedNativeQuery批注的一個(gè)例子:
@NamedNativeQuery(
name="findCustAndCCNum",
query="SELECT c.id, c.firstName, c.lastName, cc.number AS CC_NUM
FROM CUST_TABLE c, CREDIT_CARD_TABLE cc
WHERE c.credit_card_id = cc.id",
resultSetMapping="customerAndCCNumMapping")
@SqlResultSetMapping(name="customerAndCCNumMapping",
entities={@EntityResult(entityClass=Customer.class)},
columns={@ColumnResult(name="CC_NUM")}
)
@Entity
public class Customer {...}
你可以參考EntityManager.createNamedQuery( ) 的定義:
Query query = em.createNamedQuery("findCustAndCCNum");
等價(jià)的XML文件:
<entity-mappings>
<named-native-query name="findCustAndCCNum"
result-set-mapping="customerAndCCNumMapping"/>
<query>
SELECT c.id, c.firstName, c.lastName,
cc.number AS CC_NUM
FROM CUST_TABLE c, CREDIT_CARD_TABLE cc
WHERE c.credit_card_id = cc.id
</query>
</named-native-query>
</entity-mappings>
Phone實(shí)體中加入
@NamedNativeQuery(name="NativePhone",
query="SELECT p.phone_PK, p.phone_number, p.type FROM PHONE AS p",
resultClass=Phone.class)
Named Native Query, implicit mapping
--------------------------------
Executing @NamedNativeQuery(name="NativePhone")
Exception in thread "main" javax.persistence.RollbackException: Transaction marked as rollbackOnly
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:50)
at com.lyh.ejb3.clients.NativeQueries.main(NativeQueries.java:42)
Initialize DB
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.MappingException: Named query not known: NativePhone
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:567)
at org.hibernate.ejb.AbstractEntityManagerImpl.createNamedQuery(AbstractEntityManagerImpl.java:90)
at com.lyh.ejb3.clients.NativeQueries.nativeSql(NativeQueries.java:54)
at com.lyh.ejb3.clients.NativeQueries.main(NativeQueries.java:34)
Caused by: org.hibernate.MappingException: Named query not known: NativePhone
Named Native Query, implicit mapping
--------------------------------
Executing @NamedNativeQuery(name="NativePhone")
at org.hibernate.impl.AbstractSessionImpl.getNamedQuery(AbstractSessionImpl.java:70)
at org.hibernate.impl.SessionImpl.getNamedQuery(SessionImpl.java:1260)
at org.hibernate.ejb.AbstractEntityManagerImpl.createNamedQuery(AbstractEntityManagerImpl.java:87)
... 2 more
Customer實(shí)體中加入
@Table(name="CUST_TABLE")
@SqlResultSetMapping(name="customerAndCreditCardMapping",
entities={@EntityResult(entityClass=Customer.class),
@EntityResult(entityClass=CreditCard.class,
fields={@FieldResult(name="id", column="CC_ID"),
@FieldResult(name="number", column="number")}
)})
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown SqlResultSetMapping [customerAndCreditCardMapping]
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:567)
at org.hibernate.ejb.AbstractEntityManagerImpl.createNativeQuery(AbstractEntityManagerImpl.java:128)
at com.lyh.ejb3.clients.NativeQueries.nativeWithMultipleEntities(NativeQueries.java:74)
at com.lyh.ejb3.clients.NativeQueries.main(NativeQueries.java:36)
Caused by: org.hibernate.MappingException: Unknown SqlResultSetMapping [customerAndCreditCardMapping]
at org.hibernate.impl.SQLQueryImpl.setResultSetMapping(SQLQueryImpl.java:290)
at org.hibernate.ejb.AbstractEntityManagerImpl.createNativeQuery(AbstractEntityManagerImpl.java:124)
... 2 more
加入@Table(name="CREDIT_CARD_TABLE") 并在相應(yīng)屬性加入@Column批注
ERROR - Table 'titan.credit_card_table' doesn't exist
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:53)
... 2 more
強(qiáng)制類型轉(zhuǎn)換
Exception in thread "main" java.lang.ClassCastException: java.math.BigInteger
at com.lyh.ejb3.clients.NativeQueries.mixedNative(NativeQueries.java:109)
at com.lyh.ejb3.clients.NativeQueries.main(NativeQueries.java:38)