jpa查询学习实例

//模糊条件查询
@Query("select a.id from Room a where a.fullName like CONCAT('%',?1,'%')")
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
Set<Long> findIdByFullName(String name);

@Query("select a from Room a where a.name = ?1 and a.fullName like CONCAT('%',?2,'%')")
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
List<Room> findByNameAndPName(String name, String fullName);
//条件查询排序
@Query("select a from Room a where a.parentId = ?1 order by a.id")
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
List<Room> findByPidOrderName(Long pid);
//多数据更新
@Query("update GZFUser set pIdCard = ?2, holderName = ?3, type='other' where id in (?1)")
@Modifying
void updateOtherTypeByIds(Set<Long> ids, String pIdCard, String holderName);
//根据日期查询当年当月数据按照部门分组统计
@Query("select a.organization.id,count (a) from Education a where month(a.dateCreated) = month(?1) and year(a.dateCreated) = year(?1) group by a.organization.id")
List<Object[]> findHomeStateResult(String date);
//按名称分组排序
@Query("select a.name,a.spec,a.munit,sum(a.number),a.month from FireEquipment a group by a.name")
List<Object[]> findByNameAndMonth();
//删除
@Modifying
@Query("delete from OtherDocumentState where organization.id in (?1)")
int deleteIdIn(Long[] longs);

@Modifying
@Query("delete from OtherDocumentState where document.id=1")
int deleteByOtherDocumentId(Long id);
//简写方式
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByUsername(String username);

@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByEmail(String email);

@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByUsernameAndIdIsNot(String username, Long id);

@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByEmailAndIdIsNot(String username, Long id);