Mybatis Plus 入门

使用SpringBoot集成MybatisPlus

Mybatis-Plus官网

  1. pom
<dependency>
	 <groupId>com.baomidou</groupId>
	 <artifactId>mybatis-plus-boot-starter</artifactId>
	 <version>3.2.0</version>
</dependency>
  1. yml
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/user?useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: 123456
#打开打印sql
logging:
  level:
    com.scitc.dao: trace
#  pattern:
#    console: '%p%m%n'
########################################  后面配置的
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml   #配置mapper.xml位置
  global-config:
    db-config:
      id-type: uuid   #全局主键策略
#  config-location: classpath:mybatis-config.xml
  type-aliases-package: com.scitc.entity

#  config-location和configuration同时出现会错
  configuration:
    map-underscore-to-camel-case: true
  1. entity
@Data
@TableName("tb_user") //指定表名
@EqualsAndHashCode(callSuper = false)
public class User extends Model<User> {
    @TableId(type = IdType.AUTO) //指定主键自增策略
    private Long id;
    //指定列名 使用entity作为条件查询的时候,模糊字段
    @TableField(value = "name" condition = SqlCondition.LIKE)
    private String name;
    //指定默认查询条件
    //@TableField(condition = "%s&gt;#{%s}")
    private Integer age;
    private String email;
    private Long managerId;
    private LocalDateTime createTime;
}

  1. mapper

继承BaseMapper<T>

public interface UserMapper extends BaseMapper<User> {
}

常用注解

  1. @TableName 表名注解
    在这里插入图片描述
  2. @TableId 主键注解,不设置的话默认为id字段
    在这里插入图片描述
    IdType
    在这里插入图片描述
  3. @TableField
  • 也可以自定义 condition

  • 设置了@TableField(update = "%s+1"),不管设置多少就只+1

  • 忽略表字段三种方式

    • transient关键字
    private transient String remark;
    
    • 使用 static关键字
    private static String remark;
    
    • @TableField(exist = false) 用这个

在这里插入图片描述在这里插入图片描述

crud操作

1. insert

添加成功后,主键id会被添加到user.id中

//junit5
@SpringBootTest
@ExtendWith(SpringExtension.class)

public class UserMapperTest {
    @Autowired
    private UserMapper userMapper;


    @Test
    public void insertUserTest() {
        User user = new User();
        user.setName("小咸鱼233333");
        user.setAge(30);
        user.setEmail("4444444@qq.com");
        user.setManagerId(1L);
        user.setCreateTime(LocalDateTime.now());
        int rows = userMapper.insert(user);
        System.out.println("rows:" + rows);
        System.out.println("user:" + user);
    }
}

2. 查询

普通查询

  • 根据id查询
@Test
public void selectByIdTest() {
    User user = userMapper.selectById(10L);
    System.out.println(user);
}
  • 根据id批量查询
@Test
public void selectBatchIdsTest() {
    List<Long> longList = Arrays.asList(1L, 2L);
    List<User> users = userMapper.selectBatchIds(longList);
    users.forEach(System.out::println);
}
  • map条件查询
@Test
public void selectByMapTest() {
    Map<String, Object> map = new HashMap<>();
    map.put("age", 58);
//    key对应的数据库字段
//    where age=85
    List<User> users = userMapper.selectByMap(map);
    users.forEach(System.out::println);
}

条件构造查询

构建条件的方式

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

QueryWrapper<User> query = Wrappers.query();

