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));
    }

If the developer lost the Admin rights on Windows

Ok, sometimes you can not define your development environment. You have to use linux, you have to use windows. Or you don't have admin rights on your machine any more.

I created a software collection, what to do in this case BEFORE.

The conditions:

• we have a c:\users\username user folder. we can store here files, we can not run executables.
• we have preinstalled applications in the c:\Program Files
• If you are lucky, you have a dedicated folder for the portable applications, lets call c:\PGM. You can execute the files here, but you don't have permission to modify, create.

 Recommended steps

1. Ensure installed applications are in the c:\Program files
2. Move you portable applications to PGM folder
3. Reconfigure certain applications to reach config, ini, log, plugin etc files.
4. hope

Reconfiguration

Ant

• he has only a bat file which calls java, so you can put it into the c:\users\username folder. just update the ANT_HOME env variable.

Eclipse

• for the plug-ins and features he uses the c:\users\username\.eclipse folder
• check the maven/User settings where the settings.xml and the repository are located

Notepad++

• if you have an old version he want to use the admin right when you restart after a plugin installation
  if you reinstall , he will ask to store plugins to the %APPDATA% folder

Maven

• settings.xml
• .m2 folder for the repo, in default it is in the users folder
• maven just a java, not executable, you can place it under the user folder. dont forget to update the MAVEN_HOME

Total commander

•  no problem with this, the FTP connections and settings are stored in the userfolder\Appdata

Weblogic

• you have to move you Oracle Weblogic domains to your user folders