常规动态参数实现:使用 ?数字 的参数类型
@Query(value = "select a.* from user a where " +
"and (?1 is null or a.name = ?1) " +
"and (?2 is null or a.email = ?1) ")
List<User> findUsersByNameAndEmail(String name , String email);
in 条件的动态查询
@Query(value = "select a.* from user a where " +
"and (?1 is null or a.name = ?1) " +
"and (?2 is null or a.email in (?3)) ")
List<User> findUsersByNameAndEmail(String name , String emailValue , List<String> emails);
注意这里不能直接使用List emails 作为?2 is not null 判断
两种情况sql的 if 判断
@Query(value = "select a.* from user a where " +
"and if(?1 is not null , a.name = ?1 , 1=1) " +
"and if(?2 is not null , a.email in (?3) , 1=1) ")
List<User> findUsersByNameAndEmail(String name , String emailValue , List<String> emails);
HQL 中不支持子查询, 如果必须使用请 nativeQuery = true