如何使用条件api转换以下sql查询?

问题描述

我有一个已经编写在扩展JpaRepository的存储库中的查询.但是我需要在这里添加逻辑运算符.

I have a query which is already written in a Repository extending JpaRepository. But I need to add logical operator here.

select ac from Accounts ac where ac.userId = :userId and ac.accountId = :accountID

当客户端提供userId或accountId时，要求正在获取帐户详细信息.

Requirement is getting account details when either userId or accountId is provided by the client.

public interface GoalPlanRepository extends JpaRepository<GoalPlan, String>{

    @Query("select ac from Accounts ac where ac.userId = :accountID ")
    public List<Accounts> getCalculateRecurringAmount(@Param("accountID") String accountID, @Param("userId") String userId);

这是我的存储库.我正在GoalPlanDaoImplementation中实现此方法

This is My repository. I am implementing this method in goalPlanDaoImplementation

    public List<Accounts> getCalculateRecurringAmount(String accountID) {


    //      CriteriaBuilder cb = em.getCriteriaBuilder();
    //        CriteriaQuery<Accounts> cq = cb.createQuery(Accounts.class);
    //
    //        Root<Accounts> acc = cq.from(Accounts.class);
    //
    //        Predicate accountIDPredicate = null;
    //        Predicate userIdPredicate = null;
    //
    //        if(accountID != null) {
    //        accountIDPredicate = cb.equal(acc.get("accountID"), accountID);
    //        }
    //
    //        if(userId != null) {
    //        userIdPredicate = cb.equal(acc.get("userId"), userId);
    //        }
    //
    //
    //        cq.where(accountIDPredicate, userIdPredicate);
    //
    //        TypedQuery<Accounts> query = em.createQuery(cq);

              List<Accounts> goalPlan = null;
              goalPlan = goalPlanRepository.getCalculateRecurringAmount(accountID);
               return goalPlan;
            //return query.getResultList();




        }

评论部分是我尝试做的.提前致谢. :)

commented section is what I tried doing. Thanks in advance. :)

推荐答案

我不确定我是否理解您的正确，但这也许会有所帮助:

I'm not completly sure if I understood you correct, but maybe this will help:

final ArrayList<Predicate> predicates = new ArrayList<Predicate>();
if(accountID != null)
    predicates.add(cb.equal(acc.get("accountID"), accountID));
}
if(userId != null) {
    predicates.add(cb.equal(acc.get("userId"), userId));
}

switch(predicates.size()){
case 0:
    return null;
    //or maybe just continue to return all accounts...
case 1:
    cq.where(predicates.get(0));
    break;
default:
    cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
}

这篇关于如何使用条件api转换以下sql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！