spring boot JPA 多条件查询实例

数据表结构

CREATE TABLE `t_example` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `realname` varchar(128) DEFAULT NULL,
  `card_no` varchar(128) DEFAULT NULL,
  `addr` varchar(256) DEFAULT NULL,
  `school` varchar(128) DEFAULT NULL,
  `company` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

实体类

package com.example.demo.pojo;

import javax.persistence.*;
import java.util.Objects;

@Entity
@Table(name = "t_example", schema = "db_test", catalog = "")
public class TExampleEntity {
    private long id;
    private String realname;
    private String cardNo;
    private String addr;
    private String school;
    private String company;

    @Id
    @Column(name = "id")
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Basic
    @Column(name = "realname")
    public String getRealname() {
        return realname;
    }

    public void setRealname(String realname) {
        this.realname = realname;
    }

    @Basic
    @Column(name = "card_no")
    public String getCardNo() {
        return cardNo;
    }

    public void setCardNo(String cardNo) {
        this.cardNo = cardNo;
    }

    @Basic
    @Column(name = "addr")
    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    @Basic
    @Column(name = "school")
    public String getSchool() {
        return school;
    }

    public void setSchool(String school) {
        this.school = school;
    }

    @Basic
    @Column(name = "company")
    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        TExampleEntity that = (TExampleEntity) o;
        return id == that.id &&
                Objects.equals(realname, that.realname) &&
                Objects.equals(cardNo, that.cardNo) &&
                Objects.equals(addr, that.addr) &&
                Objects.equals(school, that.school) &&
                Objects.equals(company, that.company);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, realname, cardNo, addr, school, company);
    }
}

DAO层

package com.example.demo.dao;

import com.example.demo.pojo.TExampleEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ExampleRepo extends JpaRepository<TExampleEntity, Long> {
    @Query(value = "select * from t_example where if(?1!='', realname=?1, 1) and if(?2!='', card_no=?2, 1)", nativeQuery = true)
    List<TExampleEntity> listAll(String realname, String cardNo, String addr, String school, String company);


    @Query(value = "select * from t_example where if(?1!='', realname=?1, 1) and if(?2!='', card_no=?2, 1)", nativeQuery = true)
    Page<TExampleEntity> listPage(String realname, String cardNo, String addr, String school, String company, Pageable page);

    @Query(value = "select * from t_example where if(?1!='', realname=?1, 1) and if(?2!='', card_no=?2, 1) order by id desc", nativeQuery = true)
    Page<TExampleEntity> listPageDesc(String realname, String cardNo, String addr, String school, String company, Pageable page);
}

控制器

package com.example.demo.controller;

import com.example.demo.dao.ExampleRepo;
import com.example.demo.pojo.TExampleEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class ExampleController {

    @Autowired
    private ExampleRepo _repo;

    @RequestMapping("/listAll")
    public Object listAll(@RequestParam(required = false) String realname,
                          @RequestParam(required = false) String cardNo,
                          @RequestParam(required = false) String addr,
                          @RequestParam(required = false) String school,
                          @RequestParam(required = false) String company) {
        List<TExampleEntity> res = _repo.listAll(realname, cardNo, addr, school, company);
        return res;
    }

    @RequestMapping("/listPage")
    public Object listPage(@RequestParam(required = false) String realname,
                           @RequestParam(required = false) String cardNo,
                           @RequestParam(required = false) String addr,
                           @RequestParam(required = false) String school,
                           @RequestParam(required = false) String company,
                           @RequestParam(required = false) Integer number) {
        int n = 0;
        if (number != null) {
            n = number.intValue();
            n--;
            if (n < 0) {
                n = 0;
            }
        }
        Pageable page = PageRequest.of(n, 1);
        Page<TExampleEntity> res = _repo.listPage(realname, cardNo, addr, school, company, page);
        return res;
    }

    @RequestMapping("/listPageDesc")
    public Object listPageDesc(@RequestParam(required = false) String realname,
                               @RequestParam(required = false) String cardNo,
                               @RequestParam(required = false) String addr,
                               @RequestParam(required = false) String school,
                               @RequestParam(required = false) String company,
                               @RequestParam(required = false) Integer number) {
        int n = 0;
        if (number != null) {
            n = number.intValue();
            n--;
            if (n < 0) {
                n = 0;
            }
        }
        Pageable page = PageRequest.of(n, 1);
        Page<TExampleEntity> res = _repo.listPageDesc(realname, cardNo, addr, school, company, page);
        return res;
    }
}