springDataJpa @Query注解多条件动态模糊分页查询,传入形参对象

最新推荐文章于 2024-08-21 10:26:47 发布

绅士1993

最新推荐文章于 2024-08-21 10:26:47 发布

阅读量3.4k

点赞数 2

分类专栏： hibernate jpa 文章标签： jpa hibernate sql orm

本文链接：https://blog.csdn.net/aitanxiaofeng/article/details/104129869

版权

hibernate 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

jpa

1 篇文章 0 订阅

订阅专栏

在这里插入图片描述
1.代码:

    @Query(value = "SELECT * FROM s_order_baseinfo WHERE 1=1 " +
            "AND IF(''!=:#{#orderBaseInfo.siteId}, site_id=:#{#orderBaseInfo.siteId},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.userId}, user_id=:#{#orderBaseInfo.userId},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.orderNumber}, order_no like %:#{#orderBaseInfo.orderNumber},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.visitorCardNumber}, user_credentials_num=:#{#orderBaseInfo.visitorCardNumber},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.visitorPhone}, user_phone=:#{#orderBaseInfo.visitorPhone},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.orderState}, order_state=:#{#orderBaseInfo.orderState},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.queryConditionStartTime}, order_time>=:#{#orderBaseInfo.queryConditionStartTime},1=1) " +
            "AND IF(''!=:#{#orderBaseInfo.queryConditionEndTime}, order_time<=:#{#orderBaseInfo.queryConditionEndTime},1=1) " +
            "ORDER BY order_time DESC",
            countQuery = "SELECT current(*) FROM s_order_baseinfo WHERE 1=1 " +
                    "AND IF(''!=:#{#orderBaseInfo.siteId}, site_id=:#{#orderBaseInfo.siteId},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.userId}, user_id=:#{#orderBaseInfo.userId},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.orderNumber}, order_no like %:#{#orderBaseInfo.orderNumber},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.visitorCardNumber}, user_credentials_num=:#{#orderBaseInfo.visitorCardNumber},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.visitorPhone}, user_phone=:#{#orderBaseInfo.visitorPhone},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.orderState}, order_state=:#{#orderBaseInfo.orderState},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.queryConditionStartTime}, order_time>=:#{#orderBaseInfo.queryConditionStartTime},1=1) " +
                    "AND IF(''!=:#{#orderBaseInfo.queryConditionEndTime}, order_time<=:#{#orderBaseInfo.queryConditionEndTime},1=1) " +
                    "ORDER BY order_time DESC",
            nativeQuery = true)
    Page<OrderBaseInfo> getOrderBaseInfoPage(@Param("orderBaseInfo") OrderBaseInfo orderBaseInfo, Pageable pageable);

1.有几个疑问,有知道的大佬麻烦帮小弟解惑!

1). 听说用@Query注解的原生sql,会被二次转换成orm的实体类语法,不知道是不是这样.
2).#{#bean.xxx}
这里的为啥两个#
3). 语法中的冒号[’:’]和?一样的作用吗?包装类单属性形参的赋值方式为: select * from xxx where id=?1
是否可以这样赋值对象的属性呢? select * from xxx where id=?#{#对象.属性}
4).@Query的countQuery=的分页语句有没有简化的写法?两大坨,太臃肿了.
5.)官方api看着脑壳痛,有没有@Query的比较易读的详解文章推荐下啊.