JPA-HIBERNATE: I understand the conjunctures and disjunctions? That's right?

Question

JPA-HIBERNATE: I understand the conjunctures and disjunctions? That's right?

Navigation

#1 by (1 votes)

0

I took some JPA handouts and read some tutorials on the internet, but I'm still a bit lost with regard to disjunction and conjunction in JPA.

From what I understand the conjunction serves to group conditions with the "AND" of the sql, since the disjunction serves to group conditions with "OR". Am I right? The material I searched for 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 was not successful 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 does not make sense in my understanding of conjunction ... I believe both conditions: "account.Id = 1 and account.title =?" should be out of kinship or even be both under the same parentage.

Did I get it right this conjunction story?

java hibernate jpa

asked by anonymous 19.02.2017 / 23:02

1 answer

How to limit a JDBC connection to only doing "select" queries? Fragment Transaction - Error commit already called

score 1 · Accepted Answer

The SQL operators follow the order of precedence: NOT > AND > OR . That is, expressions are first evaluated with the NOT operator, then AND , and last OR . This way, the query:

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 as:

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

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

As for the parentheses in your query, they are added by Hibernate to a LIKE expression when it is next to the AND operator. I still do not know / discovered why Hibernate does this (if you find out, I'll come back and add the answer).

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

Predicate conjuncao1 = criteriaBuilder.conjunction();

E:

Predicate conjuncao2 = criteriaBuilder.conjunction();

An excerpt from your Criteria with some 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 would like an operation that would not normally take precedence to occur before the others, using the parentheses would be necessary, Hibernate will insert them into 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 ?)

In other words, OR will be executed before AND .