I tried to execute the following query using JBoss declared-sql:
SELECT c.Status, Month(c.firstUsed), COUNT(c.serialNumber) FROM Card c WHERE c.status = 4 GROUP BY Month(c.firstUsed)
I have the following in the jbosscmp-jdbc.xml:
<query-method>
<method-name>ejbSelectCountCardsUsed</method-name>
<method-params>
</method-params>
</query-method>
<declared-sql>
< select >
<ejb-name><![CDATA[Card]]></ejb-name>
<field-name><![CDATA[status]]></field-name>
< alias ><![CDATA[c]]>< /alias >
<additional-columns><![CDATA[, Month(c.firstUsed), COUNT(c.serialNumber)]]></additional-columns>
< /select >
< where ><![CDATA[ c.status = 4 GROUP BY Month(c.firstUsed)]]>< /where >
</declared-sql>
And I have the following method in the Card Entity Bean:
/**
* @ejb.home-method view-type="local"
*/
public Collection ejbHomeCountCardsUsed() throws FinderException {
return ejbSelectCountCardsUsed();
}
/**
* @ejb.select query=""
*/
public abstract Collection ejbSelectCountCardsUsed() throws FinderException;
When I read from the Collection returned by ejbHomeCountCardsUsed(), I was only be able to get the c.Status value. How can I get the Month(c.firstUsed), COUNT(c.serialNumber)?
Or is there any better way to implement this SELECT COUNT...GROUP BY query?