Java: create Inner Join using criteria

Question

I’m having a hard time finding good examples of how to create a query criteria using Inner Join. I created the query below that would be what I would like to do with the criteria.

SELECT DISTINCT *
FROM DT_DOCUMENT as document 
INNER JOIN DT_TRANSLATION as translation
ON translation.language_id IN(1, 2, 3) 
WHERE document.id = translation.document_id
AND document.title LIKE '%documento%';

This will return all documents with the title "Document" and that are associated with translations with id 1, 2 and 3.

I managed to split this query into 2 different selects using the criteria and are returning the results, but I need the Inner Join to make the necessary filter.

Translation criteria query

CriteriaQuery<Translation> translationQuery = builder.createQuery(Translation.class);
Root<Translation> translation = translationQuery.from(Translation.class);

List<Long> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
Predicate idPredicate = translation.in(ids);
translationQuery.where(idPredicate);
translationQuery.distinct(true);


TypedQuery<Translation> query = 

this.entityManager.createQuery(translationQuery);
query.getResultList();

Returns all translations with id 1, 2 and 3;

Document criteria query

CriteriaQuery<Document> documentQuery = builder.createQuery(Document.class);
Root<Document> document = documentQuery.from(Document.class);

Predicate titlePredicate = builder.like(document.get("title"), "%documento%");
documentQuery.where(titlePredicate);
TypedQuery<Document> query = this.entityManager.createQuery(documentQuery);
query.getResultList();

Returns all documents with the title "Document".

Thank you for any contribution, thank you.

Answer 1

After much research I managed to arrive in this solution unifying the 2 queries and using the Internet:

    //criação da query
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Document> documentQuery = builder.createQuery(Document.class);
    Root<Document> root = documentQuery.from(Document.class);

    //lista de parâmetros para where
    List<Predicate> predicateList = new ArrayList<Predicate>();

    //fazendo o join: passo a referência de translations (list<Translations) que existe no model do documento.
    Join<Document, Translation> documentTranslationJoin = root.join("translations");
   //pego as ids das linguagens associadas as traduções encontradas no join
    Path<Long> translationLanguageId = documentTranslationJoin.get("language");
    //passo os ids das linguagens que quero encontrar traduções
    List<Long> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(3);

    //considera linguagens das traduções
    Predicate predicateTranslationId = builder.isTrue(translationLanguageId.in(ids));
    //passei os ids das linguagens que será o where buscando as traduções que tem os ids das linguagens que desejo ver
    predicateList.add(predicateTranslationId);


    //considera titulo do documento
    Predicate titlePredicate = builder.like(root.get("title"),"%documento%");
    predicateList.add(titlePredicate);

    //where
    Predicate[] predicates = new Predicate[predicateList.size()];
    predicateList.toArray(predicates);
    documentQuery.where(predicates);

    //execução da query com seus parâmetros
    TypedQuery<Document> query = this.entityManager.createQuery(documentQuery);

    query.setFirstResult(0);
    query.setMaxResults(8);

    //resultados da query
    List<Document> documents = query.getResultList();

Answer 2

Following the Oracle documentation Would look like this:

CriteriaQuery<Translation> translationQuery = builder.createQuery(Translation.class);
Metamodel m = em.getMetamodel();
EntityType<Translation> Translation_ = m.entity(Translation.class);

Root<Translation> translation = translationQuery.from(Translation.class);
Join<Translation, Document> documents = translation.join(Translation_.documents);

cq.where(pet.get(Pet_.color).isNull());

List<Long> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
Predicate idPredicate = translation.in(ids);
translationQuery.where(idPredicate);
translationQuery.distinct(true);