mybatis-plus的CRUD详细操作及实例

Mybatis-plus地址：https://mp.baomidou.com/guide/

Mybatis-plus的基本增删改查操作

创建自己的service接口继承

com.baomidou.mybatisplus.extension.service.IService

public interface UserService extends IService<User> {}

@Service("userService")
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {}

实体

@Data
@Accessors(chain = true)
@TableName("user")
public class User implements Serializable {

    private static final long serialVersionUID = 276143166320688337L;

    @TableId
    private Long id;
    private String username;
    private String password;
    private String phone;
    private String email;
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updateTime;
    private String dbSource;


}

进行基本的CRUD操作

新增

IService新增的方法：

Save、SaveOrUpdate

// 插入一条记录（选择字段，策略插入）
boolean save(T entity);
// 插入（批量）
boolean saveBatch(Collection<T> entityList);
// 插入（批量）
boolean saveBatch(Collection<T> entityList, int batchSize);

// TableId 注解存在更新记录，否插入一条记录
boolean saveOrUpdate(T entity);
// 根据updateWrapper尝试更新，否继续执行saveOrUpdate(T)方法
boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper);
// 批量修改插入
boolean saveOrUpdateBatch(Collection<T> entityList);
// 批量修改插入
boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize);

 

// 简单新增
 @RequestMapping(value = "/add", method = RequestMethod.POST)
    public boolean add(@RequestBody User user) {
        ValidatorUtils.validateEntity(user, AddGroup.class);
        user.setCreateTime(new Date());
        user.setDbSource("testdb01");
        return userService.save(user);
    }


// 批量新增
    @RequestMapping(value = "/add2", method = RequestMethod.POST)
    public boolean add2(@RequestBody List<User> userList) {
        ValidatorUtils.validateEntity(userList, AddGroup.class);   //  AddGroup.class
        boolean b = userService.saveBatch(userList);
//        boolean b = userService.saveOrUpdateBatch(userList);

        return b;
    }

删除Remove

// 根据 entity 条件，删除记录
boolean remove(Wrapper<T> queryWrapper);
// 根据 ID 删除
boolean removeById(Serializable id);
// 根据 columnMap 条件，删除记录
boolean removeByMap(Map<String, Object> columnMap);
// 删除（根据ID 批量删除）
boolean removeByIds(Collection<? extends Serializable> idList);

 

// 根据主键ID删除
    @RequestMapping(value = "/delete/{id}", method = RequestMethod.GET)
    public boolean delete(@PathVariable("id") Long id) {
        return userService.removeById(id);
    }

// 删除符合条件的数据
// 删除username和id符合的数据
 @RequestMapping(value = "/delete2", method = RequestMethod.GET)
    public boolean delete2(@RequestBody User user) {
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("username", user.getUsername());
        columnMap.put("id", user.getId());
        return userService.removeByMap(columnMap);
    }

// 批量删除
    @RequestMapping(value = "/delete4", method = RequestMethod.GET)
    public boolean delete4(@RequestBody List<Long> ids) {
        return userService.removeByIds(ids);
    }

// 删除id>10的数据
   @RequestMapping(value = "/delete3", method = RequestMethod.GET)
    public boolean delete3() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
        queryWrapper.gt("id", 10);
        return userService.remove(queryWrapper);
    }

 

修改Update

// 根据 UpdateWrapper 条件，更新记录 需要设置sqlset
boolean update(Wrapper<T> updateWrapper);
// 根据 whereEntity 条件，更新记录
boolean update(T entity, Wrapper<T> updateWrapper);
// 根据 ID 选择修改
boolean updateById(T entity);
// 根据ID 批量更新
boolean updateBatchById(Collection<T> entityList);
// 根据ID 批量更新
boolean updateBatchById(Collection<T> entityList, int batchSize);

// 根据ID修改
@RequestMapping(value = "/update", method = RequestMethod.POST)
    public boolean update(@RequestBody User user) {
        user.setUpdateTime(new Date());
        return userService.updateById(user);
    }


// 批量更新
  @RequestMapping(value = "/update4", method = RequestMethod.POST)
    public boolean update4(@RequestBody List<User> userList) {

        boolean b = userService.updateBatchById(userList);
//        boolean b = userService.saveOrUpdateBatch(userList, userList.size());
        return b;
    }

// 根据条件更新
@RequestMapping(value = "/update2", method = RequestMethod.POST)
    public boolean update2(@RequestBody User user) {
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.isNotNull("db_source");
        boolean b = userService.update(user, updateWrapper);
        return b;
    }


 @RequestMapping(value = "/update3", method = RequestMethod.POST)
    public boolean update3() {
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.isNotNull("db_source");
        return userService.update(updateWrapper);
    }

