I have an issue with Hibernate. I'm trying to return a list of objects from my Database and need to join with a view that is not mapped in Hibernate. So I am using the session.createSQLQuery method to create the query, then using the SQLQuery.addEntity(Entity.class) method to add the class. Whenever I run this on my system I get the error:
com.microsoft.sqlserver.jdbc.SQLServerException: The column name clazz_ is not valid.
Creating the query and executing (Note, the Employee object is being passed into the method):
String sqlQuery = "select * from company where company_id in (select company_id from company_authority_access_view where employee_id = "+employee.getEmployeeId()+")";
SQLQuery query = session.createSQLQuery(sqlQuery);
query.addEntity(Company.class);
return query.list();
the HQL Output (This returns the values from the company table that I need):
select * from company where company_id in (select company_id from company_authority_access_view where employee_id = 1)
The query never contains a column 'clazz_' I'm unable to identify what's causing this error and have used this process with other objects and it works fine. I've tried modifying my query to just use select * from company and this produces the same error, so it seems like there is something wrong with my object mapping, but I am unable to identify what. The Company class uses annotations to map the columns the full company class is below
@Entity
@Inheritance(strategy=InheritanceType.JOINED)
@Table(name="company")
@Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
public class Company extends OrganizationalUnit implements JobCostingSpecific, ITracksModificationUser, ITracksModificationTimestamp, Serializable {
private static final long serialVersionUID = 1L;
/** identifier field */
private Integer companyId;
/** nullable persistent field */
private net.company.model.data.Employee modificationUser;
/** nullable persistent field */
private String name;
/** nullable persistent field */
private String description;
/** nullable persistent field */
private Date modificationTimestamp;
/** persistent field */
private Map<String, CompanyProperty> companyProperties;
/** persistent field */
private Set<Division> divisions;
private SortedSet<Division> sortedDivisions;
private Integer version;
/** full constructor */
public Company(net.company.model.data.Employee modificationUser, String name, String description, Date modificationTimestamp, Map<String, CompanyProperty> companyProperties, Set<Division> divisions) {
this.modificationUser = modificationUser;
this.name = name;
this.description = description;
this.modificationTimestamp = modificationTimestamp;
this.companyProperties = companyProperties;
this.divisions = divisions;
}
/** default constructor */
public Company() {
}
/** minimal constructor */
public Company(Map<String, CompanyProperty> companyProperties, Set<Division> divisions) {
this.companyProperties = companyProperties;
this.divisions = divisions;
}
@Transient
@Override
public Integer getId() {
return (companyId == null ? 0 : companyId.intValue());
}
@Id
@Column(name = "company_id", nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getCompanyId() {
return this.companyId;
}
public void setCompanyId(Integer companyId) {
this.companyId = companyId;
}
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "modification_user", nullable = false)
public net.company.model.data.Employee getModificationUser() {
return this.modificationUser;
}
public void setModificationUser(net.company.model.data.Employee modificationUser) {
this.modificationUser = modificationUser;
}
@Column(name="name", nullable=true)
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
@Column(name="description", nullable=true)
public String getDescription() {
return this.description;
}
public void setDescription(String description) {
this.description = description;
}
@Column(name="modification_timestamp", nullable=true)
public Date getModificationTimestamp() {
return this.modificationTimestamp;
}
public void setModificationTimestamp(Date modificationTimestamp) {
this.modificationTimestamp = modificationTimestamp;
}
@OneToMany(mappedBy="company",fetch=FetchType.EAGER)
@MapKey(name="name")
public Map<String, CompanyProperty> getCompanyProperties() {
return this.companyProperties;
}
public void setCompanyProperties(Map<String, CompanyProperty> companyProperties) {
this.companyProperties = companyProperties;
}
@OneToMany(mappedBy="company",fetch=FetchType.EAGER)
@MapKey(name="companyId")
public Set<Division> getDivisions() {
return this.divisions;
}
public void setDivisions(Set<Division> divisions) {
this.divisions = divisions;
}
@Transient
public SortedSet<Division> getSortedDivisions(){
if(divisions != null){
sortedDivisions = new TreeSet<Division>(OrganizationUnitComparator.LABEL_COMPARATOR());
sortedDivisions.addAll(divisions);
}
return sortedDivisions;
}
public String toString() {
return name + " (" + companyId + ")";
}
/* (non-Javadoc)
* @see net.company.data.OrganizationalUnit#getChildren()
*/
@Transient
public Set<? extends OrganizationalUnit> getChildren() {
return getDivisions();
}
/* (non-Javadoc)
* @see net.company.data.OrganizationalUnit#getParent()
*/
@Transient
public OrganizationalUnit getParent() {
return null;
}
/* (non-Javadoc)
* @see net.company.data.OrganizationalUnit#getAccessibleType()
*/
@Transient
public OrganizationType getAccessibleType() {
return OrganizationType.COMPANY;
}
@Transient
public Integer getJobCostingSpecificId() {
return companyId;
}
@Transient
public JobCostingType getJobCostingType() {
return null;
}
@ReportColumn(name="employeeCompanyLabel")
@Transient
@Override
public String getLabel(){
return LabelUtil.getLabel(this);
}
@Version
@Column(name="version",nullable=false)
public Integer getVersion() {
return version;
}
public void setVersion(Integer version) {
this.version = version;
}
}
The full stack trace of the error is below:
14:06:25,994 INFO [STDOUT] 14:06:25,992 WARN JDBCExceptionReporter:233 - SQL Error: 0, SQLState: S1093
14:06:25,995 INFO [STDOUT] 14:06:25,994 ERROR JDBCExceptionReporter:234 - The column name clazz_ is not valid.
14:06:25,995 INFO [STDOUT] 14:06:25,995 ERROR AbstractCompanyDAO:799 - Error Retrieving List
14:06:25,999 INFO [STDOUT] 14:06:25,997 ERROR companyRequestProcessor:496 - Error Processing Request.
14:06:25,999 INFO [STDOUT] net.company.dao.impl.DAOException: Exception while getting authority access for employee: company Admin (admin)
14:06:25,999 INFO [STDOUT] at net.company.businesslogic.security.impl.AccessRuleService.getAuthorityAccessForEmployee(AccessRuleService.java:830)
14:06:26,000 INFO [STDOUT] at net.company.ui.controller.masterdata.security.accessset.AccessRuleHelper.initRequestScope(AccessRuleHelper.java:80)
14:06:26,000 INFO [STDOUT] at net.company.ui.controller.masterdata.workorder.WorkOrderViewAction.execute(WorkOrderViewAction.java:109)
14:06:26,000 INFO [STDOUT] at net.company.ui.controller.companyAction$RequestHandler.handleRequest(companyAction.java:116)
14:06:26,000 INFO [STDOUT] at net.company.ui.controller.companyRequestProcessor.tryExecuteHandler(companyRequestProcessor.java:443)
14:06:26,000 INFO [STDOUT] at net.company.ui.controller.companyRequestProcessor.processRequest(companyRequestProcessor.java:208)
14:06:26,000 INFO [STDOUT] at net.company.ui.controller.companyAction.execute(companyAction.java:85)
14:06:26,000 INFO [STDOUT] at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:53)
14:06:26,000 INFO [STDOUT] at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:64)
14:06:26,000 INFO [STDOUT] at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:48)
14:06:26,000 INFO [STDOUT] at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
14:06:26,000 INFO [STDOUT] at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
14:06:26,000 INFO [STDOUT] at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
14:06:26,000 INFO [STDOUT] at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:280)
14:06:26,000 INFO [STDOUT] at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858)
14:06:26,000 INFO [STDOUT] at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:446)
14:06:26,000 INFO [STDOUT] at javax.servlet.http.HttpServlet.service(HttpServlet.java:734)
14:06:26,001 INFO [STDOUT] at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:324)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242)
14:06:26,001 INFO [STDOUT] at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:413)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:274)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242)
14:06:26,001 INFO [STDOUT] at net.company.jsf.LeftNavTabFilter.doFilter(LeftNavTabFilter.java:44)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:274)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242)
14:06:26,001 INFO [STDOUT] at net.company.jsf.companyJsfFilter.doFilter(companyJsfFilter.java:60)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:274)
14:06:26,001 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242)
14:06:26,001 INFO [STDOUT] at net.company.businesslogic.context.filter.companyContextFilter.doFilterInternal(companyContextFilter.java:72)
14:06:26,001 INFO [STDOUT] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
14:06:26,002 INFO [STDOUT] at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
14:06:26,002 INFO [STDOUT] at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:274)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242)
14:06:26,002 INFO [STDOUT] at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
14:06:26,002 INFO [STDOUT] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:274)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:242)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:161)
14:06:26,002 INFO [STDOUT] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java)
14:06:26,002 INFO [STDOUT] at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:181)
14:06:26,002 INFO [STDOUT] at org.jboss.modcluster.catalina.CatalinaContext$RequestListenerValve.event(CatalinaContext.java:285)
14:06:26,002 INFO [STDOUT] at org.jboss.modcluster.catalina.CatalinaContext$RequestListenerValve.invoke(CatalinaContext.java:261)
14:06:26,002 INFO [STDOUT] at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:88)
14:06:26,003 INFO [STDOUT] at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:100)
14:06:26,003 INFO [STDOUT] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:159)
14:06:26,003 INFO [STDOUT] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
14:06:26,003 INFO [STDOUT] at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
14:06:26,003 INFO [STDOUT] at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
14:06:26,003 INFO [STDOUT] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
14:06:26,003 INFO [STDOUT] at org.jboss.web.tomcat.service.request.ActiveRequestResponseCacheValve.invoke(ActiveRequestResponseCacheValve.java:53)
14:06:26,003 INFO [STDOUT] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:362)
14:06:26,003 INFO [STDOUT] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)
14:06:26,003 INFO [STDOUT] at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:654)
14:06:26,003 INFO [STDOUT] at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:951)
14:06:26,003 INFO [STDOUT] at java.lang.Thread.run(Thread.java:724)
14:06:26,003 INFO [STDOUT] Caused by: net.company.dao.impl.DAOException: org.hibernate.exception.GenericJDBCException: could not execute query
14:06:26,004 INFO [STDOUT] at net.company.dao.impl.AbstractCompanyDAO.getList(AbstractCompanyDAO.java:800)
14:06:26,004 INFO [STDOUT] at net.company.dao.impl.CompanyDAO.getCompanyListByAuthority(CompanyDAO.java:184)
14:06:26,004 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
14:06:26,004 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
14:06:26,004 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
14:06:26,004 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:606)
14:06:26,004 INFO [STDOUT] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
14:06:26,004 INFO [STDOUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
14:06:26,004 INFO [STDOUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
14:06:26,004 INFO [STDOUT] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
14:06:26,004 INFO [STDOUT] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
14:06:26,004 INFO [STDOUT] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
14:06:26,004 INFO [STDOUT] at com.sun.proxy.$Proxy185.getCompanyListByAuthority(Unknown Source)
14:06:26,004 INFO [STDOUT] at net.company.businesslogic.security.impl.AccessRuleService.getAuthorityAccessForEmployee(AccessRuleService.java:825)
14:06:26,004 INFO [STDOUT] ... 57 more
14:06:26,005 INFO [STDOUT] Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
14:06:26,005 INFO [STDOUT] at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
14:06:26,005 INFO [STDOUT] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
14:06:26,005 INFO [STDOUT] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
14:06:26,005 INFO [STDOUT] at org.hibernate.loader.Loader.doList(Loader.java:2536)
14:06:26,005 INFO [STDOUT] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
14:06:26,005 INFO [STDOUT] at org.hibernate.loader.Loader.list(Loader.java:2271)
14:06:26,005 INFO [STDOUT] at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
14:06:26,005 INFO [STDOUT] at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
14:06:26,005 INFO [STDOUT] at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
14:06:26,005 INFO [STDOUT] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
14:06:26,005 INFO [STDOUT] at net.company.dao.impl.AbstractCompanyDAO.getList(AbstractCompanyDAO.java:797)
14:06:26,005 INFO [STDOUT] ... 70 more
14:06:26,005 INFO [STDOUT] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name clazz_ is not valid.
14:06:26,006 INFO [STDOUT] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
14:06:26,006 INFO [STDOUT] at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:625)
14:06:26,006 INFO [STDOUT] at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(SQLServerResultSet.java:2218)
14:06:26,006 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:698)
14:06:26,006 INFO [STDOUT] at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:61)
14:06:26,006 INFO [STDOUT] at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
14:06:26,006 INFO [STDOUT] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
14:06:26,006 INFO [STDOUT] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
14:06:26,006 INFO [STDOUT] at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234)
14:06:26,006 INFO [STDOUT] at org.hibernate.loader.Loader.getInstanceClass(Loader.java:1589)
14:06:26,006 INFO [STDOUT] at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1431)
14:06:26,006 INFO [STDOUT] at org.hibernate.loader.Loader.getRow(Loader.java:1355)
14:06:26,006 INFO [STDOUT] at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:611)
14:06:26,006 INFO [STDOUT] at org.hibernate.loader.Loader.doQuery(Loader.java:829)
14:06:26,007 INFO [STDOUT] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
14:06:26,007 INFO [STDOUT] at org.hibernate.loader.Loader.doList(Loader.java:2533)
14:06:26,007 INFO [STDOUT] ... 77 more
