Hi
I am trying to retrieve all instances of an entity called Referral.
I am using Spring to create instances of my EJB 3 interface class called ReferralSessionLocal. I use Hibernate 3.3, hibernate-jpa-2.0-final.jar and the Oracle database, running on WebSphere 7 and Windows XP.
I am getting the following exceptions:
Code:
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:74)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at java.lang.reflect.Method.invoke(Method.java:599)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:247)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at $Proxy57.getResultList(Unknown Source)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.att.referraltracker.core.dataaccess.dao.referral.ReferralDAO.findAllReferrals(ReferralDAO.java:245)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.att.referraltracker.core.logic.referral.ejb.ReferralServicesBean.findAllReferrals(ReferralServicesBean.java:86)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.att.referraltracker.web.action.referral.ADisplayDashboard.execute(ADisplayDashboard.java:93)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1486)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:528)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1449)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1384)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:104)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:852)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:785)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:443)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:175)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:325)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1070)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1486)
[7/22/10 11:19:18:664 CDT] 0000001a SystemErr R at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:528)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1449)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1384)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:104)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:852)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:785)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:443)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:175)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3610)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:274)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:926)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1557)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:173)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:455)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:384)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:83)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:202)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:807)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:896)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1527)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.doList(Loader.java:2235)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.list(Loader.java:2124)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:65)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R ... 56 more
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R Caused by: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:654)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.doQuery(Loader.java:697)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R at org.hibernate.loader.Loader.doList(Loader.java:2232)
[7/22/10 11:19:18:680 CDT] 0000001a SystemErr R ... 64 more
My entity class is as follows:
Code:
package com.att.rt.dto.referral;
import java.io.Serializable;
import java.util.Date;
import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;
import java.util.List;
import java.util.ArrayList;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.NamedQuery;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.SecondaryTable;
import javax.persistence.SecondaryTables;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.CascadeType;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.JoinTable;
import javax.persistence.FetchType;
import com.att.referraltracker.dto.history.History;
import com.att.referraltracker.dto.role.Role;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.SqlResultSetMappings;
import javax.persistence.EntityResult;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.SqlResultSetMappings;
import javax.persistence.ColumnResult;
@Entity
@Table(name="REFERRAL_INCIDENT", schema = "DOTS")
public class Referral implements Serializable{
/**
*
*/
private static final long serialVersionUID = 7422574264557894637L;
/**
* String queryStr = "select history from History as history where not exists (select his from History his where his.issue = history.issue and his.date > history.date)";
*/
public static final int UNSAVED_VALUE = 0;
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@SequenceGenerator(name = "referral_incident_sequence", sequenceName = "ref_inc_id_seq")
@Column (name="ID")
private int id = UNSAVED_VALUE;
@Column(name="CODE", table = "REFERRAL_INCIDENT")
private String code;
@Column(name="DESCRIPTION", table = "REFERRAL_INCIDENT")
private String description;
@Column(name="REFERAL_DATE", table = "REFERRAL_INCIDENT")
private Date referral_date;
@OneToMany(cascade=CascadeType.ALL, mappedBy="referral")
private SortedSet<History> referralHistories;
@ManyToOne(optional=true)
private Role role;
//private Set<Role> roles;
@Column(name="PARENTID", table = "Referral_Incident")
private Integer parentId;
public Date getReferral_date() {
return referral_date;
}
public void setReferral_date(Date referral_date) {
this.referral_date = referral_date;
}
public Referral() {
role = new Role();
referralHistories = new TreeSet<History>();
}
public Referral(int id, String code, String description, Date referral_date){
this.id = id;
this.code = code;
this.description = description;
this.referral_date = referral_date;
}
public long getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
@ManyToOne(optional=true)
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public Integer getParentId() {
return parentId;
}
public void setParentId(Integer parentId) {
this.parentId = parentId;
}
public String toString() {
return "Referral id:" + getId() + ", code: " + getCode() + ", description: " + getDescription() + ", Date: " + getReferral_date();
}
public SortedSet<History> getReferralHistories() {
return referralHistories;
}
public void setReferralHistories(SortedSet<History> referralHistories) {
this.referralHistories = referralHistories;
}
}
The relevant method in my EJB class:
@SuppressWarnings("unchecked")
Code:
public List<Referral> findAllReferrals() throws ReferralServicesException {
logger.info("ReferralServicesBean: inside method findAllReferrals()");
//Collection<Referral> referrals = getReferralDAO().findAllReferrals();
//List<Object[]> referrals = getReferralDAO().findAllReferrals();
//Referral[] referrals = getReferralDAO().findAllReferrals();
List<Referral> referrals = getReferralDAO().findAllReferrals();
logger.info("ReferralServicesBean: referrals is : " + referrals);
if(null != referrals){
this.referrals = referrals;
}
return referrals;
}
-----
An Excerpt from my DAO with the relevant portions attached:
Code:
public class ReferralDAO {
private final static Logger logger = Logger.getLogger(ReferralDAO.class);
public static final int ID_DEFAULT_USER = 0;
public static final long UNSAVED_VALUE = -1;
private ReferralDAO referralDAO;
public ReferralDAO getReferralDAO() {
return referralDAO;
}
public void setReferralDAO(ReferralDAO referralDAO) {
this.referralDAO = referralDAO;
}
String dataSourceName = "test";
Category category;
List<Category> categories;
List<Referral> referrals;
@PersistenceContext
private EntityManager entityManagerFactory;
void setEntityManager(EntityManager entityManagerFactory) {
this.entityManagerFactory = entityManagerFactory;
}
private EntityManager getEntityManager() {
return entityManagerFactory;
}
Referral referral;
public ReferralDAO(){
}
@SuppressWarnings("unchecked")
public List<Referral> findAllReferrals() throws ReferralServicesException{
javax.persistence.Query query = entityManagerFactory.createQuery("SELECT ref FROM com.att.referraltracker.dto.referral.Referral ref ORDER BY ref.id");
logger.info("ReferralDAO: the query string in findAllReferrals() is " + query.toString());
try {
referralsList2 = (List<Referral>)query.getResultList();
} catch (Exception ex) {
ex.printStackTrace();
}
//String[] stringArray = (String[])validPapers.toArray(new String[validPapers.size()]);
return referralsList2;
}
}
The exception is pointing to the query.getResultSet statement, but I do not understand what is wrong with the query.
I have all the database tables in Oracle.
Thanks in advance