依赖

  1. <dependency>
  2.     <groupId>com.baomidou</groupId>
  3.     <artifactId>mybatis-plus</artifactId>
  4.     <version>3.4.2</version>
  5. </dependency>

编写mapper

  1. /*
  2.     TODO:  mp需要dao层接口继承BaseMapper接口
  3.          1. BaseMapper底层封装了很多方法
  4.              1). 返回值类型不知道 -> 泛型指定JavaBean类型
  5.                 比如 BaseMapper<User>
  6.          2. 自动进行sql配置
  7.             1). 表名不知道
  8.                 默认是javaBean的类名
  9.                 如果类名跟表名不一致,需要在类名上加注解 @TableName("tb_user")
  10.             2). 主键字段名不知道
  11.                 默认找名为id的属性
  12.                 如果不一致, 需要在属性上加  @TableId("id")
  13.             3). 非主键字段名不知道
  14.                 默认跟属性名一致, 注意自动小驼峰映射(守护星userName 映射 字段 user_name)
  15.                 如果不一致,需要在属性上加    @TableField("password")    
  16.  */
  17. //@Mapper
  18. public interface UserMapper extends BaseMapper<User> {
  20. //    @Select("select * from tb_user where id = #{id}")
  21. //    User selectById(Long id);
  23. //    @Update("update tb_user set pwd = #{password},user_name= #{userName},name=#{name} where id = #{id}")
  24. //      int updateById(User user);
  25. }

常用方法
image.png

插入

  1.  @Test
  2.     public void insert(){
  3.         /*
  4.             TODO: mp的insert方法的主键生成策略
  5.             1. 主键自增
  6.                 IdType.AUTO
  7.                 主键字段必须number,并且设置了自增
  8.                 (单体架构)
  9.             2. 雪花算法 (默认)
  10.                 IdType.ASSIGN_ID
  11.                 1). 可自增的10进制随机数
  12.                 2). 要求主键类型是number或字符串
  13.                 (分布式)
  14.             3. uuid
  15.                 IdType.ASSIGN_UUID
  16.                 1). 16进制随机数
  17.                 2). 要求主键字段类型为字符串(char,varchar)
  18.          */
  19. //        User user = new User(null, "张三", "123", "瘪三", 18, "zs@itcast.cn");
  20.         //建造者模式
  21.         User user = User.builder()
  22.                 .userName("张三")
  23.                 .password("123")
  24.                 .age(18)
  25.                 .email("zs@itcast.cn").build();
  26.         int count = mapper.insert(user);
  27.         System.out.println("被影响的行数:" + count); // 1
  28.     }
  30. }

删除

  1.  int count = userMapper.deleteById(8L);  //根据id删除
  2. List ids = new ArrayList(); //根据id集合批量删除
  3.         ids.add(6);
  4.         ids.add(7);
  5. userMapper.deleteBatchIds(ids);
  7. Map<String, Object> map = new HashMap<>();  //根据map构造条件，删除
  8. //delete from tb_user where user_name = ? and age = ?
  9. map.put("user_name","itcast");
  10. map.put("age","18");
  12. userMapper.deleteByMap(map);

更新

  1. @Test
  2. public void testUpdateById() {
  3.   User user = new User();
  4.   user.setId(2L);
  5.   user.setPassword("1111111");
  6.   int count = userMapper.updateById(user);
  7. }

分页查询

配置分页拦截器

  1. package com.itheima.sh.config;
  2. import com.baomidou.mybatisplus.annotation.DbType;
  3. import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
  4. import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
  5. import org.springframework.context.annotation.Bean;
  6. import org.springframework.context.annotation.Configuration;
  8. /**
  9.  * @Description:
  10.  * @Version: V1.0
  11.      1. 此配置类的目的创建一个分页拦截器
  12.      2. 此拦截器会被加入到IOC容器中
  13.      3. MP的底层会获取此拦截器并拦截所有查询方法
  15.      效果: 只要设置此拦截器,本工程的所有查询只要调用对应的API就都有分页效果
  16.  */
  17. @Configuration
  18. public class MybatisPlusConfig {
  19.     //此方法的返回值被添加IOC容器中
  20.     @Bean
  21.     public MybatisPlusInterceptor mybatisPlusInterceptor() {
  22.         MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
  23.         //创建分页拦截器
  24.         PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL); // 方言
  25.         interceptor.addInnerInterceptor(paginationInterceptor);
  26.         return interceptor;
  27.     }
  28. }
  1. /**
  2.   * 分页查询：
  3.   *  1. 当前页码：currentPage
  4.   *  2. 每页显示条数：size
  5.   *
  6.   *  注意：使用mp的分页要设置一个拦截器！！！
  7. */
  8. @Test
  9. public void testSelectPage() {
  10.   int current = 1;//当前页码
  11.   int size = 2;//每页显示条数
  12.   IPage<User> page = new Page(current,size);
  13.   userMapper.selectPage(page,null);
  14.   List<User> records = page.getRecords();//当前页的数据
  15.   long pages = page.getPages();//总页数 2
  16.   long total = page.getTotal();//总记录数 4
  17.   System.out.println(records);
  18.   System.out.println(pages);
  19.   System.out.println(total);
  20. }