几个案例

  1. 名字中包含雨,并且年龄小于40
    name like '%雨%' and age<40

    @Test
    public void selectByWrapperTest1() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name", "雨").lt("age", 40);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  2. 名字中包含 雨,并且年龄大于等于20且小于等于40并且email不为空
    name like '%雨%' and age between 20 and 40 and email is not null

    @Test
    public void selectByWrapperTest2() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  3. 姓王或者年龄大于25,按照年龄呢降序排序,年龄相同按照id升序排序
    name like '王%' or age>=25 order by age desc,id asc

    @Test
    public void selectByWrapperTest3() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王").or().ge("age", 25).orderByDesc("age").orderByAsc("id");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  4. 创建日期为2019年2月14日并且直属上级为名字为王姓
    date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')

    @Test
    public void selectByWrapperTest4() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14")
                .inSql("manager_id", "select id from tb_user where name like '王%'");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  5. 名字为王姓并且(年龄小于40或邮箱不为空)
    name like '王%' and (age<40 or email is not null)

    @Test
    public void selectByWrapperTest5() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王").and(wq -> wq.lt("age", 40).or().isNotNull("email"));
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  6. 名字为王姓或者(年龄小于40并且年龄大于20并且邮箱不为空)
    name like '王%' or (age<40 and age>20 and email is not null)

    @Test
    public void selectByWrapperTest6() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王").or(wp -> wp.lt("age", 40).gt("age", 20).isNotNull("email"));
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  7. (年龄小于40或邮箱不为空)并且名字为王姓
    (age<40 or email is not null) and name like '王%'

    @Test
    public void selectByWrapperTest7() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(wp -> wp.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  8. 年龄为30、31、34、35
    age in (30、31、34、35)

    @Test
    public void selectByWrapperTest8() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30, 31, 34, 35));
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  9. 只返回满足条件的其中一条语句即可
    limit 1

    @Test
    public void selectByWrapperTest9() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    

select不查处全字段

  1. 名字中包含雨并且年龄小于40
    select id,name,age,email

    @Test
    public void selectByWrapperSupper2() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(User.class, info -> !info.getColumn().equals("create_time") &&
                !info.getColumn().equals("manager_id")).like("name", "雨").lt("name", 40);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  2. 排除不相关字段
    名字中包含雨并且年龄小于40(需求1加强版)
    select id,name,age,email

    @Test
    public void selectByWrapperSupper2() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(User.class, info -> !info.getColumn().equals("create_time") &&
                !info.getColumn().equals("manager_id")).like("name", "雨").lt("age", 40);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
  • entity 查询
@Test
public void selectByWrapperEntity() {

    User user = new User();
    user.setName("张");
    user.setAge(30);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}
  • allEq
@Test
public void selectByWrapperAllEq() {

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Map<String, Object> params = new HashMap<>();
    params.put("name", "王天风");
    params.put("age", null);
    queryWrapper.allEq(params);

    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}
  • selectMaps
    查什么就只有什么字段映射
@Test
public void selectByWrapperMaps() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);

    List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
    userList.forEach(System.out::println);
   /*结果
    {name=张雨琪, id=1094590409767661570}
	{name=刘红雨, id=1094592041087729666}
	{name=大雨, id=1194180916445003778}
	*/
}
  • 按照直属上级分组,查询每组的平均年龄、最大年龄、最小年龄。并且只取年龄总和小于500的组
    select avg(age) avg_age,min(age) min_age,max(age) max_age from user group by manager_id having sum(age) <500
@Test
public void selectByWrapperMaps2() {

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    queryWrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age")
            .groupBy("manager_id").having("sum(age)<{0}", 500);
    List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
    userList.forEach(System.out::println);
}
  • selectObjs 只返回第一个字段的值
@Test
public void selectByWrapperObjs() {

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);

    List<Object> objectList = userMapper.selectObjs(queryWrapper);
    System.out.println("objectList:" + objectList);
//        objectList:[1094590409767661570, 1094592041087729666, 1194180916445003778]
}
  • selectCount 根据 Wrapper 条件,查询总记录数
  • selectOne 查一条,多了报错

lambda三种方式

1.LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
2.LambdaQueryWrapper<User> lambda2 = new LambdaQueryWrapper<>();
3. LambdaQueryWrapper<User> lambda3 = Wrappers.<User>lambdaQuery();

@Test
public void selectLambda() {
    LambdaQueryWrapper<User> lambda3 = Wrappers.<User>lambdaQuery();
    lambda3.like(User::getName,"雨").lt(User::getAge,40);
    List<User> users = userMapper.selectList(lambda3);
    users.forEach(System.out::println);
}
@Test
public void selectLambda2() {
    List<User> userList = new LambdaQueryChainWrapper<User>(userMapper)
            .like(User::getName, "雨")
            .gt(User::getAge, 20).list();
    userList.forEach(System.out::println);
}

