java.lang.NumberFormatException after trying to present values from a query

Question

public List findCatalog() {
    Query query = getEntityManager().createQuery("SELECT pc.productCatalog, p.name, p.product FROM ProductCatalog pc JOIN pc.products p");
    return query.getResultList();
}

Hello, with such query my application compiles okay. But when i open the page where this query is executed i get the following error:

org.apache.jasper.JasperException: java.lang.NumberFormatException: For input string: "productCatalog"

The productCatalog is my primary key, but same happens when i just include the name column

exception org.apache.jasper.JasperException: java.lang.NumberFormatException: For input string: "name" root cause

java.lang.NumberFormatException: For input string: "name"

If i don't make the join in my query then the results from 1 table are printed okay. I'm clueless for now where the problem could be, i have read Pro JPA 2 book, official java EE 6 tutorial, googled alot. The tables design is ManyToMany. I have product table, *product_catalog* table and the binding table *product_product_catalog. I don't have foreign keys in the binding table. I have created the entity and facade classes with the NetBeans EclipseLink wizard.

Here is how i mapped on my ProductCatalog class many to many relationship

@ManyToMany
    @JoinTable(name = "product_product_catalog",
    joinColumns = {
        @JoinColumn(name = "product_catalog")
    },
    inverseJoinColumns = {
        @JoinColumn(name = "product")
    })
    private Collection<Product> products;

product and product_catalog are the primary keys for corresponding tables. Now i have no idea where i could be wrong, perhaps is the mapping wrong? Though many examples which i have seen, have the same mapping implementation, but in their tables they have foreign keys, could that be an issue? Moreover as i said if i don't join second table, everything works well.

Here is my servlet with initializing that method getServletContext().setAttribute("productCatalog", productCatalogFacade.findCatalog());

And here is my JSP page fragment of that

<table>
            <c:forEach var="list" items="${productCatalog}" varStatus="iter">
                <tr>
                    <td>${list.productCatalog}</td>
                    <td>${list.product}</td>
                    <td>${list.name}</td>
                </tr>
            </c:forEach>
        </table>

And here is my native SQL query what i'm trying to achieve

SELECT PPC.product_product_catalog, PPC.product_catalog, PPC.product, P.name, PC.name AS  "catalog name", P.code, P.description, P.price, P.producer
FROM product_catalog PC
INNER JOIN product_product_catalog PPC ON PC.product_catalog = PPC.product_catalog
INNER JOIN product P ON P.product = PPC.product

As my last piece of code here is the output which glassfish produces when i go to that page where the query is executed:

WARNING: StandardWrapperValve[jsp]: PWC1406: Servlet.service() for servlet jsp threw exception
java.lang.NumberFormatException: For input string: "productCatalog"
        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
        at java.lang.Integer.parseInt(Integer.java:449)
        at java.lang.Integer.parseInt(Integer.java:499)
        at javax.el.ArrayELResolver.toInteger(ArrayELResolver.java:375)
        at javax.el.ArrayELResolver.getValue(ArrayELResolver.java:195)
        at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:175)
        at com.sun.el.parser.AstValue.getValue(AstValue.java:116)
        at com.sun.el.parser.AstValue.getValue(AstValue.java:163)
        at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:219)
        at org.apache.jasper.runtime.PageContextImpl.evaluateExpression(PageContextImpl.java:1007)
        at org.apache.jsp.index_jsp._jspx_meth_c_forEach_0(index_jsp.java from :206)
        at org.apache.jsp.index_jsp._jspService(index_jsp.java from :137)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:109)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:406)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:483)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:373)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
        at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1523)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:641)
        at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
        at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
        at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
        at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
        at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
        at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
        at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
        at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
        at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
        at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
        at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
        at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
        at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
        at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
        at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
        at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
        at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
        at java.lang.Thread.run(Thread.java:619)

Solution 1

When you execute a request with many variables in SELECT clause, such as

SELECT pc.productCatalog, p.name, p.product ...

each row of result is returned in form of Object[], so you need to use numeric indexes to access its elements:

<c:forEach var="list" items="${productCatalog}" varStatus="iter"><tr>
    <td>${list[0]}</td> 
    <td>${list[2]}</td>
    <td>${list[1]}</td>
</tr></c:forEach> 

Solution 2

I had a similar problem in my implementation using Java EE7, JSF2.2, JPA2 and @NamedStoredProcedureQuery which @axtavt solution helped solve (+1). I wasn't defining the result class correctly and hence was returning an Object[] instead of a List of the defined entity class.

If you create an entity class to handle the data return type you can then use annotations on that entity class and it will handle mapping automagically:

@NamedStoredProcedureQuery(
    name = "getJoinedSiteData",
    procedureName = "func_joined_site_data",
    resultClasses = Site.class,
    parameters = {@StoredProcedureParameter(mode = IN, name = "site_id", type = Integer.class)}
)
public class Site implements Serializable{
...
}

You can call this then in your controller like:

public List<Site> getItems(Integer siteId){
            StoredProcedureQuery query = em.createNamedStoredProcedureQuery("getJoinedSiteData");
    return query.setParameter("site_id", siteId).getResultList();
}

This then enables direct binding to the data model as per JSF2.2 you can use the standard ui:repeat or h:dataTable like below. eg.

<h:dataTable value="#{controller.getItems(someSiteId)}" var="item">
    <h:column>
        <f:facet name="header">
            <h:outputText value="Site Name"/>
        </f:facet>
        <h:outputText value="#{item.name}"/>
    </h:column>
</h:dataTable>

Comments

[Skyzer]

Yes, i've seen it, but as there is said, the problem was in glassfish v2. I'm using 3.0.1

[Skyzer]

hey, the productCatalog is declared as Integer type and name is of type String. I've put break point in my JSP page and ran glassfish in debug mode, but none of the variables even appeared in the NetBeans debugger

[Dead Programmer]

@Skyzer why don't u print the variable value, and see if it has string or number.

[Robert]

@axtavt does the same apply for Hashmaps?