首先,有兩個實體,分别是Client和Trade,它們之間是一對多的關系。
Client類部分代碼如下:
@OneToMany(mappedBy="client",
cascade={CascadeType.REMOVE}
//fetch=FetchType.EAGER
)
public Set<Trade> getTrades() {
return trades;
}
public void setTrades(Set<Trade> trades) {
this.trades = trades;
}
Trade類部分代碼如下:
@ManyToOne
//@JoinColumn(name="CLIENT_ID")
public Client getClient() {
return client;
}
public void setClient(Client client) {
this.client = client;
}
當我做複合查詢即按照trade的date屬性和client的id屬性去查詢時,如果是通過criteria.add(Example)方式,則查詢傳回的結果是所有trade,查詢代碼如下:
Criteria tradeCriteria = session.createCriteria(Trade.class);
Example tradeExample = Example.create(trade);
tradeExample.enableLike(MatchMode.ANYWHERE);//使QBE支援模糊查詢
tradeCriteria.add(tradeExample);
Criteria clientCriteria = tradeCriteria.createCriteria("client");
if(null != trade.getClient()) {
Example clientExample = Example.create(trade.getClient());
clientExample.enableLike(MatchMode.ANYWHERE);
clientCriteria.add(clientExample);
}
clientCriteria.list();
背景列印出的sql語句為:
select this_.id as id1_1_, this_.abst as abst1_1_, this_.amount as amount1_1_, this_.balance as balance1_1_, this_.carriage as carriage1_1_, this_.client_id as client15_1_1_, this_.date as date1_1_, this_.flag as flag1_1_, this_.IS_LOAN as IS8_1_1_, this_.level as level1_1_, this_.packages as packages1_1_, this_.payment as payment1_1_, this_.price as price1_1_, this_.remark as remark1_1_, this_.verify as verify1_1_, client1_.id as id0_0_, client1_.COMPANY_ADDR as COMPANY2_0_0_, client1_.COMPANY_NAME as COMPANY3_0_0_, client1_.FAX as FAX0_0_, client1_.MOBILE_PHONE as MOBILE5_0_0_, client1_.name as name0_0_, client1_.OFFICE_PHONE as OFFICE7_0_0_, client1_.REMARK as REMARK0_0_, client1_.type as type0_0_ from Trade this_ inner join Client client1_ on this_.client_id=client1_.id where (this_.date=?) and (1=1)
如果查詢client時,不使用Example,而使用criteria.add(Restrictions.eq())方式,則會查詢出滿足條件的記錄,查詢代碼如下:
Criteria criteria = session.createCriteria(Trade.class);
Example tradeExample = Example.create(trade);
tradeExample.enableLike(MatchMode.ANYWHERE);//使QBE支援模糊查詢
criteria.add(tradeExample);
criteria.addOrder(Order.desc("date"));
if(null != trade.getClient()) {
criteria.createCriteria("client")
.add(Restrictions.eq("id", trade.getClient().getId()));
}
List<Trade> list = criteria.list();
背景列印的sql語句如下:
select this_.id as id1_1_, this_.abst as abst1_1_, this_.amount as amount1_1_, this_.balance as balance1_1_, this_.carriage as carriage1_1_, this_.client_id as client15_1_1_, this_.date as date1_1_, this_.flag as flag1_1_, this_.IS_LOAN as IS8_1_1_, this_.level as level1_1_, this_.packages as packages1_1_, this_.payment as payment1_1_, this_.price as price1_1_, this_.remark as remark1_1_, this_.verify as verify1_1_, client1_.id as id0_0_, client1_.COMPANY_ADDR as COMPANY2_0_0_, client1_.COMPANY_NAME as COMPANY3_0_0_, client1_.FAX as FAX0_0_, client1_.MOBILE_PHONE as MOBILE5_0_0_, client1_.name as name0_0_, client1_.OFFICE_PHONE as OFFICE7_0_0_, client1_.REMARK as REMARK0_0_, client1_.type as type0_0_ from Trade this_ inner join Client client1_ on this_.client_id=client1_.id where (this_.date=?) and client1_.id=? order by this_.date desc limit ?
可以看到,使用這種方式查詢時,在where條件中會有client1_id=?,而使用Example就沒有。不知道什麼原因,困擾了我好幾天了。
雖然可以使用這中方式,解決使用Example查詢時的問題,但我想弄明白原因。
剛才試了下查詢Client的非主鍵屬性,則查詢結果為滿足條件的記錄。where條件語句中and後面會加上Client的條件,而不是1=1了。
是以,在做複合查詢時首先要判斷是否為主鍵查詢,主鍵查詢需要差別對待,需要使用userCriteria.add(Restrictions.idEq(id)),我想着也就是為什麼會有idEq這個方法的原因吧,而且如果隻是主鍵查詢,那麼列印出的sql語句中and後面會有client1_id=? and 1=1。
解釋下hibernate列印出的sql語句,複合查詢使用Example時,hibernate列印出的sql語句where部分其實分為兩個部分,第一個部分是針對主表的查詢條件,第二部分是針對關聯表的查詢條件。
如果沒有查詢條件,那麼列印出的sql語句where部分為:
where 1=1 and 1= 1
如果是主表的非主鍵查詢條件,那麼列印出的sql語句where部分為:
where 主表條件 and 1=1
如果是主表的主鍵查詢條件,那麼列印出的sql語句where部分為:
where this_.id=? and 1=1 and 1=1
[注] this_.id=? and 1=1屬于查詢的第一部分,主表查詢條件。主鍵查詢需要使用criteria.add(Restrictions.idEq(id))。
如果是關聯表的查詢條件,那麼列印出的sql語句where部分為:
where 1=1 and 關聯表條件
如果是按照關聯表的主鍵查詢,那麼列印出的sql語句where部分為:
where 1=1 and 關聯表.id = ? and 1=1
[注] 關聯表.id = ? and 1=1屬于查詢的第二部分,關聯表查詢條件。主鍵查詢需要使用criteria.add(Restrictions.idEq(id))。
總結:
Hiberante在使用QBE查詢時,如果是對主鍵查詢,是不起作用的,即使主鍵設定了值。
在這種情況下,需要對主鍵查詢時,可以使用criteria.add(Restrictions.idEq(id))來實作。