java进阶|MyBatis系列文章(四)分页查询加动态sql

这篇文章是自己对mybatis系列文章的最后一篇文章了，原来写的文章包括基础性文章的思考也就是一些入门型的文章，后面开始写了一篇基础性文章使用原生的sqlSession进行操作，然后基于xml和注解的方式进行增删改查的操作，后面继续使用的mybatis的Provider高级用法去写了一篇文章，然后进行关联表的查询文章的分享，这里最后涉及到了web技术的最常见的分页查询的操作，到这里就结束了。

MyBatis是支持定制化SQL,存储过程以及高级映射的优秀的持久层框架。MyBatis避免了几乎所有的JDBC代码和手动参数以及获取结果集。MyBatis可以对配置和原生Map使用简单的XML或注解，将接口和Java的POJOS(Plain Old Java Objects，普通的Java对象)映射成数据库中的记录。

       <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-data-jdbc</artifactId>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-jdbc</artifactId>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <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>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>            <exclusions>                <exclusion>                    <groupId>org.junit.vintage</groupId>                    <artifactId>junit-vintage-engine</artifactId>                </exclusion>            </exclusions>        </dependency>        <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->        <dependency>            <groupId>com.github.pagehelper</groupId>            <artifactId>pagehelper-spring-boot-starter</artifactId>        </dependency>        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>        </dependency>

package com.wpw.mybatispagehelper.entity;
import com.wpw.mybatispagehelper.vo.RequestVo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Accessors(chain = true)public class Person extends RequestVo implements Serializable {    private Integer id;    private String name;    private String sex;    private Integer age;}

package com.wpw.mybatispagehelper.vo;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class RequestVo implements Serializable {    private Integer pageNumber;    private Integer pageSize;}

package com.wpw.mybatispagehelper.controller;
import com.github.pagehelper.PageInfo;import com.wpw.mybatispagehelper.entity.Person;import com.wpw.mybatispagehelper.service.PersonService;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RestController;
/** * @author wpw */@RestControllerpublic class PersonController {    private final PersonService personService;
    public PersonController(PersonService personService) {        this.personService = personService;    }

    @PostMapping(value = "/list-pagination")    public PageInfo<Person> listPerson(@RequestBody Person person) {        PageInfo<Person> pageInfo = personService.listPerson(person);        return pageInfo;    }}

package com.wpw.mybatispagehelper.service;
import com.github.pagehelper.PageInfo;import com.wpw.mybatispagehelper.entity.Person;
/** * @author wpw */public interface PersonService {    /**     * @param  person 请求对象     * @return 分页后的数据信息     */    PageInfo<Person> listPerson( Person  person);
}

package com.wpw.mybatispagehelper.service.impl;
import com.github.pagehelper.PageHelper;import com.github.pagehelper.PageInfo;import com.wpw.mybatispagehelper.service.PersonService;import com.wpw.mybatispagehelper.entity.Person;import com.wpw.mybatispagehelper.mapper.PersonMapper;import org.springframework.stereotype.Service;
import java.util.List;
/** * @author wpw */@Servicepublic class PersonServiceImpl implements PersonService {    private PersonMapper personMapper;
    public PersonServiceImpl(PersonMapper personMapper) {        this.personMapper = personMapper;    }
    @Override    public PageInfo<Person> listPerson(Person person) {        PageHelper.startPage(person.getPageNumber(), person.getPageSize());        List<Person> personList = personMapper.listPerson(person);        return PageInfo.of(personList);    }}

package com.wpw.mybatispagehelper.mapper;
import com.wpw.mybatispagehelper.entity.Person;import org.apache.ibatis.annotations.Mapper;import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper@Repositorypublic interface PersonMapper {    /**     * 获取列表信息数据     *     * @param person 请求对象     * @return 满足指定查询的列表信息     */    List<Person> listPerson(Person person);}

<?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，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的 --><mapper namespace="com.wpw.mybatispagehelper.mapper.PersonMapper">    <resultMap id="map" type="com.wpw.mybatispagehelper.entity.Person">        <id property="id" column="id"></id>        <result property="name" column="name"></result>        <result property="sex" column="sex"></result>        <result property="age" column="age"></result>    </resultMap>    <select id="listPerson" parameterType="com.wpw.mybatispagehelper.entity.Person" resultMap="map">        select *        from person        <where>            <if test="name!=null">                name like concat ('%',#{name},'%')            </if>            <if test="sex!=null">                and sex=#{sex}            </if>            <if test="age!=null">                and age=#{age}            </if>        </where>    </select></mapper>

use study;select database();create table if not exists `person`(    id   int(11) auto_increment primary key comment '主键',    name varchar(255) comment '名称',    sex   varchar(20) comment '性别',    age  int(3) comment '年纪') engine = InnoDb  charset = utf8;insert into  person(name,sex,age) values("zhangsan","man",10);insert into  person(name,sex,age) values("zhangsan2","man",11);insert into  person(name,sex,age) values("zhangsan3","man",12);insert into  person(name,sex,age) values("zhangsan4","man",13);insert into  person(name,sex,age) values("zhangsan5","man",14);insert into  person(name,sex,age) values("zhangsan6","man",15);insert into  person(name,sex,age) values("zhangsan7","man",16);insert into  person(name,sex,age) values("zhangsan8","man",17);insert into  person(name,sex,age) values("zhangsan9","man",18);insert into  person(name,sex,age) values("zhangsan10","man",19);insert into  person(name,sex,age) values("zhangsan11","man",20);insert into  person(name,sex,age) values("zhangsan12","man",21);

server:  port: 8080spring:  application:    name: mybatis-page-helper  datasource:    url: jdbc:mysql://localhost:3306/study?serverTimezone=UTC    username: root    password: root    driver-class-name: com.mysql.cj.jdbc.Drivermybatis:  mapper-locations: classpath:/mapper/**.xmlpagehelper:  reasonable: true

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。内容参考:w3cSchool片段