[Java知识库]快速入门MyBatis-Plus，看这一篇就够了。

1. 创建数据库以及表

-- 创建测试表 CREATE TABLE `tb_user` ( `id` 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 (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- 插入测试数据 INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('1', 'zhangsan', '123456', '张三', '18', 'test1@itcast.cn'); INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('2', 'lisi', '123456', '李四', '20', 'test2@itcast.cn'); INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('3', 'wangwu', '123456', '王五', '28', 'test3@itcast.cn'); INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('4', 'zhaoliu', '123456', '赵六', '21', 'test4@itcast.cn'); INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES ('5', 'sunqi', '123456', '孙七', '24', 'test5@itcast.cn');

2. Mybatis传统写法

2.1 建立实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user")
public class User {

    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
}

2.2 创建UserMapper

public interface UserMapper {

    List<User> findAll();
}

2.3 创建配置文件，建立数据源

<?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.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis_plus?useUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true&amp;allowMultiQueries=true&amp;useSSL=false&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

2.4 创建xml，写SQL

<?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="cn.itcast.mp.simple.mapper.UserMapper">

    <select id="findAll" resultType="cn.itcast.mp.simple.pojo.User">
        select * from tb_user
    </select>

</mapper>

2.5 编写测试用例

public class TestMybatis {

    @Test
    public void testFindAll() throws Exception{

        String config = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(config);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        //测试查询
        List<User> users = userMapper.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }
}

2.6 测试结果

[main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@48ae9b55]
[main] [cn.itcast.mp.simple.mapper.UserMapper.findAll]-[DEBUG] ==>  Preparing: select * from tb_user 
[main] [cn.itcast.mp.simple.mapper.UserMapper.findAll]-[DEBUG] ==> Parameters: 
[main] [cn.itcast.mp.simple.mapper.UserMapper.findAll]-[DEBUG] <==      Total: 4
User(id=null, userName=null, password=123456, name=张三, age=20, email=111111@qq.com)
User(id=null, userName=null, password=135246, name=李四, age=21, email=222222@qq.com)
User(id=null, userName=null, password=234451, name=王五, age=22, email=333333@qq.com)
User(id=null, userName=null, password=3413412, name=赵六, age=23, email=444444@qq.com)

3. Mybatis改造成MyBatis-Plus

3.1 将UserMapper继承BaseMapper，将拥有了BaseMapper中的所有方法：

public interface UserMapper extends BaseMapper{

    List<User> findAll();
}

3.2 使用MP中的MybatisSqlSessionFactoryBuilder进程构建

public class TestMybatisPlus {

    @Test
    public void testFindAll() throws Exception{
        String config = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(config);
        SqlSessionFactory sqlSessionFactory = new MybatisSqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        //测试查询
//        List<User> users = userMapper.findAll();
        List<User> users = userMapper.selectList(null);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

3.3 执行结果

[main] [cn.itcast.mp.simple.mapper.UserMapper.selectList]-[DEBUG] ==>  Preparing: SELECT id,user_name,password,name,age,email FROM tb_user 
[main] [cn.itcast.mp.simple.mapper.UserMapper.selectList]-[DEBUG] ==> Parameters: 
[main] [cn.itcast.mp.simple.mapper.UserMapper.selectList]-[DEBUG] <==      Total: 4
User(id=1, userName=zhangsan, password=123456, name=张三, age=20, email=111111@qq.com)
User(id=2, userName=lisi, password=135246, name=李四, age=21, email=222222@qq.com)
User(id=3, userName=wangwu, password=234451, name=王五, age=22, email=333333@qq.com)
User(id=4, userName=zhaoliu, password=3413412, name=赵六, age=23, email=444444@qq.com)

4. Spring + Mybatis + Mybatis-plus整合

4.1 子工程导入依赖

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>
    </dependencies>

4.2 建立配置文件

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:*.properties"/>

    <!-- 定义数据源 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="maxActive" value="10"/>
        <property name="minIdle" value="5"/>
    </bean>

    <!--这里使用MP提供的sqlSessionFactory，完成了Spring与MP的整合-->
    <bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--扫描mapper接口，使用的依然是Mybatis原生的扫描器-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.itcast.mp.simple.mapper"/>
    </bean>

</beans>

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis_plus?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
jdbc.username=root
jdbc.password=root

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

4.3 实体和mapper类不变

4.4 编写测试用例

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class TestMybatisSpring {

    @Autowired
    private UserMapper userMapper;

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

4.5 执行结果

[main] [cn.itcast.mp.simple.mapper.UserMapper.selectList]-[DEBUG] ==>  Preparing: SELECT id,user_name,password,name,age,email FROM tb_user 
[main] [cn.itcast.mp.simple.mapper.UserMapper.selectList]-[DEBUG] ==> Parameters: 
[main] [cn.itcast.mp.simple.mapper.UserMapper.selectList]-[DEBUG] <==      Total: 4
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2438dcd]
User(id=1, userName=zhangsan, password=123456, name=张三, age=20, email=111111@qq.com)
User(id=2, userName=lisi, password=135246, name=李四, age=21, email=222222@qq.com)
User(id=3, userName=wangwu, password=234451, name=王五, age=22, email=333333@qq.com)
User(id=4, userName=zhaoliu, password=3413412, name=赵六, age=23, email=444444@qq.com)

5. SpringBoot + Mybatis + Mybatis-plus整合

5.1 新建工程，导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.4.RELEASE</version>
    </parent>

    <groupId>cn.itcast.mp</groupId>
    <artifactId>itcast-mp-springboot3</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>


    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--简化代码的工具包-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--mybatis-plus的springboot支持-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
        </dependency>
        <!--        &lt;!&ndash;oracle驱动包&ndash;&gt;
                <dependency>
                    <groupId>com.oracle</groupId>
                    <artifactId>ojdbc8</artifactId>
                    <version>12.1.0.1</version>
                </dependency>-->

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

5.2 配置数据连接

spring.application.name = itcast-mp-springboot3

#mysql连接信息:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis_plus?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root

logging.level.org.springframework.boot.autoconfigure: ERROR 

5.3 实体类和mapper接口不变

5.4 编写测试用例

/**
 * @author LongXi
 * @create 2021-09-23 19:57
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class TestMybatisSpringBoot {
    @Autowired
    private UserMapper userMapper1;

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

5.5 执行结果

6. 通用crud（增删改查）

6.1 Insert

    @Test
    public void testInsert() {
        User user = new User();
        user.setEmail("zhugeliang@itcast.cn");
        user.setAge(25);
        user.setUserName("zhugeliang");
        user.setName("诸葛亮");
        user.setPassword("123456");
        user.setAddress("广州");

        int result = this.userMapper.insert(user); //result数据库受影响的行数
        System.out.println("result => " + result);

        //获取自增长后的id值, 自增长后的id值会回填到user对象中
        System.out.println("id => " + user.getId());
    }

结果ID不正确，ID应该是数据库自增长，现在是java传过去的，所以需要设置ID增长策略。修改如下：指定id自增长策略

/**
 * @author LongXi
 * @create 2021-09-23 19:53
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
    private String address;
}

删除数据，更改主键的值，重新测试

这样就可以正常插入了

6.1.1 @TableField的使用

在MP中通过@TableField注解可以指定字段的一些属性，常常解决的问题有2个：
1、对象中的属性名和字段名不一致的问题（非驼峰）
2、对象中的属性字段在表中不存在的问题
使用1：

通过log打印出来的sql语句可以看到，用的是我们TableField指定的列名。

使用2：

3、不希望字段被查出来

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.selectById]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE id=? 
[main] [cn.itcast.mp.mapper.UserMapper.selectById]-[DEBUG] ==> Parameters: 2(Long)
[main] [cn.itcast.mp.mapper.UserMapper.selectById]-[DEBUG] <==      Total: 1

6.2 更新操作

在MP中，更新操作有2种，一种是根据id更新，另一种是根据条件更新。

6.2.1 根据id更新

方法定义

/*** 根据 ID 修改 
*
* @param entity 实体对象 
* */ 
int updateById(@Param(Constants.ENTITY) T entity);

测试用例

    @Test
    public void testUpdateById() {
        User user = new User();
        user.setId(1L); //条件，根据id更新
        user.setAge(19); //更新的字段
        user.setPassword("666666");

        int result = this.userMapper.updateById(user);
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.updateById]-[DEBUG] ==>  Preparing: UPDATE tb_user SET password=?, age=? WHERE id=? 
[main] [cn.itcast.mp.mapper.UserMapper.updateById]-[DEBUG] ==> Parameters: 666666(String), 19(Integer), 1(Long)
[main] [cn.itcast.mp.mapper.UserMapper.updateById]-[DEBUG] <==    Updates: 1

6.2.2 根据条件更新

6.2.2.1 QueryWrapper

测试用例

    @Test
    public void testUpdate() {
        User user = new User();
        user.setAge(20); //更新的字段
        user.setPassword("8888888");

        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("user_name", "zhangsan"); //匹配user_name = zhangsan 的用户数据

        //根据条件做更新
        int result = this.userMapper.update(user, wrapper);
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.update]-[DEBUG] ==>  Preparing: UPDATE tb_user SET password=?, age=? WHERE user_name = ? 
[main] [cn.itcast.mp.mapper.UserMapper.update]-[DEBUG] ==> Parameters: 8888888(String), 20(Integer), zhangsan(String)
[main] [cn.itcast.mp.mapper.UserMapper.update]-[DEBUG] <==    Updates: 1

6.2.2.2 UpdateWrapper

注意：操作的都是字段名字
测试用例

    @Test
    public void testUpdate2() {
        //操作的都是字段名字
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.set("age", 21).set("password", "999999") //更新的字段
        .eq("user_name", "zhangsan"); //更新的条件

        //根据条件做更新
        int result = this.userMapper.update(null, wrapper);
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.update]-[DEBUG] ==>  Preparing: UPDATE tb_user SET age=?,password=? WHERE user_name = ? 
[main] [cn.itcast.mp.mapper.UserMapper.update]-[DEBUG] ==> Parameters: 21(Integer), 999999(String), zhangsan(String)
[main] [cn.itcast.mp.mapper.UserMapper.update]-[DEBUG] <==    Updates: 1

6.3 删除操作

6.3.1 deleteById

测试用例

    @Test
    public void testDeleteById(){
        // 根据id删除数据
        int result = this.userMapper.deleteById(5L);
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.deleteById]-[DEBUG] ==>  Preparing: DELETE FROM tb_user WHERE id=? 
[main] [cn.itcast.mp.mapper.UserMapper.deleteById]-[DEBUG] ==> Parameters: 5(Long)
[main] [cn.itcast.mp.mapper.UserMapper.deleteById]-[DEBUG] <==    Updates: 1

6.3.2 deleteByMap

测试用例

    @Test
    public void testDeleteByMap(){

        Map<String,Object> map = new HashMap<>();
        map.put("user_name", "zhangsan");
        map.put("password", "999999");

        // 根据map删除数据，多条件之间是and关系
        int result = this.userMapper.deleteByMap(map);
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.deleteByMap]-[DEBUG] ==>  Preparing: DELETE FROM tb_user WHERE password = ? AND user_name = ? 
[main] [cn.itcast.mp.mapper.UserMapper.deleteByMap]-[DEBUG] ==> Parameters: 999999(String), zhangsan(String)
[main] [cn.itcast.mp.mapper.UserMapper.deleteByMap]-[DEBUG] <==    Updates: 1

6.3.3 delete

测试用例

    @Test
    public void testDelete(){

        //用法一：
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("user_name", "zhugeliang")
                .eq("password", "123456");

        //用法二：
//        User user = new User();
//        user.setPassword("123456");
//        user.setUserName("caocao");
//
//        QueryWrapper<User> wrapper = new QueryWrapper<>(user);

        // 根据包装条件做删除
        int result = this.userMapper.delete(wrapper);
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.delete]-[DEBUG] ==>  Preparing: DELETE FROM tb_user WHERE user_name = ? AND password = ? 
[main] [cn.itcast.mp.mapper.UserMapper.delete]-[DEBUG] ==> Parameters: zhugeliang(String), 123456(String)
[main] [cn.itcast.mp.mapper.UserMapper.delete]-[DEBUG] <==    Updates: 2

第一种：需要自己手动设置字段名
第二种：是面向对象思想，不需要手动设置字段名

推荐第二种用法，因为不会写错字段，造成不必要的麻烦。

6.3.4 deleteBatchIds

测试用例

    @Test
    public void  testDeleteBatchIds(){
        // 根据id批量删除数据
        int result = this.userMapper.deleteBatchIds(Arrays.asList(5L, 6L));
        System.out.println("result => " + result);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.deleteBatchIds]-[DEBUG] ==>  Preparing: DELETE FROM tb_user WHERE id IN ( ? , ? ) 
[main] [cn.itcast.mp.mapper.UserMapper.deleteBatchIds]-[DEBUG] ==> Parameters: 5(Long), 6(Long)
[main] [cn.itcast.mp.mapper.UserMapper.deleteBatchIds]-[DEBUG] <==    Updates: 2

6.4 查询操作

MP提供了多种查询操作，包括根据id查询、批量查询、查询单条数据、查询列表、分页查询等操作。

6.4.1 selectById

测试用例

    @Test
    public void testSelectById() {
        User user = this.userMapper.selectById(2L);
        System.out.println(user);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.selectById]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE id=? 
[main] [cn.itcast.mp.mapper.UserMapper.selectById]-[DEBUG] ==> Parameters: 2(Long)
[main] [cn.itcast.mp.mapper.UserMapper.selectById]-[DEBUG] <==      Total: 1

6.4.2 selectBatchIds

测试用例

    @Test
    public void testSelectBatchIds(){
        // 根据id批量查询数据
        List<User> users = this.userMapper.selectBatchIds(Arrays.asList(2L, 3L, 4L, 100L));
        for (User user : users) {
            System.out.println(user);
        }
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.selectBatchIds]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE id IN ( ? , ? , ? , ? ) 
[main] [cn.itcast.mp.mapper.UserMapper.selectBatchIds]-[DEBUG] ==> Parameters: 2(Long), 3(Long), 4(Long), 100(Long)
[main] [cn.itcast.mp.mapper.UserMapper.selectBatchIds]-[DEBUG] <==      Total: 3
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@23ee75c5]
User(id=2, userName=lisi, password=null, name=李四, age=21, mail=222222@qq.com, address=null)
User(id=3, userName=wangwu, password=null, name=王五, age=22, mail=333333@qq.com, address=null)
User(id=4, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)

6.4.3 selectOne

测试用例

    @Test
    public void testSelectOne(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //查询条件
        wrapper.eq("password", "123456");
        // 查询的数据超过一条时，会抛出异常
        User user = this.userMapper.selectOne(wrapper);
        System.out.println(user);
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.selectOne]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE password = ? 
[main] [cn.itcast.mp.mapper.UserMapper.selectOne]-[DEBUG] ==> Parameters: 135246(String)
[main] [cn.itcast.mp.mapper.UserMapper.selectOne]-[DEBUG] <==      Total: 1
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@71391b3f]
User(id=2, userName=lisi, password=null, name=李四, age=21, mail=222222@qq.com, address=null)```

 注意：多条会报错
 
```java
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

6.4.4 selectCount

测试用例

    @Test
    public void testSelectCount(){

        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age", 22); // 条件：年龄大于22岁的用户

        // 根据条件查询数据条数
        Integer count = this.userMapper.selectCount(wrapper);
        System.out.println("count => " + count);
    }

执行结果

ain] [cn.itcast.mp.mapper.UserMapper.selectCount]-[DEBUG] ==>  Preparing: SELECT COUNT( 1 ) FROM tb_user WHERE age > ? 
[main] [cn.itcast.mp.mapper.UserMapper.selectCount]-[DEBUG] ==> Parameters: 22(Integer)
[main] [cn.itcast.mp.mapper.UserMapper.selectCount]-[DEBUG] <==      Total: 1
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@71391b3f]
count => 2