update2的sql：

查询

Get

// 根据 ID 查询
T getById(Serializable id);
// 根据 Wrapper，查询一条记录。结果集，如果是多个会抛出异常，随机取一条加上限制条件 wrapper.last("LIMIT 1")
T getOne(Wrapper<T> queryWrapper);
// 根据 Wrapper，查询一条记录
T getOne(Wrapper<T> queryWrapper, boolean throwEx);
// 根据 Wrapper，查询一条记录
Map<String, Object> getMap(Wrapper<T> queryWrapper);
// 根据 Wrapper，查询一条记录
<V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);

@RequestMapping(value = "/get/{id}", method = RequestMethod.GET)
    public User get(@PathVariable("id") Long id) {

        User user = userService.getById(id);
        return user;
    }

    @RequestMapping(value = "/getOne", method = RequestMethod.GET)
    public User getOne() {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("phone", "13322445555");
//        User one = userService.getOne(queryWrapper);
        User one = userService.getOne(queryWrapper,false);
        return one;
    }

  @RequestMapping(value = "/getMap", method = RequestMethod.GET)
    public Map<String, Object> getMap() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("phone", "13322445555");
        Map<String, Object> one = userService.getMap(queryWrapper);
        return one;
    }


    @RequestMapping(value = "/getObj", method = RequestMethod.GET)
    public Map<String, Object> getObj() {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("phone", "13322445555");
        Function<? super Object, Map<String, Object>> mapper = null;
        Map<String, Object> one = userService.getObj(queryWrapper, mapper);
        return one;
    }

数据库有不止一条符合条件，只返回第一条

List

// 查询所有
List<T> list();
// 查询列表
List<T> list(Wrapper<T> queryWrapper);
// 查询（根据ID 批量查询）
Collection<T> listByIds(Collection<? extends Serializable> idList);
// 查询（根据 columnMap 条件）
Collection<T> listByMap(Map<String, Object> columnMap);
// 查询所有列表
List<Map<String, Object>> listMaps();
// 查询列表
List<Map<String, Object>> listMaps(Wrapper<T> queryWrapper);
// 查询全部记录
List<Object> listObjs();
// 查询全部记录
<V> List<V> listObjs(Function<? super Object, V> mapper);
// 根据 Wrapper 条件，查询全部记录
List<Object> listObjs(Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询全部记录
<V> List<V> listObjs(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);

 @RequestMapping(value = "/list", method = RequestMethod.GET)
    public List<User> list() {

        return userService.list();
    }

@RequestMapping(value = "/list6", method = RequestMethod.GET)
    public List<User> list6( ) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("id", 10);
        List<User> user = userService.list(queryWrapper);
        return user;
    }


    @RequestMapping(value = "/listByIds", method = RequestMethod.GET)
    public Collection<User> listByIds(@RequestBody List<Long> ids) {

        Collection<User> users = userService.listByIds(ids);
        return users;
    }

 @RequestMapping(value = "/listMaps", method = RequestMethod.GET)
    public List<Map<String, Object>> listMaps(Map<String, Object> map ) {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//        queryWrapper.eq("", "");
        List<Map<String, Object>> users = userService.listMaps(queryWrapper);
        return users;
    }

    @RequestMapping(value = "/listObjs", method = RequestMethod.GET)
    public List<Object> listObjs(Map<String, Object> map ) {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//        queryWrapper.eq("", "");
        List<Object> users = userService.listObjs(queryWrapper);
        return users;
    }

Page

// 无条件翻页查询
IPage<T> page(IPage<T> page);
// 翻页查询
IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper);
// 无条件翻页查询
IPage<Map<String, Object>> pageMaps(IPage<T> page);
// 翻页查询
IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);

@RequestMapping(value = "/page", method = RequestMethod.GET)
    public IPage<User> page() {
        IPage<User> page = new Page<>();

        IPage<User> userIPage = userService.page(page);
        return userIPage;
    }

    @RequestMapping(value = "/page2", method = RequestMethod.GET)
    public IPage<User> page2() {

        IPage<User> page = new Page<>();

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("username", "user99999").eq("password","123456");
        IPage<User> userIPage = userService.page(page,queryWrapper);
        return userIPage;
    }

 @RequestMapping(value = "/pageMaps", method = RequestMethod.GET)
    public IPage<Map<String, Object>> pageMaps() {

        IPage<User> page = new Page<>();
        IPage<Map<String, Object>> userIPage = userService.pageMaps(page);
        return userIPage;
    }

    @RequestMapping(value = "/pageMaps2", method = RequestMethod.GET)
    public IPage<Map<String, Object>> pageMaps2() {
        IPage<User> page = new Page<>();
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("password","123456").gt("id",10);
        IPage<Map<String, Object>> userIPage = userService.pageMaps(page,queryWrapper);
        return userIPage;
    }

