HQL与SQL关于DATEDIFF的使用工作备份

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();

    }