6.4.5 selectList

测试用例

    @Test
    public void testSelectList(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //设置查询条件
        wrapper.like("email", "qq.com");

        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.selectList]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE email LIKE ? 
[main] [cn.itcast.mp.mapper.UserMapper.selectList]-[DEBUG] ==> Parameters: %qq.com%(String)
[main] [cn.itcast.mp.mapper.UserMapper.selectList]-[DEBUG] <==      Total: 4
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a7f0ab6]
User(id=2, userName=lisi, password=null, name=李四, age=21, mail=222222@qq.com, address=null)
User(id=3, userName=wangwu, password=null, name=王五, age=22, mail=333333@qq.com, address=null)
User(id=4, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)
User(id=7, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)

6.4.6 selectPage

添加分页拦截器

/**
 * @author LongXi
 * @create 2021-09-24 20:56
 */
@MapperScan("cn.itcast.mp.mapper")
@Configuration
public class MyBatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

测试用例

    // 测试分页查询
    @Test
    public void testSelectPage(){

        Page<User> page = new Page<>(2,2); //查询第一页，查询1条数据

        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //设置查询条件
        wrapper.like("email", "qq.com");

        IPage<User> iPage = this.userMapper.selectPage(page, wrapper);
        System.out.println("数据总条数： " + iPage.getTotal());
        System.out.println("数据总页数： " + iPage.getPages());
        System.out.println("当前页数： " + iPage.getCurrent());

        List<User> records = iPage.getRecords();
        for (User record : records) {
            System.out.println(record);
        }
    }

