can you parameterized where clause in jpql

Question

I'm not super familiar about JPQL, but I know you can parameterize a condition by doing something like:

 em.createQuery("SELECT p FROM Person p WHERE p.firstName = :name)
               .setParameter("name","Bill");

Or:

em.createQuery("SELECT p FROM Person p WHERE p.firstName= ?1)
              .setParameter(1,"Bill");

But can you parameterize something else? Say a criteria by parameterize a WHERE clause. For example can you do something like:

em.createQuery("SELECT p FROM Person p WHERE :criteria = Bill)
                   .setParameter("criteria","p.lastName");

I test the code above but it doesn't work, so my question is: is there a way to parameterize a WHERE clause in JPQL. Or is there any other query language support WHERE clause parameterization? Lots of thanks!

Answer 1

Try this

TypedQuery<Person> query = em.createQuery("SELECT p FROM Person p WHERE p.lastName = :lastName", Person.class);
query.setParameter("lastname", "Bill");

If you wish to add more parameters then

TypedQuery<Person> query = em.createQuery("SELECT p FROM Person p WHERE p.lastName = :lastName AND p.firstName = :firstName", Person.class);
query.setParameter("lastName", "Bill");
query.setParameter("firstName", "Jack");

Once query is created, you can use

query.getResultList();

If you are expecting multiple results

or

query.getSingleResult();

If you expect only one result (Ex. querying by Primary Key or Unique Column)

For more information, refer to TypedQuery

To answer the question from the comments:

a. Either have three query and use the query based on what they selected in the UI

String query;
if (isFirstName) {
    query = "SELECT p FROM Person p WHERE p.firstName = :searchCriteria"
} else if (isMiddleName) {
    query = "SELECT p FROM Person p WHERE p.middleName = :searchCriteria"
} else {
    query = "SELECT p FROM Person p WHERE p.lastName = :searchCriteria"
}
TypedQuery < Person > query = em.createQuery(query, Person.class);
query.setParameter("searchCriteria", "Bill");

b. Change the search:

String searchCriteria = "p.lastName";
TypedQuery<Person> query = em.createQuery(("SELECT p FROM Person p WHERE " + searchCriteria + " = :name"), Person.class);
query.setParameter("name", "Bill");

*For string comparison, you can also use LIKE