mybatis的example.createCriteria()方法学习记录

example.createCriteria()中的方法学习笔记

VVAIVG

14995人浏览 · 2022-04-02 16:18:06

VVAIVG · 2022-04-02 16:18:06 发布

一、mapper的crud方法:

1. selectByPrimaryKey(id)

根据主键查询,返回的是个对象

mapper.selectByPrimaryKey(id)

2. selectByExample(example)

根据条件查询,返回的是一个list,下面的and/or方法测试用的就是这个

mapper.selectByExample(example)

3. selectCountByExample(example)

根据条件查询后计数,返回的是int

mapper.selectCountByExample(example)

4. updateByPrimaryKey(User user)

根据主键修改,返回的是int

mapper.updateByPrimaryKey(user)

5. updateByPrimaryKeySelective(User user) (建议使用)

根据主键修改不为null的字段,返回的是int

mapper.updateByPrimaryKeySelective(user)

6. updateByExample(User user,Example example)

根据条件修改,返回的是int,注意:前面的参数user是要修改的内容,后面的example是查询条件,查到结果后将结果按user的值修改

mapper.updateByExample(user, example)

7. updateByExampleSelective(User user,Example example) (建议使用)

根据条件修改不为null的字段,返回的是int

mapper.updateByExampleSelective(user, example)

8. deleteByPrimaryKey(id)

根据主键删除,返回的是int

mapper.deleteByPrimaryKey(id)

9. deleteByExample(example)

根据条件删除,返回的是int

mapper.deleteByExample(example)

10. insert(User user)

插入一条数据,返回值是id

mapper.insert(User user)

11. insertSelective(User user) (推荐使用)

插入一条数据,值为null的字段会做判空操作,不会添加

mapper.insertSelective(user)

二、and/or方法:

and方法

1. andEqualTo("field",value)

表示条件为实体类字段"field"等于value值

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andEqualTo("createUserId","1")
                .andEqualTo("isDelete",0);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where( ( create_user_id = ? and is_delete = ? ) )

where后为什么会多两层括号我也不知道...反正查询结果是对的,下面为了美观和方便,就手动把括号去掉了

另一种单参数写法: 参数为map

Map<String, String> param = new HashMap<>();
        param.put("createUserId","1");
        param.put("isDelete","0");
        
        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andEqualTo(param);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

2. andAllEqualTo（param）

与andEqualTo的单参数形式一样,参数为map

3. andNotEqualTo("field",value)

与andEqualTo相反,条件为实体类字段"field"不等于value值,同时此方法没有单参数

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andNotEqualTo("createUserId","1");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id <> ?

4. andIn("field",list)

表示条件为实体类"field"字段的值包含ids里的值,与sql语句中的in()相同

List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);

        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIn("createUserId",ids);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id in ( ? , ? )

list中不一定要是Integer类型,也可以是String

List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");

        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIn("title",titles);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

举一反三:

与sql语句中的FIND_IN_SET也相同

需要注意的是,FIND_IN_SET(str,strList),这里的str为数据库中的字段名,如create_user_id,而不是实体类的createUserId

执行sql:

select * from tb_work_guide where FIND_IN_SET (create_user_id , '1,2')

5. andNotIn("field",list)

与andIn()相反,查询"field"字段不包含list中的值的结果

执行sql:

select * from tb_work_guide where create_user_id not in ( ? , ? )

6. andIsNull("field")

表示实体类"field"字段为null

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIsNull("createUserId");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id is null

7. andIsNotNull("field")

与andIsNull()相反,表示实体类"field"字段不为null

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andIsNotNull("createUserId");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id is not null

8. andBetween("field",value1,value2)

表示"field"字段的值在value1和value2之间,注意:这个区间是包头包尾的,1 <= field <= 7

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andBetween("createUserId",1,7);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id between ? and ?

9. andNotBetween("field",value1,value2)

与andBetween()相反,表示"field"字段的值不在value1和value2之间,注意:这个区间也是包头包尾的,field < 1 或 field >7

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andNotBetween("createUserId",1,7);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id not between ? and ?

10. andLike("field",str)