执行结果

[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==>  Preparing: SELECT COUNT(1) FROM tb_user WHERE email LIKE ? 
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==> Parameters: %qq.com%(String)
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE email LIKE ? LIMIT ?,? 
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==> Parameters: %qq.com%(String), 2(Long), 2(Long)
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] <==      Total: 2
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6ceb7b5e]
数据总条数： 4
数据总页数： 2
当前页数： 2
User(id=4, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)
User(id=7, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)

6.5 MyBatis-plus基本配置

6.5.1 configLocation

MyBatis 配置文件位置，如果您有单独的 MyBatis 配置，请将其路径配置到 configLocation 中。 MyBatis
Configuration 的具体内容请参考MyBatis 官方文档
Spring Boot：

mybatis-plus.config-location = classpath:mybatis-config.xml

Spring Mvc

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

1. 在resources下新建[mybatis-config.xml]配置文件

<?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>
    
    <plugins>
        <plugin interceptor="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor"></plugin>
    </plugins>

</configuration>

2. 在application.properties中引用[mybatis-config.xml]

spring.application.name = itcast-mp-springboot3

#mysql连接信息:
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis_plus?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
# 指定全局的配置文件
mybatis-plus.config-location=classpath:mybatis-config.xml

3. 删除MyBatisPlusConfig文件中分页的配置

