使用ChatGPT开发SpringBoot+Mybatis项目记录——多条件查询

chatGPT对话分享链接：
https://chat.openai.com/share/64424e89-5dfc-4237-9139-64bd3501a688

Prompt：

如何在springboot+mybatis当中规范地实现筛选查询以及分页功能，筛选查询中有根据两种时间范围进行筛选。
实体类如下：
==================================================
package com.example.watermanagement.model;

public class Machine {
    public String id;//水机编号
    public String manufacturer;//生产厂家
    public String area;//所属区域
    public String community;//所属社区
    public String installTime;//安装时间
    public String renewTime;//维保时间
}
==================================================

想让你帮忙编码：controller层、service层、DAO层以及mapper.xml。
要求：接口采用POST请求。
分页功能使用sql中的LIMIT实现，接受两个额外的参数分别为from和num。
接口的参数相当于有：manufacturer、area、community、installTimeStart、installTimeEnd、renewTimeStart、renewTimeEnd、from、num

最终解决方案：

创建Mapper接口 MachineMapper.java：

package com.example.watermanagement.mapper;

import com.example.watermanagement.model.Machine;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
@Repository
public interface MachineMapper {
    List<Machine> GetMachinesByFilterAndPage(
            @Param("manufacturer") String manufacturer,
            @Param("area") String area,
            @Param("community") String community,
            @Param("installTimeStart") String installTimeStart,
            @Param("installTimeEnd") String installTimeEnd,
            @Param("renewTimeStart") String renewTimeStart,
            @Param("renewTimeEnd") String renewTimeEnd,
            @Param("from") int from,
            @Param("num") int num
    );

    int CountMachinesByFilter(
            @Param("manufacturer") String manufacturer,
            @Param("area") String area,
            @Param("community") String community,
            @Param("installTimeStart") String installTimeStart,
            @Param("installTimeEnd") String installTimeEnd,
            @Param("renewTimeStart") String renewTimeStart,
            @Param("renewTimeEnd") String renewTimeEnd
    );

编写Mapper XML文件 MachineMapper.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="com.example.watermanagement.mapper.MachineMapper">
    <!-- 条件查询（筛选） -->
    <!-- 如果时间日期固定位数则用'%Y/%m/%d'，否则用'%Y/%c/%e' -->
    <select id="GetMachinesByFilterAndPage" resultType="Machine">
        SELECT * FROM machine
        <where>
        <if test="manufacturer != null and manufacturer != ''">
            manufacturer = #{manufacturer}
        </if>
        <if test="area != null and area != ''">
            AND area = #{area}
        </if>
        <if test="community != null and community != ''">
            AND community = #{community}
        </if>
        <if test="installTimeStart != null and installTimeStart != ''">
            AND installTime &gt;= STR_TO_DATE(#{installTimeStart}, '%Y/%c/%e')
        </if>
        <if test="installTimeEnd != null and installTimeEnd != ''">
            AND installTime &lt;= STR_TO_DATE(#{installTimeEnd}, '%Y/%c/%e')
        </if>
        <if test="renewTimeStart != null and renewTimeStart != ''">
            AND renewTime &gt;= STR_TO_DATE(#{renewTimeStart}, '%Y/%c/%e')
        </if>
        <if test="renewTimeEnd != null and renewTimeEnd != ''">
            AND renewTime &lt;= STR_TO_DATE(#{renewTimeEnd}, '%Y/%c/%e')
        </if>
        </where>
        LIMIT #{from}, #{num}
    </select>

    <!-- 统计数目，用于分页 -->
    <select id="CountMachinesByFilter" resultType="int">
        SELECT COUNT(*) FROM machine
        <where>
        <if test="manufacturer != null and manufacturer != ''">
            manufacturer = #{manufacturer}
        </if>
        <if test="area != null and area != ''">
            AND area = #{area}
        </if>
        <if test="community != null and community != ''">
            AND community = #{community}
        </if>
        <if test="installTimeStart != null and installTimeStart != ''">
            AND installTime &gt;= STR_TO_DATE(#{installTimeStart}, '%Y/%c/%e')
        </if>
        <if test="installTimeEnd != null and installTimeEnd != ''">
            AND installTime &lt;= STR_TO_DATE(#{installTimeEnd}, '%Y/%c/%e')
        </if>
        <if test="renewTimeStart != null and renewTimeStart != ''">
            AND renewTime &gt;= STR_TO_DATE(#{renewTimeStart}, '%Y/%c/%e')
        </if>
        <if test="renewTimeEnd != null and renewTimeEnd != ''">
            AND renewTime &lt;= STR_TO_DATE(#{renewTimeEnd}, '%Y/%c/%e')
        </if>
        </where>
    </select>

</mapper>

创建Service层接口 MachineService.java：

package com.example.watermanagement.service;

import com.example.watermanagement.model.Machine;

import java.util.List;


public interface MachineService {
    List<Machine> GetMachinesByFilterAndPage(
            String manufacturer,
            String area,
            String community,
            String installTimeStart,
            String installTimeEnd,
            String renewTimeStart,
            String renewTimeEnd,
            int from,
            int num
    );

    int CountMachinesByFilter(
            String manufacturer,
            String area,
            String community,
            String installTimeStart,
            String installTimeEnd,
            String renewTimeStart,
            String renewTimeEnd
    );
}

创建Service层实现类 MachineServiceImpl.java：

package com.example.watermanagement.service;

import com.example.watermanagement.mapper.MachineMapper;
import com.example.watermanagement.model.Machine;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class MachineServiceImpl implements MachineService {

    @Autowired
    MachineMapper machineMapper;

    @Override
    public List<Machine> GetMachinesByFilterAndPage(
            String manufacturer,
            String area,
            String community,
            String installTimeStart,
            String installTimeEnd,
            String renewTimeStart,
            String renewTimeEnd,
            int from,
            int num
    ) {
        System.out.println("----GetMachinesByFilters Param----");
        System.out.println("Manufacturer: " + manufacturer);
        System.out.println("Area: " + area);
        System.out.println("Community: " + community);
        System.out.println("InstallTimeStart: " + installTimeStart);
        System.out.println("InstallTimeEnd: " + installTimeEnd);
        System.out.println("RenewTimeStart: " + renewTimeStart);
        System.out.println("RenewTimeEnd: " + renewTimeEnd);
        System.out.println("From: " + from);
        System.out.println("Num: " + num);
        System.out.println("----------------------------------");

        return machineMapper.GetMachinesByFilterAndPage(
                manufacturer, area, community, installTimeStart, installTimeEnd, renewTimeStart, renewTimeEnd, from, num
        );
    }

    @Override
    public int CountMachinesByFilter(
            String manufacturer,
            String area,
            String community,
            String installTimeStart,
            String installTimeEnd,
            String renewTimeStart,
            String renewTimeEnd
    ) {
        return machineMapper.CountMachinesByFilter(
                manufacturer, area, community, installTimeStart, installTimeEnd, renewTimeStart, renewTimeEnd
        );
    }
}

创建Controller层 MachineController.java（待修改）：

package com.example.watermanagement.controller;

import com.example.watermanagement.model.Machine;
import com.example.watermanagement.service.MachineService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/machines")
public class MachineController {

    private final MachineService machineService;

    @Autowired
    public MachineController(MachineService machineService) {
        this.machineService = machineService;
    }

    @PostMapping("/filter")
    public List<Machine> getMachinesByFilterAndPage(
        @RequestParam(required = false) String manufacturer,
        @RequestParam(required = false) String area,
        @RequestParam(required = false) String community,
        @RequestParam(required = false) String installTimeStart,
        @RequestParam(required = false) String installTimeEnd,
        @RequestParam(required = false) String renewTimeStart,
        @RequestParam(required = false) String renewTimeEnd,
        @RequestParam int from,
        @RequestParam int num
    ) {
        return machineService.getMachinesByFilterAndPage(
            manufacturer, area, community, installTimeStart, installTimeEnd, renewTimeStart, renewTimeEnd, from, num
        );
    }

    @PostMapping("/filter/count")
    public int countMachinesByFilter(
        @RequestParam(required = false) String manufacturer,
        @RequestParam(required = false) String area,
        @RequestParam(required = false) String community,
        @RequestParam(required = false) String installTimeStart,
        @RequestParam(required = false) String installTimeEnd,
        @RequestParam(required = false) String renewTimeStart,
        @RequestParam(required = false) String renewTimeEnd
    ) {
        return machineService.countMachinesByFilter(
            manufacturer, area, community, installTimeStart, installTimeEnd, renewTimeStart, renewTimeEnd
        );
    }
}

测试

package com.example.watermanagement;

import com.example.watermanagement.model.Machine;
import com.example.watermanagement.service.MachineService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class WaterManagementApplicationTests {
    @Autowired
    MachineService machineService;

    @Test
    void contextLoads2() {
        String manufacturer = "";
        String area = "";
        String community = "";
        String installTimeStart = "";
        String installTimeEnd = "";
        String renewTimeStart = "2022/11/26";
        String renewTimeEnd = "2023/3/10";
        int from = 0;
        int num = 20;

        List<Machine> machines = machineService.GetMachinesByFilterAndPage(
                manufacturer, area, community, installTimeStart, installTimeEnd, renewTimeStart, renewTimeEnd, from, num
        );

        System.out.println("#####");
        for (Machine machine : machines) {
            System.out.println("Machine ID: " + machine.id);
            System.out.println("Manufacturer: " + machine.manufacturer);
            System.out.println("Area: " + machine.area);
            System.out.println("Community: " + machine.community);
            System.out.println("Install Time: " + machine.installTime);
            System.out.println("Renew Time: " + machine.renewTime);
            System.out.println("-----------------------------");
        }
        
        int return_num = machineService.CountMachinesByFilter(
                manufacturer, area, community, installTimeStart, installTimeEnd, renewTimeStart, renewTimeEnd
        );
        System.out.println("查询结果总数: " + return_num);
    }

}

测试结果：

----GetMachinesByFilters Param----
Manufacturer: 
Area: 
Community: 
InstallTimeStart: 
InstallTimeEnd: 
RenewTimeStart: 2022/11/26
RenewTimeEnd: 2023/3/10
From: 0
Num: 20
----------------------------------
#####
Machine ID: SJLCQ0300025
Manufacturer: A公司
Area: Z小区
Community: 清苑区
Install Time: 2022-11-25
Renew Time: 2023-03-04
-----------------------------
Machine ID: SJLCQ0300026
Manufacturer: B公司
Area: Z小区
Community: 清苑区
Install Time: 2022-11-25
Renew Time: 2023-03-04
-----------------------------
Machine ID: SJLCQ0300028
Manufacturer: D公司
Area: Z小区
Community: 清苑区
Install Time: 2022-11-25
Renew Time: 2023-03-04
-----------------------------
查询结果总数: 3

问题记录：

java中字符串不能像Python中不区分引号，表示空字符串必须用String area = "";而不能使用String area = '';
java中传递参数只能采用位置顺序，不能像python中指定参数名传参，不过也有其他方法可以
IDEA光标多选是按住ctrl+shift+alt再用鼠标左键点击，快捷键不要忘
SQL中的日期比较，由于这里前端传过来的日期类型是String，而为了筛选时间区间，数据库中installTime和renewTime这两个字段必须采用DATE类型存储，所以在mapper.xml文件中使用STR_TO_DATE()内置函数进行转换，功能很强大。
- 如果前端传来的字符串日期不固定位数，例如"2023/2/28"，就用
  STR_TO_DATE(#{installTime}, '%Y/%c/%e')
- 如果前端传来的字符串日期固定位数，例如"2023/02/28"，就替换为
  '%Y/%m/%d'
使用方式参考：MySQL STR_TO_DATE() Function (w3schools.com)
减小数据库连接池maxLifetime以解决报错问题。

长时间过后调用接口出现500错误，服务器终端输出如下：

HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6c5cdcbb (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.

可能是由于连接在池中保持的时间过长导致的，修改application.yml配置文件，将数据库连接池的 max-lifetime由默认的30分钟缩减到8分钟：

    hikari:
      max-lifetime: 500000 # 设置连接的最大生存时间，单位是毫秒 (默认为1800000，即30分钟)

数据库时区问题，数据库获取当前的时间为世界标准时，修改数据库时区为中国时间（需要root权限）

show variables like '%time_zone%';  #查看时区
select now();

set global time_zone = '+8:00';  #修改mysql全局时区为北京时间
set time_zone = '+8:00';  #修改当前会话时区
flush privileges;  #立即生效

使用ChatGPT开发SpringBoot+Mybatis项目记录——多条件查询

Prompt：

最终解决方案：

问题记录：

CSDN课设项目汇总链接

wandb使用方法