Mybatis-plus 入门笔记（一）配置、查询、自定义查询

Myth丶恋晨 2023-06-18 06:53 14阅读 0赞

1、实体和数据库中对应

@Table

@TableId

@TableField

其中TableField

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mjc1MjMz_size_16_color_FFFFFF_t_70][]

2、排除非表字段的三种

a）在字段前家transit

b)  设置字段为static，并添加get、set方法

c)  @TableField(exist = false) 表示不是表中的字段

3、配置文件

>     spring:
>       datasource:
>         driver-class-name: com.mysql.cj.jdbc.Driver
>         url: jdbc:mysql://127.0.0.1:3306/mybatis_plus?useSSL=true&serverTimezone=GMT%2B8
>         username: root
>         password: 123456
>     logging:
>       level:
>         root: warn
>         com.smiletimli.mybatis_plus_demo.dao: trace
>       pattern:
>         console: '%p%m%n'

4、简单查询使用

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
    import com.baomidou.mybatisplus.core.toolkit.Wrappers;
    import com.smiletimli.mybatis_plus_demo.entity.User;
    import org.junit.Assert;
    import org.junit.Before;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import javax.annotation.Resource;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class UserMapperTest {
    
        @Resource
        private UserMapper userMapper;
    
        /**
         * 普通方法查询全部
         */
        @Test
        public void selectList() {
            List<User> userList = userMapper.selectList(null);
            Assert.assertEquals(5,userList.size());
            userList.forEach(System.out::println);
        }
    
        /**
         * selectByMap
         */
        @Test
        public void selectByMap() {
            Map<String,Object> map = new HashMap<>();
            map.put("age",25);
            List<User> userList = userMapper.selectByMap(map);
            userList.forEach(System.out::println);
        }
    
        @Before
        public void test() {
            System.out.println("考试");
        }
    
    
        /**
         * 1、名字中包含雨并且年龄小于40
         * 	name like '%雨%' and age<40
         */
        @Test
        public void selectLikeAndLt() {
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.likeRight("name","雨").lt("age", 40);
            List<User> userList = userMapper.selectList(queryWrapper);
            Assert.assertEquals(2,userList.size());
            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 selectByListBetwweenAndNotNull() {
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            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 selectListByListOrAgeOrderBy(){
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            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 selectApplyAndInSql() {
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14")
                    .inSql("manager_id","select id from 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 selectByLikeAnd() {
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.likeRight("name","王").and(i -> i.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 selectByLikeOr() {
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.likeRight("name","王")
                    .or().nested(i -> i.between("age",20,40).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 selectByOr(){
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.nested(i->i.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)
         *  9、只返回满足条件的其中一条语句即可
         *     limit 1
         */
        @Test
        public void selectByInAndLimit() {
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.inSql("age","30,31,32,35").last("limit 1");
            List<User> userList = userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);
        }
    }

5、只查询部分字段

6、根据字段是否为空进行查询

String name = "";
            String age = "25";
            QueryWrapper<User> queryWrapper = Wrappers.<User>query();
            queryWrapper.like(StringUtils.isNotEmpty(name),name,"王")
                       .lt(StringUtils.isNotEmpty(age), age, 40);
            List<User> userList = userMapper.selectList(queryWrapper);
            userList.forEach(System.out::println);

7、使用lambda进行查询

lambdaQuery

LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.<User>lambdaQuery();
            lambdaQueryWrapper.like(User::getName,"王").lt(User::getAge, 40);
            List<User> userList = userMapper.selectList(lambdaQueryWrapper);
            userList.forEach(System.out::println);

`LambdaQueryChainWrapper`

List<User> userList = new LambdaQueryChainWrapper<User>(userMapper)
                    .like(User::getName, "雨").ge(User::getAge, 20).list();
            userList.forEach(System.out::println);

其中list()方法使用的源码如下
    public interface ChainQuery<T> extends ChainWrapper<T> {
        default List<T> list() {
            return this.getBaseMapper().selectList(this.getWrapper());
        }
    
        default T one() {
            return this.getBaseMapper().selectOne(this.getWrapper());
        }
    
        default Integer count() {
            return this.getBaseMapper().selectCount(this.getWrapper());
        }
    
        default IPage<T> page(IPage<T> page) {
            return this.getBaseMapper().selectPage(page, this.getWrapper());
        }
    }

8、自定义sql

方式一：通过注解@Select

public interface UserMapper extends BaseMapper<User> {
    
        @Select("select * from user ${ew.customSqlSegment}")
        List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
    }

方式二：

通过xml文件

>     mybatis-plus:
>       mapper-locations: classpath:/mapper/*Mapper.xml

>     <?xml version="1.0" encoding="UTF-8"?>
>     <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
>             "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
>     <mapper namespace="com.smiletimli.mybatis_plus_demo.dao.UserMapper">
>     
>         <select id="selectAll" resultType="com.smiletimli.mybatis_plus_demo.entity.User">
>           select * from user ${ew.customSqlSegment}
>        </select>
>     
>     </mapper>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mjc1MjMz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191202232628945.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mjc1MjMz,size_16,color_FFFFFF,t_70