/**
 * @author LongXi
 * @create 2021-09-24 20:56
 */
@MapperScan("cn.itcast.mp.mapper")
@Configuration
public class MyBatisPlusConfig {
/*    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }*/
}

4. 执行分页查询

[main] [com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize]-[DEBUG] JsqlParserCountOptimize sql=SELECT  id,user_name,name,age,email AS mail  FROM tb_user 
 
 WHERE email LIKE ?
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==>  Preparing: SELECT COUNT(1) FROM tb_user WHERE email LIKE ? 
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==> Parameters: %qq.com%(String)
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==>  Preparing: SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE email LIKE ? LIMIT ?,? 
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] ==> Parameters: %qq.com%(String), 2(Long), 2(Long)
[main] [cn.itcast.mp.mapper.UserMapper.selectPage]-[DEBUG] <==      Total: 2
[main] [org.mybatis.spring.SqlSessionUtils]-[DEBUG] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6272c96f]
数据总条数： 4
数据总页数： 2
当前页数： 2
User(id=4, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)
User(id=7, userName=zhaoliu, password=null, name=赵六, age=23, mail=444444@qq.com, address=null)

分页依然好用，说明配置文件起作用了。

6.5.2 mapperLocations

MyBatis Mapper 所对应的 XML 文件位置，如果在 Mapper 中有自定义方法（XML 中有自定义实现），需要进行该配置，告诉 Mapper 所对应的 XML 文件位置。

