Native SQL Query Problem

[redijedi]

For some reason the following code is driving me mad. All 2 posts in the forums that seem to relate do not have a conclusion that helps. Google searches also do not pull much up. Nonetheless, I am determined to understand why this doesn't work.

Works:

 public User getUser(Integer userId) {
 User user = new User();
 //Query q = em.createNamedQuery("UserSelect");
 String sql = "select top 1 user_id, user_fname, user_lname from users";
 //String sql = "select user_id, user_fname, user_lname from users where user_id = ?";
 Query q = em.createNativeQuery(sql, "UserMapping");
 //q.setParameter(1,Integer.valueOf(userId));
 user = (User) q.getResultList().get(0);
 return user;
 }

Doesn't work:

 public User getUser(Integer userId) {
 User user = new User();
 //Query q = em.createNamedQuery("UserSelect");
 //String sql = "select top 1 user_id, user_fname, user_lname from users";
 String sql = "select user_id, user_fname, user_lname from users where user_id = ?";
 Query q = em.createNativeQuery(sql, "UserMapping");
 q.setParameter(1,Integer.valueOf(userId));
 user = (User) q.getResultList().get(0);
 return user;
 }

The trace:

22:12:24,727 INFO [STDOUT] javax.ejb.EJBException: null; CausedByException is:
 Parameter 1 does not exist as a named parameter in [select user_id, user_fname, user_lname from users where user_id = ?]
22:12:24,727 INFO [STDOUT] at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:46)
22:12:24,727 INFO [STDOUT] at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:70)
22:12:24,727 INFO [STDOUT] at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:134)
22:12:24,727 INFO [STDOUT] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
22:12:24,727 INFO [STDOUT] at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:61)
22:12:24,727 INFO [STDOUT] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
22:12:24,727 INFO [STDOUT] at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:39)
...

All the documentation that I've read indicates that this would work:
http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/#queryhql-from
http://www.hibernate.org/hib_docs/entitymanager/reference/en/html/query_native.html#d0e2693

Any and all assistance is really appreciated.

[redijedi]

Freakin-A! As is always the case, I started trying every combination after posting and the winner is:

 public User getUser(Integer userId) {
 User user = new User();
 //Query q = em.createNamedQuery("UserSelect");
 //String sql = "select top 1 user_id, user_fname, user_lname from users";
 String sql = "select user_id, user_fname, user_lname from users where user_id = :1";
 Query q = em.createNativeQuery(sql, "UserMapping");
 q.setParameter(1,Integer.valueOf(userId));
 user = (User) q.getResultList().get(0);
 return user;
 }
[\code]

I hope this helps someone else in my situation. This is the problem with the cutting edge - docs don't get updated as quickly.

[majohnst]

I think when you have parameters in the sql query marked as ?'s, then you refer to those positions by numbers, like an array. The first ? will be position 0, the second position 1, etc. Using the :param notation is like an associative array and uses a string to find the location of the parameter.

[fatskin]

Right you are. See http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html#d0e564

[fatskin]

Oops, sorry- wrong link. Should have been http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html#d0e497

[redijedi]

Sorry to burst the bubble, but in the example above I've tried it all ways and it only works as described.