QueryWrapper实现基础查询

QueryWrapper常用API

  1. eq( ) :  等于 =   (equals)
  2. ne( ) :  不等于 <>   (not equals)
  3. gt( ) :  大于 >      (greater than)
  4. ge( ) :  大于等于  >=    (greater than or equals)
  5. lt( ) :  小于 <        (less than)
  6. le( ) :  小于等于 <=    (less than or equals)
  7. between ( ) :  BETWEEN 值1 AND 值2 
  8. notBetween ( ) :  NOT BETWEEN 值1 AND 值2 
  9. in( ) :  in
  10. notIn( ) ：not in

示例

  1. @Test
  2. public void testWrapper1() throws Exception{
  3.   QueryWrapper<User> wrapper = new QueryWrapper<>();
  5.   // 封装查询条件
  6.   wrapper.like("user_name", "伤")
  7.     .eq("password","123456")
  8.     .in("age",19,25,29)
  9.     .orderByDesc("age","id");   // 降序   升序：asc
  11.   List<User> users = userMapper.selectList(wrapper);
  12.   System.out.println(users);
  13. }
  15. @Test
  16. public void testWrapper2(){
  17.     //1.创建查询条件构建器
  18.     QueryWrapper<User> wrapper = new QueryWrapper<>();
  19.     //2.设置条件
  20.     wrapper.eq("user_name","lisi")
  21.         .or()
  22.         .lt("age",23)
  23.         .in("name","李四","王五");
  24.     /*
  25.             select * from tb_user where user_name = ? or age < ? and name in (?,?)
  26.          */
  27.     List<User> users = userMapper.selectList(wrapper);
  28.     System.out.println(users);
  29. }
  31.     /**
  32.      * 模糊查询
  33.      */
  34.     @Test
  35.     public void testWrapper3(){
  36.         //1.创建查询条件构建器
  37.         QueryWrapper<User> wrapper = new QueryWrapper<>();
  38.         //2.设置条件
  39.         wrapper.likeLeft("user_name","zhang");
  40.         /*
  41.             SELECT id,user_name,password,name,age,email
  42.              from tb_user
  43.              where user_name like ?
  44.              %zhang
  45.          */
  46.         List<User> users = userMapper.selectList(wrapper);
  47.         System.out.println(users);
  48.     }
  50. @Test
  51.     public void testWrapper4(){
  52.         //1.创建查询条件构建器
  53.         QueryWrapper<User> wrapper = new QueryWrapper<>();
  54.         //2.设置条件
  55.         wrapper.eq("user_name","lisi")
  56.                 .or()
  57.                 .lt("age",23)
  58.                 .in("name","李四","王五")
  59.                 //.orderBy(true,true,"age")
  60.                 .orderByDesc("age");
  61.         /*
  62.             select * from tb_user where user_name = ? or age < ? and name in (?,?) order by age asc
  63.          */
  64.         List<User> users = userMapper.selectList(wrapper);
  65.         System.out.println(users);
  66.     }
  68. @Test
  69.     public void testWrapper5(){
  70.         //1.创建查询条件构建器
  71.         QueryWrapper<User> wrapper = new QueryWrapper<>();
  72.         //2.设置条件
  73.         wrapper.eq("user_name","lisi")
  74.                 .or()
  75.                 .lt("age",23)
  76.                 .in("name","李四","王五")
  77.                 //.orderBy(true,true,"age")
  78.                 .orderByDesc("age")
  79.                 .select("id","user_name");  //限定字符串
  80.         /*
  81.             select id,user_name from tb_user where user_name = ? or age < ? and name in (?,?) order by age asc
  82.          */
  83.         List<User> users = userMapper.selectList(wrapper);
  84.         System.out.println(users);
  85.     }

QueryWrapper实现分页条件查询

    @Test
    public void testWrapper6(){
        int current = 1;//当前页码
        int size = 2;//每页显示条数
        //1. 构建分页对象
        Page<User> page = new Page<>(current,size);
        //2. 构建条件对象
        QueryWrapper<User> wrapper = new QueryWrapper();
        wrapper.lt("age",23);
        userMapper.selectPage(page,wrapper);
        List<User> records = page.getRecords();
        long total = page.getTotal();
        long pages = page.getPages();
        System.out.println(records);
        System.out.println(total);//2
        System.out.println(pages);//1
    }

LambdaQueryWrapper查询

@Test
public void testWrapper4() throws Exception{
  // LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
  LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery();

  //        wrapper.like("user_name", "%伤%")
  //                .eq("password","123456")
  //                .ge("age", 28)
  //                .between("age",29 , 39);  // 包含边界值

  wrapper.like(User::getUserName, "%伤%")
    .eq(User::getPassword, "123456")
    .ge(User::getAge, 28)
    .between(User::getAge, 29, 39)
    .orderByDesc(User::getAge)
    .select(User::getId, User::getUserName);


  List<User> users = userMapper.selectList(wrapper);
  System.out.println(users);
}


