Each finder method in the component’s home interface requires a database query to select a set of primary keys. For example, the findByPrimaryKey method selects the key that matches the input parameter. A findAll method might return all keys in the table. ejbSelect methods in an EJB 2.0 entity bean also require query mappings.
There are two ways to specify query mappings:
Using EJB-QL EJB 2.0 entity beans can use EJB Query Language (EJB-QL) in the EJB-JAR deployment descriptor. EJB-QL allows portability among EJB 2.0-compliant servers. EAServer translates EJB-QL to SQL at runtime. You can configure additional EAServer query mapping properties to enable caching of the query results for improved performance.
Using extended SQL If you cannot use EJB-QL, you must specify the query mappings using the EAServer extended SQL mapping language. This language is based on standard SQL, with extensions to allow substitution of method parameters and invocation of stored procedures. In some cases, no mapping is required: EAServer can correctly infer the query required to execute the findByPrimaryKey method. EAServer can also infer some queries based on finder-method naming patterns, for example:
findAll to return keys for all rows.
findByField where field is the name of a container-managed field in the component, to return the rows that match the specified field value.
findLikeField where field is the name of a container-managed field in the component, to return rows where the column mapped to field contains the specified field value
If you have deployed EJB CMP entity beans from an EJB-JAR
file
If you have deployed EJB CMP entity beans from an EJB-JAR
file, the special query mapping value [unknown] indicates
that you must specify a query. The special value [default] means
that EAServer can infer the query based on the method’s
name pattern. The special value ejbQuery: indicates
the query uses EJB-QL that was specified in the deployment descriptor.
Configuring queries for methods
Display the Persistence/Query Mapping subtab in the Component Properties dialog box. One mapping displays for each finder method in the component’s home interface and for each ejbSelect method.
To change the query mapping, highlight the method name or query, then click Modify. Edit the value as follows:
Using EJB-QL: The special EAServer query ejbQuery: indicates
that an EJB-QL query is defined for the finder or ejbSelect method.
You can add additional parameters to configure caching of the query
results, as described in “Entity
instance and query caching” in the EAServer Performance
and Tuning Guide, for example:
ejbQuery:[cache]
If the special mapping ejbQuery: is specified for the method, the EJB-QL query can be specified using a second query mapping for the method. The EJB-QL query must follow the syntax described by the EJB 2.0 specification.
In EJB-QL, each entity bean is represented by a schema name. EAServer defines the EJB-QL schema names in the properties of the package that contains the component. To map schema names to components in the package, specify a package property of the form:
schema:name=package/component
Where name is the schema name, package is the EAServer package name and component is the component name. If the package properties do not specify a schema name for the component, the default is the component name. Schemas used in queries for a component can only refer to components in the same EAServer package.
Using EAServer extended SQL: Enter one of the following for the query:
[default] if
EJB-QL is not used and EAServer can correctly infer the query.
A SQL query appropriate for the semantics of the finder method, which can be a query filter or the syntax to call a stored procedure. For ordinary select queries that select only from the main table, you can omit the select keyword and column list, and specify only a where clause. Use the placeholders described in Table 27-2 to represent column and table names and parameter values.
Placeholder |
To indicate |
|---|---|
[key] |
The table’s primary key (which can consist of multiple columns). |
[table] |
The name of the main table, specified in the Table field on the Persistence/General subtab. |
@param |
Reference the value of parameter param in the finder method’s IDL signature.
|
@param.fieldName |
If method parameter param is not a simple type, reference the value of field fieldName. |
[cache cache-props] |
When appended to the query, configures query caching. See “Entity instance and query caching” in the EAServer Performance and Tuning Guide. |
select queries for ejbSelect methods
can return one column only.
Use the syntax of these examples if you are using database tables that can be accessed directly with standard SQL select statements.
For simple queries that select only from the main table, you can omit the select keyword and column list as in this example. This query uses the value of the expiryDate parameter to filter a range of closingDate column values. Note the select keyword, column list, and from clause are omitted:
where closingDate < @expiryDate
When you omit the select keyword for a finder query, all columns are selected. If you are using query caching, selecting all columns allows EAServer to preload the query cache when the finder method executes.
If you include the select keyword and a
column list in the query, you must use binding syntax (pseudoassignments)
to bind the selected columns to the container-managed fields unless
you are using the [key] placeholder
for the table’s primary key, as in:
select @field1 = col1, @field2 = (select col2 from t2 where t2.a = t1.a) from table1 t1, table2 t2
In this example, field1 and field2 are
the names of the container-managed fields that are bound to the
columns returned in the result set.
If you specify a column list and you are using entity object caching, specify a select list that returns all columns so that the object cache can be populated from the query results. Omit the select keyword, the column list, and the from clause to avoid this complication.
The query must be a complete select statement or omit the select keyword, column list, and from clause.
You can call stored procedures to return the results required to execute a finder or ejbSelect query. To specify a stored procedure call, use the syntax:
select column-list from {call proc param-list}
Where:
column-list contains placeholders for the field values and specifies their positions in the row returned by the procedure. Use the following syntax to indicate fields:
@fieldName to
specify the value of field fieldName.
@fieldName.subfield to
specify the value of subfield subField where fieldName is
a container-managed field that takes structured types.
If you are using entity object caching, the stored procedure should return values for all columns, so that the object cache can be populated when the procedure is called.
proc is the stored procedure name.
param-list is a parameter list that contains the parameters required by the stored procedure. Use the placeholder syntax described in Table 27-2.
For example, you might use this query for a findByPrimaryKey method:
select @firstName, @lastName, @ts from {call sp_select_CustomerProcs @primaryKey}
