关闭

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

标签: sql工作applicationstringobjectlist
1132人阅读 评论(0) 收藏 举报

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

    }

 

 

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4813次
    • 积分:106
    • 等级:
    • 排名:千里之外
    • 原创:5篇
    • 转载:0篇
    • 译文:0篇
    • 评论:4条
    文章分类
    最新评论