我希望创建一个Spring Data存储库接口,它需要两个参数。有没有办法使它具有以下行为?
MyObject findByParameterOneAndParameterTwo( String parameterOne, String parameterTwo);
如果两个参数都有值,我希望它能够正常工作并对这两个值执行“AND”操作。如果例如第二个参数为null,则仅通过ParameterOne进行搜索。
有什么建议吗?
我希望创建一个Spring Data存储库接口,它需要两个参数。有没有办法使它具有以下行为?
MyObject findByParameterOneAndParameterTwo( String parameterOne, String parameterTwo);
如果两个参数都有值,我希望它能够正常工作并对这两个值执行“AND”操作。我不确定是否可以通过仓库方法的命名实现,但是你可以像这样使用@Query
(:parameterOne is null or parameter1 = :parameterOne) and (:parameterTwo is null or parameter2 = :parameterTwo)
这里缺少的一个解决方案是 Spring Data JPA 的 Query By Example 特性,使用 ExampleMatcher#ignoreNullValues
即可解决此问题。自定义查询和查询构建器不是必要的。
这是Spring Data查询:
ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreNullValues();
Example<MyObject> exampleQuery = Example.of(new MyObject("foo", null), matcher);
List<MyObject> results = repository.findAll(exampleQuery);
生成一个类似以下查询的查询:
select *
from myObject
where parameter1 = "foo"
下面是:
ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreNullValues();
Example<MyObject> exampleQuery = Example.of(new MyObject("foo", "bar"), matcher);
List<MyObject> results = repository.findAll(exampleQuery);
产量:
select *
from myObject
where parameter1 = "foo"
and parameter2 = "bar"
非常酷!
注意: 你需要对你的Repository
接口进行一项操作,即添加QueryByExample
接口。你可以通过直接扩展QueryByExample
接口或通过JpaRepository
隐式实现来完成此操作:
public interface MyObjectRepository extends JpaRepository<MyObject, Long> {}
#withIgnoreNullValues
指定的 null
值。可以使用 withIncludeNullValues
和/或 NullHandler
来修改并对该行为进行更精细的控制。 - Dovmo以下是实现该目标的方法:
@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name) and (:email is null"
+ " or c.email = :email)")
List<Customer> findCustomerByNameAndEmail(@Param("name") String name, @Param("email") String email);
nativeQuery
。 - Serga试试这个Kolobok
@FindWithOptionalParams
Iterable<MyObject> findByParameterOneAndParameterTwo( String parameterOne, String parameterTwo);
@Query("SELECT c FROM Customer c WHERE (:name is null or c.name = :name)")
@Query(nativeQuery = true, "SELECT id, name FROM Customer WHERE (false = :nameOn OR name = :name)")
List<Entity> findAll(@Param(value = "nameOn") Boolean nameOn, @Param(value = "name ") String name);
@Repository
public interface NotificationRepository extends JpaRepository<Notification,
Long>, NotificationRepositoryCustom {
}
public interface NotificationRepositoryCustom {
List<Notification> findNotificationByCustomerIdAndRecipientAndNotificationAckStatusAndNotificationRequestChannel
(Long customerId, String userId, List<String> status, List<String> channels);
}
NotificationRepositoryCustom的实现
public class NotificationRepositoryCustomImpl implements NotificationRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<Notification> findNotificationByCustomerIdAndRecipientAndNotificationAckStatusAndNotificationRequestChannel(Long customerId, String userId, List<String> status, List<String> channels) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Notification> query = cb.createQuery(Notification.class);
Root<Notification> notification = query.from(Notification.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (userId != null)
predicates.add(cb.equal(notification.get("recipient"), userId));
if (customerId != null)
predicates.add(cb.equal(notification.get("customerId"), customerId));
if (CollectionUtils.isNotEmpty(status))
predicates.add(notification.get("notificationAckStatus").get("ackStatusCode").in(status));
if (CollectionUtils.isNotEmpty(channels))
predicates.add(notification.get("notificationRequestChannel").get("notificationChannel").get("channelCode").in(channels));
if (!predicates.isEmpty()) {
query
.select(notification)
.where(cb.and(
predicates.toArray(new Predicate[predicates.size()])));
}
return entityManager.createQuery(query).getResultList();
}
}
@Query("SELECT t FROM Test t WHERE (:parameterOne IS NULL) OR (:parameterOne = '')");
使用Spring JPA Data 规范 是解决这个问题的一个非常好的方法。
这个解决方案适用于所有数据类型,并允许所有DB函数(大于/小于/类似/等于/或/不等等)。
我个人认为,将业务逻辑/条件构建器封装到这些方法中可以使服务代码更加可读,特别是如果您给您的规范方法命名良好。您甚至可以拥有私有方法,供公共方法使用,以使您的规范代码更易读!
对于OP的示例,请创建一个包含这些方法的类。
public static Specification<Entity> equalsParameterOne(String parameterOne){
//If the specification is null it will be excluded from the SQL and ignored
if(parameterOne == null || parameterOne.length = 0) return null;
return (root, query, cb) -> cb.equal(root.get("fieldOne"), parameterOne);
}
public static Specification<Entity> equalsParameterTwo(String parameterTwo){
//If the specification is null it will be excluded from the SQL and ignored
if(parameterTwo== null || parameterTwo.length = 0) return null;
return (root, query, cb) -> cb.equal(root.get("fieldTwo"), parameterTwo);
}
然后在您使用jpaRepo的服务代码中,您可以像这样使用findAll()。
//Be careful with your parenthesis
Specification<Entity> customDynamicSpecs =
Specification
.where(equalsParameterOne(criteria.getParamOne()))
.and(equalsParameterTwo(criteria.getParamTwo()));
//.or().not()
//... as many as you want.
//findAll() can take Pageable or Sort as 2nd argument for extra functionality.
repo.findAll(customDynamicSpecs);
试试这个,
@Query(value = "SELECT pr FROM ABCTable pr " +
"WHERE((pr.parameterOne = :parameterOne) or (pr.parameterOne = null and :parameterOne = null)) and
((pr.parameterTwo = :parameterTwo) or (pr.parameterTwo = null and :parameterTwo = null)) ")
List<PaymentRequest> getSomething (@Param("parameterOne") String parameterOne,
@Param("parameterTwo") String parameterTwo);
@Query
和QueryDSL。SQL和JPA支持COALESCE
函数,该函数可用于解决某些参数可能具有NULL
值的情况。应该可以使用@Query("SELECT e FROM MyObject e WHERE COALESCE(e.parameterOne, ?1) = ?1 AND COALESCE(e.parameterOne, ?2) = ?2")
来解决问题。 - manishCOALESCE(?1, e.parameterOne) = e.parameterOne
是你的意图。 - Blank