1 Reply Latest reply on Mar 12, 2009 7:53 AM by jaikiran pai

    @SqlResultSetMapping and column not found

    Barry Newbie

      I am using JPA\Hibernate for persistance. I have created a native query that returns scalar values. The scalar values all belong to the same entity but not all of the scalar values are returned from the query. I am using a @SqlResultSetMapping with @EntityResult to map the columns returned from the query to the properties in my entity. However, when I call getResultList, I get an error saying that a column is not found.

      What I can't figure out is that the column being complained about is not in my @SqlResultSetMapping and not in the query. Even if I change my query to "select *", I still see the column not found error. Is this a bug or am I missing something? I'm pretty sure I am doing everything right. Below lists all of the information ... the entity, query, SqlResultSetMapping, and exception.

      @Entity
      @Table(name="users")
      @SqlResultSetMapping(
       name="UserSearchResults",
       entities={
       @EntityResult(
       entityClass=User.class,
       fields={
       @FieldResult(name="id", column="id"),
       @FieldResult(name="email", column="email"),
       @FieldResult(name="firstName", column="firstName"),
       @FieldResult(name="lastName", column="lastName"),
       @FieldResult(name="status", column="status"),
       @FieldResult(name="dateCreated", column="dateCreated"),
       @FieldResult(name="site", column="site")
       }
       )
       }
      )
      public class User implements Serializable
      {
       private int id;
       private String email;
       private String password;
       private int site;
       private StatusType status;
      
       private String firstName;
       private String lastName;
      
       private String securityQuestion;
       private String securityAnswer;
      
       private Date dateCreated;
      
       private Set<Role> roles;
      
       private Profile profile;
      
       public User()
       {
       roles = new HashSet<Role>();
       status = StatusType.Enabled;
       }
      
       @Id
       @GeneratedValue(strategy=GenerationType.AUTO)
       public int getId()
       {
       return id;
       }
      
       public void setId(int id)
       {
       this.id = id;
       }
      
       public int getSite() {
       return site;
       }
      
       public void setSite(int site) {
       this.site = site;
       }
      
       @Column(name="password", nullable=false)
       public String getPassword()
       {
       return password;
       }
      
       public void setPassword(String password)
       {
       this.password = password;
       }
      
       @ManyToMany(cascade={CascadeType.REMOVE, CascadeType.REFRESH, CascadeType.MERGE}, fetch=FetchType.EAGER)
       public Set<Role> getRoles()
       {
       return roles;
       }
      
       public void setRoles(Set<Role> roles)
       {
       this.roles = roles;
       }
      
       @Column(name="email", unique=true, nullable=false)
       public String getEmail() {
       return email;
       }
      
       public void setEmail(String email) {
       this.email = email;
       }
      
       @Column(nullable=false)
       public String getSecurityAnswer() {
       return securityAnswer;
       }
      
       public void setSecurityAnswer(String securityAnswer) {
       this.securityAnswer = securityAnswer;
       }
      
       @Column(nullable=false)
       public String getSecurityQuestion() {
       return securityQuestion;
       }
      
       public void setSecurityQuestion(String securityQuestion) {
       this.securityQuestion = securityQuestion;
       }
      
       @Column(nullable=false)
       public Date getDateCreated() {
       return dateCreated;
       }
      
       public void setDateCreated(Date dateCreated) {
       this.dateCreated = dateCreated;
       }
      
       @OneToOne(cascade={CascadeType.ALL}, optional=false, fetch=FetchType.EAGER)
       public Profile getProfile() {
       return profile;
       }
      
       public void setProfile(Profile profile) {
       this.profile = profile;
       }
      
       public String getFirstName() {
       return firstName;
       }
      
       public void setFirstName(String firstName) {
       this.firstName = firstName;
       }
      
       public String getLastName() {
       return lastName;
       }
      
       public void setLastName(String lastName) {
       this.lastName = lastName;
       }
      
       public StatusType getStatus() {
       return status;
       }
      
       public void setStatus(StatusType status) {
       this.status = status;
       }
      }
      


      // this is beginning of the query. There is no need to show the whole thing as there are no subqueries.
      StringBuilder query = new StringBuilder();
       query.append("select id, dateCreated, site, email, firstName, lastName, status from users where site=");
      
      Query query = manager.createNativeQuery(query.toString(), "UserSearchResults");
       return query.getResultList();
      




      2009-03-10 21:09:28,061 ERROR [com.mrm.security.business.SecurityService] (http-127.0.0.1-80-1) There was an error searching users
      javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
       at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
       at com.mrm.security.business.UserSearchHelper.search(UserSearchHelper.java:36)
       at com.mrm.security.business.SecurityService.searchUsers(SecurityService.java:325)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeTarget(MethodInvocation.java:122)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:111)
       at org.jboss.ejb3.EJBContainerInvocationWrapper.invokeNext(EJBContainerInvocationWrapper.java:69)
       at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.invoke(InterceptorSequencer.java:73)
       at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.aroundInvoke(InterceptorSequencer.java:59)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.aop.advice.PerJoinpointAdvice.invoke(PerJoinpointAdvice.java:174)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.fillMethod(InvocationContextInterceptor.java:72)
       at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_fillMethod_8522252.invoke(InvocationContextInterceptor_z_fillMethod_8522252.java)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.setup(InvocationContextInterceptor.java:88)
       at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_setup_8522252.invoke(InvocationContextInterceptor_z_setup_8522252.java)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:62)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:56)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:68)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
       at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:190)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.security.RunAsSecurityInterceptorv2.invoke(RunAsSecurityInterceptorv2.java:94)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:186)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:67)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:219)
       at org.jboss.ejb3.proxy.handler.ProxyInvocationHandlerBase.invoke(ProxyInvocationHandlerBase.java:261)
       at org.jboss.ejb3.proxy.handler.session.SessionSpecProxyInvocationHandlerBase.invoke(SessionSpecProxyInvocationHandlerBase.java:101)
       at $Proxy215.searchUsers(Unknown Source)
       at com.mrm.actions.admin.security.ManageUsersSearchAction.execute(ManageUsersSearchAction.java:36)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)
       at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
       at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)
       at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
       at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
       at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
       at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
       at com.mrm.interceptors.MrmInterceptor.intercept(MrmInterceptor.java:30)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
       at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
       at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
       at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
       at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
       at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
       at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
       at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)
       at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:395)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
       at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
       at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525)
       at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
       at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
       at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
       at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
       at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
       at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
       at java.lang.Thread.run(Thread.java:619)
      Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
       at org.hibernate.loader.Loader.doList(Loader.java:2231)
       at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
       at org.hibernate.loader.Loader.list(Loader.java:2120)
       at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
       at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
       at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
       at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
       ... 104 more
      Caused by: java.sql.SQLException: Column 'password22_0_' not found.
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
       at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)
       at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)
       at org.jboss.resource.adapter.jdbc.WrappedResultSet.getString(WrappedResultSet.java:888)
       at org.hibernate.type.StringType.get(StringType.java:41)
       at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
       at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
       at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
       at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2124)
       at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404)
       at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332)
       at org.hibernate.loader.Loader.getRow(Loader.java:1230)
       at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603)
       at org.hibernate.loader.Loader.doQuery(Loader.java:724)
       at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
       at org.hibernate.loader.Loader.doList(Loader.java:2228)
       ... 111 more