表示模糊查询,注意:需要自己拼接%或_

        String str = "缴费";

        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andLike("title","%"+str+"%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title like ?

11. andNotLike("field",str)

与andLike相反的查询,也需要自己拼接%或_

String str = "缴费";

        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andNotLike("title","%"+str+"%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title not like ?

12. andGreaterThan("field",value)

表示查询"field"字段中大于value的值

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andGreaterThan("age",20);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where age > ?

13. andGreaterThanOrEqualTo("field",value)

与andGreaterThan()差不多,表示"field"字段中大于等于value的值

执行sql:

select * from tb_work_guide where age >= ?

14. andLessThan("field",value)

表示查询"field"字段中小于value的值

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andLessThan("age",20);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where age < ?

15. andLessThanOrEqualTo("field",value)

与andLessThan()差不多,表示"field"字段中小于等于value的值

执行sql:

select * from tb_work_guide where age <= ?

16. andCondition(condition)

在where后直接拼接条件,注意:此时拼接的field字段名是数据库的列名

写法1:

直接写完全where后面的条件语句,会自动将create_user_id = 1拼接到where后

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andCondition("create_user_id = 1");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id = 1

写法2:

andCondition(condition,value) ,condition写查询字段(注意要记得加上=,不然会报错),value传值,这样value值就可以写活了

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .andCondition("create_user_id = ",1);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id = ?

andCondition()这个方法写的大佬都太少了,目前我只知道这是拼接作用的,就先这么写着,如果以后用到或者学习到,再回来补

or方法

我感觉or方法跟and方法其实差不多,只是and方法是在多条件中间拼接and,or方法是拼接or,所以下面就只举几个例子加深印象好了,主要还是and和or一同使用

1. orEqualTo(param)

Map<String, String> param = new HashMap<>();
        param.put("createUserId","1");
        param.put("isDelete","0");

        Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orEqualTo(param);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where create_user_id = ? or is_delete = ?

2. orIn("field",list)

List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orIn("title",titles);
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title in ( ? , ? )

从sql中可以看到,orIn和andIn的执行sql是一样的,其余方法就不测试了,大概是一样的,只有多个条件时,中间的连接符号不同,

如下:

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orLike("title","%标题1%")
                .orLike("createUserName","%小明%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title like ? or create_user_name like ?

List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");

Example example = new Example(WorkGuideModel.class);
        example.createCriteria()
                .orIn("title",titles)
                .orLike("createUserName","%小明%");
        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where title in ( ? , ? ) or create_user_name like ?

and和or一同使用

a and ( b or c)

Example example = new Example(WorkGuideModel.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("userId",1);
        
        Example.Criteria criteria2 = example.createCriteria();
        criteria2.orLike("title","%标题%")
                .orBetween("age",1,5);
        example.and(criteria2);

        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where ( ( user_id = ? ) and ( title like ? or age between ? and ? ) )

现在知道多出来的括号是干啥的了...

(a and b) or (c or d)

        List<String> titles = new ArrayList<>();
        titles.add("标题1");
        titles.add("标题2");

        Example example = new Example(WorkGuideModel.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("userId",1)
                .andIn("title",titles);

        Example.Criteria criteria2 = example.createCriteria();
        criteria2.orLike("name","%小明%")
                .orBetween("age",1,5);
        
        example.or(criteria2);

        List<WorkGuideModel> list = mapper.selectByExample(example);
        return list;

执行sql:

select * from tb_work_guide where ( create_user_id = ? and title in ( ? , ? ) ) or ( title like ? or open between ? and ? )

将代码中的example.or(criteria2);改成example.and(criteria2);后会发现sql变成了:

select * from tb_work_guide where ( create_user_id = ? and title in ( ? , ? ) ) and ( title like ? or open between ? and ? )

所以,总结:

多条件查询时,可以直接使用同一个条件构造器(不知道这个Example.Criteria criteria叫什么,就暂且叫他构造器),往里面直接添加条件就行.

多重条件查询时,如上面的两个括号中的条件都要分别满足时,可以分别创建条件构造器,然后分别往里添加条件

Example.Criteria criteria = example.createCriteria();
Example.Criteria criteria2 = example.createCriteria();

两个括号之间(也就是两个条件构造器之间)使用example的and或or来连接

example.and(criteria2);
example.or(criteria2);

至于为什么参数是criteria2而不是criteria,我也不知道,先来后到?criteria比criteria2先创建?

华为开发者空间

华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。

更多推荐

解析openGauss账本数据库

华为开发者空间

GaussDB高校行走进哈尔滨，与两校学子共话数据库人才发展

华为开发者空间

GaussDB数据库高可用部署方案

华为开发者空间

所有评论(0)

查看更多评论

VVAIVG

@Wang981110

已为社区贡献1条内容