Mybatis Plus分页及自定义SQL语句

**

1、数据库的连接配置

首先我们要配置数据的连接等相关信息，因此我们需要在application.properties文件中，增加如下定义：

##
spring.application.name=server-page-helper
server.port=8000

## Mybits
spring.datasource.url=jdbc:mysql://localhost:3306/db_books?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456

mybatis.typeAliasesPackage=com.shenmazong.serverpagehelper.mapper
mybatis.mapperLocations=classpath:/mapper/*.xml
logging.level.com.shenmazong.serverpagehelper.mapper=debug
mybatis-plus.configuration.map-underscore-to-camel-case=false

2、添加依赖

 <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- 分页插件 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>

3、创建与表对应的实体类

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

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "tb_book")
public class TbBook {

    @TableId(value = "BookId", type = IdType.AUTO)
    private Long BookId;

    private Integer TaskId;
    private String BookName;
    private String BookAuthor;
    private String BookUrl;

}

4、创建Mapper文件

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.shenmazong.serverpagehelper.pojo.TbBook;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Mapper
@Component(value = "iTbBookMapper")
public interface ITbBookMapper extends BaseMapper<TbBook> {

    @Select("select * from tb_book")
    IPage<TbBook> selectBookByPage(Page<TbBook> page);

    List<TbBook> selectBookForEachPage(List<Integer> ids);

    IPage<TbBook> selectBookByMyPage(Page<TbBook> page);
}

需要注意的是，这里面有一个坑点。就是需要把分页的参数放到函数的第一个参数，而不能把分页参数放到其他位置，否则分页机制不起作用。

5、定义映射xml文件

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.shenmazong.serverpagehelper.mapper.ITbBookMapper">

    <select id="selectBookForEachPage" resultType="com.shenmazong.serverpagehelper.pojo.TbBook" parameterType="list">
        select * from tb_book
        <where>
            TaskId in
            <foreach item="item" collection="list" separator="," open="(" close=")" index="">
                #{item, jdbcType=NUMERIC}
            </foreach>
        </where>
    </select>

<!--    <select id="countByUserList" resultType="_int" parameterType="list">-->
<!--        select count(*) from users-->
<!--        <where>-->
<!--            id in-->
<!--            <foreach item="item" collection="list" separator="," open="(" close=")" index="">-->
<!--                #{item.id, jdbcType=NUMERIC}-->
<!--            </foreach>-->
<!--        </where>-->
<!--    </select>-->

    <select id="selectBookByMyPage" resultType="com.shenmazong.serverpagehelper.pojo.TbBook">
        select * from tb_book
    </select>
</mapper>

6、增加MyBatis Plus分页拦截器

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

@Configuration
public class MybatisPlusConfig {

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

注意：这个拦截器非常重要，不能缺少，否则分页功能不会生效。
**

7、使用非结构化返回结果

当SQL语句是一个动态语句，而又不想重新定义一个对象，则可以直接使用map来代替实体类，具体代码如下：

    @Select("select a.id,a.title,a.dyid,a.dynasty,a.authorid,a.author,a.content,\n" +
            "b.clicks,b.collections,b.difficult,b.voice\n" +
            "from tb_poetry_info as b\n" +
            "left join tb_poetry as a on b.poetryid=a.id\n" +
            "order by b.clicks desc")
    IPage<Map<String, Object>> selectMapPoetry4ClickByPage(Page<Map<String,Object>> page);

8、测试mapper文件

@Test
    void testMyPage() {

        Page<TbBook> page = new Page<>(1, 10);
        IPage<TbBook> listByAuthorId = iTbBookMapper.selectBookByPage(page);
        System.out.println(listByAuthorId);
    }

    @Test
    void testForEach() {
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(20);
        integers.add(21);
        integers.add(22);
        integers.add(23);

        List<TbBook> books = iTbBookMapper.selectBookForEachPage(integers);
        books.forEach(book->{
            System.out.println(book);
        });
    }

    @Test
    void testSelectBookByMyPage() {
        Page<TbBook> page = new Page<>(1, 10);
        IPage<TbBook> books = iTbBookMapper.selectBookByMyPage(page);

        System.out.println(books.getTotal());
        System.out.println(books.getRecords());
    }