How to Use a Dynamic Parameter in a in Clause of a JPA Named Query

How to use a dynamic parameter in a IN clause of a JPA named query?

JPA support the use of a collection as a list literal parameter only in JPQL queries, not in native queries. Some JPA providers support it as a proprietary feature, but it's not part of the JPA specification (see https://stackoverflow.com/a/3145275/1285097).

Named parameters in native queries also aren't part of the JPA specification. Their behavior depends on the persistence provider and/or the JDBC driver.

Hibernate with the JDBC driver for Oracle support both of these features.

List<String> selectedValues = Arrays.asList("STRING1", "STRING2");
final String parameterizedQuery = "select * from SOMETABLE where SOMEFIELD in (:selectedValues)";
return em.createNativeQuery(parameterizedQuery)
         .setParameter("selectedValues", selectedValues)
         .getResultList();

JPA passing list to IN clause in named native query

A list is not a valid parameter for a native SQL query, as it cannot be bound in JDBC. You need to have a parameter for each argument in the list.

where u.user_id in (?id1, ?id2)

This is supported through JPQL, but not SQL, so you could use JPQL instead of a native query.

Some JPA providers may support this, so you may want to log a bug with your provider.

Named query with input parameter

In JPA 2.0 and below, parameters are not allowed in the set clause of a named query; only literals. This limitation is lifted if you are using JPA 2.1.

From what I can gather, you are not using JPA 2.1. Hence, I'll give you a couple of ways to sidestep this limitation.

Option 1:
Use the createQuery method and pass a dynamically generated string to the method.

    String queryString = generateQueryString(email, username);
    entityManager.createQuery(queryString).executeUpdate();

Option 2:
Update the associated entity and merge.

    List<User> result = entityManager.createQuery('select u from user u where 
    u.username = :username').setParameter('username', username).getResultList(); 
    for (User user : result) {
        user.setEmail(email);
        entityManager.merge(user);
    }

Option 3:
Create the query using HQL not JPQL. I haven't tested this nor do I recommend it because you are going behind the entity manager's back.

    Query q = sessionFactory.getCurrentSession().createNamedQuery('updateEmailAddress');
    q.setParameter('email', email);
    q.setParameter('username', username);
    q.executeUpdate();

How to create a dynamic WHERE clause for all queries in Spring Data JPA?

Spring Data (I assume you are using Spring Data based on your post tags) doesn't support query augmentation (at time of writing). There is a long standing request for that feature here that also describes some alternatives that others have tried.

Good luck!

How to create a dynamic WHERE clause for a query using an array list

Either check if JDBC Template from Spring handle that for you using a syntax which could be something like (from the doc, I don't think it does)

SELECT question_id FROM question WHERE category in (?...)

Or write your own query with the problems that may arise:

List<Object> parameters = new ArrayList<>(categories.size());
StringBuilder sb = new StringBuilde("SELECT question_id FROM question WHERE 1=1");
if (!categories.isEmpty()) {
  if (categories.size() == 1) {
    sb.append(" and category = ?");
  } else {
    sb.append(" and category in ");
    sb.append(categories.stream()
                        .map(ignored -> "?")
                        .collect(joining(", ", "(", ")")));                      
    sb.append(")"); 
  }
  parameters.addAll(categories);
}
Object[] paramArray = parameters.toArray();
jdbcTemplate.query(sb.toString(), stringMapper, paramArray);

Notes:

1. some security/quality tool may report SQL issues because you are writing a dynamic SQL.
2. Oracle put a limit on 1000 elements per IN. You would have to partition categories per group of 1000 (or less).
3. I used a stream() in a more or less strange fashion in order to generate the "?". If you use commons-lang3, you can replace it by "(" + StringUtils.repeat("?", ", ", categories.size()) + ")" (the example in the javadoc was probably done with this kind of use).
4. if you only have category as single criteria, you may probably remove the 1=1 as well as the and.

---

Related Topics