spring data jpa分页查询示例代码

spring data jpa分页查询示例代码

最近项目上用就hibernate+spring data jpa，一开始感觉还不错，但是随着对业务的复杂，要求处理一些复杂的sql，就顺便研究了下，把结果记录下，以便日后查看。用到Specification，需要继承JpaSpecificationExecutor接口。（下面代码有的分页从0开始作为第一页，有的从1开始作为作为第一页，我也忘记，请自己测试）

DAO层：

import java.util.List;

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.JpaSpecificationExecutor;

import org.springframework.data.jpa.repository.Modifying;

import org.springframework.data.jpa.repository.Query;

import org.springframework.stereotype.Repository;

@Repository

public interface CameraInfoRepo extends JpaRepository, JpaSpecificationExecutor {

@Query("select c from CameraInfoPO c where c.deviceInfo.id = ?1")

public List findCamerasByDeviceId(String listDeviceInfoId);

//更新之后不清空缓存，在一个事务里查询到旧数据(hibernate)

@Modifying

@Query(value = "update CameraInfoPO c set c.isEnabled = 1 where c.id = ?1")

public void updateEnabled(String cameraIdhttp://);

//更新之后清空缓存，不保留旧对象(hibernate)

@Modifying(clearAutomatically = true)

@Query(value = "update CameraInfoPO c set c.isEnabled = 0 where c.id = ?1")

public void updateUnEnabled(String cameraId);

//带条件的分页查询

public Page findByIsEnabled(Integer isEnabled, Pageable pageable);

}

DAO实现层

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import javax.persistence.TypedQuery;

import javax.persistence.criteria.CriteriaBuilder;

import javax.persistence.criteria.CriteriaQuery;

import javax.persistence.criteria.Predicate;

import javax.persistence.criteria.Root;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.PageImpl;

import org.springframework.data.domain.Pageable;

import org.springframework.data.jpa.domain.Specification;

import org.springframework.stereotype.Repository;

@Repository

public class CameraInfoRepoImpl {

@PersistenceContext

private EntityManager em;

@Resource

private CameraInfoRepo cameraInfoRepo;

public Page findCameraInfoByPage(Pageable pageable, Integer isEnabled) {

//CriteriaBuilder,用来构建CritiaQuery的构建器对象

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

//CriteriaQuery,它包含着查询语句的条件各个部分，比如：select 、from、where、group by、order by等

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(CameraInfoPO.class);

//查询根，用于获取查询实例的属性，通过CriteriaQuery的from方法获取

Root rootFrom = criteriaQuery.from(CameraInfoPO.class);

//查询条件

List predicates = new ArrayList();

if (null != isEnabled) {

Predicate predicate = criteriaBuilder.equal(rootFrom.get("isEnabled").as(Integer.class), isEnabled);

predicates.add(predicate);

}

//格式化参数

criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));

//默认按照id排序（从小到大）

criteriaQuery.orderBy(criteriaBuilder.asc(rootFrom.get("id")));

//SQL查询对象

TypedQuery createQuery = em.createQuery(criteriaQuery);

//分页参数

Integer pageSize = pageable.getPageSize();

Integer pageNo = pageable.getPageNumber();

//计数查询结果条数

TypedQuery createCountQuery = em.createQuery(criteriaQuery);

// 实际查询返回分页对象

int startIndex = pageSize * pageNo;

createQuery.setFirstResult(startIndex);

createQuery.setMaxResults(pageable.getPageSize());

Page pageRst =

new PageImpl(createQuery.getResultList(), pageable, createCountQuery.getResultList().size());

return pageRst;

}

//制造查询条件结果(建议存放map)

private Specification getWhereClause(final Integer isEnabled) {

return new Specification() {

public Predicate toPredicate(Root r, CriteriaQuery> q, CriteriaBuilder cb) {

Predicate predicate = cb.conjunction();

if (null != isEnabled) {

predicate = cb.equal(r.get("isEnabled").as(Integer.class), isEnabled);

}

return predicate;

}

};

}

//单表根据查询条件的分页

public Page findCameraInfoByPageForJpa(Pageable pageable, Integer isEnabled) {

Specification spec = getWhereClause(isEnabled);

Page pageRst = cameraInfoRepo.findAll(spec, pageable);

return pageRst;

}

}

还有另外一种就更简单了，如果只是根据表里面的一个或者几个字段，可以直接写jpa实现，不用复杂

Pageable pageable = new PageRequest(1, 1);

Page page = cameraInfoRepo.findByIsEnabled(1, pageable);

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。