Criteria and 和or 组合查询

ITdfq

于 2023-07-06 14:33:43 发布

阅读量987

点赞数

文章标签： sql java

本文链接：https://blog.csdn.net/qq_43417276/article/details/131576105

版权

目标

比如所要实现如下查询条件

SELECT
    * 
FROM prod_image 
WHERE
    ( prod_p_id = 15 AND STATUS = 1 ) 
    AND ( label IS NULL OR label NOT IN ( 'sexy', 'abc' ) )

写法一

Example ex = new Example(ProdImage.class);
Example.Criteria criteria = ex.createCriteria().andEqualTo("prodPId", 15).andEqualTo("status", true);
Example.Criteria criteria1 = ex.createCriteria();criteria1.orCondition("label is null").orNotIn("label",Arrays.asList("sexy", "abc"));
ex.and(criteria1);
List<ProdImage> singles = prodImageMapper.selectByExample(ex);

写法二

更换查询的sql:

SELECT
    * 
FROM prod_image 
WHERE
    ( prod_p_id = 15 AND STATUS = 1  and label is null) or  ( prod_p_id = 15 AND STATUS = 1  and label is null and label NOT IN ( 'sexy', 'abc' ) )

代码可以这样写：

Example ex = new Example(ProdImage.class);
Example.Criteria criteria = ex.createCriteria().andEqualTo("prodPId", 15).andEqualTo("status", true).andNotIn("label",Arrays.asList("sexy", "abc"));
Example.Criteria criteria1 = ex.createCriteria().andEqualTo("prodPId", 15).andEqualTo("status", true).andIsNull("label");
ex.or(criteria1);
List<ProdImage> singles = prodImageMapper.selectByExample(ex);