Criteriabuilder equal on two columns

Question

When writing queries using Criteriabuilder, how to add an equal condition on two columns? e.g.:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<String> cq = cb.createQuery(String.class);
Root<MyTable> root = cq.from(MyTable.class);
Predicate cond = cb.equal(root.get(MyTable_.columnA), root.get(MyTable_.columnB));

Doing the above caused an exception:

java.lang.IllegalArgumentException: Parameter value [org.hibernate.jpa.criteria.path.SingularAttributePath@236f0ece] did not match expected type [java.lang.String (n/a)]`

you mean 2 fields? because JPQL and JPA Criteria do not deal with columns. Post the rest of the criteria code + stack trace of that exception; which then reveals where the problem is. Is it in the where clause? or in the select clause? The code you post is perfectly correct, as far as it goes. — , Feb 20 '18 at 09:24
as DN1 states: where is your `where`? What do you `select`? How do you execute your `cq`? — pirho, Feb 20 '18 at 16:49

score 3 · Answer 1 · answered Apr 27 '18 at 12:14

You can achieve this by creating 2 separate "equal" predicates and using these 2 to create a new predicate with criteriaBuilder.and(predicate1, predicate2);

Your query should look like this:

//Sample values to use in where clause
String columnATest = "Jimmy";
String columnBTest = "18";

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
//This will return a list of MyTable objects.
CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
Root<MyTable> root = cq.from(MyTable.class);
Predicate cond = null; 

//If both columns should match the test values
cond = cb.and(cb.equal(root.get(MyTable_.columnA), columnATest), cb.equal(root.get(MyTable_.columnB), columnBTest));

//If only one of the 2 columns should match the test values
cond = cb.or(cb.equal(root.get(MyTable_.columnA), columnATest), cb.equal(root.get(MyTable_.columnB), columnBTest));

CriteriaQuery cq = criteriaQuery.select(root).where(cond);
TypedQuery typedQuery = entityManager.createQuery(cq);
List<MyTable> results = (List<MyTable>) typedQuery.getResultList();

Criteriabuilder equal on two columns

1 Answers1