Projections returns a wrong data type?

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

This Criteria query works fine without the Projections. To get a unique result, I added Projections. It seems that Projections only returns String data type. How to solve this problem?

Thanks very much for your input in advance.

Hibernate version:
3.x
Mapping documents:

Code:

<class name="EntryBriefInfo" table="entry">

    <!-- Common id property -->
    <id name="id" type="int" unsaved-value="null" >
      <generator class="native">
          <param name="sequence">entry_id_seq</param>
       </generator>   
    </id>

   <!-- Immutable property -->
   <property   name="name" type="string" length="255"   not-null="true"   update="false" column="name"/>

   <!-- Limit item description to 4000 characters, Oracle -->
   <property   name="description"  type="string" column="description" not-null="true"/>   

</class>

Code between sessionFactory.openSession() and session.close():

Code:

this.getSession().createCriteria(
            EntryBriefInfo.class).add(
            Restrictions.like("name", searchString, MatchMode.ANYWHERE))
            .setProjection(
                  Projections.distinct(Projections.projectionList().add(
                        Projections.property("id")).add(
                        Projections.property("name")).add(
                        Projections.property("description")))).list()

Full stack trace of any exception that occurs:

Code:

      javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "value" with value "${entry.id}": The "." operator was supplied with an index value of type "java.lang.String" to be applied to a List or array, but that value cannot be converted to an integer. (null)
   at org.apache.taglibs.standard.lang.jstl.Evaluator.evaluate(Evaluator.java:109)
   at org.apache.taglibs.standard.lang.jstl.Evaluator.evaluate(Evaluator.java:129)
   at org.apache.taglibs.standard.lang.support.ExpressionEvaluatorManager.evaluate(ExpressionEvaluatorManager.java:75)
   at org.apache.taglibs.standard.tag.el.core.ExpressionUtil.evalNotNull(ExpressionUtil.java:48)
   at org.apache.taglibs.standard.tag.el.core.ParamTag.evaluateExpressions(ParamTag.java:108)
   at org.apache.taglibs.standard.tag.el.core.ParamTag.doStartTag(ParamTag.java:60)
   at org.apache.jsp.WEB_002dINF.jsp.directory.entryList.content_jsp._jspx_meth_c_param_0(content_jsp.java:192)
   at org.apache.jsp.WEB_002dINF.jsp.directory.entryList.content_jsp._jspx_meth_c_url_0(content_jsp.java:163)
   at org.apache.jsp.WEB_002dINF.jsp.directory.entryList.content_jsp._jspx_meth_c_forEach_0(content_jsp.java:111)
   at org.apache.jsp.WEB_002dINF.jsp.directory.entryList.content_jsp._jspService(content_jsp.java:75)
   at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
   at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
   at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
   at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:574)
   at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:499)
   at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:966)
   at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:602)
   at org.apache.struts.tiles.TilesUtilImpl.doInclude(TilesUtilImpl.java:101)
   at org.apache.struts.tiles.TilesUtil.doInclude(TilesUtil.java:137)
   at org.apache.struts.taglib.tiles.InsertTag.doInclude(InsertTag.java:758)
   at org.apache.struts.taglib.tiles.InsertTag$InsertHandler.doEndTag(InsertTag.java:890)
   at org.apache.struts.taglib.tiles.InsertTag.doEndTag(InsertTag.java:460)
   at org.apache.jsp.WEB_002dINF.jsp.template.main_jsp._jspx_meth_tiles_insert_2(main_jsp.java:494)
   at org.apache.jsp.WEB_002dINF.jsp.template.main_jsp._jspService(main_jsp.java:157)
   at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
   at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
   at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
   at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:463)
   at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:398)
   at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
   at org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:167)
   at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:239)
   at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1158)
   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:808)
   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476)
   at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:441)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at com.vernonwu.ilocalcommunity.web.EncodingFilter.doFilter(EncodingFilter.java:39)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
   at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
   at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
   at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
   at java.lang.Thread.run(Unknown Source)

Name and version of the database you are using:
PostGreSQL 8.x
The generated SQL (show_sql=true):

Code:

Hibernate: 
    select
        distinct this_.id as y0_,
        this_.name as y1_,
        this_.description as y2_ 
    from
        entry this_ 
    where
        this_.name like ?
Hibernate: 
    select
        distinct this_.id as y0_,
        this_.name as y1_,
        this_.description as y2_ 
    from
        entry this_ 
    where
        this_.name like ?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

vw729 ,

If I understand them correctly, Projections are a way to perform multiple aggregate calculations on the same resultset.
But in the case you describe, why use projections at all?
What's wrong with a

Code:

select distinct ebi.id, ebi.name, ebi.description from EntryBriefInfo ebi

?

Incidentally, your error stack trace shows a JSP (JSTL) errror, which occurs when the variables expected are arrays instead of primitive values.
I believe that your query is not returning EntryBriefInfo beans as you expect, but some sort of Object[] array containing separate EntryBriefInfo properties.

You have 2 options:

1) cast those individual properties and feed them to the JSP in a different fashion

2) or modify your query in such a way that it returns EntryBriefInfo beans as expected. Notice that this second option is only possible if you have a one-element select list, as in

Code:

select ebi from EntryBriefInfo ebi ...

or directly

Code:

from EntryBriefInfo ebi

However, since you want to perform some sort of distinct on some of the members of the class, probably you want to go with option 1).

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

Thanks Gonzalo for your input.

I was using a HQL for this string search situation to handle any given strings entered by a user. The criteria seems to be the only way to retrieve any strings containing the search string, for example, "Bank of American" and "CitiBank" for a search string "Bank". That is the reason I use createCriteria.

After searching over this forum, the Projects seems to the only way to get unique result of the fetch.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

vw729,

HQL has the "like" statement, just like normal SQL. You can perfectly write a simple HQL line with both "selec distinct ..." and " where string1 like ..." for your problem.

Remember, again, that the result won't be a bean (i.e. you will probably have to change your JSP code), but an array of objects.

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

gonzao_diaz wrote:

vw729,

HQL has the "like" statement, just like normal SQL. You can perfectly write a simple HQL line with both "selec distinct ..." and " where string1 like ..." for your problem.

Remember, again, that the result won't be a bean (i.e. you will probably have to change your JSP code), but an array of objects.

In my original approach, I used "like" and it didn't retrieve strings as I described in my previous post.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Could it be a parameter problem?
Are you adding '%' after abd before your string parameter?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Could it be a parameter problem?
Are you adding '%' after and before your string parameter?

vw729 · **Joined:** Wed Aug 25, 2004 7:40 pm **Posts:** 65

gonzao_diaz wrote:

Could it be a parameter problem?
Are you adding '%' after and before your string parameter?

You beat on this post. After I found that I need to add "%" on the both ends of a search string, I come back to update this thread and see you already post the subject.

Thanks.