## 一、简单查询 自定义的简单查询就是根据方法名来自动生成 SQL，主要的语法是`findXXBy`,`readAXXBy`,`queryXXBy`,`countXXBy`,`getXXBy`后面跟属性名称： ~~~ User findByUserName(String userName); ~~~ 也使用一些加一些关键字`And`、`Or` ~~~ User findByUserNameOrEmail(String username, String email); ~~~ 修改、删除、统计也是类似语法 ~~~ Long deleteById(Long id); Long countByUserName(String userName) ~~~ 基本上 SQL 体系中的关键词都可以使用，例如：`LIKE`、`IgnoreCase`、`OrderBy`。 ~~~ List<User> findByEmailLike(String email);//传入的like字符串，根据需要，可以为%test%形式，或%test，或test%等 List<SysProxyLog> findByRequestAddrLikeAndRequestBodyLike(String requestAddr, String requestBody); User findByUserNameIgnoreCase(String userName); List<User> findByUserNameOrderByEmailDesc(String email); #多个order by List<CardPooledQuota> findByCardIdAndReviseValidTimeLessThanEqualOrderByReviseInvalidTimeAscPackageTypeAsc(long cardId, String reviseValidTime); ~~~ **具体的关键字，使用方法和生产成SQL如下表所示** | Keyword | Sample | JPQL snippet | | --- | --- | --- | | And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 | | Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 | | Is,Equals | findByFirstnameIs,findByFirstnameEquals | … where x.firstname = ?1 | | Between | findByStartDateBetween | … where x.startDate between ?1 and ?2 | | LessThan | findByAgeLessThan | … where x.age < ?1 | | LessThanEqual | findByAgeLessThanEqual | … where x.age ⇐ ?1 | | GreaterThan | findByAgeGreaterThan | … where x.age > ?1 | | GreaterThanEqual | findByAgeGreaterThanEqual | … where x.age >= ?1 | | After | findByStartDateAfter | … where x.startDate > ?1 | | Before | findByStartDateBefore | … where x.startDate < ?1 | | IsNull | findByAgeIsNull | … where x.age is null | | IsNotNull,NotNull | findByAge(Is)NotNull | … where x.age not null | | Like | findByFirstnameLike | … where x.firstname like ?1 | | NotLike | findByFirstnameNotLike | … where x.firstname not like ?1 | | StartingWith | findByFirstnameStartingWith | … where x.firstname like ?1 (parameter bound with appended %) | | EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1 (parameter bound with prepended %) | | Containing | findByFirstnameContaining | … where x.firstname like ?1 (parameter bound wrapped in %) | | OrderBy | findByAgeOrderByLastnameDesc | … where x.age = ?1 order by x.lastname desc | | Not | findByLastnameNot | … where x.lastname <> ?1 | | In | findByAgeIn(Collectionages) | … where x.age in ?1 | | NotIn | findByAgeNotIn(Collectionage) | … where x.age not in ?1 | | TRUE | findByActiveTrue() | … where x.active = true | | FALSE | findByActiveFalse() | … where x.active = false | | IgnoreCase | findByFirstnameIgnoreCase | … where UPPER(x.firstame) = UPPER(?1) | ## 二、自定义多表语句（JPQL） 其实 Spring Data 觉大部分的 SQL 都可以根据方法名定义的方式来实现，但是由于某些原因我们想使用自定义的 SQL 来查询，Spring Data 也是完美支持的；在 SQL 的查询方法上面使用@Query注解； >[danger] 如涉及到删除和修改在需要加上@Modifying也可以根据需要添加@Transactional对事务的支持，查询超时的设置等； ``` @Modifying @Query("update User u set u.userName = ?1 where u.id = ?2") int modifyByIdAndUserId(String userName, Long id); ``` ``` @Transactional @Modifying @Query("delete from User where id = ?1") void deleteByUserId(Long id); ``` ``` @Transactional(timeout = 10) @Query("select u from User u where u.emailAddress = ?1") User findByEmailAddress(String emailAddress); @Query("select staff from SysStaff staff where exists (select 1 from SysStaffInvitation siv where siv.staffId=staff.staffId and siv.fromStaffId=?1 and staff.createTime like ?2%)") List<SysStaff> getInvitedSysStaffList(long staffId, String invitationTime); ``` **命名查询和占位符查询** 命名查询中，参数必须加上@Param定义； 占位符查询，则需要注意，索引序号从1开始； ``` @Query("select role from SysRole role,SysStaffRole sr where sr.roleId = role.roleId and sr.staffId = ?1") List<SysRole> getRoleListBySaffId(long staffId); ``` ``` @Query("select staff from SysStaff staff,SysStaffRole sr where sr.staffId = staff.staffId and sr.roleId = :roleId") List<SysStaff> getSysStaffListOfSpecRole(@Param("roleId") long roleId); ``` **IN子句及判空条件** 传入的参数不能是字符串，必须是集合； 例如：List<Long> filterDepartmentIds 判空：coalesce(?6, null) ``` @Query(value = "select staff from SysStaff staff where staff.sts=" + StaffStatus.VALID // + " and (?1=0L or staff.domainId=?1)"// + " and (?2=0L or staff.departmentId=?2)"// filterDepartmentId + " and (?3=0L or staff.corporationId=?3)"// filterCorporationId + " and (?4=0L or exists(select 1 from SysStaffRole sr where sr.staffId=staff.staffId and sr.roleId=?4))"// filterRoleId + " and (?5=0L or exists(select 1 from SysStaffSetting ss where ss.staffId=staff.staffId and ss.identityId=?5))"// filterIdentityId + " and (coalesce(?6, null) is null or staff.departmentId in (?6))"// filterDepartmentIds + " and (coalesce(?7, null) is null or staff.corporationId in (?7))"// filterCorporationIds + " and (coalesce(?8, null) is null or exists(select 1 from SysStaffRole sr where sr.staffId=staff.staffId and sr.roleId in (?8)))"// filterRoleIds + " and (coalesce(?9, null) is null or exists(select 1 from SysStaffSetting ss where ss.staffId=staff.staffId and ss.identityId in (?9)))"// filterIdentityIds + " and (?10 is null or ?10 = '' or staff.staffCode like ?10)"// staffCode + " and (?11 is null or ?11 = '' or staff.staffName like ?11)"// staffName + " and (?12 is null or ?12 = '' or staff.roleNames like ?12)"// roleNames + " and (?13=0L or staff.departmentId=?13)"// departmentId ) Page<SysStaff> getStaffOfSpecCondintionForComponent(long filterDomainId, long filterDepartmentId, long filterCorporationId, long filterRoleId, long filterIdentityId, List<Long> filterDepartmentIds, List<Long> filterCorporationIds, List<Long> filterRoleIds, List<Long> filterIdentityIds, String staffCode, String staffName, String roleNames, long departmentId, Pageable pageable); ``` 注意使用的时候，需要确保List默认值为null； ``` sysStaffDao.getStaffOfSpecCondintionForComponent(filterDomainId, filterDepartmentId, filterCorporationId, filterRoleId, filterIdentityId, filterDepartmentIds.size() == 0 ? null : filterDepartmentIds, filterCorporationIds.size() == 0 ? null : filterCorporationIds, filterRoleIds.size() == 0 ? null : filterRoleIds, filterIdentityIds.size() == 0 ? null : filterIdentityIds, staffCode, staffName, roleNames, departmentId, PagableBuilder.getPageable(curPage, pageSize, orderFieldName, orderDirection)) ``` **模糊查询** ``` @Query("select department from SysDepartment department where department.parentDepartmentId=?1 and department.sts=" + DepartmentStatus.VALID // + " and (?2 is null or ?2 = '' or department.departmentName like ?2)" // + " and (?3 is null or ?3 = '' or department.departmentFullName like ?3)" // + " and (?4 is null or ?4 = '' or department.departmentCode like ?4)" // ) List<SysDepartment> getChildDepartmentsOfSpecDepartment(long departmentId, String subDepartmentName, String subDepartmentFullName, String subDepartmentCode); ``` 调用： ``` sysDepartmentDao.getChildDepartmentsOfSpecDepartment(departmentId, wrapLike(subDepartmentName, false), wrapLike(subDepartmentFullName, false), wrapLike(subDepartmentCode, false)) ``` ## 三、动态查询条件 使用JpaSpecificationExecutor； 构建Specification查询条件，然后传给JpaSpecificationExecutor的findAll（包含List<T>或Page<T>两种返回值，代表分页与非分页的方法），分页的在Specification之后多传入一个Pageable pageable参数； 一般将这种判断逻辑代码封装在Service中，最后调用JpaSpecificationExecutor的方法，参考如下：  ## 四、Predicate ### **OR** ``` Predicate predicate1 = criteriaBuilder.like(root.get("goodsTitle"), keyword); Predicate predicate2 = criteriaBuilder.like(root.get("eShopName"), keyword); predicates.add(criteriaBuilder.or(predicate1, predicate2)); ``` ### **IN** ``` List<Long> myAgentList = sysStaffRelationDao.getStaffIdListByRelatedStaffIdAndRelationType(model.getCurLoginData().getCurStaffId(), RayiotSysStaffRelationType.parent_agent); myAgentList.add(model.getCurLoginData().getCurStaffId()); In<Long> inStaffIdListClause= criteriaBuilder.in(root.get("terminalAgentStaff")); for (Long id : myAgentList) { inStaffIdListClause.value(id); } predicates.add(inStaffIdListClause); ``` ### **EXIST** ``` List<Predicate> predicates = new ArrayList<>(); if (!StringUtil.isEmpty(staffName)) { predicates.add(criteriaBuilder.like(root.get("staffName"), wrapLike(staffName))); } if (!StringUtil.isEmpty(staffCode)) { predicates.add(criteriaBuilder.like(root.get("staffCode"), wrapLike(staffCode))); } Subquery<SysStaffRole> subquery = query.subquery(SysStaffRole.class); Root<SysStaffRole> subRoot = subquery.from(SysStaffRole.class); subquery.select(subRoot.get("id")); subquery.where(criteriaBuilder.equal(subRoot.get("roleId"), roleId), criteriaBuilder.equal(subRoot.get("staffId"), root.get("staffId"))); predicates.add(criteriaBuilder.exists(subquery)); return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); ``` ### **关联表** **不推荐使用，因为需要在实体中，建立好映射；** ``` List<Predicate> predicates = new ArrayList<>(); if (!StringUtil.isEmpty(staffName)) { Join<SysStaffDialogue, SysStaff> startStaffJoin = root.join("SysStaff", JoinType.INNER); startStaffJoin.on(criteriaBuilder.equal(startStaffJoin.get("staffId"), root.get("staffId"))); predicates.add(criteriaBuilder.like(startStaffJoin.get("staffName"), wrapLike(staffName))); } if (!StringUtil.isEmpty(dialogicalStaffName)) { Join<SysStaffDialogue, SysStaff> dialogicalStaffJoin = root.join("SysStaff", JoinType.INNER); dialogicalStaffJoin.on(criteriaBuilder.equal(dialogicalStaffJoin.get("staffId"), root.get("dialogicalStaffId"))); predicates.add(criteriaBuilder.like(dialogicalStaffJoin.get("staffName"), wrapLike(dialogicalStaffName))); } if (!StringUtil.isEmpty(getFirstDateTimePossible(startTimeDateTimeScope))) { predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("startTime"), getFirstDateTimePossible(startTimeDateTimeScope))); } if (!StringUtil.isEmpty(getSecondDateTimePossible(startTimeDateTimeScope))) { predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("startTime"), getSecondDateTimePossible(startTimeDateTimeScope))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } ``` ## 五、@Query ``` @Query("from CardSource where isValid=" + YesOrNo.YES) ``` >[danger] 这里写入@Query里面的常量定义，必须用final来修饰； ## 六、构造新对象 构造一个新的对象： 定义一个对象： ``` public class CardFlowPackageOrderObject implements Serializable { private long packageId; private String packageName; private Long salePrice; public CardFlowPackageOrderObject(long packageId, String packageName, Long salePrice) { this.packageId = packageId; this.packageName = packageName; this.salePrice = salePrice; } public long getPackageId() { return packageId; } public void setPackageId(long packageId) { this.packageId = packageId; } public String getPackageName() { return packageName; } public void setPackageName(String packageName) { this.packageName = packageName; } public Long getSalePrice() { return salePrice; } public void setSalePrice(Long salePrice) { this.salePrice = salePrice; } } ``` 构成新对象的dao方法： ``` @Query(value = "select new com.ray.iot.controller.data.meta.CardFlowPackageOrderObject(cfp.packageId, cfp.packageName, cfp.salePrice) from CardFlowPackage cfp where cfp.cardSource=?1 and cfp.packageStatus=?2 and cfp.packageType=?3"// ) List<CardFlowPackageOrderObject> getCardFlowPackageOfInnerAndCardSourceAndPackageStatusAndPackageType(long cardSource, long packageStatus, long packageType); @Query(value = "select new com.ray.iot.controller.data.meta.CardFlowPackageOrderObject(cfp.packageId, cfp.packageName, cafp.agentSalePrice) from CardFlowPackage cfp,CardAgentFlowPackage cafp where cfp.cardSource=?1 and cfp.packageStatus=?2 and cfp.packageType=?3"// + " and cafp.packageId=cfp.packageId and cafp.agentStaffId=?4 and cafp.agentPackageStatus=" + CardAgentPackageStatus.enabled // ) List<CardFlowPackageOrderObject> getCardFlowPackageOfWechatAccessAndCardSourceAndPackageStatusAndPackageType(long cardSource, long packageStatus, long packageType, long wechatTopAgentStaffId); @Query("select new org.ray.framework.system.controller.data.account.sysrelationmodel.SysStaffWithRelationFlag(staff,1) from SysStaff staff where not exists(select 1 from SysStaffRelation sr where sr.staffId=staff.staffId and sr.relationType=?1)") List<SysStaffWithRelationFlag> getStaffListNotRelateByAnyone(long relationType); ```