I am having issue with code that use Native query below:
public Result<IDto> getSearchResult(String classID, Integer pageNo, Integer pageSize
, String orderBy, String orderType,String language){
TrainingFeedbackReportDetailDTO reportDetailDTO = new TrainingFeedbackReportDetailDTO();
//add information of class into response
TrainingFeedbackReportDTO classInfo = DaoManager.getTrainingFeedbackReportDao().getClassInfo(classID,language);
reportDetailDTO.setClassInfo(classInfo);
//add agent list with list of question & answer for each agent
AgentResponseDTO agentResponse = new AgentResponseDTO();
reportDetailDTO.setAgentResponse(agentResponse);
Map<Integer, Object> paramMap = new HashMap<Integer, Object>();
String sqlStatement = getAgentInfo(classID, orderBy, orderType, paramMap, language);
//get list of agent register for specific class
List<IDto> agentResponseList = getResult(sqlStatement, pageNo, pageSize, paramMap, AgentFeedbackDTO.class.getName());
Integer totalRecord = getResult(sqlStatement, 0, 0, paramMap, AgentFeedbackDTO.class.getName()).size();
agentResponse.setTotalRecord(totalRecord);
//get list of question & answer for each class & agent
agentResponse.setDataList(getAgentFeedbackDetail(classID, agentResponseList));
reportDetailDTO.setAgentResponse(agentResponse);
Result<IDto> result = new Result<IDto>();
result.setData(reportDetailDTO);
return result;
}
private String getAgentInfo(String classID, String orderBy, String orderType,
Map<Integer, Object> paramMap, String language){
StringBuilder builder = new StringBuilder();
builder.append("SELECT tmp0.agent_id agentID, tmp0.agent_code agentCode, tmp0.agent_name agentName, tmp0.agent_name_thai agentNameThai, ");
builder.append( "tmp0.division division, tmp0.agency_code agencyCode, tmp0.agency_name_th agencyNameTh, tmp0.agency_name_en agencyNameEn ");
builder.append("FROM (" + getAgentFinishFeedback() + ") ac ");
builder.append("INNER JOIN (" + getAgentInfo() + ") tmp0 ON tmp0.agent_id=ac.FK_AGENT_ID ");
builder.append("WHERE ac.FK_CLASS_ID=?1 ");
if (orderBy.isEmpty()){
orderBy = "agentID";
orderType = "ASC";
}
if(orderBy.equals("agencyName")) {
if(language.equals(Constant.HTTP_LANGUAGE_EN)) {
orderBy = "agencyNameEn";
} else {
orderBy = "agencyNameTh";
}
}
builder.append("ORDER BY " + orderBy + " " + orderType);
paramMap.put(1, new BigDecimal(classID));
return builder.toString();
}
private List<IDto> getResult(String sqlStatement, Integer pageNo, Integer pageSize,
Map<Integer, Object> paramMap, String dtoClassName){
List<IDto> list = getList(sqlStatement, pageNo, pageSize, paramMap, dtoClassName);
if (list.size() > 0){
return list;
} else {
return new ArrayList<IDto>();
}
}
@SuppressWarnings("unchecked")
private List<IDto> getList(String sqlStatement, Integer pageNo, Integer pageSize,
Map<Integer, Object> paramMap, String dtoClassName){
EntityManager em = null;
try {
em = PersistenceManager.getLMSEntityManagerFactory().createEntityManager();
Query query = em.createNativeQuery(sqlStatement, Class.forName(dtoClassName));
if (paramMap != null) {
for (Integer name : paramMap.keySet()) {
query.setParameter(name, paramMap.get(name));
}
}
if (pageSize > 0){
query
.setMaxResults(pageSize)
.setFirstResult(pageNo*pageSize);
}
return query.getResultList();
} catch (Exception ex){
ex.printStackTrace();
} finally {
if (em!=null){
em.close();
}
}
return null;
}
My POJO code:
package com.aia.lms.dto;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import com.aia.lms.persistence.entity.IEntity;
public class AgentFeedbackDTO implements IDto, IExtractDto{
private String agentCode;
private String agentNameThai;
private String division;
private String agencyCode;
private String agencyName;
private String agencyNameTh;
private String agencyNameEn;
private BigDecimal agentID;
private String agentName;
private List<ShortFeedbackDTO> questions = new ArrayList<ShortFeedbackDTO>();
public String getAgencyName() {
return agencyName;
}
public void setAgencyName(String agencyName) {
this.agencyName = agencyName;
}
public BigDecimal getAgentID() {
return agentID;
}
public void setAgentID(BigDecimal agentID) {
this.agentID = agentID;
}
public String getAgentCode() {
return agentCode;
}
public void setAgentCode(String agentCode) {
this.agentCode = agentCode;
}
public String getAgentName() {
return agentName;
}
public void setAgentName(String agentName) {
this.agentName = agentName;
}
public String getDivision() {
return division;
}
public void setDivision(String division) {
this.division = division;
}
public String getAgencyCode() {
return agencyCode;
}
public void setAgencyCode(String agencyCode) {
this.agencyCode = agencyCode;
}
public String getAgencyNameTh() {
return agencyNameTh;
}
public void setAgencyNameTh(String agencyNameTh) {
this.agencyNameTh = agencyNameTh;
}
public String getAgencyNameEn() {
return agencyNameEn;
}
public void setAgencyNameEn(String agencyNameEn) {
this.agencyNameEn = agencyNameEn;
}
public String getAgentNameThai() {
return agentNameThai;
}
public void setAgentNameThai(String agentNameThai) {
this.agentNameThai = agentNameThai;
}
public List<ShortFeedbackDTO> getQuestions() {
return questions;
}
public void setQuestions(List<ShortFeedbackDTO> questions) {
this.questions = questions;
}
public void setFeedback(ShortFeedbackDTO feedback){
for (ShortFeedbackDTO dto : this.questions){
if (dto.isExist(feedback.getDetailID())){
return;
}
}
this.questions.add(feedback);
Collections.sort(this.questions);
}
@Override
public IDto extractParent(IEntity entry) {
return null;
}
@Override
public void updateDTO(IEntity entry) {
}
}
These code run normally when I use Websphere server 8.5 with Java 6 and JPA 1.1. But after updating to Websphere server 9.0 with Java 8 and JPA 2.0, the system get issue as below:
[5/27/20 14:40:40:157 ICT] 000000b7 eclipselink W Exception [EclipseLink-6007] (Eclipse Persistence Services - 2.6.8.WAS-v20181218-0accd7f): org.eclipse.persistence.exceptions.QueryException
Exception Description: Missing descriptor for [class com.aia.lms.dto.AgentFeedbackDTO].
Query: ReadAllQuery(referenceClass=AgentFeedbackDTO sql="SELECT tmp0.agent_id agentID, tmp0.agent_code agentCode, tmp0.agent_name agentName, tmp0.agent_name_thai agentNameThai, tmp0.division division, tmp0.agency_code agencyCode, tmp0.agency_name_th agencyNameTh, tmp0.agency_name_en agencyNameEn FROM (SELECT DISTINCT ctf.FK_AGENT_ID, ctf.FK_CLASS_ID FROM LMS_CLASS_TRAINING_FEEDBACK ctf LEFT JOIN LMS_AGENT_CLASS LAC ON LAC.FK_AGENT_ID = ctf.FK_AGENT_ID AND LAC.FK_CLASS_ID = ctf.FK_CLASS_ID WHERE LAC.FK_CLASS_ENROLL_STATUS_ID = 1) ac INNER JOIN ( SELECT ui.ID agent_id, CASE WHEN LUR.NAME = 'PROSPECT' THEN LP.PROSPECT_CODE ELSE ai.AGENT_CODE END agent_code, CASE WHEN LUR.NAME = 'PROSPECT' THEN CONCAT(CONCAT(LP.AGENT_FIRSTNAME_EN, ' '),LP.AGENT_SURNAME_EN) ELSE CONCAT(CONCAT(ai.AGENT_FIRSTNAME_EN, ' '),ai.AGENT_SURNAME_EN) END agent_name, CASE WHEN LUR.NAME = 'PROSPECT' THEN LP.AGENT_NAME_THAI ELSE ai.AGENT_NAME_THAI END agent_name_thai, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(LHS2.DIVISION_CODE, ' ') ELSE NVL(LHS.DIVISION_CODE, ' ') END division, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(lai.AGENCY_CODE, ' ') ELSE NVL(ai.AGENCY_CODE, ' ') END agency_code, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(lai.AGENCY_NAME_THAI, ' ') ELSE NVL(ai.AGENCY_NAME_THAI, ' ') END agency_name_th, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(lai.AGENCY_NAME_ENG, ' ') ELSE NVL(ai.AGENCY_NAME_ENG, ' ') END agency_name_en FROM LMS_USER_INFO ui LEFT JOIN LMS_AGENT_INFO ai ON ui.ID=ai.FK_USER_INFO_ID LEFT JOIN LMS_HIERARCHY_SETUP LHS ON LHS.AGENCY_CODE = ai.AGENCY_CODE LEFT JOIN LMS_PROSPECT LP ON LP.FK_USER_INFO_ID = ui.ID LEFT JOIN ( SELECT FK_USER_INFO_ID,LUR.NAME FROM LMS_USER_INFO_ROLE LUIR LEFT JOIN LMS_USER_ROLE LUR ON LUIR.FK_USER_ROLE_ID = LUR.ID WHERE LUR.NAME = 'PROSPECT' ) LUR ON LUR.FK_USER_INFO_ID = LP.FK_USER_INFO_ID LEFT JOIN LMS_AGENT_INFO lai ON lai.FK_USER_INFO_ID = LP.CREATED_BY LEFT JOIN LMS_HIERARCHY_SETUP LHS2 ON LHS2.AGENCY_CODE = lai.AGENCY_CODE ) tmp0 ON tmp0.agent_id=ac.FK_AGENT_ID WHERE ac.FK_CLASS_ID=?1 ORDER BY agentCode ASC")
[5/27/20 14:40:40:611 ICT] 000000b7 SystemErr R javax.persistence.PersistenceException: Exception [EclipseLink-6007] (Eclipse Persistence Services - 2.6.8.WAS-v20181218-0accd7f): org.eclipse.persistence.exceptions.QueryException
Exception Description: Missing descriptor for [class com.aia.lms.dto.AgentFeedbackDTO].
Query: ReadAllQuery(referenceClass=AgentFeedbackDTO sql="SELECT tmp0.agent_id agentID, tmp0.agent_code agentCode, tmp0.agent_name agentName, tmp0.agent_name_thai agentNameThai, tmp0.division division, tmp0.agency_code agencyCode, tmp0.agency_name_th agencyNameTh, tmp0.agency_name_en agencyNameEn FROM (SELECT DISTINCT ctf.FK_AGENT_ID, ctf.FK_CLASS_ID FROM LMS_CLASS_TRAINING_FEEDBACK ctf LEFT JOIN LMS_AGENT_CLASS LAC ON LAC.FK_AGENT_ID = ctf.FK_AGENT_ID AND LAC.FK_CLASS_ID = ctf.FK_CLASS_ID WHERE LAC.FK_CLASS_ENROLL_STATUS_ID = 1) ac INNER JOIN ( SELECT ui.ID agent_id, CASE WHEN LUR.NAME = 'PROSPECT' THEN LP.PROSPECT_CODE ELSE ai.AGENT_CODE END agent_code, CASE WHEN LUR.NAME = 'PROSPECT' THEN CONCAT(CONCAT(LP.AGENT_FIRSTNAME_EN, ' '),LP.AGENT_SURNAME_EN) ELSE CONCAT(CONCAT(ai.AGENT_FIRSTNAME_EN, ' '),ai.AGENT_SURNAME_EN) END agent_name, CASE WHEN LUR.NAME = 'PROSPECT' THEN LP.AGENT_NAME_THAI ELSE ai.AGENT_NAME_THAI END agent_name_thai, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(LHS2.DIVISION_CODE, ' ') ELSE NVL(LHS.DIVISION_CODE, ' ') END division, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(lai.AGENCY_CODE, ' ') ELSE NVL(ai.AGENCY_CODE, ' ') END agency_code, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(lai.AGENCY_NAME_THAI, ' ') ELSE NVL(ai.AGENCY_NAME_THAI, ' ') END agency_name_th, CASE WHEN LUR.NAME = 'PROSPECT' THEN NVL(lai.AGENCY_NAME_ENG, ' ') ELSE NVL(ai.AGENCY_NAME_ENG, ' ') END agency_name_en FROM LMS_USER_INFO ui LEFT JOIN LMS_AGENT_INFO ai ON ui.ID=ai.FK_USER_INFO_ID LEFT JOIN LMS_HIERARCHY_SETUP LHS ON LHS.AGENCY_CODE = ai.AGENCY_CODE LEFT JOIN LMS_PROSPECT LP ON LP.FK_USER_INFO_ID = ui.ID LEFT JOIN ( SELECT FK_USER_INFO_ID,LUR.NAME FROM LMS_USER_INFO_ROLE LUIR LEFT JOIN LMS_USER_ROLE LUR ON LUIR.FK_USER_ROLE_ID = LUR.ID WHERE LUR.NAME = 'PROSPECT' ) LUR ON LUR.FK_USER_INFO_ID = LP.FK_USER_INFO_ID LEFT JOIN LMS_AGENT_INFO lai ON lai.FK_USER_INFO_ID = LP.CREATED_BY LEFT JOIN LMS_HIERARCHY_SETUP LHS2 ON LHS2.AGENCY_CODE = lai.AGENCY_CODE ) tmp0 ON tmp0.agent_id=ac.FK_AGENT_ID WHERE ac.FK_CLASS_ID=?1 ORDER BY agentCode ASC")
[5/27/20 14:40:40:611 ICT] 000000b7 SystemErr R at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:484)
[5/27/20 14:40:40:612 ICT] 000000b7 SystemErr R at com.aia.lms.persistence.dao.TrainingFeedbackReportDetailDao.getList(TrainingFeedbackReportDetailDao.java:244)
[5/27/20 14:40:40:612 ICT] 000000b7 SystemErr R at com.aia.lms.persistence.dao.TrainingFeedbackReportDetailDao.getResult(TrainingFeedbackReportDetailDao.java:219)
[5/27/20 14:40:40:612 ICT] 000000b7 SystemErr R at com.aia.lms.persistence.dao.TrainingFeedbackReportDetailDao.getSearchResult(TrainingFeedbackReportDetailDao.java:43)
[5/27/20 14:40:40:612 ICT] 000000b7 SystemErr R at com.aia.lms.service.TrainingFeedbackReportService.getPaging(TrainingFeedbackReportService.java:124)
[5/27/20 14:40:40:612 ICT] 000000b7 SystemErr R at com.aia.lms.rest.RESTResource.getFeedbackPaging(RESTResource.java:3216)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:90)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at java.lang.reflect.Method.invoke(Method.java:508)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at com.ibm.ws.jaxrs20.server.LibertyJaxRsServerFactoryBean.performInvocation(LibertyJaxRsServerFactoryBean.java:659)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at com.ibm.ws.jaxrs20.server.LibertyJaxRsInvoker.performInvocation(LibertyJaxRsInvoker.java:108)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at com.ibm.ws.jaxrs20.server.LibertyJaxRsInvoker.invoke(LibertyJaxRsInvoker.java:174)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:191)
[5/27/20 14:40:40:613 ICT] 000000b7 SystemErr R at com.ibm.ws.jaxrs20.server.LibertyJaxRsInvoker.invoke(LibertyJaxRsInvoker.java:346)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:101)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:73)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:111)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:309)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:136)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:280)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at com.ibm.ws.jaxrs20.endpoint.AbstractJaxRsWebEndpoint.invoke(AbstractJaxRsWebEndpoint.java:147)
[5/27/20 14:40:40:614 ICT] 000000b7 SystemErr R at com.ibm.websphere.jaxrs.server.IBMRestServlet.handleRequest(IBMRestServlet.java:187)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.websphere.jaxrs.server.IBMRestServlet.doGet(IBMRestServlet.java:153)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.websphere.jaxrs.server.IBMRestServlet.service(IBMRestServlet.java:133)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1235)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:779)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:179)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:143)
[5/27/20 14:40:40:615 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:96)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.aia.lms.filter.ResponseCorsFilter.doFilter(ResponseCorsFilter.java:55)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:969)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1109)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:82)
[5/27/20 14:40:40:616 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:963)
[5/27/20 14:40:40:617 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
[5/27/20 14:40:40:617 ICT] 000000b7 SystemErr R at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:382)
[5/27/20 14:40:40:618 ICT] 000000b7 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465)
[5/27/20 14:40:40:618 ICT] 000000b7 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:532)
[5/27/20 14:40:40:618 ICT] 000000b7 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:318)
[5/27/20 14:40:40:618 ICT] 000000b7 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:88)
[5/27/20 14:40:40:618 ICT] 000000b7 SystemErr R at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
[5/27/20 14:40:40:618 ICT] 000000b7 SystemErr R at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
[5/27/20 14:40:40:619 ICT] 000000b7 SystemErr R at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
[5/27/20 14:40:40:619 ICT] 000000b7 SystemErr R at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
[5/27/20 14:40:40:619 ICT] 000000b7 SystemErr R at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
[5/27/20 14:40:40:619 ICT] 000000b7 SystemErr R at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
[5/27/20 14:40:40:619 ICT] 000000b7 SystemErr R at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
[5/27/20 14:40:40:619 ICT] 000000b7 SystemErr R at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1909)
Anyone help me show how could I resolve this problem ? Thanks.