Spring Boot：

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>

Maven 多模块项目的扫描路径需以 classpath*: 开头 （即加载多个 jar 包下的 XML 文件）
带*号：扫描所有依赖下的XML文件。

1. 在application.properties中加入配置

# 指定Mapper.xml文件的路径
mybatis-plus.mapper-locations = classpath*:mybatis/*.xml

2. 在resources下新建mybatis/UserMapper.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="cn.itcast.mp.mapper.UserMapper">
    <select id="findById" resultType="cn.itcast.mp.pojo.User">
        select *
        from tb_user
        where id = #{id}
   </select>
</mapper>

3. 在UserMapper接口中加入方法

@Mapper
public interface UserMapper extends BaseMapper<User> {
    User findById(Long id);
}

4. 编写测试用例

    /**
     * 自定义的方法
     */
    @Test
    public void testFindById(){
        User user = this.userMapper.findById(2L);
        System.out.println(user);
    }

5. 执行结果

[main] [cn.itcast.mp.mapper.UserMapper.findById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
[main] [cn.itcast.mp.mapper.UserMapper.findById]-[DEBUG] ==> Parameters: 2(Long)
[main] [cn.itcast.mp.mapper.UserMapper.findById]-[DEBUG] <==      Total: 1

执行成功，说明配置起作用了。

6.5.3 typeAliasesPackage

MyBaits 别名包扫描路径，通过该属性可以给包中的类注册别名，注册后在 Mapper 对应的 XML 文件中可以直接使用类名，而不用使用全限定的类名（即 XML 中调用的时候不用包含包名）。
Spring Boot：

mybatis-plus.type-aliases-package = cn.itcast.mp.pojo

Spring MVC：

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

1. 在application.properties中加入配置

# 实体对象的扫描包
mybatis-plus.type-aliases-package = cn.itcast.mp.pojo

2. UserMapper.xml中，删除实体的包名的前缀

<mapper namespace="cn.itcast.mp.mapper.UserMapper">
    <select id="findById" resultType="User">
        select *
        from tb_user
        where id = #{id}
   </select>
</mapper>

3. 执行测试用例

[main] [cn.itcast.mp.mapper.UserMapper.findById]-[DEBUG] ==>  Preparing: select * from tb_user where id = ? 
[main] [cn.itcast.mp.mapper.UserMapper.findById]-[DEBUG] ==> Parameters: 2(Long)
[main] [cn.itcast.mp.mapper.UserMapper.findById]-[DEBUG] <==      Total: 1

没有问题

6.6 MyBatis-plus进阶配置

6.6.1 mapUnderscoreToCamelCase

• 类型： boolean
• 默认值： true
  是否开启自动驼峰命名规则（camel case）映射，即从经典数据库列名 A_COLUMN（下划线命名） 到经典 Java 属性名 aColumn（驼峰命名） 的类似映射。

注意：
此属性在 MyBatis 中原默认值为 false，在 MyBatis-Plus 中，此属性也将用于生成最终的 SQL 的 select body
如果您的数据库命名符合规则无需使用 @TableField 注解指定数据库字段名

1. 在application.properties中加入配置

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

运行测试用例

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.ibatis.session.SqlSessionFactory]: Factory method 'sqlSessionFactory' threw exception; nested exception is java.lang.IllegalStateException: Property 'configuration' and 'configLocation' can not specified with together
	at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:627)
	at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:607)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1321)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1160)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)

