Build a search query with a search criteria in another table using JPA criteria

Question

I need to search states from StateTable based on a given country name (not countryId) in the Country table which should match the like SQL operator using JPA criteria API (countryId is the foreign key in StateTable as the name implies).

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable> criteriaQuery = criteriaBuilder
                                          .createQuery(StateTable.class);
Root<StateTable>root=criteriaQuery.from(StateTable.class);

List<Predicate>predicates=new ArrayList<Predicate>();

predicates.add(criteriaBuilder
          .like(root.<String>get("countryName"), "%"+countryName+"%"));

criteriaQuery.where(predicates.toArray(new Predicate[0]));

entityManager.createQuery(criteriaQuery)
             .setFirstResult(first)
             .setMaxResults(pageSize)
             .getResultList();

How is the following statement to be modified to fulfill the need? (again countryName is available in the Country table and this criteria query is about StateTable).

predicates.add(criteriaBuilder
          .like(root.<String>get("countryName"), "%"+countryName+"%"));

Using JPQL is tedious, since a query needs to be built for multiple search criterion. This is just a demonstration/illustration.

The Country entity:

@Entity
public class Country implements Serializable {
    @Id
    private Long countryId;             //<----------------------
    @Column(name = "country_name")
    private String countryName;
    @Column(name = "country_code")
    private String countryCode;
    @OneToMany(mappedBy = "countryId", fetch = FetchType.LAZY)
    private Set<StateTable> stateTableSet;
}

The StateTable entity:

@Entity
public class StateTable implements Serializable {
    @Id
    private Long stateId;
    @Column(name = "state_name")
    private String stateName;
    @JoinColumn(name = "country_id", referencedColumnName = "country_id")
    @ManyToOne(fetch = FetchType.LAZY)
    private Country countryId;                //<-------------------------------
}

How have you defined the relationship between Country and StateTable? Anyway, the solution seems to require a join (both with JPQL and Criteria API) — perissf, May 14 '13 at 09:48
Yes both of them have a relationship. `StateTable` has a foreign key which references to the primary key of `Country`. — Tiny, May 14 '13 at 10:03
In order to give the correct answer it's necessary to know how the relationship is defined in JPA. Please edit your question and add this information — perissf, May 14 '13 at 10:14

score 5 · Accepted Answer · answered May 14 '13 at 10:38

5

You need to perform a join:

Join<StateTable, Country> country = root.join("countryId");
predicates.add(criteriaBuilder.like(country.<String>get("countryName"), "%"+countryName+"%"));

answered May 14 '13 at 10:38

perissf

15,979
14
80
117

I was refering to [this](http://stackoverflow.com/a/4668015/1391249) answer in between that uses a subquery but I'm unable to understand it because I have used the criteria API quite less frequently (indeed I have to study more on it). Generally, I use JPQL or (HQL with Hibernate) but here it is almost mandatory to use the criteria API. Your answer works. Thank you! – Tiny May 14 '13 at 10:46

pedjaradenkovic · Answer 2 · 2013-05-14T10:49:58.817

1

You can extend your criteria definition like (assuming that there is a property country in the State entity - in other words, assuming you have country_id as a foreign key in state table):

Join<StateTable, Country> country = root.join("country", JoinType.LEFT);

// and change predicate with 
predicates.add(cb.like(country.<String>get("countryName"), "%"+countryName+"%"));

This should do all the work.

edited May 14 '13 at 10:49

answered May 14 '13 at 09:48

pedjaradenkovic

241
1
9

There is a syntax error - `cannot find symbol symbol: method like(Path,String) location: variable country of type Join`, on the `like()` method. – Tiny May 14 '13 at 10:01
It's criteriaBuilder.like(...) – perissf May 14 '13 at 10:13
With `criteriaBuilder.like(...)`, join is completely unused - `Join country = root.join("country", JoinType.LEFT);` that throws an exception - `java.lang.IllegalArgumentException: Unable to resolve attribute [countryName] against path`. – Tiny May 14 '13 at 10:32
I just needed to replace `root` with `country` but I myself couldn't figure that out, sorry. – Tiny May 14 '13 at 10:48

Build a search query with a search criteria in another table using JPA criteria

2 Answers2