CODEWITHSUNDEEP
javasqlhibernatejpapredicates
Oleh Linnyk
Oleh Linnyk

Reputation: 59

Map SQL on Predicates

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!

Upvotes: 0

Views: 857

Answers (1)

Frank
Frank

Reputation: 881

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>

Upvotes: 1

Related Questions