While working on the website for Tobacco Palace, I ran into an issue with ordering the results of my queries. I am using JBoss 3.2.3 which follows the EJB 2.0 spec. This spec does not allow for ORDER BY clauses in it's custom finders for CMP Entity Beans. After some poking around, I found a few different suggestions. One was to ditch the custom finder and use a stateless session bean to do the queries. Not a bad solution, but I dont' know that I'm ready to go down that road yet. I have enough to do to just get this up and running. The second suggestion, was to create a view that did the ordering for you and then select from that, and not the underlying table. Not sure how this would work for inserts, updates and deletes. I decided to to persue that avenue either.
Another suggestion, was to use a J-Boss specific X-Doclet tag, @jboss.declared-sql. I decided to research this avenue.
The @jboss.declared-sql allows you to specify multiple tables for a "from clause", ordering of the resultset, setting limits, and a general nicer approach to writing the SQL if you are familar with SQL, and not necessarily the EJB-QL language.
Not much to it, just use the example below, and everything works wonderfully. I have not seen a noticible speed difference, but then again, I only have about 300 records in my database.
* @ejb.finder
* signature = "java.util.Collection findByCategoryId(
* java.lang.Long category_id)"
* query = ""
* @jboss.declared-sql
* from = "tbl_item"
* order = "item_name"
* where = "category_id = {0}"
* signature = "java.util.Collection findByCategoryId(
* java.lang.Long category_id)"
Posted by doug at May 15, 2004 12:15 PM