springboot+mybatis+pagehelper实现分页排序以及动态sql

1. IDEA新建springboot工程承，命名为springboot-mybatis-sz
   

2. 修改pom文件，引入所需依赖

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.5.7
         
    
    com.yf.cn
    mybatis
    0.0.1-SNAPSHOT
    mybatis
    Demo project for Spring Boot
    
        1.8
        UTF-8
        UTF-8
        UTF-8
    
    
        
        
            org.springframework.boot
            spring-boot-starter-web
        
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            1.3.2
        
        
        
            com.alibaba
            druid
            1.1.13
        
        
        
            com.github.pagehelper
            pagehelper-spring-boot-starter
            1.2.12
        
        
        
            mysql
            mysql-connector-java
            runtime
        
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
        
        
            org.mybatis.generator
            mybatis-generator-core
            1.3.5
        
        
        
            junit
            junit
            4.12
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
        
    

3. 配置application.yml

server:
  port: 8088
spring:
  datasource:
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECt 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
mybatis:
  type-aliases-package: com.yf.cn.pojo,com.yf.cn.query
  mapper-locations: classpath:mappers/*Mapper.xml # 指定配置sql文件的位置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

4. 建立项目包结构，在基础包com.yf.cn包结构下面新建pojo、mapper包，在resources目录下新建mappers（和配置文件中mapper-locations: classpath:mappers/*Mapper.xml 中mappers是对应的）文件
5. 通过代码生成器生成pojo、xml和mapper接口，生成代码的配置文件和生成代码如下：

package com.yf.cn.common;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Generator {
    public static void main(String[] args) throws Exception {
        List warnings = new ArrayList();
        boolean overwrite = true;
        File configFile = new File("D:/eclipse-mars/mybatis-sz/src/main/resources/mybatis-generator.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
        System.out.println("执行成功");
    }
}

6. 代码生成成功后，将代码复制到实际项目对应的目录，如果包结构不 一直，则进行修改
7. 编写单元测试类进行测试：在test文件夹下新建对应的测试类（TestStudentDao）

package com.yf.cn.dao;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.yf.cn.mapper.TestStudentMapper;
import com.yf.cn.pojo.TestStudent;
import com.yf.cn.query.StudentQueryParam;
import org.junit.Test;
import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
public class TestStudentDao {
    @Autowired
    private TestStudentMapper testStudentMapper;


    @Test
    public void testSaveStudent(){
        TestStudent testStudent = new TestStudent();
        testStudent.setSname("谢晓峰");
        testStudent.setSage(13);
        testStudent.setSsex("男");
        testStudentMapper.insert(testStudent);

    }

    @Test
    public void testPaget(){
        PageHelper.startPage(1,3,"id desc ");
        StudentQueryParam query = new StudentQueryParam();
        query.setSname("峰");
        query.setSnameLike(true);
        List students = testStudentMapper.getAllStudentsForPage(query);
        PageInfo pageInfo = new PageInfo(students);
        System.out.println(pageInfo.getList());

    }
}

mapper接口引入后，IDEA工具一直提示该接口不存在，此时需要在settings中进行设置，将error设置问warning

8. 进行数据的新增测试，sql建表语句以及测试数据如下：

DROp TABLE IF EXISTS `test_student`;
CREATE TABLE `test_student`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sage` int NULL DEFAULT NULL,
  `ssex` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test_student
-- ----------------------------
INSERT INTO `test_student` VALUES (1, '刘一', 18, '男');
INSERT INTO `test_student` VALUES (2, '钱二', 19, '女');
INSERT INTO `test_student` VALUES (3, '张三', 17, '男');
INSERT INTO `test_student` VALUES (4, '李四', 18, '女');
INSERT INTO `test_student` VALUES (5, '王五', 17, '男');
INSERT INTO `test_student` VALUES (6, '赵六', 19, '女');
INSERT INTO `test_student` VALUES (7, '五千二', 21, '男');
INSERT INTO `test_student` VALUES (14, '谢晓峰', 13, '男');
INSERT INTO `test_student` VALUES (15, '卢俊峰', 25, '男');
INSERT INTO `test_student` VALUES (16, '缥缈峰', 31, '女');

9. 进行新增测试，执行测试方法，test方法没有报错，说明执行成功，但是发现控制台没有任何的提示，此时在配置文件中新增执行的sql输出，增加如下配置后再次执行新增可以看到控制台已经有执行sql输出

mybatis:
  type-aliases-package: com.yf.cn.pojo,com.yf.cn.query
  mapper-locations: classpath:mappers/*Mapper.xml # 指定配置sql文件的位置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

10.需求：分页查询学生列表
在StudentMapper接口中新增查询方法：

List getAllStudentsForPage(StudentQueryParam query);

StudentMapper.xml中添加对应的查询方法

    select * from test_student
  
 
单元测试中添加对应的查询方法：
 
@Test
    public void testPaget(){
        PageHelper.startPage(1,3,"id desc ");
        StudentQueryParam query = new StudentQueryParam();
        List students = testStudentMapper.getAllStudentsForPage(query);
        PageInfo pageInfo = new PageInfo(students);
        System.out.println(pageInfo.getList());

    }
 
 
可以看到控制台的sql输出分页和排序已经生效
 
需求：可以动态的设置学生名称是否要模糊查询
 在StudentQueryParam（查询参数）中新增：private boolean isSnameLike;添加get and setter
 在测试方法中进行设置，如果不需要模糊则改参数无需设置，默认false，如果需要模糊则设置改参数为：true
 对应的sql xml配置如下：
 

    select * from test_student
    
      
        
          and sname = #{sname}
        

        
          and sname like "%"#{sname}"%"
        

      
    
  

至此既可以实现根据名称设置动态查询的功能