Searching with subqueries and get last version using CriteriaBuilder

I found a complex solution I share the details.

The FindAll()

You can put parameter using the Spring DAO findAll() method. So we have to create a Specification<T> object, which contain the conditions for the filtering. Of course we receive T entities, but what happen If we need to filter by children records, or these child tables are not linked in the T entity, or we also store different versions in the same table.

The desired SELECT statement

So I have TDocument entity, and a TDocuTranslationReq where we have versions. The link between the tables are the docuKy, it is a PK in the TDocument. We did not declared this relation in TDocument entity.
The TDomain table a simple dictionary table, we store there for example the status records.

The goal is to receive that TDocument records where the TDocuTranslatioReqLanguage status is a given status. It is a search functionality on the UI.

SELECT *
  FROM T_DOCUMENT DOC
 WHERE EXISTS
 (SELECT null
          FROM T_DOCU_TRANSREQ TR
         INNER JOIN T_DOCU_TRANSREQ_LAN TR_LAN
            ON TR.DOTR_KY = TR_LAN.DOTR_KY
         CROSS JOIN T_DOMAIN STAT
         WHERE TR_LAN.STATUS_DOMA_KY = STAT.DOMA_KY
           AND DOC.DOCU_KY = TR.DOCU_KY
           AND STAT.CODE_CD = 'EXECUTED'
           AND TR.DOTR_KY =
               (SELECT MAX(TR_LAST.DOTR_KY) FROM T_DOCU_TRANSREQ TR_LAST WHERE TR_LAST.DOCU_KY = TR.DOCU_KY))

 

toPredicate()

You can overwrite the toPredicate() function to get the right Specification<TDocument>.

• we need a subquery to get all the linked TranslationRequest records
• we have to filter this result with the dedicated status parameter in the TranslationRequestLanguages
• we need a subquery to get the last version of the TranslationRequests

Solution

    public static Specification<TDocument> translationRequestLanguageInStatusExists(final String status) {
        return new Specification<TDocument>() {
            @Override
            public Predicate toPredicate(Root<TDocument> doc, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Subquery<TDocuTranslationRequest> subQuery = query.subquery(TDocuTranslationRequest.class);
                Root<TDocuTranslationRequest> tr = subQuery.from(TDocuTranslationRequest.class);

                ArrayList<Predicate> subQueryElements = new ArrayList<Predicate>();
                subQueryElements.add(cb.equal(doc.get(TDocument_.docuKy), tr.get(TDocuTranslationRequest_.tDocument).get(TDocument_.docuKy)));
                subQueryElements.add(cb.equal(tr.join(TDocuTranslationRequest_.tTransReqLanguages).get(TDocuTranslationRequestLanguage_.status).get(TDomain_.codeCd), status));

                Subquery<Long> maxQuery = subQuery.subquery(Long.class);
                Root<TDocuTranslationRequest> trMax = maxQuery.from(TDocuTranslationRequest.class);
                maxQuery.select(cb.greatest(trMax.get(TDocuTranslationRequest_.dotrKy)));
                ArrayList<Predicate> maxQueryElements = new ArrayList<Predicate>();
                maxQueryElements.add(cb.equal(trMax.get(TDocuTranslationRequest_.tDocument).get(TDocument_.docuKy), tr.get(TDocuTranslationRequest_.tDocument).get(TDocument_.docuKy)));
                maxQuery.where(maxQueryElements.toArray(new Predicate[] {}));
                subQueryElements.add(cb.equal(tr.get(TDocuTranslationRequest_.dotrKy), maxQuery));

                subQuery.select(tr);
                subQuery.where(subQueryElements.toArray(new Predicate[] {}));
                return cb.exists(subQuery);
            }
        };
    }

• subQuery is a query 
• on TDocuTranlationRequest entities (from clause), 
• the conditions are collected in the subQueryelements list (where clause).
• subQueryelements list contains all the conditions:
• docuKy equijoin
• status equijoin
• last version equijoin
• maxQuery is a subquery of the defined subQuery. 
• It will return the primary key of the last version, the type is java Long and it . maxQuery.select(cb.greatest(trMax.get(TDocuTranslationRequest_.dotrKy))) (select clause)
• from TDocuTranslationRequest (from clause)
• The conditions are in the maxQueryelements list (where clause)
• maxQueryElements list contains the conditions:
• equijoin on docuKy

Integration

the integration of this seach parameter is additional. Of course there are other search parameters.
specs = Specifications.where(specs).and(translationRequestLanguageInStatusExists(criteriaDTO.getTranslationRequestLanguageStatus().name()));
List<TDocument> result = documentDAO.findAll(specs);

Test

Using dbUnit tests are very easy. I created sample data which are loaded to memory database.

    @Test
    public void shouldFindByRequestLangStatusByMax() throws Exception {
        // given
        Specifications<TDocument> spec = Specifications.where(translationRequestLanguageInStatusExists(STATUS));
        // when
        List<TDocument> result = documentDAO.findAll(spec);
        // then
        assertThat(result.size(), is(1));
        TDocument doc = result.iterator().next();
        assertThat(doc.getDocuKy(), is(10L));
    }