HQL优化查询使用exist

Question

StringBuffer sb = new StringBuffer();
sb.append("select distinct t.inquiryNum,t.contractNum,t.orderNum,t.tcNumTc,t.collectType,t.pdfName,t.signedName,t.invoiceNum,t.deliveryDateChr"
+ ",(select count(t4.id) from TcsCtPdfLog t4 where t4.tcNumTc=t.tcNumTc and t4.opFlag='"
+ Constants.CERTIFI_QUALITY_LOG_OPTION_TYPE_PRINT
+ "' and t4.userNum='"
+ user.getMemberNumber()
+ "')"
+ ",t.factory,t.inDate,t.collectNum,t.accpAuthority,t.machineId"
+ ",(select count(t4.id) from TcsCtPdfLog t4 where t4.tcNumTc=t.tcNumTc and t4.opFlag='"
+ Constants.CERTIFI_QUALITY_LOG_OPTION_TYPE_DOWNLOAD
+ "' and t4.userNum='"
+ user.getMemberNumber()
+ "')"
+ ",t.attitudeFlag,t.attitudeType "
+ ",t.dqContractId "
+ " from VcsCeQualitySummary t, TLfqmPdfCertificateModel t1 "
+ " where 1=1"
+ " and t.tcNumTc is not null and t.status "
+ Constants.INSPE_CERTI_STATUS_VALID
+ " and (t.attitudeFlag = '1' or t.attitudeFlag = '9') "
+ " and t.attitudeType = '1'"
+ " and t.tcNumTc = t1.id.tcNumTc "
+ " and t.orderNum = t1.id.orderNum ");
if (!ConstantsCsm.ROLE_GF.equals(user.getMemberNumber())) {
sb.append(" and t.ordOwner='" + user.getMemberNumber() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getOrderNumBoard())) {
sb.append(" and t.orderNum= '" + searchModel.getOrderNumBoard()
+ "'");
} else if (null != orderNums && orderNums.size() > 0) {
sb.append(" and t.orderNum in (");
for(Iterator it = orderNums.iterator(); it.hasNext();){
String orderNum = (String)it.next();
sb.append("'"+ orderNum +"'").append(",");
}
sb.delete(sb.length() - 1, sb.length());
sb.append(")");
}
if (!TLfqmUtil.IsEmpty(searchModel.getTcNumTcBoard())) {
sb.append(" and t.tcNumTc='" + searchModel.getTcNumTcBoard()
+ "' or (t.collectNum = '" + searchModel.getTcNumTcBoard()
+ "' and t.collectType in " + Constants.COLLECTTYPE
+ " ) ");
}
if (!TLfqmUtil.IsEmpty(searchModel.getPackNumBoard())) {
sb.append(" and t.packNum='" + searchModel.getPackNumBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getInvoiceNumBoard())) {
sb.append(" and t.invoiceNum='" + searchModel.getInvoiceNumBoard()
+ "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getContractNumBoard())) {
sb.append(" and t.contractNum='"
+ searchModel.getContractNumBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getBoardPlankBoard())) {
sb.append(" and t.boardPlank='" + searchModel.getBoardPlankBoard()
+ "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getCollectTypeBoard())) {
sb.append(" and t.collectType='"
+ searchModel.getCollectTypeBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getCustOrdNumBoard())) {
sb.append(" and t.custOrdNum like'"
+ searchModel.getCustOrdNumBoard() + "%'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getHeatNumBoard())) {
sb.append(" and t.ponoNo ='" + searchModel.getHeatNumBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getDeliveryDateChrStarBoard())) {
sb.append(" and t.deliveryDateChr >='"
+ searchModel.getDeliveryDateChrStarBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getDeliveryDateChrEndBoard())) {
sb.append(" and t.deliveryDateChr <='"
+ searchModel.getDeliveryDateChrEndBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getShipNumBoard())) {
sb.append(" and t.shipNum ='" + searchModel.getShipNumBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getPartNumberBoard())) {
sb.append(" and t.partNumber ='" + searchModel.getPartNumberBoard() + "'");
}
if (!TLfqmUtil.IsEmpty(searchModel.getDqContractId())){
sb.append(" and t.dqContractId ='" + searchModel.getDqContractId() + "'");
}
System.out.println("sql：" + sb.toString());

Answer 1

HQL 可以直接用SQL
在传递一个集合的索引集或者是元素集(elements与indices 函数) 或者传递一个子查询的结果的时候，可以使用SQL函数any, some, all, exists, in
select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)
select p from NameList list, Person p
where p.name = some elements(list.names)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)