结果报错，启动不了：
原因是该配置和【mybatis-config.xml】配置冲突，

如果想在【application.properties】中用驼峰配置，就注释掉【mybatis-config】的相关配置
如果想用【mybatis-config】配置，那就把驼峰配置移动到【mybatis-config】文件中

6.6.2 mapUnderscoreToCamelCase

• 类型： boolean
• 默认值： true
  全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存，默认为 true。

1. 在application.properties中加入配置

# 禁用缓存
mybatis-plus.configuration.cache-enabled=false

6.7 DB 策略配置

6.7.1 idType

• 类型： com.baomidou.mybatisplus.annotation.IdType
• 默认值： ID_WORKER
  全局默认主键类型，设置后，即可省略实体对象中的@TableId(type = IdType.AUTO)配置。
  示例：
  SpringBoot：

mybatis-plus.global-config.db-config.id-type=auto

SpringMVC：

<!--这里使用MP提供的sqlSessionFactory，完成了Spring与MP的整合--> <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>

6.7.2 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>

7 条件构造器

在MP中，Wrapper接口的实现类关系如下：

可以看到，AbstractWrapper和AbstractChainWrapper是重点实现，接下来我们重点学习AbstractWrapper以及其子类

说明:
QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类 用于生成 sql
的 where 条件, entity 属性也用于生成 sql 的 where 条件 注意: entity 生成的 where 条件与 使用各个 api 生成
的 where 条件没有任何关联行为

