Named query with input parameter

Question

I am learning JPA with Hibernate, using maven as well. My problem is How can I use input parameters with UPDATE and SET clause in named query ?

 @NamedQuery(name = "updateEmailAddress", query = "Update User u set u.email = :email where u.username = :username")

It gives me an error that a parameter can only be used in the WHERE or HAVING clause. I referred several article but still cannot find the proper solution.

Answer 1

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();