[jboss-user] [EJB 3.0] - @SqlResultSetMapping and column not found
leobaz2
do-not-reply at jboss.com
Tue Mar 10 21:38:20 EDT 2009
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
|
View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4216826#4216826
Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4216826
More information about the jboss-user
mailing list