Red Hat Summit Red Hat Summit지원콘솔개발자평가판 시작연락처

이 콘텐츠는 선택한 언어로 제공되지 않습니다.

31.6.6. DeclaredSQL

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 31.12, “The jbosscmp-jdbc declared-sql element content model.>”.
The jbosscmp-jdbc declared-sql element content model.>
View larger image
The jbosscmp-jdbc declared-sql element content model.>

Figure 31.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>
Copy to Clipboard Toggle word wrap
The generated SQL would be: 
SELECT id
FROM gangster
WHERE badness > ?
ORDER BY badness DESC
Copy to Clipboard Toggle word wrap
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>
Copy to Clipboard Toggle word wrap
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
Copy to Clipboard Toggle word wrap
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>
Copy to Clipboard Toggle word wrap
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
Copy to Clipboard Toggle word wrap

31.6.6.1. Parameters
링크 복사

DeclaredSQL uses a completely new parameter handling system, which supports entity and DVC parameters. Parameters are enclosed in curly brackets and use a zero-based index, which is different from the one-based EJB-QL parameters. There are three categories of parameters: simple, DVC, and entity.
  • simple: A simple parameter can be of any type except for a known (mapped) DVC or an entity. A simple parameter only contains the argument number, such as {0}. When a simple parameter is set, the JDBC type used to set the parameter is determined by the datasourcemapping for the entity. An unknown DVC is serialized and then set as a parameter. Note that most databases do not support the use of a BLOB value in a WHERE clause.
  • DVC: A DVC parameter can be any known (mapped) DVC. A DVC parameter must be dereferenced down to a simple property (one that is not another DVC). For example, if we had a CVS property of type ContactInfo, valid parameter declarations would be {0.email} and {0.cell.areaCode} but not {0.cell}. The JDBC type used to set a parameter is based on the class type of the property and the datasourcemapping of the entity. The JDBC type used to set the parameter is the JDBC type that is declared for that property in the dependent-value-class element.
  • entity: An entity parameter can be any entity in the application. An entity parameter must be dereferenced down to a simple primary key field or simple property of a DVC primary key field. For example, if we had a parameter of type Gangster, a valid parameter declaration would be {0.gangsterId}. If we had some entity with a primary key field named info of type ContactInfo, a valid parameter declaration would be {0.info.cell.areaCode}. Only fields that are members of the primary key of the entity can be dereferenced (this restriction may be removed in later versions). The JDBC type used to set the parameter is the JDBC type that is declared for that field in the entity declaration.
맨 위로 이동
Red Hat logoGithubredditYoutubeTwitter

자세한 정보

평가판, 구매 및 판매

커뮤니티

Red Hat 문서 정보

Red Hat을 사용하는 고객은 신뢰할 수 있는 콘텐츠가 포함된 제품과 서비스를 통해 혁신하고 목표를 달성할 수 있습니다. 최신 업데이트를 확인하세요.

보다 포괄적 수용을 위한 오픈 소스 용어 교체

Red Hat은 코드, 문서, 웹 속성에서 문제가 있는 언어를 교체하기 위해 최선을 다하고 있습니다. 자세한 내용은 다음을 참조하세요.Red Hat 블로그.

Red Hat 소개

Red Hat은 기업이 핵심 데이터 센터에서 네트워크 에지에 이르기까지 플랫폼과 환경 전반에서 더 쉽게 작업할 수 있도록 강화된 솔루션을 제공합니다.

Theme

© 2025 Red Hat