Mybatis-plus
Mybatis-plus
官网：https://mp.baomidou.com/guide/#%E7%89%B9%E6%80%A7
MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
maven依赖：
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
引入 MyBatis-Plus 之后请不要再次引入 MyBatis 以及 MyBatis-Spring，以避免因版本差异导致的问题。
yml:
# mybatis-plus相关配置 mybatis-plus: # xml扫描，多个目录用逗号或者分号分隔（告诉 Mapper 所对应的 XML 文件位置） mapper-locations: classpath*:/mapper/**/*.xml # 以下配置均有默认值,可以不设置 global-config: db-config: #主键类型 AUTO:"数据库ID自增" INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID"; id-type: auto # 配置表明前缀，例如表设计时表名为tb_manager，对应entity为Manager table-prefix: tb_ # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置@TableLogic,注意需要与JavaBean的字段一样) logic-delete-field: isDelete # 逻辑已删除值(默认为 1) logic-delete-value: 1 # 逻辑未删除值(默认为 0) logic-not-delete-value: 0 configuration: # 是否开启自动驼峰命名规则映射:从数据库列名到Java属性驼峰命名的类似映射 map-underscore-to-camel-case: true # 如果查询结果中包含空值的列，则 MyBatis 在映射的时候，不会映射这个字段 call-setters-on-nulls: true # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用 log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
启动类添加@MapperScan
配置分页插件：
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
* paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不受限制);
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
paginationInterceptor.setLimit(100);
return paginationInterceptor;
}
/**
* Map下划线自动转驼峰
* 指的是：resultType="java.util.Map"
* @return
*/
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return i -> i.setObjectWrapperFactory(new MybatisMapWrapperFactory());
}
}
编写dao:
/**
* @Author dw
* @ClassName IUserDao
* @Description
* @Date 2020/6/9 18:11
* @Version 1.0
*/
@Mapper
public interface IUserDao extends BaseMapper<MyUser> {
}
编写service:
/** * @Author dw * @ClassName IUserServcie * @Description * @Date 2020/6/10 13:04 * @Version 1.0 */ public interface IUserService extends IService<MyUser> { }
编写service实现:
/** * @Author dw * @ClassName UserServiceImpl * @Description * @Date 2020/6/10 13:30 * @Version 1.0 */ @Service public class UserServiceImpl extends ServiceImpl<IUserDao, MyUser> implements IUserService { }
编写controller:
package com.dw.study.controller; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.dw.study.model.MyUser; import com.dw.study.service.impl.UserServiceImpl; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.*; /** * @Author dw * @ClassName MyUserController * @Description mybatis-plus 常用的一些方法 * @Date 2020/6/10 13:34 * @Version 1.0 */ @RestController @RequestMapping("/userInfo") public class MyUserController { @Autowired private UserServiceImpl userService; /** * 根据ID获取用户信息 * @Author * @CreateTime 2019/6/8 16:34 * @Param userId 用户ID * @Return UserInfoEntity 用户实体 */ @RequestMapping("/getInfo") public MyUser getById(String userId){ MyUser byId = userService.getById(userId); return byId; } /** * 查询全部信息 * @Author * @CreateTime 2019/6/8 16:35 * @Param userId 用户ID * @Return List<UserInfoEntity> 用户实体集合 */ @RequestMapping("/getList") public List<MyUser> getList(){ List<MyUser> userInfoEntityList = userService.list(); return userInfoEntityList; } /** * 分页查询全部数据 * @Author * @CreateTime 2019/6/8 16:37 * @Return IPage<UserInfoEntity> 分页数据 */ @RequestMapping("/getInfoListPage") public IPage<MyUser> getByPageLimit(){ //需要在Config配置类中配置分页插件 IPage<MyUser> page = new Page<>(); //当前页 page.setCurrent(5); //每页条数 page.setSize(1); page = userService.page(page); return page; } /** * 根据指定字段查询用户信息集合 * @Author * @CreateTime 2019/6/8 16:39 * @Return Collection<UserInfoEntity> 用户实体集合 */ @RequestMapping("/getListMap") public Collection<MyUser> getListByMapParams(){ Map<String,Object> map = new HashMap<>(); //kay是字段名 value是字段值 map.put("age",20); Collection<MyUser> userInfoEntityList = userService.listByMap(map); return userInfoEntityList; } /** * 新增用户信息 * @Author Sans * @CreateTime 2019/6/8 16:40 */ @RequestMapping("/saveInfo") public void save(){ MyUser userInfoEntity = new MyUser(); userInfoEntity.setName("小龙"); userInfoEntity.setAge(18); userService.save(userInfoEntity); } /** * 批量新增用户信息 * @Author * @CreateTime 2019/6/8 16:42 */ @RequestMapping("/saveBatch") public void saveBatch(){ //创建对象 MyUser sans = new MyUser(); sans.setName("zhangsan"); sans.setAge(18); MyUser papyrus = new MyUser(); papyrus.setName("lisi"); papyrus.setAge(18); //批量保存 List<MyUser> list =new ArrayList<>(); list.add(sans); list.add(papyrus); userService.saveBatch(list); } /** * 更新用户信息 * @Author * @CreateTime 2019/6/8 16:47 */ @RequestMapping("/updateById") public void updateById(){ //根据实体中的ID去更新,其他字段如果值为null则不会更新该字段,参考yml配置文件 MyUser userInfoEntity = new MyUser(); userInfoEntity.setId(1L); userInfoEntity.setAge(19); userService.updateById(userInfoEntity); } /** * 批量更新用户信息 * @Author * @CreateTime 2019/6/8 16:47 */ @RequestMapping("/updateById") public void updateBatchById(){ //根据实体中的ID去更新,其他字段如果值为null则不会更新该字段,参考yml配置文件 MyUser user1 = new MyUser(); user1.setId(1L); user1.setName("刘亦菲"); user1.setAge(20); MyUser user2 = new MyUser(); user2.setId(1L); user2.setName("成龙"); user2.setAge(20); ArrayList<MyUser> usersList = new ArrayList<>(); usersList.add(user1); usersList.add(user2); userService.updateBatchById(usersList); } /** * 新增或者更新用户信息 * @Author * @CreateTime 2019/6/8 16:50 */ @RequestMapping("/saveOrUpdate") public void saveOrUpdate(){ //传入的实体类userInfoEntity中ID为null就会新增(ID自增) //实体类ID值存在,如果数据库存在ID就会更新,如果不存在就会新增 MyUser userInfoEntity = new MyUser(); userInfoEntity.setId(1L); userInfoEntity.setAge(20); userService.saveOrUpdate(userInfoEntity); } /** * 批量新增或者更新用户信息 * @Author * @CreateTime 2019/6/8 16:50 */ @RequestMapping("/saveOrUpdate") public void saveOrUpdateBatch(){ //传入的实体类userInfoEntity中ID为null就会新增(ID自增) //实体类ID值存在,如果数据库存在ID就会更新,如果不存在就会新增 MyUser user1 = new MyUser(); user1.setId(1L); user1.setName("刘亦菲"); user1.setAge(20); MyUser user2 = new MyUser(); user2.setId(1L); user2.setName("成龙"); user2.setAge(20); ArrayList<MyUser> usersList = new ArrayList<>(); usersList.add(user1); usersList.add(user2); userService.saveOrUpdateBatch(usersList); } /** * 根据ID删除用户信息 * @Author * @CreateTime 2019/6/8 16:52 */ @RequestMapping("/deleteInfo") public void removeById(String userId){ userService.removeById(userId); } /** * 根据ID批量删除用户信息 * @Author * @CreateTime 2019/6/8 16:55 */ @RequestMapping("/deleteInfoList") public void removeByIds(){ List<String> userIdlist = new ArrayList<>(); userIdlist.add("12"); userIdlist.add("13"); userService.removeByIds(userIdlist); } /** * 根据指定字段删除用户信息 * @Author * @CreateTime 2019/6/8 16:57 */ @RequestMapping("/deleteInfoMap") public void removeByMap(){ //kay是字段名 value是字段值 Map<String,Object> map = new HashMap<>(); map.put("skill","删除"); map.put("fraction",10L); userService.removeByMap(map); } }
sql条件查询
package com.dw.study.controller; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.dw.study.model.MyUser; import com.dw.study.service.impl.UserServiceImpl; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @Author dw * @ClassName MyUserControllerPlus * @Description MyBatis-Plus的 QueryWrapper条件构造器 * @Date 2020/6/11 14:26 * @Version 1.0 */ @RestController public class MyUserControllerPlus { @Autowired private UserServiceImpl userService; /** * MP扩展演示 * @Author Sans * @CreateTime 2019/6/8 16:37 * @Return Map<String,Object> 返回数据 */ @RequestMapping("/getInfoListPlus") public Map<String,Object> getInfoListPage(){ //初始化返回类 Map<String,Object> result = new HashMap<>(); //查询年龄等于18岁的学生 //等价SQL: SELECT id,name,age,email FROM my_user WHERE age = 18 QueryWrapper<MyUser> queryWrapper1 = new QueryWrapper<>(); queryWrapper1.lambda().eq(MyUser::getAge,18); List<MyUser> userInfoEntityList1 = userService.list(queryWrapper1); result.put("studentAge18",userInfoEntityList1); //查询年龄大于5岁的学生且小于等于18岁的学生 //等价SQL: SELECT id,name,age,skill,evaluate,fraction FROM user_info WHERE age > 5 AND age <= 18 QueryWrapper<MyUser> queryWrapper2 = new QueryWrapper<>(); queryWrapper2.lambda().gt(MyUser::getAge,5); queryWrapper2.lambda().le(MyUser::getAge,18); List<MyUser> userInfoEntityList2 = userService.list(queryWrapper2); result.put("studentAge5",userInfoEntityList2); //模糊查询name字段带有"刘亦菲"的数据,并按照年龄降序 //等价SQL: SELECT id,name,age,email FROM my_user WHERE age = 18 WHERE name LIKE '%刘亦菲%' ORDER BY age DESC QueryWrapper<MyUser> queryWrapper3 = new QueryWrapper<>(); queryWrapper3.lambda().like(MyUser::getName,"刘亦菲"); queryWrapper3.lambda().orderByDesc(MyUser::getAge); List<MyUser> userInfoEntityList3 = userService.list(queryWrapper3); result.put("studentAgeName",userInfoEntityList3); //模糊查询名字带有"小"或者年龄大于18的学生 //等价SQL:SELECT id,name,age,email FROM my_user WHERE age = 18 WHERE name LIKE '%小%' OR age > 18 QueryWrapper<MyUser> queryWrapper4 = new QueryWrapper<>(); queryWrapper4.lambda().like(MyUser::getName,"小"); queryWrapper4.lambda().or().gt(MyUser::getAge,18); List<MyUser> userInfoEntityList4 = userService.list(queryWrapper4); result.put("studentOr",userInfoEntityList4); //查询评价不为null的学生,并且分页 //等价SQL: SELECT id,name,age,email FROM my_user WHERE age = 18 WHERE evaluate IS NOT NULL LIMIT 0,5 IPage<MyUser> page = new Page<>(); page.setCurrent(1); page.setSize(5); QueryWrapper<MyUser> queryWrapper5 = new QueryWrapper<>(); queryWrapper5.lambda().isNotNull(MyUser::getEmail); page = userService.page(page,queryWrapper5); result.put("studentPage",page); return result; } }
问题：分页查询总数不准确(eg:查询结果聚合)，自定义sql进行count
page.setSearchCount(false); page.setTotal(mapper.repListCount(sns));
更多操作，，请参考官网，以上只做部分示例
