Блог

Я очень новичок в использовании построителя критериев, так что это мой первый опыт. Я пытаюсь использовать несколько классов сущностей, поэтому я использую несколько корней для сравнения с материалом. Я чувствую, что мой оператор select ближе к концу первого метода неверен, потому что я не включаю в него оба корня. Тем более, что я использую sl и sld для сравнения данных в предложении predicates where . Нужно ли мне как-то их объединять? Как бы я добавил больше классов в будущем? Должен ли я изменить оператор select, если я хочу выбрать определенные элементы из каждой таблицы для возврата в пользовательский объект? Спасибо.

Извлеченный оператор выбора:

 cq.select(sl);
cq.where(predicates.toArray(new Predicate[] {}));
  

Полный класс:

 @Repository
public class DealerTotalsQueryRepository {

    private static final String START_DATE = "0001-01-01";
    private static final String TERMINATION_DATE = "terminationDate";
    private static final String KEY = "key";
    private static final String DEALER_CODE = "dealerCode";
    private static final String SERVICE_LETTER_NUMBER = "serviceLetterNumber";
    private static final String OPEN_COMPLETE_IND_O = "O";
    private static final String OPEN_COMPLETE_IND_C = "C";
    private static final String COMPLETION_DATE = "completionDate";
    private static final String TERM_IND_NTERM = "nterm";
    private static final String TERM_IND_TERM = "term";
    private static final String SERVICE_LETTER_DATE = "serviceLetterDate";

    @PersistenceContext
    private EntityManager em;

    public List < ServiceLetter > findAllServiceLetters(Map < String, String > searchCriteriaData) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery < ServiceLetter > cq = cb.createQuery(ServiceLetter.class);

        Root < ServiceLetter > sl = cq.from(ServiceLetter.class);
        Root < ServiceLetterDesc > sld = cq.from(ServiceLetterDesc.class);

        List < Predicate > predicates = new ArrayList < > ();

        String marketOrg = searchCriteriaData.get(GlobalConstants.MARKET_ORG);
        String dealerCd = searchCriteriaData.get(GlobalConstants.DEALER_CD);
        String programNo = searchCriteriaData.get(GlobalConstants.PROGRAM_NO);
        String openCompleteInd = searchCriteriaData.get(GlobalConstants.OPEN_COMPLETE_IND);
        String termInd = searchCriteriaData.get(GlobalConstants.TERM_IND);
        String dateFrom = searchCriteriaData.get(GlobalConstants.DATE_FROM);
        String dateTo = searchCriteriaData.get(GlobalConstants.DATE_TO);
        String pinPrefix = searchCriteriaData.get(GlobalConstants.SERIAL_NO_PREFIX);
        String letterType = searchCriteriaData.get(GlobalConstants.LETTER_TYPE);
        java.sql.Date currentDate = new java.sql.Date(System.currentTimeMillis());

        if (marketOrg != null amp;amp; !marketOrg.isEmpty()) {
            //predicates.add(cb.equal(sl.get(""), ""));
        }
        if (dealerCd != null amp;amp; !dealerCd.isEmpty()) {
            predicates.add(cb.equal(sl.get(KEY).get(DEALER_CODE), dealerCd));
        }
        if (programNo != null amp;amp; !programNo.isEmpty()) {
            predicates.add(cb.equal(sl.get(KEY).get(SERVICE_LETTER_NUMBER), programNo));
        } else {
            predicates.add(pipPspIndCodeClause(cb, sld, letterType));
        }

