Fórum

custom query error

thumbnail
reza shams, modificado 9 Anos atrás.

custom query error

Junior Member Postagens: 25 Data de Entrada: 30/08/14 Postagens Recentes
hi every body
I've faced to problem about custom query. I've created a query :
SELECT s.* FROM IDLP_status s

this query run true but when I changed this query to:
SELECT DISTINCT s.status FROM IDLP_status s

I faced to error:
08:16:53,733 ERROR [RuntimePageImpl-6][JDBCExceptionReporter:82] Invalid column name id_.
com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
at com.liferay.portal.dao.orm.hibernate.ExceptionTranslator.translate(ExceptionTranslator.java:30)
at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:189)
at com.liferay.portal.dao.orm.hibernate.SQLQueryImpl.list(SQLQueryImpl.java:162)
at com.rose.idlp.service.persistence.statusFinderImpl.getAllStatus(statusFinderImpl.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:320)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at com.liferay.portal.dao.shard.advice.ShardPersistenceAdvice.invoke(ShardPersistenceAdvice.java:54)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy764.getAllStatus(Unknown Source)
at com.rose.idlp.service.persistence.statusFinderUtil.getAllStatus(statusFinderUtil.java:16)
at com.rose.idlp.service.impl.statusLocalServiceImpl.getAllStatus(statusLocalServiceImpl.java:65)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:115)
at com.liferay.portal.spring.transaction.DefaultTransactionExecutor.execute(DefaultTransactionExecutor.java:62)
at com.liferay.portal.spring.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:51)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ChainableMethodAdvice.invoke(ChainableMethodAdvice.java:56)
at com.liferay.portal.spring.aop.ServiceBeanMethodInvocation.proceed(ServiceBeanMethodInvocation.java:111)
at com.liferay.portal.spring.aop.ServiceBeanAopProxy.invoke(ServiceBeanAopProxy.java:175)
at com.sun.proxy.$Proxy762.getAllStatus(Unknown Source)
at com.rose.idlp.service.statusLocalServiceUtil.getAllStatus(statusLocalServiceUtil.java:271)
at com.model.Report.webTestReport(Report.java:54)
at org.apache.jsp.html.view.index_jsp._jspService(index_jsp.java:532)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:116)
at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilter.doFilter(InvokerFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:749)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:605)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:544)
at com.liferay.portlet.PortletRequestDispatcherImpl.dispatch(PortletRequestDispatcherImpl.java:331)
at com.liferay.portlet.PortletRequestDispatcherImpl.include(PortletRequestDispatcherImpl.java:112)
at com.liferay.util.bridges.mvc.MVCPortlet.include(MVCPortlet.java:371)
at com.liferay.util.bridges.mvc.MVCPortlet.include(MVCPortlet.java:387)
at com.liferay.util.bridges.mvc.MVCPortlet.doView(MVCPortlet.java:144)
at com.liferay.portal.kernel.portlet.LiferayPortlet.doDispatch(LiferayPortlet.java:213)
at com.liferay.util.bridges.mvc.MVCPortlet.doDispatch(MVCPortlet.java:323)
at javax.portlet.GenericPortlet.render(GenericPortlet.java:233)
at com.liferay.portlet.FilterChainImpl.doFilter(FilterChainImpl.java:103)
at com.liferay.portlet.ScriptDataPortletFilter.doFilter(ScriptDataPortletFilter.java:55)
at com.liferay.portlet.FilterChainImpl.doFilter(FilterChainImpl.java:100)
at com.liferay.portal.kernel.portlet.PortletFilterUtil.doFilter(PortletFilterUtil.java:64)
at com.liferay.portal.kernel.servlet.PortletServlet.service(PortletServlet.java:112)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilterChain.doFilter(InvokerFilterChain.java:116)
at com.liferay.portal.kernel.servlet.filters.invoker.InvokerFilter.doFilter(InvokerFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:749)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:605)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:544)
at com.liferay.portlet.InvokerPortletImpl.invoke(InvokerPortletImpl.java:604)
at com.liferay.portlet.InvokerPortletImpl.invokeRender(InvokerPortletImpl.java:677)
at com.liferay.portlet.InvokerPortletImpl.render(InvokerPortletImpl.java:379)
at org.apache.jsp.html.portal.render_005fportlet_jsp._jspService(render_005fportlet_jsp.java:1230)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at com.liferay.portal.servlet.DirectRequestDispatcher.include(DirectRequestDispatcher.java:57)
at com.liferay.portal.servlet.ClassLoaderRequestDispatcherWrapper.doDispatch(ClassLoaderRequestDispatcherWrapper.java:78)
at com.liferay.portal.servlet.ClassLoaderRequestDispatcherWrapper.include(ClassLoaderRequestDispatcherWrapper.java:53)
at com.liferay.portlet.PortletContainerImpl._doRender(PortletContainerImpl.java:655)
at com.liferay.portlet.PortletContainerImpl.render(PortletContainerImpl.java:138)
at com.liferay.portlet.SecurityPortletContainerWrapper.render(SecurityPortletContainerWrapper.java:141)
at com.liferay.portlet.RestrictPortletContainerWrapper.render(RestrictPortletContainerWrapper.java:126)
at com.liferay.portal.kernel.portlet.PortletContainerUtil.render(PortletContainerUtil.java:156)
at com.liferay.portal.layoutconfiguration.util.PortletRenderer._render(PortletRenderer.java:120)
at com.liferay.portal.layoutconfiguration.util.PortletRenderer.access$4(PortletRenderer.java:107)
at com.liferay.portal.layoutconfiguration.util.PortletRenderer$PortletRendererCallable.doCall(PortletRenderer.java:174)
at com.liferay.portal.layoutconfiguration.util.PortletRenderer$PortletRendererCallable.doCall(PortletRenderer.java:1)
at com.liferay.portal.kernel.executor.CopyThreadLocalCallable.call(CopyThreadLocalCallable.java:69)
at java.util.concurrent.FutureTask.run(Unknown Source)
at com.liferay.portal.kernel.concurrent.ThreadPoolExecutor$WorkerTask._runTask(ThreadPoolExecutor.java:682)
at com.liferay.portal.kernel.concurrent.ThreadPoolExecutor$WorkerTask.run(ThreadPoolExecutor.java:593)
...