7.1 allEq

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

编写测试用例

    @Test
    public void testAllEq(){

        Map<String,Object> params = new HashMap<>();
        params.put("name", "李四");
        params.put("age", "20");
        params.put("password", null);

        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE password IS NULL AND name = ? AND age = ?
        wrapper.allEq(params);
        //SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE name = ? AND age = ?
//        wrapper.allEq(params, false);

        //SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE age = ?
//        wrapper.allEq((k, v) -> (k.equals("age") || k.equals("id")) , params);
        //SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE name = ? AND age = ?
        wrapper.allEq((k, v) -> (k.equals("age") || k.equals("id") || k.equals("name")) , params);

        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

1. 参数null2IsNull默认是true，如果是true，则空字段也作为查询条件【password IS NULL】
2. 如果传的是false，则不加这个条件
3. 【boolean condition】相当于过滤器，只有符合过滤条件的才会被当作条件。

7.2 基本比较操作

1. eq
   等于 =

2. ne
   不等于 <>

3. gt
   大于 >

4. ge
   大于等于 >=

5. lt
   小于 <

6. le
   小于等于 <=

7. between
   BETWEEN 值1 AND 值2

8. notBetween
   NOT BETWEEN 值1 AND 值2

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

10. notIn
    字段 NOT IN (v0, v1, …)

编写测试用例

    @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.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

7.3 模糊查询

1. like：LIKE ‘%值%’
   例: like(“name”, “王”) —> name like ‘%王%’

2. notLike：NOT LIKE ‘%值%’
   例: notLike(“name”, “王”) —> name not like ‘%王%’

3. likeLeft：LIKE ‘%值’
   例: likeLeft(“name”, “王”) —> name like ‘%王’

4. likeRight：LIKE ‘值%’
   例: likeRight(“name”, “王”) —> name like ‘王%’

编写测试用例

    @Test
    public void testLike(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE name LIKE ?
        // 参数：%五(String)
        wrapper.likeLeft("name", "五");

        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

7.4 排序

1. orderBy：
   排序：ORDER BY 字段, …
   例: orderBy(true, true, “id”, “name”) —> order by id ASC,name ASC
2. orderByAsc
   排序：ORDER BY 字段, … ASC
   例: orderByAsc(“id”, “name”) —> order by id ASC,name ASC
3. orderByDesc
   排序：ORDER BY 字段, … DESC
   例: orderByDesc(“id”, “name”) —> order by id DESC,name DESC

测试用例

    @Test
    public void testOrderByAgeDesc(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //按照年龄倒序排序
        // SELECT id,user_name,name,age,email AS mail FROM tb_user ORDER BY age DESC
        wrapper.orderByDesc("age");

        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

7.5 逻辑查询

1. or
   拼接 OR
   主动调用 or 表示紧接着下一个方法不是用 and 连接!(不调用 or 则默认为使用 and 连接)
2. and
   AND 嵌套
   例: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”)) —> and (name = ‘李白’ and status <> ‘活着’)

测试用例

    @Test
    public void testOr(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // SELECT id,user_name,name,age,email AS mail FROM tb_user WHERE name = ? OR age = ?
        wrapper.eq("name", "王五").or().eq("age", 21);

        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

7.6 select

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

测试用例

    @Test
    public void testSelect(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //SELECT id,name,age FROM tb_user WHERE name = ? OR age = ?
        wrapper.eq("name", "王五")
                .or()
                .eq("age", 21)
                .select("id","name","age"); //指定查询的字段

        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }