mybatisplus做分页查询-模糊查询

测试案例

用mybatisplus写一个分页查询代码，关联设备表Device和场地表Venue，设备表里面有外键venueId,做模糊查询，用场地表里面的name，设备表里面的SNnumber做条件

Device:实体类

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("device")
public class Device {
    private Long id;
    private String SNnumber;
    @TableField("venueId")
    private Long venueId;
    // 其他设备相关字段
}

Venue:实体类

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("venue")
public class Venue {
    private Long id;
    private String name;
    // 其他场地相关字段
}

Mapper接口定义

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Device;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;

public interface DeviceMapper extends BaseMapper<Device> {
    Page<Device> getDeviceByConditions(Page<Device> page, @Param("venueName") String venueName, @Param("deviceSn") String deviceSn);
}

XML映射文件定义

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//" "http://mybatis.org/dtd/mybatis - 3.dtd">
<mapper namespace="com.example.demo.mapper.DeviceMapper">
    <select id="getDeviceByConditions" resultMap="DeviceResultMap">
        SELECT device.*
        FROM device
                 LEFT JOIN venue ON device.venueId = venue.id
        <where>
            <if test="venueName!= null and venueName!= ''">
                AND venue.name LIKE concat('%', #{venueName}, '%')
            </if>
            <if test="deviceSn!= null and deviceSn!= ''">
                AND device.SNnumber LIKE concat('%', #{deviceSn}, '%')
            </if>
        </where>
        ORDER BY device.id
    </select>
    <!-- 定义结果映射，这里假设Device实体类中的属性和数据库表中的字段名是一一对应的 -->
    <resultMap id="DeviceResultMap" type="com.example.demo.entity.Device">
        <id property="id" column="id"/>
        <result property="SNnumber" column="SNnumber"/>
        <result property="venueId" column="venueId"/>
    </resultMap>
</mapper>

Service层使用示例

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.mapper.DeviceMapper;
import com.example.demo.entity.Device;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class DeviceService {
    @Autowired
    private DeviceMapper deviceMapper;

    public IPage<Device> getDeviceByConditions(int currentPage, int pageSize, String venueName, String deviceSn) {
        Page<Device> page = new Page<>(currentPage, pageSize);
        return deviceMapper.getDeviceByConditions(page, venueName, deviceSn);
    }
}

Controller层使用示例（可选，如果需要通过接口调用的话）

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.demo.entity.Device;
import com.example.demo.service.DeviceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/device")
public class DeviceController {
    @Autowired
    private DeviceService deviceService;

    @GetMapping("/list")
    public IPage<Device> getDeviceList(@RequestParam(defaultValue = "1") int currentPage,
                                       @RequestParam(defaultValue = "10") int pageSize,
                                       @RequestParam(required = false) String venueName,
                                       @RequestParam(required = false) String deviceSn) {
        return deviceService.getDeviceByConditions(currentPage, pageSize, venueName, deviceSn);
    }
}

请注意： 上述代码中的com.example.demo是示例的包名，需要根据实际项目情况进行替换。
在实际使用中，可能需要根据数据库的具体情况（如数据库类型、字段类型等）进行适当的调整

。