Count

// 查询总记录数
int count();
// 根据 Wrapper 条件，查询总记录数
int count(Wrapper<T> queryWrapper);

  @RequestMapping(value = "/count", method = RequestMethod.GET)
    public int count() {

        int count = userService.count();
        return count;
    }


 @RequestMapping(value = "/count2", method = RequestMethod.GET)
    public int count2() {

     QueryWrapper<User> queryWrapper = new QueryWrapper<>();
     queryWrapper.gt("id", 10);
     int count = userService.count(queryWrapper);
        return count;
    }

@RequestMapping(value = "/conditionQuery", method = RequestMethod.GET)
public IPage<User> conditionQuery() {
    IPage<User> page = new Page<>();
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Date date = new Date();
    queryWrapper.lt("create_time",date ).gt("update_time",date);

    IPage<User> userIPage = userService.page(page,queryWrapper);
    return userIPage;
}

 @RequestMapping(value = "/conditionQuery2", method = RequestMethod.GET)
    public IPage<User> conditionQuery2() {
        IPage<User> page = new Page<>();
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        Date date = new Date();
        List<Long> ids = new ArrayList<>();
        ids.add(1L);
        ids.add(2L);
        ids.add(3L);
        ids.add(4L);
        ids.add(5L);
        ids.add(1253530057372975105L);
        ids.add(1253530057372975106L);

        queryWrapper.lt("create_time",date ).gt("update_time",date).in("id",ids).like("username","9999");
//        queryWrapper.lt("create_time",date ).or().gt("update_time",date).in("id",ids).like("username","9999");

        IPage<User> userIPage = userService.page(page,queryWrapper);
        return userIPage;
    }

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@71c2fe62] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.jdbc.Connection@777e0d76] will not be managed by Spring
JsqlParserCountOptimize sql=SELECT  id,username,password,phone,email,create_time,update_time,db_source  FROM user 
 
 WHERE create_time < ? AND update_time > ? AND id IN (?,?,?,?,?,?,?) AND username LIKE ?
==>  Preparing: SELECT COUNT(1) FROM user WHERE create_time < ? AND update_time > ? AND id IN (?, ?, ?, ?, ?, ?, ?) AND username LIKE ? 
==> Parameters: 2020-04-24 16:41:46.778(Timestamp), 2020-04-24 16:41:46.778(Timestamp), 1(Long), 2(Long), 3(Long), 4(Long), 5(Long), 1253530057372975105(Long), 1253530057372975106(Long), %9999%(String)
<==    Columns: COUNT(1)
<==        Row: 2
==>  Preparing: SELECT id,username,password,phone,email,create_time,update_time,db_source FROM user WHERE create_time < ? AND update_time > ? AND id IN (?,?,?,?,?,?,?) AND username LIKE ? LIMIT ?,? 
==> Parameters: 2020-04-24 16:41:46.778(Timestamp), 2020-04-24 16:41:46.778(Timestamp), 1(Long), 2(Long), 3(Long), 4(Long), 5(Long), 1253530057372975105(Long), 1253530057372975106(Long), %9999%(String), 0(Long), 10(Long)
<==    Columns: id, username, password, phone, email, create_time, update_time, db_source
<==        Row: 1253530057372975105, user99999, 123456, 13322445555, @163, 2020-04-24 10:20:54.0, 2020-09-18 14:30:35.0, clouddb01
<==        Row: 1253530057372975106, user99999, 123456, 13322445555, @163, 2019-12-21 10:20:54.0, 2020-09-01 14:30:35.0, clouddb01
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@71c2fe62]

需要注意的一点：构造条件查询的时候，无论是updateWrapper还是QueryWrapper中的条件字段的字段名与数据库保持一致

 

MybatisPlus的IPage失效问题：mybatis plus分页不出来pages和total的解决记录

解决办法：

在同一项目目录下新建一个配置类

@Configuration
public class MybatisPlusConfig {
    /**
     *   mybatis-plus
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor page = new PaginationInterceptor();
        page.setDialectType("mysql");
        return page;
    }
}

添加之后的效果