JBoss.org Community Documentation
DeclaredSQL is based on the legacy JAWS CMP 1.1 engine finder declaration, but has been updated for CMP 2.0. Commonly this declaration is used to limit a query with a WHERE
clause that cannot be represented in q EJB-QL or JBossQL. The content model for the declared-sql element is given in Figure 12.12, “The jbosscmp-jdbc declared-sql element content model.>”.
select
: The select
element specifies what is to be selected and consists of the following elements:
distinct
: If this empty element is present, JBoss will add the DISTINCT
keyword to the generated SELECT
clause. The default is to use DISTINCT
if method returns a java.util.Set
ejb-name
: This is the ejb-name
of the entity that will be selected. This is only required if the query is for a select method.
field-name : This is the name of the CMP field that will be selected from the specified entity. The default is to select entire entity.
alias
: This specifies the alias that will be used for the main select table. The default is to use the ejb-name
.
additional-columns : Declares other columns to be selected to satisfy ordering by arbitrary columns with finders or to facilitate aggregate functions in selects.
from
: The from
element declares additional SQL to append to the generated FROM
clause.
where
: The where
element declares the WHERE
clause for the query.
order
: The order
element declares the ORDER
clause for the query.
other
: The other
element declares additional SQL that is appended to the end of the query.
The following is an example DeclaredSQL declaration.
<jbosscmp-jdbc> <enterprise-beans> <entity> <ejb-name>GangsterEJB</ejb-name> <query> <query-method> <method-name>findBadDudes_declaredsql</method-name> <method-params> <method-param>int</method-param> </method-params> </query-method> <declared-sql> <where><![CDATA[ badness > {0} ]]></where> <order><![CDATA[ badness DESC ]]></order> </declared-sql> </query> </entity> </enterprise-beans> </jbosscmp-jdbc>
The generated SQL would be:
SELECT id FROM gangster WHERE badness > ? ORDER BY badness DESC
As you can see, JBoss generates the SELECT
and FROM
clauses necessary to select the primary key for this entity. If desired an additional FROM
clause can be specified that is appended to the end of the automatically generated FROM
clause. The following is example DeclaredSQL declaration with an additional FROM
clause.
<jbosscmp-jdbc> <enterprise-beans> <entity> <ejb-name>GangsterEJB</ejb-name> <query> <query-method> <method-name>ejbSelectBoss_declaredsql</method-name> <method-params> <method-param>java.lang.String</method-param> </method-params> </query-method> <declared-sql> <select> <distinct/> <ejb-name>GangsterEJB</ejb-name> <alias>boss</alias> </select> <from><![CDATA[, gangster g, organization o]]></from> <where><![CDATA[ (LCASE(g.name) = {0} OR LCASE(g.nick_name) = {0}) AND g.organization = o.name AND o.the_boss = boss.id ]]></where> </declared-sql> </query> </entity> </enterprise-beans> </jbosscmp-jdbc>
The generated SQL would be:
SELECT DISTINCT boss.id FROM gangster boss, gangster g, organization o WHERE (LCASE(g.name) = ? OR LCASE(g.nick_name) = ?) AND g.organization = o.name AND o.the_boss = boss.id
Notice that the FROM
clause starts with a comma. This is because the container appends the declared FROM
clause to the end of the generated FROM
clause. It is also possible for the FROM
clause to start with a SQL JOIN
statement. Since this is a select method, it must have a select
element to declare the entity that will be selected. Note that an alias is also declared for the query. If an alias is not declared, the table-name
is used as the alias, resulting in a SELECT
clause with the table_name.field_name
style column declarations. Not all database vendors support the that syntax, so the declaration of an alias is preferred. The optional empty distinct
element causes the SELECT
clause to use the SELECT DISTINCT
declaration. The DeclaredSQL declaration can also be used in select methods to select a CMP field.
Now we well see an example which overrides a select to return all of the zip codes an Organization
operates in.
<jbosscmp-jdbc>
<enterprise-beans>
<entity>
<ejb-name>OrganizationEJB</ejb-name>
<query>
<query-method>
<method-name>ejbSelectOperatingZipCodes_declaredsql</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<declared-sql> <select> <distinct/> <ejb-name>LocationEJB</ejb-name> <field-name>zipCode</field-name> <alias>hangout</alias> </select> <from><![CDATA[ , organization o, gangster g ]]></from> <where><![CDATA[ LCASE(o.name) = {0} AND o.name = g.organization AND g.hangout = hangout.id ]]></where> <order><![CDATA[ hangout.zip ]]></order> </declared-sql>
</query>
</entity>
</enterprise-beans>
</jbosscmp-jdbc>
The corresponding SQL would be:
SELECT DISTINCT hangout.zip FROM location hangout, organization o, gangster g WHERE LCASE(o.name) = ? AND o.name = g.organization AND g.hangout = hangout.id ORDER BY hangout.zip