前言
spring jpa常用查询
jpa group by查询
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<StatEntity> q = cb.createQuery(StatEntity.class);
Root<StatEntity> c = q.from(StatEntity.class);
q.multiselect(c.get("userId"), c.get("userName"),cb.count(c.get("userId")));
q.where(cb.equal(c.get("status"), 8));
q.groupBy(c.get("userId"), c.get("userName"));
Order userIdOrder = new OrderImpl(c.get("userId"), true);
Order countOrder = new OrderImpl(cb.count(c.get("userId")), false);
q.orderBy(countOrder, userIdOrder);
System.out.println("con:" + em.createQuery(q).getResultList());
CriteriaQuery
这部分创建query的时候,可以不使用entity,普通的java bean对象也可以,要有相对应的构造函数
查询个别的字段
只查询StatEntity里面的id列
@Query("select new com.illegalaccess.po.StatEntity(id) from StatEntity te " +
"where te.status=:status and te.updateTime<:updateTime and te.retryCount<:retryCount")
Page<StatEntity> findAllByTaskStatus(@Param("status") Integer status,
@Param("updateTime") Date updateTime,
@Param("retryCount") Integer retryCount,
Pageable pageable);
在StatEntity里面添加只包含id的构造函数,则查询只查询id列
若想查询id,status,则在StatEntity里面添加如下构造函数
public StatEntity(Long id, Integer status) {
this.id = id;
this.status = status;
}
上面的查询换成如下格式
@Query("select new com.illegalaccess.po.StatEntity(id,status) from StatEntity te " +
"where te.status=:status and te.updateTime<:updateTime and te.retryCount<:retryCount")
Page<StatEntity> findAllByTaskStatus(@Param("status") Integer status,
@Param("updateTime") Date updateTime,
@Param("retryCount") Integer retryCount,
Pageable pageable);
select new com.illegalaccess.po.StatEntity(id,status),这里的StatEntity也可以换成普通的java bean