this is my finder function:
public List<status> getAllStatus() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get("getstatusSQLQuery");
SQLQuery queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);
queryObject.addEntity("status", statusImpl.class);
QueryPos qPos = QueryPos.getInstance(queryObject);
return (List<status>) queryObject.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}//end of function


who can help me?
thumbnail
Abdollah Esmaeilpour, modificado 9 Anos atrás.

RE: custom query error

Junior Member Postagens: 60 Data de Entrada: 22/08/09 Postagens Recentes
Dear Reza

Consider two lines below:
First,
queryObject.addEntity("status", statusImpl.class);

This means you have all fields of "status" entity in your ResultSet. But you have just one field in your select list of the query, so change it to:
q.addScalar("status", Type.String);
where status is the field name in your select list of the query and String is the type of "status".

Second,
return (List<status>) queryObject.list();</status>

Modify it to
return (List<string>) queryObject.list();</string>
because, you are getting a list of String objects instead of status ones.
kailash behara, modificado 9 Anos atrás.

RE: custom query error

New Member Postagens: 14 Data de Entrada: 11/05/13 Postagens Recentes
Dear Reza,

As mentioned by you
SELECT DISTINCT s.status FROM IDLP_status s


let's assume the query will be as below

SELECT DISTINCT tableName.fieldName FROM tableName


then you need to change your code instated of
queryObject.addEntity("status", statusImpl.class);


queryObject.addEntity("tableName", statusImpl.class);


Thanks,
Kailash
thumbnail
reza shams, modificado 9 Anos atrás.

RE: custom query error

Junior Member Postagens: 25 Data de Entrada: 30/08/14 Postagens Recentes
tnx problem is solved