Problem with view datas in h:dataTable

[Charles Conneley]

Hi All,
I have small problem with view datas in h:dataTable item. I have native query which is working properly in database and in java:

SELECT SUM(price_list.first_class_price), SUM(price_list.second_class_price) FROM price_list, connections WHERE connections.id = price_list.id_connect GROUP BY connections.source; . 

The method in EJB returns a List of elements. This method looks like this:

 public List<PriceList> getFirstClassPrices() {
Query q = em.createNativeQuery("SELECT SUM(price_list.first_class_price), SUM(price_list.second_class_price) FROM price_list, connections WHERE connections.id = price_list.id_connect GROUP BY connections.source");
return q.getResultList();
}

I want to show result of this query in dataTableItem, but I don`t know how to get to this values. I tried to do something like this (FCP is the method in JSF ManagedBean that returns mentioned list):

<h:dataTable value="#{priceList.FCP}}" var="item" cellspacing="0" cellpadding="1" border="1">
<h:column>
<f:facet name="header">
<h: outputText value="First class"/>
</f:facet>
<h: outputText value="#{item}"/>
</h:column>
<h:column>
<f:facet name="header">
<h: outputText value="Second class"/>
</f:facet>
<h: outputText value="#{item}"/>
</h:column>
</h:dataTable>

but this shows me only references as the result in table.

FCP method in JSF ManagedBean looks like this:

public List<PriceList> getFCP() {
return priceListFacade.getFirstClassPrices();
}

This solution is working properly when I have a query like this:

SELECT SUM(price_list.first_class_price) FROM price_list, connections WHERE connections.id = price_list.id_connect GROUP BY connections.source; 

, with one SUM element.

Then correct datas are in the table and there is no problem. My question is: how to get datas, no references from the result list when I have two or more SUM elements in a query?

I tried use code like this:

<h:dataTable value="#{priceList.FCP}}" var="item" cellspacing="0" cellpadding="1" border="1">
<h:column>
<f:facet name="header">
<h: outputText value="First class"/>
</f:facet>
<h: outputText value="#{item.first_class_price}"/>
</h:column>
<h:column>
<f:facet name="header">
<h: outputText value="Second class"/>
</f:facet>
<h: outputText value="#{item.second_class_price}"/>
</h:column>
</h:dataTable>

but then I have an EJB exception: there is no properties like first_class_price and second_class_price.
I tried to change the query for this:

SELECT SUM(price_list.first_class_price) [b]AS p1[/b], SUM(price_list.second_class_price) [b]AS p2[/b] FROM price_list, connections WHERE connections.id = price_list.id_connect GROUP BY connections.source;

and then in JSF I used a code like this:

<h:dataTable value="#{priceList.FCP}}" var="item" cellspacing="0" cellpadding="1" border="1">
<h:column>
<f:facet name="header">
<h: outputText value="First class"/>
</f:facet>
<h: outputText value="#{item.p1}"/>
</h:column>
<h:column>
<f:facet name="header">
<h: outputText value="Second class"/>
</f:facet>
<h: outputText value="#{item.p2}"/>
</h:column>
</h:dataTable>

but I have the same EJB exception tkat properties p1 and p2 doesn`t exists .

I am new in Java EE and I can`t handle with this. Maybe there is another way to solve my problem? Maybe I shouldn`t use h:dataTableItem?

Thanks for your replies.

[Charles Conneley]

Hi again, I have just solved my problem. Maybe it will be helpfull for somebody:

Code from SessionBean:

public List<Object[]> getFirstClassPrices() {
Query q = em.createNativeQuery("SELECT SUM(price_list.first_class_price), SUM(price_list.second_class_price) FROM price_list, connections WHERE connections.id = price_list.id_connect GROUP BY connections.source");
List<Object[]> lista = q.getResultList();
return lista;
}

Code from JSF ManagedBean:

public List<Object[]> getFCP() {
return priceListFacade.getFirstClassPrices();
}

Code from JSF page:

<h:dataTable value="#{priceList.FCP}" var="item" border="1" >
<h:column>
<f:facet name="header">
<h:outputText value="S1" />
</f:facet>
<h:outputText value="#{item[0]}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="S1" />
</f:facet>
<h:outputText value="#{item[1]}"/>
</h:column>
</h:dataTable>

Greetings.