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.
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();
While in fact until JPA 2.1 this was not allowed, you can actually use it because the providers will let you provide parameters in that way (which turns out to be a good thing!).
It seems the JPA providers are not conforming to the spec regarding this validation, and I think is just because it didn't make any sense (you can see in 2.1 it is now permitted). "Why would me make it difficult do developers?"
I am also using EclipseLink 2.3.1 and it is working fine.
Just disable Eclipse's JPQL query validation.
If the provider accepts it, you should be fine, otherwise you need to conform to the spec. Very simple. Code will be cleaner and it will conform to recent evaluations of the spec.
Just go to: Preferences > Java Persistence > JPA > Errors/Warnings > Queries and Generators > Invalid or incomplete JPQL queries: and Ignore it
Check this article for details:
Conclusion
Hibernate does not follow the specification on this point but one might guess that the new version of the JPA-spec will allow this behavior as indicated by the draft JSR. JBoss Tools is probably validating the query against the JPQL-grammar which is based on the specification and is therefore showing a validation error.
And this is the resolution:
End remark
After a discussion in out team we decided to keep the current implementation despite the breach of specification. Changing the behavior would mean string concatenation or string substitution to build the query and the current approach is much cleaner. As we see no indications of a shift in persistence provider or application server at this stage we believe the gains of keeping the code are larger than the risks at this point.
Can you try positional parameter and see if it works?
@NamedQuery(name = "updateEmailAddress", query = "UPDATE User u SET u.email = ?1 WHERE u.username = ?2")
//The parameter needs to be passed as
query.setParameter(1, "the_emailaddress");
query.setParameter(2, "the_username");
You must build a query named as follows:
Query query = getEntityManager().createNamedQuery("updateEmailAddress");
query.setParameter("email", "email@test.com");
query.setParameter("username", "emailuser");
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);
Sources:
