mybatisplus学习（二）

 @TableId(value = "id",type = IdType.AUTO)

mybatisplus主键类型：IdType
none:没有主键
auto:自动增长
input：手工输入
id_worker:实体类用Long id，表的列用bigint，int类型大小不够(雪花算法，生成分布式ID)
id_worker_str:实体类使用String id，表的列使用varchar 50
uuid:实体类使用String id，列使用varchar 50
mybatisplus指定表名：
位置：在类定义的上面
通常用在实体类与表名不匹配的情况

@TableName（value="数据库表名")

mybatisplus指定列名：
通常用在实体类属性名与表的列名不匹配的情况下
写在属性定义之上

@TableField(value="数据库中列名")

自定义sql：
1.先编写实体类
2.创建Mapper

package com.example.mybatisplusdemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mybatisplusdemo.entity.Student;

import java.util.List;

public interface StudentMapper extends BaseMapper<Student> {
    //自定义的方法
    public int insertStudent(Student student);
    public Student selectStudentById(Integer id);
    public List<Student> selectByName(String name);
}

3.新建sql映射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.example.mybatisplusdemo.mapper.StudentMapper">
    <insert id="insertStudent">
        insert into student(name,age,email,status) values (#{name},#{age},#{email},#{status})
    </insert>

    <select id="selectStudentById" resultType="com.example.mybatisplusdemo.entity.Student">
        select id,name,age,email,status from student where id=#{StudentId}
    </select>

    <select id="selectByName" resultType="com.example.mybatisplusdemo.entity.Student">
        select id,name,age,email,status from student where name=#{name}
    </select>
</mapper>

4.配置xml文件位置

QueryWrapper:查询条件封装类
方法：select 说明：设置查询字段select后面的内容

alleq:(And 的关系）

allEq(Map<R,V> parama):

@Test
    public void testAllEq(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        HashMap<String, Object> map = new HashMap<>();
        map.put("name","lilei");
        map.put("age",31);
        wrapper.allEq(map);
        List<Student> students = studentMapper.selectList(wrapper);
        students.forEach(student -> System.out.println(student));
    }

allEq(Map<R,V> params ,boolean null2IsNull):
结论：alleq(param,boolean)
true:处理null值，where条件加入 字段is null
false:忽略null，不作为where条件

Map中有对象的key的value是null，使用alleq(params,true),结果是:WHERE name=? AND age IS NULL

 @Test
    public void testAllEq2(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        HashMap<String, Object> map = new HashMap<>();
        map.put("name","lilei");
        map.put("age",null);
        wrapper.allEq(map,true);
        List<Student> students = studentMapper.selectList(wrapper);
        students.forEach(student -> System.out.println(student));
    }

Map对象中有key的value是null，使用alleq(params,false),结果是:WHERE name=?

@Test
    public void testAllEq2(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        HashMap<String, Object> map = new HashMap<>();
        map.put("name","lilei");
        map.put("age",null);
        wrapper.allEq(map,false);
        List<Student> students = studentMapper.selectList(wrapper);
        students.forEach(student -> System.out.println(student));
    }

eq（column,val)：等于

@Test
    public void testeq(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();

        wrapper.eq("name","zhangsan");
        List<Student> students = studentMapper.selectList(wrapper);
        students.forEach(student -> System.out.println(student));
    }

ne(column,val):不等于

@Test
    public void testne(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.ne("name","zhangsan");
        List<Student> students = studentMapper.selectList(wrapper);
        students.forEach(student -> System.out.println(student));
    }

gt大于
ge大于等于
lt小于
le小于等于

between: between(column,val1,val2)
功能：column(列名） between(val1 and val2)
>= val1 and <=val2

@Test
    public void testbetween(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.between("age",10,30);
        List<Student> students = studentMapper.selectList(wrapper);
        for (Student s :
                students) {
            System.out.println(s);
        }
    }

notBetween(column,val1,val2):
column< val1 and column >val2

like:like(column,val):匹配column的值是否包含“val"

 @Test
    public void testLike(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.like("name","zh");
        List<Student> students = studentMapper.selectList(wrapper);
        students.forEach(student -> System.out.println(student));
    }

notLike:列中不包含val的值
likeLeft:likeLeft(column,val)cloumn列中以val结尾的值 ”%值”
likeRight:likeRight(column,val) “值%”

isNull:isNull(column) column列里为空的
isNotNull:isNotNull(column)column列里不为空的

in:in(column,values)cloumn列在values值的列表
notIn：不在values中

inSql: notInSql类似

@Test
    public void testInSql(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.inSql("age","select age from student where id=1");
        print(wrapper);
    }


    public void print(QueryWrapper queryWrapper){
        List<Student> students = studentMapper.selectList(queryWrapper);
        for (Student s :
                students) {
        System.out.println(s);
    }
    }

groupBy:分组

@Test
    public void testGroupBy(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.select("status,count(*) ");
        wrapper.groupBy("status");
        print(wrapper);
    }


    public void print(QueryWrapper queryWrapper){
        List<Student> students = studentMapper.selectList(queryWrapper);
        for (Student s :
                students) {
        System.out.println(s);
    }
    }

排序：
orderBy：每个字段指定排序方向
orderBy(condition,isAsc,columns)
condition是一个boolean值，为true时说明将后面的排序加入sql语句中,为false时说明排序不生效。

orderByAsc:按字段升序
orderByDesc：按字段降序

or():

@Test
    public void testor(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        wrapper.eq("name","lisi")
                .or()
                .eq("age",36);
        print(wrapper);
    }

UpdateWrappe：更新条件封装类
方法：set 说明：设置要更新的字段，MP拼接sql语句
方法：setSql 说明：参数是sql语句，MP 不在处理语句

分页操作：

package com.example.mybatisplusdemo.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
//该注解表示当前类是配置文件
public class Config {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

 @Test
    public void testPage(){
        QueryWrapper<Student> wrapper = new QueryWrapper<>();
        IPage<Student> page = new Page<>();
        //设置分页的数据
        page.setCurrent(2);//第二页
        page.setSize(3);//每一页记录数
        IPage<Student> result = studentMapper.selectPage(page, wrapper);

        //获取分页后的数据
        List<Student> students = result.getRecords();
        System.out.println(students.size());
        long pages = result.getPages();
        System.out.println(pages);
        for (Student s :
                students) {
            System.out.println(s);
        }
    }