JPA-HIBERNATE : I understood the conjunctions and disjunctions ? Is that right?

Question

I took some JPA booklets and read some tutorials on the internet, but I’m still a little lost with regards to disjunction and Conjunction in JPA.

From what I understood the conjunction serves to group conditions with the sql "AND", already the disjunction serves to group conditions with "OR". Am I right? The material I researched seems very confusing.

I then tried to make the criteria generate queries with the following pattern:

WHERE (condicao1 = valor1 AND condicao2 = valor2) or (condicao3=valor3 and condicao4=valor4)

However I did not succeed with my implementation:

 EntityManager em = JPAUtil.getEntityManager();

// Instanciar o BUILDER de criteria
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

//Cria uma criteria query que trabalha com Conta
CriteriaQuery<Conta> cquery = criteriaBuilder.createQuery(Conta.class);

//Obter RAIZ da query que consulta da entidade CONTA
Root<Conta> root = cquery.from(Conta.class);

Path<String> titularPath = root.<String>get("titular");
Path<Integer> idPath = root.<Integer>get("id");
Path<String> numeroPath = root.<String>get("numero");
Path<String> bancoPath = root.<String>get("banco");

Predicate titularIgual = criteriaBuilder.like(titularPath, "M%");
Predicate idIgual = criteriaBuilder.equal(idPath, 1);
Predicate idDiferente = criteriaBuilder.equal(idPath, 500);     

//Conjuncao1 seria (idDiferente AND titularIgual) ???
Predicate conjuncao1 = criteriaBuilder.conjunction();
conjuncao1 = criteriaBuilder.and(conjuncao1,idDiferente);
conjuncao1 = criteriaBuilder.and(conjuncao1,titularIgual);

//Conjunao2 seria (idIgual AND titularIgual) ???
Predicate conjuncao2 = criteriaBuilder.conjunction();
conjuncao2 = criteriaBuilder.and(conjuncao2,idIgual);
conjuncao2 = criteriaBuilder.and(conjuncao2,titularIgual);

//Se minha logica estiver correta entao a disjuncao das duas conjuncoes deveria ser
// (idDiferente AND titularIgual) or (idIgual AND titularIgual) Não?
Predicate disjuncao = criteriaBuilder.disjunction();
disjuncao = criteriaBuilder.or(conjuncao1,conjuncao2);  


cquery.where(disjuncao);
TypedQuery<Conta> tQuery = em.createQuery(cquery);
List<Conta> contas = tQuery.getResultList();

I did not understand the fact that Hibernate generated the query below:

where
1=1 
and conta0_.id=500 
and (
conta0_.titular like ?
) 
or 1=1 
and conta0_.id=1 
and (
conta0_.titular like ?
)

This makes no sense in my understanding of Conjunction... I believe both conditions : "account. Id = 1 and account.holder = ?" should be out of parentheses or even both under the same parentage.

I got the whole Conjunction thing right?

Answer 1

SQL operators follow the following order of precedence: NOT > AND > OR. That is, expressions with the operator are first evaluated NOT, next AND and finally OR . Thus, the consultation:

WHERE 
1=1 
AND conta.id = 500 
AND (conta.titular like ?)
OR
1=1 
AND conta.id = 1 
AND (conta.titular like ?)

It’s the same thing:

WHERE (
1=1 
AND conta.id = 500 
AND (conta.titular like ?))
OR (
1=1 
AND conta.id = 1 
AND (conta.titular like ?))

Therefore, parentheses are unnecessary and Hibernate does not add them to the query.

As for the parentheses present in your query, they are added by Hibernate to an expression LIKE when she’s with the operator AND. I still don’t know/figured out why Hibernate does it (if I find out, I’ll come back and add the answer).

About the expressions 1=1, they are added to your query in the following snippets:

Predicate conjuncao1 = criteriaBuilder.conjunction();

And:

Predicate conjuncao2 = criteriaBuilder.conjunction();

An excerpt from his Criteria with a few comments:

Predicate titularIgual = criteriaBuilder.like(titularPath, "M%");   //conta0_.titular like ?
Predicate idIgual = criteriaBuilder.equal(idPath, 1);               //conta0_.id=1 
Predicate idDiferente = criteriaBuilder.equal(idPath, 500);         //conta0_.id=500

Predicate conjuncao1 = criteriaBuilder.conjunction();               //1=1
conjuncao1 = criteriaBuilder.and(conjuncao1,idDiferente);           //1=1 AND conta0_.id=500
conjuncao1 = criteriaBuilder.and(conjuncao1,titularIgual);          //1=1 AND conta0_.id=500 AND (conta0_.titular like ?)

Predicate conjuncao2 = criteriaBuilder.conjunction();               //1=1
conjuncao2 = criteriaBuilder.and(conjuncao2,idIgual);               //1=1 AND conta0_.id=1 
conjuncao2 = criteriaBuilder.and(conjuncao2,titularIgual);          //1=1 AND conta0_.id=1 AND (conta0_.titular like ?)

Predicate disjuncao = criteriaBuilder.disjunction();                //0=1
                                                                    //Como você não a adiciona na criteriaBuilder.or abaixo
                                                                    //essa expressão não aparece na consulta

disjuncao = criteriaBuilder.or(conjuncao1,conjuncao2);              //1=1 AND conta0_.id=500 AND (conta0_.titular like ?) OR 
                                                                    //1=1 AND conta0_.id=1 AND (conta0_.titular like ?)

Edit:

If you want an operation that normally would not take precedence before the others and, for this, the use of parentheses would be necessary, Hibernate will insert them in the query. Example:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Conta> cquery = criteriaBuilder.createQuery(Conta.class);
Root<Conta> root = cquery.from(Conta.class);

Path<String> titularPath = root.<String>get("titular");
Path<Integer> idPath = root.<Integer>get("id");
Path<String> numeroPath = root.<String>get("numero");

Predicate titularIgual = criteriaBuilder.like(titularPath, "M%");
Predicate idIgual = criteriaBuilder.equal(idPath, 1);
Predicate idDiferente = criteriaBuilder.equal(idPath, 500);     ;  

cquery.where(criteriaBuilder.and(idDiferente, criteriaBuilder.or(idIgual, titularIgual)));

List<Conta> contas = em.createQuery(cquery).getResultList();

SQL generated:

SELECT conta0_.id AS id1_0_,
       conta0_.titular AS titular2_0_
FROM Conta conta0_
WHERE conta0_.id=500
  AND (conta0_.id=1
       OR conta0_.titular LIKE ?)

That is, in this case the OR will be executed before the AND.