分页查询

他的分页已经封装好了,直接传入 当前页码(current)—> page,记录条数(size)—>pageSize

@Test
public void selectPage(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
   queryWrapper.ge("age", 50);

    Page<User> page = new Page<>(1,5);
    IPage<User> userIPage = userMapper.selectPage(page, queryWrapper);
    List<User> userList = userIPage.getRecords();
    userList.forEach(System.out::println);
}
自定义分页

mapper

  List<User> selectMyLimit(@Param("start") Integer start, @Param("size") Integer size);

mapper.xml

    <select id="selectMyLimit" resultType="com.scitc.entity.User">
        SELECT id,name,age,email,manager_id,create_time FROM tb_user
        ORDER BY create_time
        limit  #{start},#{size}
    </select>

调用

    @Test
    public void MyLimit(){
        List<User> userList = userMapper.selectMyLimit(2,5);
        userList.forEach(System.out::println);
    }
Wrapper 构造条件分页

mapper

IPage<User> selectPageWrapper(Page page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);

mapper.xml

<select id="selectPageWrapper" resultType="com.scitc.entity.User">
    SELECT id,name,age,email,manager_id,create_time FROM tb_user
    ${ew.customSqlSegment}
</select>

调用

@Test
public void queryWrapperLimit2(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.gt("age",30).like("name","雨");
    Page<User> page = new Page<>(1,10);
    IPage<User> userIPage = userMapper.selectPageWrapper(page, queryWrapper);
    List<User> userList = userIPage.getRecords();

    userList.forEach(System.out::println);
}

更新

@Test
public void updateUser(){
    User user = new User();
    user.setName("哈哈哈哈");
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("id","1194173798547456001");
    int update = userMapper.update(user, wrapper);
    System.out.println(update);
}

删除

@Test
public void deleteUser(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("id","1194173798547456001");
    int effectedNum = userMapper.delete(wrapper);
    System.out.println(effectedNum);`在这里插入代码片`

}

批量删除

@Test
public void deleteUser2(){
    List<Long> longList = Arrays.asList(1194525879024263169L, 1194208277894840321L);
    int effectedNum = userMapper.deleteBatchIds(longList);
    System.out.println(effectedNum);
}

AR模式

public class User extends Model<User> {
	...
}

通过user对象去调crud方法

User user = new User();

  1. 添加:user.insert()
  2. 查询:user.selectById();
  3. 更新:user.updateById()
    4.删除: user.deleteById();

通用service

继承 extends IService<User>

public interface UserService extends IService<User> {
}

impl

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

测试:
getOne() 有多个 result 是否抛出异常

    @Test
    public void getOne() {
        User one = userService.getOne(Wrappers.<User>lambdaQuery().ge(User::getAge, 30), false);

        System.out.println(one);
    }

saveBatch():批量添加

@Test
public void saveBatch() {
    User user1 = new User();
    user1.setName("批量用户1");
    user1.setAge(30);
    user1.setManagerId(1L);
    user1.setEmail("123466789@qq.com");

    User user2 = new User();
    BeanUtils.copyProperties(user1, user2);
    user2.setName("批量用户2");
    List<User> userList = Arrays.asList(user1, user2);

    boolean batch = userService.saveBatch(userList);
    System.out.println(batch);
}
  • 批量修改 updateBatchById
@Test
public void updateBatch() {
    User user1 = new User();
    user1.setId(1L);
    user1.setName("批量用户11");

    User user2 = new User();
    user2.setId(2L);
    user2.setName("批量用户22");

    List<User> userList = Arrays.asList(user1, user2);
    boolean batch = userService.updateBatchById(userList);
    System.out.println(batch);
}
  • 调用lambdaQuery
@Test
public void chain() {
    List<User> userList = userService.lambdaQuery().gt(User::getAge, 25).list();
    userList.forEach(System.out::println);
}

学习于 慕课网

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页