        if (openCompleteInd != null amp;amp; !openCompleteInd.equalsIgnoreCase("all")) {
            if (openCompleteInd.equalsIgnoreCase(OPEN_COMPLETE_IND_O)) {
                predicates.add(cb.equal(sl.get(COMPLETION_DATE), START_DATE));
            } else if (openCompleteInd.equalsIgnoreCase(OPEN_COMPLETE_IND_C)) {
                predicates.add(cb.notEqual(sl.get(COMPLETION_DATE), START_DATE));
            }
        }
        if (termInd != null amp;amp; !termInd.equalsIgnoreCase("all")) {
            if (termInd.equalsIgnoreCase(TERM_IND_NTERM)) {
                Predicate termEToStart = cb.equal(sld.get(TERMINATION_DATE), START_DATE);
                Predicate termGTCurrentDate = cb.greaterThan(sld.get(TERMINATION_DATE), currentDate);
                predicates.add(cb.or(termEToStart, termGTCurrentDate));
            } else if (termInd.equalsIgnoreCase(TERM_IND_TERM)) {
                Predicate termGTStartDate = cb.greaterThan(sld.get(TERMINATION_DATE), START_DATE);
                Predicate termLTOETCurrentDate = cb.lessThanOrEqualTo(sld.get(TERMINATION_DATE), currentDate);
                predicates.add(cb.and(termGTStartDate, termLTOETCurrentDate));
            }
        }
        if (dateFrom != null amp;amp; !dateFrom.isEmpty()) {
            predicates.add(cb.between(sld.get(SERVICE_LETTER_DATE), dateFrom, dateTo));
        }
        if (pinPrefix != null amp;amp; pinPrefix.isEmpty()) {
            /*          buff.append("AND A.SER_NO_PFX IN ("   setupWhereIn(pv, parms.get(ChooseQuery.PIN_PREFIX_KEY), true, 3)   ") "); */

        }
        cq.select(sl);
        cq.where(predicates.toArray(new Predicate[] {}));

        TypedQuery < ServiceLetter > q = em.createQuery(cq);
        return q.getResultList();
    }

    private Predicate pipPspIndCodeClause(CriteriaBuilder cb, Root < ServiceLetterDesc > sld, String letterType) {

        Predicate pipPspIndCode = null;
        Predicate predTemp1 = null;
        Predicate predTemp2 = null;
        Predicate predTemp3 = null;
        Predicate predTemp4 = null;

        List < String > serviceLetterNumberList = Arrays.asList(new String[] {
            "956151",
            "956170"
        });
        Expression < String > serviceLetterNumberExpression = sld.get(SERVICE_LETTER_NUMBER);

        if (letterType != null amp;amp; !letterType.equalsIgnoreCase("all")) {
            switch (letterType) {
                case "pip":
                    pipPspIndCode = cb.like(sld.get(SERVICE_LETTER_NUMBER), "94%");
                    break;
                case "pipsaf":
                    predTemp1 = cb.like(sld.get(SERVICE_LETTER_NUMBER), "940%");
                    predTemp2 = cb.like(sld.get(SERVICE_LETTER_NUMBER), "941%");
                    pipPspIndCode = cb.or(predTemp1, predTemp2);
                    break;
                case "pippri":
                    pipPspIndCode = cb.like(sld.get(SERVICE_LETTER_NUMBER), "943%");
                    break;
                case "psp":
                    pipPspIndCode = cb.like(sld.get(SERVICE_LETTER_NUMBER), "95%");
                    break;
                case "pspbaf":
                    predTemp1 = cb.like(sld.get(SERVICE_LETTER_NUMBER), "954%");
                    predTemp2 = cb.like(sld.get(SERVICE_LETTER_NUMBER), "958%");
                    predTemp3 = cb.or(predTemp1, predTemp2);

                    predTemp4 = cb.between(sld.get(SERVICE_LETTER_NUMBER), "9590016", "9590109");
                    pipPspIndCode = cb.or(predTemp3, predTemp4);
                    break;
                case "pspaf":
                    predTemp1 = cb.like(sld.get(SERVICE_LETTER_NUMBER), "955%");
                    predTemp2 = cb.between(sld.get(SERVICE_LETTER_NUMBER), "9560000", "9560027");
                    predTemp3 = cb.or(predTemp1, predTemp2);

                    predTemp4 = serviceLetterNumberExpression.in(serviceLetterNumberList);
                    pipPspIndCode = cb.or(predTemp3, predTemp4);

                    //predTemp4 = cb.in(sld.get(SERVICE_LETTER_NUMBER)).value(serviceLetterNumberList);
                    break;
                case "pspcn9":
                    pipPspIndCode = cb.between(sld.get(SERVICE_LETTER_NUMBER), "9590110", "9599999");
                    break;
                case "pspcn6":
                    predTemp1 = cb.between(sld.get(SERVICE_LETTER_NUMBER), "9590110", "9599999");
                    predTemp2 = serviceLetterNumberExpression.in(serviceLetterNumberList).not();
                    pipPspIndCode = cb.or(predTemp1, predTemp2);
                    break;
            }
        }
        return pipPspIndCode;
    }
}