1. select o.id from insuranceorder as o where (select datediff(o.expireDate,o.buyDate)<1460 ) and (select datediff(o.expireDate,o.buyDate)>=1095)
2. select v.id from vehicleorder as v where v.insuranceOrderId in(select o.id from insuranceorder as o where (select datediff(o.expireDate,o.buyDate)<1460 ) and (select datediff(o.expireDate,o.buyDate)>=0))
3. select count(*) from vs_mortgage_application as m where m.mortgageCompany = ' 中国银行广州白云支行 ' and m.vehicleOrderId in (select v.id from vehicleorder as v where v.insuranceOrderId in(select o.id from insuranceorder as o where o.companyName = ' 中国太平洋财产保险股份有限公司广东分公司 ' and (select datediff(o.expireDate,o.buyDate)<1460 ) and (select datediff(o.expireDate,o.buyDate)>=0)))
3 是正确得到的 SQL 语句
HQL 中不支持 datediff ,故以下方法作废,复制于此,以作备份
public List findInsuranceOrder(String mortgageCompany, String companyName, int start,int end) {
return em.createQuery("select object(o) from VsMortgageApplication as o " +
"where o.mortgageCompany like :mortgageCompany " +
"and o.vehicleOrder.insuranceOrder.companyName like :companyName " +
"and (select datediff(o.vehicleOrder.insuranceOrder.expireDate,o.vehicleOrder.insuranceOrder.buyDate)>= :start)" +
"and (select datediff(o.vehicleOrder.insuranceOrder.expireDate,o.vehicleOrder.insuranceOrder.buyDate)< :end)").setParameter("end", end).setParameter("start", start).setParameter("mortgageCompany", "%" + mortgageCompany + "%").setParameter("companyName", "%" + companyName + "%").getResultList();
}