一、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先创建?