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

最新推荐文章于 2023-09-11 13:58:21 发布

玄妙之门

最新推荐文章于 2023-09-11 13:58:21 发布

阅读量1.4k

点赞数

分类专栏：笔记文章标签： spring boot sql java

本文链接：https://blog.csdn.net/yangfenggh/article/details/121873413

版权

笔记专栏收录该内容

55 篇文章 0 订阅

订阅专栏

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

IDEA新建springboot工程承，命名为springboot-mybatis-sz
修改pom文件，引入所需依赖

<?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 https://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.5.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.yf.cn</groupId>
    <artifactId>mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
    </properties>
    <dependencies>
        <!--mvc所需jar，必须引入-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mybatis所需jar-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--数据库连接池所需jar-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.13</version>
        </dependency>
        <!--分页查询所需jar-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
        <!--mysql所需驱动jar-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--单元测试所需jar-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--代码生成所需jar-->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.5</version>
        </dependency>
        <!--单元测试所需jar-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

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

</project>

配置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

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

配置文件：mybatis-generator.xml，为了不影响实际项目，生成的代码新建一个测试项目，测试项目对应的文件目录下

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>

    <!-- mysql-connector-java-5.1.46.jar的绝对路径 -->
    <classPathEntry location="D:\m2\mysql\mysql-connector-java\8.0.24\mysql-connector-java-8.0.24.jar" />

    <context id="DB2Tables" targetRuntime="MyBatis3">
        <!-- 配置数据源 -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis"
                        userId="root"
                        password="123456">
        </jdbcConnection>

        <javaTypeResolver >
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!-- 配置生成的javabean所存放的路径和包（使用绝对路径） -->
        <javaModelGenerator targetPackage="com.yf.cn.pojo" targetProject="D:\eclipse-mars\mybatissql\src\main\java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!-- 配置生成的**Mapper.xml文件所存放的路径和包（使用绝对路径） -->
        <sqlMapGenerator targetPackage="mappers"  targetProject="D:\eclipse-mars\mybatissql\src\main\resources">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>

        <!-- 配置生成的**Mapper.java接口文件所存放的路径和包（使用绝对路径） -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.yf.cn.mapper"  targetProject="D:\eclipse-mars\mybatissql\src\main\java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!-- 指定我们要操作的表明和生成的javabean类名 -->
        <table tableName="view_student" domainObjectName="ViewStudent" >
        </table>

    </context>
</generatorConfiguration>

生成代码：Generator，代码生成项目正式运行时不需要，所以放到test目录下

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<String> warnings = new ArrayList<String>();
        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("执行成功");
    }
}

代码生成成功后，将代码复制到实际项目对应的目录，如果包结构不一直，则进行修改
编写单元测试类进行测试：在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<TestStudent> students = testStudentMapper.getAllStudentsForPage(query);
        PageInfo<TestStudent> pageInfo = new PageInfo<TestStudent>(students);
        System.out.println(pageInfo.getList());

    }
}

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

进行数据的新增测试，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, '女');

进行新增测试，执行测试方法，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<TestStudent> getAllStudentsForPage(StudentQueryParam query);

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

<select id="getAllStudentsForPage" parameterType="StudentQueryParam" resultType="TestStudent">
    select * from test_student
  </select>

单元测试中添加对应的查询方法：

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

    }

在这里插入图片描述

可以看到控制台的sql输出分页和排序已经生效

需求：可以动态的设置学生名称是否要模糊查询
在StudentQueryParam（查询参数）中新增：private boolean isSnameLike;添加get and setter
在测试方法中进行设置，如果不需要模糊则改参数无需设置，默认false，如果需要模糊则设置改参数为：true
对应的sql xml配置如下：

<select id="getAllStudentsForPage" parameterType="StudentQueryParam" resultType="TestStudent">
    select * from test_student
    <where>
      <if test="sname!=null and sname!=''">
        <if test="isSnameLike == false">
          and sname = #{sname}
        </if>

        <if test="isSnameLike == true ">
          and sname like "%"#{sname}"%"
        </if>

      </if>
    </where>
  </select>