Spring data JPA complex multi condition query

I Sorting and paging

// 排序
Direction direction = Direction.DESC;
//directionStr为前端传值，asc代表正序
if ("asc".equals(directionStr)) {
	direction = Direction.ASC;
}
//默认使用datetime字段进行排序
String sortProperty = "datetime";
//sortParam为前端传的排序字段
if (UtilValidate.isNotEmpty(sortParam)) {
	sortProperty = sortParam;
}
Sort sort = new Sort(direction,sortProperty);

// 分页
// pageNumParam为页码，默认为0，pageSizeParam为每页条数
Pageable pageable = PageRequest.of(pageNumParam,pageSizeParam,sort);

be careful:

If you want to sort by a property of an entity associated with a foreign key, change the sortproperty directly to the corresponding entity name. Property name:

1. For example, for a user entity object, the foreign key is associated with the address entity object address one-to-one. If you want to sort through the ID field of the address object, the sortproperty should pass "address. ID".

@Entity
@Table(name="t_user")
public class User{
	@Id
	private Long id;

	@ManyToOne(fetch=FetchType.EAGER)
	@JoinColumn(name = "address_id")
	@NotFound(action=NotFoundAction.IGNORE)
	private Address address;
	
	/**
	 * 登录名
	 */
	@Column
	private String username;

	/**
	 * 状态
	 */
	@Column
	private Integer state;

	/**
	 * 所属机构
	 */
	@ManyToOne(fetch=FetchType.EAGER)
	@JoinColumn(name = "department_id")
	@NotFound(action=NotFoundAction.IGNORE)
	private Department department;

	/**
	 * 注册时间
	 */
	@Column
	private Long datetime;
}

2. The Oracle database is used in the project. Errors are always reported at the beginning. After troubleshooting, it is found that it is the problem of the configured Oracle dialect. If there are small partners who also use Oracle database, please note that the following are the relevant configuration items modified:

#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle9Dialect

II Complex query

The following complex queries contain four types:

1. Foreign key Association query - annotation 1

2. Or query - label 2

3. Foreign key in query - annotation 3

4. In query of ID - annotation 4

For complex multi condition queries using JPA, it is generally necessary to rewrite the methods in the jpaspecificationexecutor interface:

public interface JpaSpecificationExecutor<T> {

    T findOne(Specification<T> spec);

    List<T> findAll(Specification<T> spec);

    Page<T> findAll(Specification<T> spec,Pageable pageable);

    List<T> findAll(Specification<T> spec,Sort sort);

    long count(Specification<T> spec);
}

We need to use the page findall (specification spec, pageable pageable) method here, so we need to rewrite it:

// 假设此处查询的是用户列表
return userRepository.findAll(new Specification() {
	@Override
	public Predicate toPredicate(Root root,CriteriaQuery query,CriteriaBuilder cb) {
		List<Predicate> pList = new ArrayList<Predicate>();
		Predicate[] ps = new Predicate[pList.size()];
		List<Predicate> stateList = new ArrayList<Predicate>();
		Predicate[] statePs = new Predicate[stateList.size()];
		
		// 查询用户表中未删除的地址 （标注1）
		// 假设address是用户表User中的外键关联，存放地址信息，state为0代表有效
		pList.add(cb.equal(root.join("address").get("state"),0));
		
		//查询状态为10-未审核或0-已审核状态的用户 （标注2）
		stateList.add(cb.equal(root.get("state"),0));
		stateList.add(cb.equal(root.get("state"),10));
		pList.add(cb.or(stateList.toArray(statePs)));
		
		// 时间查询
		pList.add(cb.between(root.get("datetime"),start,end));
				
		// 机构查询 （标注3）
		if (UtilValidate.isNotEmpty(departsStr)) {
			Predicate pDepart = inQuery(departsStr,"department",cb,root);
			pList.add(pDepart);
		}
			
		query.where(pList.toArray(ps));
		return null;
	}
},pageable);

The following is the method used to associate in queries with foreign keys:

public Predicate inQuery(String entityIds,String entityName,CriteriaBuilder cb,Root root) {
	List<Predicate> list = new ArrayList<>();
	String[] ids = entityIds.split(",");
	if (ids != null && ids.length > 0) {
		In<Object> in = cb.in(root.join(entityName).get("id"));
		for (String id : ids) {
			Long idNum = Long.parseLong(id);
			in.value(idNum);
		}
		list.add(in);
	}

	Predicate[] p = new Predicate[list.size()];
	return cb.and(list.toArray(p));
}

If you want to query the ID in, just put root join(entityName). Change get ("Id") to root Just get ("Id") (Note 4).