Native SQL Query Problem
redijedi Oct 27, 2005 1:20 AMFor 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.