/**
     * 条件删除
     * @throws Exception
*/
@Test
public void testWrapper5() throws Exception{

  LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery().eq(User::getUserName, "武大郎");
  int i = userMapper.delete(wrapper);
  System.out.println(i);
}

/**
     * 条件更新
     * @throws Exception
*/
@Test
public void testWrapper6() throws Exception{

  /**
     * UPDATE tb_user SET t_name=? WHERE (id = ?)
     */
  // 参数1： 要修改的值
  User user = new User();
  user.setUserName("张三丰");

  // 参数2：更新时条件
  LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery();
  wrapper.eq(User::getId, 15);

  int update = userMapper.update(user, wrapper);
  System.out.println(update);
}


/**
     * 条件更新
     * @throws Exception
     */
@Test
public void testWrapper7() throws Exception{
  /**
         * UPDATE tb_user SET t_name=?, user_name=? WHERE (id = ?)
         */
  // 参数1： 最新的值
  // 参数2：更新时条件
  LambdaUpdateWrapper<User> wrapper = Wrappers.<User>lambdaUpdate();
  wrapper.eq(User::getId, 15)
    .set(User::getUserName, "张三丰666")
    .set(User::getName,"zsf666");

  int update = userMapper.update(null, wrapper);
  System.out.println(update);
}

MP实现Service封装

image.png
用法

//在公共接口的基础上扩展
public interface UserService extends IService<User> {
}

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> 
implements UserService {}

package com.itheima;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.User;
import com.itheima.service.UserService;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.omg.PortableInterceptor.USER_EXCEPTION;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

/**
 * @Description
 * @Created by itheima
 */
@SpringBootTest
public class MpTestService {
    @Autowired
    private UserService userService;

    /**
     * @Description 测试查询操作 根据id查询
     */
    @Test
    public void test1(){
        User user = userService.getById(3l);
        System.out.println(user);
    }

    /**
     * @Description 测试条件查询，且仅返回一个
     * getOne:sql查询的结果必须为1条或者没有，否则报错 ！！！！
     */
    @Test
    public void test2(){
        LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class);
        wrapper.gt(User::getAge,20);
        User one = userService.getOne(wrapper);
        System.out.println(one);
    }

    /**
     * @Description 根据条件批量查询
     */
    @Test
    public void test3(){
        LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class);
        wrapper.gt(User::getAge,20);
        List<User> list = userService.list(wrapper);
        System.out.println(list);
    }

    /**
     * @Description 根据条件批量查询并分页
     */
    @Test
    public void test4(){
        LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class);
        wrapper.gt(User::getAge,20);
        //构建分页对象
        IPage<User> page=new Page<>(2,3);
        userService.page(page,wrapper);
        System.out.println(page.getRecords());
        System.out.println(page.getPages());
        System.out.println(page.getTotal());
    }

    /**
     * @Description 测试服务层save保存单条操作
     */
    @Test
    public void test5(){
        User user1 = User.builder().name("wangwu").userName("laowang4").
                email("444@163.com").age(20).password("333").build();
        boolean isSuccess = userService.save(user1);
        System.out.println(isSuccess?"保存成功":"保存失败");
    }

    /**
     * @Description 测试服务层批量保存
     */
    @Test
    public void test6(){
        User user2 = User.builder().name("wangwu2").userName("laowang2").
                email("444@163.com").age(20).password("333").build();
        User user3 = User.builder().name("wangwu3").userName("laowang3").
                email("444@163.com").age(20).password("333").build();
        boolean isSuccess = userService.saveBatch(Arrays.asList(user2, user3));
        System.out.println(isSuccess?"保存成功":"保存失败");
    }

    /**
     * @Description 根据id删除操作
     */
    @Test
    public void test7(){
        boolean isSuccess = userService.removeById(17l);
        System.out.println(isSuccess?"保存成功":"保存失败");
    }

    /**
     * @Description 根据条件批量删除
     */
    @Test
    public void test8(){
        LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class);
        wrapper.gt(User::getId,12)
                .gt(User::getAge,20);
        boolean remove = userService.remove(wrapper);
        System.out.println(remove);
    }

    /**
     * @Description 测试根据id更新数据
     */
    @Test
    public void test9(){
        //UPDATE tb_user SET password=?, t_name=? WHERE id=?
        User user2 = User.builder().name("wangwu2").password("333").id(3l).build();
        boolean success = userService.updateById(user2);
        System.out.println(success);

    }

    /**
     * @Description 测试根据条件批量更新
     */
    @Test
    public void test10(){
        LambdaUpdateWrapper<User> wrapper = Wrappers.lambdaUpdate(User.class);
        //UPDATE tb_user SET age=? WHERE (id IN (?,?,?))
        wrapper.in(User::getId,Arrays.asList(1l,3l,5l)).set(User::getAge,40);
        boolean update = userService.update(wrapper);
        System.out.println(userService);

    }
}