I have some SQL
select id, name from table where id = '1' and name = 'A'
or id = '2' and name = 'B'
or id = '3' and name = 'C';
Result of SQL satisfies me, but anyway I have to map that SQL to JPA's Predicates.
What I have now
List<Predicate> predicates = new ArrayList<>();
Predicate id1 = criteriaBuilder.like(root.get(id), "%" + "1" + "%");
predicates.add(criteriaBuilder.and(id1));
Predicate nameA = criteriaBuilder.like(root.get(name), "%" + "A" + "%");
predicates.add(criteriaBuilder.and(nameA));
Predicate id2 = criteriaBuilder.like(root.get(id), "%" + "2" + "%");
predicates.add(criteriaBuilder.and(id2));
Predicate name2 = criteriaBuilder.like(root.get(name), "%" + "B" + "%");
predicates.add(criteriaBuilder.and(name2));
Predicate id3 = criteriaBuilder.like(root.get(id), "%" + "3" + "%");
predicates.add(criteriaBuilder.and(id3));
Predicate name3 = criteriaBuilder.like(root.get(name), "%" + "C" + "%");
predicates.add(criteriaBuilder.and(name3));
query.where(criteriaBuilder.or(predicates.toArray(new Predicate[predicates.size()])));
return entityManager.createQuery(query).getResultList();
Query of Predicates return also some redundant data, except of SQL query.
Please correct my Predicate condition in order to get the same result as from sql query.
Many thanks!
I would write something likethat :
Predicate id1 = criteriaBuilder.like(root.get(id), "%" + "1" + "%");
Predicate nameA = criteriaBuilder.like(root.get(name), "%" + "A" + "%");
Predicate id1nameA = criteriaBuilder.and(id1,nameA);
predicates.add(criteriaBuilder.or(id1nameA));
//...continue for the others
Anyway using like its different than = so it can make such issue .
If your jpa implementation is Hibernate you can show sql to console by adding the follow lines of code in your persistence.xml
<properties>
<property name="hibernate.dialect" value="..somethinginhere.."/>
<property name="hibernate.show_sql" value="true"/>
</properties>