MybatisPlus

环境安装

MYSQL

系统安装版本	实际使用版本并没有因为系统安装版本造成不适，但不推荐不匹配的版本

数据库表

数据库准备

-- 创建数据库
create database mybatis_plus default character set utf8mb4;

-- 使用
use mybatis_plus;

-- 创建表
create table user
(
    id    bigint(20) not null comment '主键id',
    name  varchar(30) default null comment '姓名',
    age   int(11)     default null comment '年龄',
    email varchar(50) default null comment '邮箱',
    primary key (id)
) engine = InnoDB default character set = utf8;


-- 创建测试表
CREATE TABLE `tb_user` (
 -- 主键: userId
`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(20) NOT NULL COMMENT '密码',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

测试数据

-- user 表
insert into user  values (1, 'jone', 18, 'test@baomi.com');
insert into user  values (2, 'jack', 20, 'test@baomi.com');
insert into user  values (3, 'tom', 38, 'test@baomi.com');
insert into user  values (4, 'sandy', 18, 'test@baomi.com');
insert into user  values (5, 'Billie', 28, 'test@baomi.com');

--  tb_user 表
-- 插入测试数据
INSERT INTO `tb_user`  VALUES ('1', 'zhangsan', '123456', '张三', '18', 'test@baomi.com');
INSERT INTO `tb_user`  VALUES ('2', 'lisi', '123456', '李四', '20', 'test@baomi.com');
INSERT INTO `tb_user`  VALUES ('3', 'wangwu', '123456', '王五', '28', 'test@baomi.com');
INSERT INTO `tb_user` VALUES ('4', 'zhaoliu', '123456', '赵六', '21', 'test@baomi.com');
INSERT INTO `tb_user` VALUES ('5', 'sunqi', '123456', '孙七', '24', 'test@baomi.com');

Mybatis + MP

创建maven 项目

添加依赖

<dependencies>
    
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus</artifactId>
        <version>3.1.1</version>
    </dependency>
    <!-- MySql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <!-- 连接池 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.11</version>
    </dependency>
    
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
        <version>1.18.4</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.6.4</version>
    </dependency>

</dependencies>

创建子项目

查看数据库数据信息

数据库数据信息

mybatis 回顾使用

创建主配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis_plus?
useUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true&amp;allowMultiQuerie
s=true&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="UserDao.xml"/>
    </mappers>
</configuration>

创建映射文件

<?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.coderitl.dao.UserDao">
    <select id="findAll" resultType="com.coderitl.domain.User">
        select *
        from tb_user
    </select>
</mapper>

创建dao | mapper 接口

package com.coderitl.dao;

import com.coderitl.domain.User;

import java.util.List;

public interface UserDao {
    // 查询所有
    List<User> findAll();
}

mybatis 测试

package com.coderitl.test;

import com.coderitl.dao.UserDao;
import com.coderitl.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;
import java.util.List;

public class TestMybatis {
    @Test
    public void testMybatis() throws Exception {
        String config = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(config);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        List<User> all = mapper.findAll();
        for (User user : all) {
            System.out.println(user);
        }
    }
}

输出结果

`mybatis`-测试

创建实体类

package com.coderitl.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
* 使用 lombok 简化实体类开发
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user")// 指定数据库表名(不一致时) 数据库表名称为 tb_user 
public class User {
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
}

注解作用

注解作用

日志配置

# 创建 log4j.properties
log4j.rootLogger=DEBUG,A1
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%t] [%c]-[%p] %m%n

第一步: 将dao | mapper(接口)继承BaseMapper，将拥有了BaseMapper 中所有的方法

package com.coderitl.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.coderitl.domain.User;

import java.util.List;

/**
 * Mapper | dao 继承该接口后,无需编写 mapper.xml | dao.xml 文件
 * 即可获得 CRUD 功能 这个 Mapper 支持 id 泛型: BaseMapper<User>
 */
public interface UserDao extends BaseMapper<User> {
    // 查询所有
    List<User> findAll();
}

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package com.baomidou.mybatisplus.core.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
/**
* BaseMapper 提供的 CRUD 
*/
public interface BaseMapper<T> extends Mapper<T> {
    int insert(T entity);

    int deleteById(Serializable id);

    int deleteByMap(@Param("cm") Map<String, Object> columnMap);

    int delete(@Param("ew") Wrapper<T> wrapper);

    int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    int updateById(@Param("et") T entity);

    int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);

    T selectById(Serializable id);

    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);

    T selectOne(@Param("ew") Wrapper<T> queryWrapper);

    Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);

    List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);

    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);

    List<Object> selectObjs(@Param("ew") Wrapper<T> queryWrapper);

    IPage<T> selectPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);

    IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
}

查看BaseMapper 中方法idea: ctrl+F12

`BaseMapper` 方法查看

第二步: 使用MP 中的MybatisSqlSessionFactoryBuild 进程构建

@Test
   public void testMybatisSqlSessionBuild() throws Exception {
       String config = "mybatis-config.xml";
       InputStream inputStream = Resources.getResourceAsStream(config);
       // 使用 MP: MybatisSqlSessionFactoryBuilder 进行构建
       SqlSessionFactory sqlSessionFactory = new MybatisSqlSessionFactoryBuilder().build(inputStream);
       SqlSession sqlSession = sqlSessionFactory.openSession();
       UserDao mapper = sqlSession.getMapper(UserDao.class);
       // 调用查询 List 方法
       List<User> all = mapper.selectList(null);
       for (User user : all) {
           System.out.println(user);
       }
   }

整合细节

`MP`	`查询结果`

由于使用MybatisSqlSessionFactoryBuilder 进行了构建,继承的BaseMapper 中的方法就载入到了SqlSession 中(断点调试),所以就可以直接使用相关的方法

`MybatisSqlSessionFactoryBuilder`

SpringBoot+Mybatis+MP

springboot 项目依赖

<dependencies>
    <!-- mybatis-plus -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.2</version>
    </dependency>
    <!-- mysql8 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.31</version>
    </dependency>
    <!-- druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.15</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <!-- test -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <!-- lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

配置文件

spring:
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
      username: root
      password: root
# 需要将 mybatis的配置修改为 mybatis-plus 前缀
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 日志输出
    map-underscore-to-camel-case: false # mysql: userId => java: user_id(false)
  # 全局的 id 类型
  global-config:
    db-config:
      id-type: auto # 主键自增

实体类

package com.coderitl.domain;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user") // 使用注解指定特定表
public class User {
    private Long userId;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
}

测试用例

package com.coderitl.test;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class TestMP {
    @Autowired
    private UserDao dao;

    @Test
    public void testMp() {
        List<User> users = this.dao.selectList(null);
        for (User user : users) {
            System.out.println("user = " + user);
        }
    }
}

测试`BaseMapper` 功能

Mapper接口继承BaseMapper

@Repository
public interface UserMapper extends BaseMapper<User> {
}

认识通用crud

`dao-层 crud`

insert

@Test
void baseMapperInsert() {
    User user = new User();
    user.setName("coder-itl");
    user.setAge(18);
    user.setEmail("12345678@qq.com");
   	// 执行 SQL: ==>  Preparing: INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? )
    int insert = dao.insert(user);
    System.out.println("insert = " + insert);
}

输出测试

数据添加测试

数据删除

根据id 删除

// id 1518887991051976705(插入时生成的雪花id)
@Test
void deleteByIdTest(){
    // 执行 SQL: ==>  Preparing: DELETE FROM user WHERE id=?
    int i = dao.deleteById(1518887991051976705L);
    System.out.println("i = " + i);
}

输出

根据`id` 删除

deleteByMap

@Test
void deleteByMap() {
    Map<String, Object> map = new HashMap<>();
    map.put("id", 1L);
    map.put("age", 18);
    // ==>  Preparing: DELETE FROM user WHERE id = ? AND age = ?
    int i = dao.deleteByMap(map);
    System.out.println("i = " + i);
}

输出

根据`map` 删除

批量删除

@Test
void deleteBatchIdsTest() {
    List<Long> deleteList = Arrays.asList(2L, 3L);
    // ==>  Preparing: DELETE FROM user WHERE id IN ( ? , ? )
    int i = dao.deleteBatchIds(deleteList);
    System.out.println("i = " + i);
}

输出

批量删除

修改功能

updateById

@Test
void updateTest() {
    User user = new User();
    user.setId(4L);
    user.setName("coder-itl");
    user.setAge(18);
    user.setEmail("12345678@qq.com");
    // ==>  Preparing: UPDATE user SET name=?, age=?, email=? WHERE id=?
    int i = dao.updateById(user);
    System.out.println("i = " + i);
}

输出

`updateById`

查询功能

根据id 查询

@Test
void selectTest() {
    User user = dao.selectById(1L);
    // ==>  Preparing: SELECT id,name,age,email FROM user WHERE id=?
    System.out.println("user = " + user);
}

输出

根基`id` 查询

批量查询

@Test
void selectBatchIdsTest() {
    List<Long> deleteList = Arrays.asList(4L, 5L);
    // ==>  Preparing: SELECT id,name,age,email FROM user WHERE id IN ( ? , ? )
    List<User> i = dao.selectBatchIds(deleteList);
    System.out.println("i = " + i);
}

输出

批量查询

selectByMap

@Test
void selectByMap() {
    Map<String, Object> map = new HashMap<>();
    map.put("id", 1L);
    map.put("age", 18);
    List<User> i = dao.selectByMap(map);
    System.out.println("i = " + i);
}

输出

多条件查询

@TableField 注解作用

在MP 中通过@TableField 注解可以指定字段的一些属性,常常解决的问题有2 个

对象中的属性名和字段名不一致的问题(非驼峰)
对象中的属性字段在表中不存在的问题

package com.coderitl.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user")
public class User {
    private Long id;
    private String userName;
    // 查询时不希望查询出该结果
    @TableField(select = false)
    private String password;
    private String name;
    private Integer age;
    // 指定数据库表中字段名
    @TableField("email")
    private String mail;
    // 在数据库表中是不存在的
    @TableField(exist = false)
    private String address;
}

条件更新

@Test
    public void testWrapper() {
        User user = new User();
        user.setAge(18);
        user.setPassword("12094678");
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // 条件
        wrapper.eq("user_name", "zhangsan");
        // 根据条件更新
        int update = dao.update(user, wrapper);
        System.out.println("update = " + update);
    }


@Test
public void testWrapper2() {
    UpdateWrapper<User> wrapper = new UpdateWrapper<>();
    wrapper.set("age", 29)
        .set("password", "123") // 更新的字段
        .eq("user_name", "zhangsan"); // 更新的条件
    // 根据条件更新
    int update = dao.update(null, wrapper);
    System.out.println("update = " + update);
}

条件更新

条件删除

//  ==>  Preparing: DELETE FROM tb_user WHERE user_name = ? 
@Test
public void testWrapper3() {
    UpdateWrapper<User> wrapper = new UpdateWrapper<>();
    wrapper.eq("user_name", "zhangsan");
    // 根据条件更新
    int delete = this.dao.delete(wrapper);
    System.out.println("update = " + delete);
}


==>  Preparing: DELETE FROM tb_user WHERE user_name=? 
 @Test
 public void testWrapper3() {
    User user = new User();
    // 将实体对象进行包装,包装为操作条件
    UpdateWrapper<User> wrapper = new UpdateWrapper<>(user);
    user.setUserName("lisi");
    // 根据条件更新
    int delete = this.dao.delete(wrapper);
    System.out.println("update = " + delete);
}

Service

接口

package com.example.mpdemo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.mpdemo.entity.User;

public interface UserService extends IService<User> {
}

实现类

package com.example.mpdemo.service.impl;

import com.example.mpdemo.entity.User;
import com.example.mpdemo.mapper.UserMapper;
import com.example.mpdemo.service.UserService;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

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

配置

ConfigLocation

mybatis 配置文件位置,如果有单独的mybatis 配置,请将其路径配置到configLocation 中

springboot 配置方案

1 2	mybatis-plus: config-location: classpath:mybatis-config.xml

spring-mvc 配置方案

1
2
3

<bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
	<property name="typeAliasesPackage" value="com.baomidou.mybatisplus.samples.quickstart.entity"/>
</bean>

MapperLocations

mybatis mapper 所对应的xml 文件位置,如果在mapper 中有自定义方法,需要进行该配置,告诉mapper 所对应的xml 文件位置

springboot 配置方案

1 2	mybatis-plus: mapper-locations: classpath:mybatis/.xml

spring-mvc 配置方案

<bean id="sqlSessionFactory"
class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
	<property name="mapperLocations" value="classpath*:mybatis/*.xml"/>
</bean>

TypeAliasesPackage

mybatis 别名包扫描路径,通过该属性可以给包中的类注册别名,注册后在mapper 对应的xml 文件中可以直接使用类名,而不是使用全限定的类名(即xml 中调用的时候不用包含包名)

springboot 配置方案

1
2
3

# mybatis-plus 配置实体类所在包路径 
mybatis-plus:
  type-aliases-package: com.coderitl.domain

spring-mvc 配置方案

<bean id="sqlSessionFactory"
class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
	<property name="typeAliasesPackage" value="com.baomidou.mybatisplus.samples.quickstart.entity"/>
</bean>

进阶配置

支持原生mybatis 配置，意味着可以通过mybatis的xml 配置文件的形式进行配置

MapUnderscoreToCamelCase

类型:boolean
默认值: true

是否开启自动驼峰命名规则(camel case)映射,即A_COLUMN(下划线命名)到java 属性名aColumn(驼峰命名)的类似映射

注意: 此属性在mybatis 中原默认值为false，在mybatis-plus 中,此属性也将用于生成最终的sql 的select body

springboot

# 关闭自动驼峰映射，该参数不能和 mybatis-plus.config-location 同时存在
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false

驼峰映射

CacheEnabled

类型: boolean

默认值: true

全局的开启或关闭配置文件中的所有映射器已经配置的任何缓存,默认值为true

1
2
3

mybatis-plus:
  configuration:
    cache-enabled: false

DB 策略

idType

类型:com.baomidou.mybatisplus.annotation.IdType
默认值:ID_WORKER

全局默认主键类型,设置后,即可省略实体对象中的@TableId(type=idType.AUTO)配置

springboot

mybatis-plus:
  global-config:
    db-config:
      id-type: auto

spring-mvc


<bean id="sqlSessionFactory"
class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource"/>
	<property name="globalConfig">
		<bean class="com.baomidou.mybatisplus.core.config.GlobalConfig">
			<property name="dbConfig">
		<bean class="com.baomidou.mybatisplus.core.config.GlobalConfig$DbConfig">
			<property name="idType" value="AUTO"/>
		</bean>
	</property>
	</bean>
	</property>
</bean>

TablePrefix

类型： String
默认值： null

表名前缀，全局配置后可省略@TableName() 配置

springboot

mybatis-plus:
  global-config:
    db-config:
      table-prefix: tb_

spring-mvc

<bean id="sqlSessionFactory"
          class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="globalConfig">
            <bean class="com.baomidou.mybatisplus.core.config.GlobalConfig">
                <property name="dbConfig">
                    <bean
                            class="com.baomidou.mybatisplus.core.config.GlobalConfig$DbConfig">
                        <property name="idType" value="AUTO"/>
                        <!-- 配置 -->
                        <property name="tablePrefix" value="tb_"/>
                    </bean>
                </property>
            </bean>
        </property>
    </bean>

条件构造器

allEq

@Test
public void testAllEq() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //设置条件
    Map<String, Object> params = new HashMap<>();
    params.put("name", "wangwu");
    params.put("age", "20");
    params.put("password", null);
    //SELECT * FROM tb_user WHERE password IS NULL AND name = ?AND age = ? null作为条件出现
    // wrapper.allEq(params);
    // wrapper.allEq(params,false); //SELECT * FROM tb_user WHERE name = ? AND age = ?
    // wrapper.allEq((k, v) -> (k.equals("name") || k.equals("age")) ,params);//SELECT * FROM tb_user WHERE name = ? AND age = ?
    List<User> users = this.dao.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

基本比较操作

eq 等于 =
ne 不等于 <>
gt 大于 >
ge 大于等于 >=
lt 小于 <
le 小于等于 <=
between BETWEEN 值1 AND 值2
notBetween NOT BETWEEN 值1 AND 值2 in 字段 IN (value.get(0), value.get(1), ...)

in 字段 IN (value.get(0), value.get(1), ...)

@Test
public void testEq() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,user_name,password,name,age,email FROM tb_user WHERE password = ?  AND age >= ? AND name IN (?,?,?)
    wrapper.eq("password", "123456")
        .ge("age", 20)
        .in("name", "李四", "王五", "赵六");
    List<User> users = this.dao.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

模糊查询

like LIKE '%值%' 例: like("name", "王") ---> name like '%王%'
notLike NOT LIKE '%值%' 例: notLike("name", "王") ---> name not like '%王%'

likeLeft LIKE '%值' 例: likeLeft("name", "王") ---> name like '%王' likeRight LIKE '值%' 例: likeRight("name", "王") ---> name like '王%'

@Test
public void testWrapper() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ?
    //Parameters: %曹%(String)
    wrapper.like("name", "曹");
    List<User> users = this.dao.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

排序

orderBy 排序：ORDER BY 字段, ... 例: orderBy(true, true, "id", "name") ---> order by id ASC,name ASC
orderByAsc 排序：ORDER BY 字段, ... ASC 例: orderByAsc("id", "name") ---> order by id ASC,name ASC

orderByDesc 排序：ORDER BY 字段, ... DESC 例: orderByDesc("id", "name") ---> order by id DESC,name DESC

@Test
public void testWrapperSort() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age DESC
    wrapper.orderByDesc("age");
    List<User> users = this.dao.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

逻辑查询

or拼接 OR 主动调用 or 表示紧接着下一个方法不是用 and 连接!(不调用 or 则默认为使用 and 连接)

and AND 嵌套例: and(i -> i.eq("name", "李白").ne("status", "活着")) ---> and (name = '李白' and status <> '活着')

@Test
public void testWrapperOr() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? OR age = ?
    wrapper.eq("name", "李四").or().eq("age", 24);
    List<User> users = this.dao.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

Select

在MP 查询中，默认查询所有的字段，如果有需要也可以通过select 方法进行指定字段

@Test
public void testWrapperSelect() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,name,age FROM tb_user WHERE name = ? OR age = ?
    wrapper.eq("name", "李四")
        .or()
        .eq("age", 24)
        .select("id", "name", "age");
    List<User> users = this.dao.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

分页查询

element-ui 组件pagination

</el-table>
<!-- 分页 -->
<el-pagination
               @size-change="handleSizeChange"
               @current-change="handleCurrentChange"
               :current-page="queryInfo.pages"
               :page-sizes="[1, 2, 5, 10]"
               :page-size="queryInfo.pagesize"
               layout="total, sizes, prev, pager, next, jumper"
               :total="total">
</el-pagination>

页面分析

分页组件分析

数据模型

queryInfo: {
	// 当前的页数
	pages: 1,
    // 当前每页显示多少条数据
    pagesize: 10,
},
// 分页获取的总条数
total: 0

Mybatis-Plus 主要进行拦截器配置

package com.example.dormitorymanager.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 配置分页插件
 */
@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

控制器

/**
     * 学生信息分页查询
     *
     * @param student
     * @return
     */
@RequestMapping("/page")
public R<Page> page(@RequestBody Student student) {
    log.info("page={},pagesize={}", student.getPages(), student.getPagesize());
    // 构造分页构造器
    Page pageInfo = new Page(student.getPages(), student.getPagesize());
    // 构造条件构造器
    LambdaQueryWrapper<Student> queryWrapper = new LambdaQueryWrapper<>();
    // 不等于空 SQL 才会出现
    queryWrapper
        .like(StringUtils.isNotEmpty(student.getS_name()), Student::getS_name, student.getS_name())
        .or()
        .like(StringUtils.isNotEmpty(String.valueOf(student.getS_studentid())), Student::getS_studentid, student.getS_studentid())
        .or()
        .like(StringUtils.isNotEmpty(String.valueOf(student.getS_classid())), Student::getS_classid, student.getS_classid())
        .or()
        .like(StringUtils.isNotEmpty(student.getS_classname()), Student::getS_classname, student.getS_classname());
    // 执行查询
    studentService.page(pageInfo, queryWrapper);
    return R.success(pageInfo, "成功获取数据所有数据!");
}

前端页面

前端页面

axios 请求

// 封装请求
export function finAllApi(params) {
    return request({
        url: "/admin/page",
        method: "post",
        // json 数据
        data: {
            ...params
        }
    })
}

// 请求调用
finAllApi(this.queryInfo).then(res => {
    console.log(res)
    this.tableData = res.data.data.records;
    // 总页数
    this.total = res.data.data.total

})

注意点

在发送请求时应该明确是get | post ..，不同的请求方式对应的参数处理不同,比如post接受 data，get 接受params

控制器层面也要明确接受参数类型,了解@RequestBody、@RequestParam 等的使用

在起手时应尽可能多分析,不至于漏洞百出

逻辑删除

当前表信息

表结构

yml 配置

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: flag # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
      logic-delete-value: 1 # 逻辑已删除值(默认为 1)
      logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)

实体类添加注解

1 2	@TableLogic // 逻辑删除标记注解 private Integer deleted;

数据库增加、删除列

alter table xx add column deleted int default(0) comment '0: 未删除,1:已删除';
-- 对 user 表添加逻辑删除字段
alter table user add column deleted int default(0) comment '0: 未删除,1:已删除';

-- 删除 user 表的逻辑删除字段
ALTER TABLE USER DROP COLUMN deleted;

-- 添加字段到指定位置
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

测试

@SpringBootTest
public class UserMapperTest {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testTableLogicDelete() {
        userMapper.selectList(null).forEach(user -> System.out.println(user));
        /** ************************************* */
        int deleteById = userMapper.deleteById(1);
        System.out.println(deleteById);
        /** ************************************* */
        userMapper.selectList(null).forEach(user -> System.out.println(user));
    }
}

删除前	删除后	数据库数据

细节注意

在实体类映射过程中、主要数据类型与数据库中数据类型匹配一致
注意表名称,有前缀时需要配置前缀
注意主键类型

注意数据库中字段与实体类字段映射问题

1
2
3

// 实体类     数据库
userId       userId   [配置一致]
userId	     user_id  [在配置中配置 map]

环境安装

数据库表

Mybatis + MP

SpringBoot+Mybatis+MP

测试BaseMapper 功能

Service

配置

ConfigLocation

MapperLocations

TypeAliasesPackage

进阶配置

MapUnderscoreToCamelCase

CacheEnabled

DB 策略

idType

TablePrefix

条件构造器

allEq

基本比较操作

模糊查询

排序

逻辑查询

Select

分页查询

逻辑删除

细节注意

测试`BaseMapper` 功能