Spring JPA 分页查询

1. 之前一直用别人的框架，进行分页。这次项目需要Spring　JPA分页 ，动手写了一下。

其实 Spring　JPA 已经有封装好的分页方法，但是，那些只适合特定的查询条件， 不适合动态条件查询。

方法1：

注意表名啥的都用数据库中的名称, 适用于特定数据库的查询
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(value = "SELECT u  FROM Users u WHERE u.username like %:username%")
	List<User> findByName(@Param("username") String username);
}

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);

方法2： 动态条件查询

单表查询;

/***
     * 查询   分页数据
     *
     **/
	public Page<DeviceInfoEntity> page(SearchDTO searchDto){
		Specification<DeviceInfoEntity> spec = new Specification<DeviceInfoEntity>() { // 查询条件构造
			@Override
			public Predicate toPredicate(Root<DeviceInfoEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicate = new ArrayList<>(); 
				Path<String> authStatus = root.get("authStatus"); //授权状态
				Path<String> devName = root.get("devName");
				Path<String> devMode = root.get("devMode");
				Predicate preP = null;
				if(searchDto.getAuthStatus() != null && searchDto.getAuthStatus() > 0){
					Predicate p1 = cb.equal(authStatus, searchDto.getAuthStatus());
					 preP = cb.and(p1);
				}
				
				if(StringUtils.isNotBlank(searchDto.getDevKeyword())){ //模糊查询
					Predicate p2 = cb.like(devName, "%" + searchDto.getDevKeyword() + "%");
					Predicate p3 = cb.like(devMode, "%" + searchDto.getDevKeyword() + "%");
					if(preP!=null){
                        preP = cb.and(preP,cb.or(p2,p3));
                    }else{
                    	preP = cb.or(p2,p3);
                    }
				} 
				predicate.add(preP);
	            Predicate[] pre = new Predicate[predicate.size()];
	            query.where(predicate.toArray(pre));
	            return query.getRestriction();
			} 
		 };   
		 Page<DeviceInfoEntity> page = deviceInfoDao.findAll(spec, searchDto.toPageable());
		 return page;
	}

 多表联合查询：

先创建一个对象， 和查询出的结果对应：

@Data //lombok
@Entity //这个注解必备
public class TempInfoPageEntity  implements Serializable {

	@Transient   
	private static final long serialVersionUID = 1L;
	
	/**
     * 注释：ID
     */
	@Id //这个注解必备 接收分页查询 ，必须有个id
    private Long id;

    private String devId;
	
    private String devName;
	
    private String devMode;
	
    private String createdBy;
	
    private String createdTime;
	
    private String updatedBy;
	
    private String updatedTime;
	
    private Integer version;

}

分页的方法;

public Page<TempInfoPageEntity  > pageInfo(SearchDTO searchDto){
		StringBuffer dataSql = new StringBuffer("SELECT tab1.id, tab1.dev_id, tab1.dev_name, tab1.dev_mode,tab1.created_by, tab1.created_time, tab1.updated_by, tab1.updated_time,tab1.version FROM temp_info tab1 LEFT JOIN temp_thm_info tab2 ON tab1.dev_id = tab2.dev_id WHERE 1=1 ");
		StringBuffer countSql = new StringBuffer("SELECT count(*) FROM temp_info tab1 LEFT JOIN temp_thm_info tab2 ON tab1.dev_id = tab2.dev_id WHERE 1=1 ");

		Map<String,Object> params = new HashMap<>();
        StringBuilder whereSql = new StringBuilder();
		if(StringUtils.isNotBlank(searchDto.getAlgName())){
			whereSql.append(" and tab2.alg_name = :alg_name ");
			params.put("alg_name",searchDto.getAlgName());
		}
		
		if(searchDto.getAuthStatus() != null && searchDto.getAuthStatus() > 0){
			whereSql.append(" and tab2.auth_status = :auth_status ");
			params.put("auth_status",searchDto.getAuthStatus());
		}

		if(StringUtils.isNotBlank(searchDto.getDevKeyword())){
			whereSql.append(" and( tab1.dev_name like :dev_name or dev_mode like :dev_mode )");
			params.put("dev_name","%"+searchDto.getDevKeyword()+"%");
			params.put("dev_mode","%"+searchDto.getDevKeyword()+"%");
		}
		String sql1 = dataSql.append(whereSql).append(" order by tab1.created_time desc ").toString();
		Query dataQuery = em.createNativeQuery(sql1,AuthDeviceInfoPageEntity.class);
		Query countQuery = em.createNativeQuery(countSql.append(whereSql).toString());
		
		setParameters(countQuery,params);
		BigDecimal totalSize = new BigDecimal(countQuery.getSingleResult().toString());
		
		int start = (searchDto.getPageNo()-1)*searchDto.getPageSize();
		setParameters(dataQuery,params);
		dataQuery.setFirstResult(start);
    	dataQuery.setMaxResults(searchDto.getPageSize());
        List<AuthDeviceInfoPageEntity> data = dataQuery.setFirstResult(start).setMaxResults(searchDto.getPageSize()).getResultList();
        
        Pageable pageable = new PageRequest(searchDto.getPageNo()-1, searchDto.getPageSize());
        Page<TempInfoPageEntity> page = new PageImpl<TempInfoPageEntity>(data, pageable, totalSize.longValue());
        return page;
	}

 

 

不足之处，请指教……