jpa 分页多条件动态查询

父类请求参数

package com.byx.taohuawu.common.request;

import com.byx.taohuawu.common.consts.WebConstants;

public class BasePageRequest {

    // 单页数据大小 默认10
    private int pageSize = WebConstants.DEFAULT_PAGE_SIZE;

    // 页码 默认1
    private int pageNum = WebConstants.DEFAULT_PAGE_NUM;


    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }
}

子类请求参数

package com.byx.taohuawu.common.request;

import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 停车场管理分页查询请求
 *
 */
@EqualsAndHashCode(callSuper = false)
@Data
public class ParkingLotPageRequest extends BasePageRequest {
	
	//停车场名称
    private String parkinglotName;
    
    //停车场类型 	0-住宅小区 1-道路 2-商场 
    private Integer type;
}

实现类步骤

import org.springframework.data.domain.Page;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.StringUtils;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.CriteriaBuilder;

 以上是下列代码所需用到的包

@Override
	public Page<ParkingLot> selectAllPage(ParkingLotPageRequest request) {
		// 模糊查询条件
		Specification<ParkingLot> query = new Specification<ParkingLot>() {
			private static final long serialVersionUID = 1L;
			@Override
			//重新Specification的toPredicate方法
			public Predicate toPredicate(Root<ParkingLot> root, CriteriaQuery<?> query,
					CriteriaBuilder criteriaBuilder) {
				List<Predicate> predicates = new ArrayList<Predicate>();
				if (!StringUtils.isEmpty(request.getParkinglotName())) {
					predicates.add(
							criteriaBuilder.like(root.get("parkinglotName"), "%" + request.getParkinglotName() + "%"));
				}
				if (null != request.getType()) {
					predicates.add(criteriaBuilder.equal(root.get("type"), request.getType()));
				}
				return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
			}
		};
		//实现分页
		PageRequest p = PageRequest.of(request.getPageNum() - 1, request.getPageSize(),
				Sort.by(Direction.DESC, "updatedTime"));
		Page<ParkingLot> pageAll = plRepo.findAll(query, p);
		return pageAll;
	}

若存在外键关联关系，想通过外键的实体属性模糊查询本实体，则代码如下：

if (!StringUtils.isEmpty(request.getProjectName())) {//通过外键关联的实体属性名模糊查询
    //通过项目名模糊查询出项目列表，以此获取项目的id
    //注：方法参数前后必须加"%"
	List<Project> projects = projectRepo.findByProjectNameLike("%" + request.getProjectName() + "%");
    if (projects != null && projects.size() > 0) {
        //此处获取实体类属性，不能写“projectId”
	    In<Object> in = criteriaBuilder.in(root.get("project"));
		for (Project p : projects) {
		    in.value(p.getId());
		}
        list.add(in);
	} else {
        //此处待确定，若项目为空，则默认查询projectId=-1
		list.add(criteriaBuilder.equal(root.get("project"), -1));
	}
}

 当外键不为主键且是其他类型时

if (StringUtils.hasText(request.getProjectName())) {
    List<Project> projects = projectRepo.findByNameLike("%" + request.getProjectName().trim() + "%");
    if (projects != null && projects.size() > 0) {
        //也可写成In<Object> in = criteriaBuilder.in(root.get("project").as(Project.class));
        In<Object> in = criteriaBuilder.in(root.get("project").get("code").as(String.class));
        for (Project project : projects) {
            //上面写成as(Project.class)的话，这边需要写成in.value(project)
            in.value(project.getCode());
        }
        predicates.add(in);
    } else {
        predicates.add(criteriaBuilder.equal(root.get("project"), -1));
    }
}

附：

Root：查询哪个表，哪个实例对象

CriteriaQuery：查询哪些字段，排序是什么（我们在这里暂时没有用上）

CriteriaBuilder：字段之间是什么关系，如何生成一个查询条件，每一个查询条件都是什么方式，比如两个查询条件是and的关系还是or的关系等

root.get(“parkinglotName”);这句话怎么理解呢，我的理解是root就是我们实例对象，这个实例对象的get方法就获取到了表里面的数据的路径，所以称之为Path。

Predicate（表达）：单独每一条查询条件的详细描述，就是查询的条件，比如模糊查询，大于小于查询，等于查询之类。

阔展：

 （本地sql查询,注意表名啥的都用数据库中的名称，适用于特定数据库的查询）

public interface UserRepository extends JpaRepository<User, Long> {
    

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

（Query注解,hql语局，适用于查询指定条件的数据）

@Query(value = "select b.roomUid from RoomBoard b where b.userId=:userId and b.lastBoard=true order by  b.createTime desc")
Page<String> findRoomUidsByUserIdPageable(@Param("userId") long userId, Pageable pageable);

Pageable pageable = new PageRequest(pageNumber,pageSize);
Page<String> page = this.roomBoardRepository.findRoomUidsByUserIdPageable(userId,pageable);
List<String> roomUids = page.getContent();

（Criteria查询，适用于动态sql查询）

@PersistenceContext
private EntityManager em;

@Override
@Transactional(readOnly = true)
public List<TFssCreditIssueLog> queryCreditIssueLogByCondition(TFssCreditIssueLog tFssCreditIssueLog, int pageIndex, int pageSize) {
    List<TFssCreditIssueLog> tFssCreditIssueLogList = null;

    //Jpa标准查询
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<TFssCreditIssueLog> criteriaQuery = criteriaBuilder.createQuery(TFssCreditIssueLog.class);
    Root<TFssCreditIssueLog> root = criteriaQuery.from(TFssCreditIssueLog.class);
    //动态条件
    List<Predicate> conditions = new ArrayList<Predicate>();
    if(tFssCreditIssueLog != null){
        if(tFssCreditIssueLog.getEmpName() != null && !tFssCreditIssueLog.getEmpName().equals("")){
            conditions.add(criteriaBuilder.like(criteriaBuilder.lower(root.get("empName")), StringUtils.lowerCase("%" + tFssCreditIssueLog.getEmpName() + "%")));
        }
    }
    //添加条件
    if(conditions != null && conditions.size() > 0){
        criteriaQuery.where(conditions.toArray(new Predicate[0]));
    }
    TypedQuery<TFssCreditIssueLog> typedQuery = em.createQuery(criteriaQuery);
    //设置分页
    typedQuery.setFirstResult(pageIndex);
    typedQuery.setMaxResults(pageSize);
    //查询结果
    tFssCreditIssueLogList = typedQuery.getResultList();

    return tFssCreditIssueLogList;
}

可以定义多个 Specification，然后通过 Specifications 对象将其连接起来

//第一个Specification定义了两个or的组合
Specification s1 = new Specification() {
    @Override
    public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
        Predicate p1 = criteriaBuilder.equal(root.get("id"),"2");
        Predicate p2 = criteriaBuilder.equal(root.get("id"),"3");
        return criteriaBuilder.or(p1,p2);
    }
};
//第二个Specification定义了两个or的组合
Specification s2 = new Specification() {
    @Override
    public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
        Predicate p1 = criteriaBuilder.like(root.get("address"),"zt%");
        Predicate p2 = criteriaBuilder.like(root.get("name"),"foo%");
        return criteriaBuilder.or(p1,p2);
    }
};
//通过Specifications将两个Specification连接起来，第一个条件加where，第二个是and
List stus = studentSpecificationRepository.findAll(Specifications.where(s1).and(s2));