You can define a named query in an <item-descriptor> element in an SQL repository definition file. A named query is defined in a <named-query> element. For example, given the following RQL query:
lastName ENDS WITH "son"
you can define a named query as follows:
<item-descriptor name=...> ... <named-query> <rql-query> <query-name>myQuery</query-name> <rql>lastName ENDS WITH "son"</rql> </rql-query> </named-query> </item-descriptor>
An item descriptor can define named queries in three ways:
RQL Named Queries
An RQL named query can use all syntactical options that available to the Repository Query Language, including parameters and fields in parameters.
The RQL statement is defined in an <rql-query> element. For example:
<item-descriptor name=...> ... <named-query> <rql-query> <query-name>myQuery</query-name> <rql>name = ?0.name AND age = ?1.age</rql> </rql-query> </named-query> </item-descriptor>
SQL Named Queries
An SQL named query defines a query’s SQL statement in an <sql-query> element. SQL named queries can include database-specific keywords.
For example:
<item-descriptor name=...> ... <named-query> <sql-query> <query-name>myQuery</query-name> <sql> select id,first_name,last_name from dps_user WHERE login=? </sql> <returns>id,firstName,lastName</returns> <input-parameter-types>java.lang.String</input-parameter-types> <dependencies>login</dependencies> </sql-query> </named-query> ... </item-descriptor>
The following table describes the child elements that are valid within an <sql-query> element:
Element | Description |
|---|---|
| Contains the SQL column and table names (not the property names defined by the repository). The Caution: Named queries must be read-only. The SQL statement must not include actions that update the datastore—for example, |
| A comma-separated list of Repository property names returned by this query. These property names let you know the type of the column when reading values from the returned |
| Indicates which properties this query depends on. If any properties in the |
| A comma-separated list of classes that must be instantiated for query parameters. There must be one value for each parameter. For example, given three query String parameters, the
This tag serves two purposes. - Enables type checking during query execution . - Specifies the number of required query parameters. |
The properties that are used in the <returns> element must be:
Defined as readable in the repository.
Persistent properties defined in a table tag and not transient properties.
Single-valued (multi-valued properties are valid only if they are composite IDs).
The property columns are returned in the same order as specified in the <returns> tag. Because users do not need to define an explicit RepositoryPropertyDescriptor for the ID property, the ID property can be omitted from the <returns> element, but it must exist in the SQL statement as the first column(s) selected.
The <returns> element is optional: select statements can return only the ID property. The <returns> element should specify the ID property only if the item descriptor explicitly defines it with the <property> element. Otherwise, the value in the id-column-name attribute of the <table> tag is used as the name of the ID column.
Stored Procedures
An SQL named query can reference a stored procedure by qualifying the <sql> element with the attribute setting stored-procedure=true. In this case, the <returns> and <dependencies> tags must conform to the stored procedure’s returns and constraints. For example:
<item-descriptor name=...>
...
<named-query>
<sql-query>
<query-name>myQuery</query-name>
<sql stored-procedure="true">
{ call myStoredProcedure (?, ?) }
</sql>
<returns>id,firstName,lastName<returns>
<input-parameter-types>java.lang.String,java.lang.String
</input-parameter-types>
<dependencies>login</dependencies>
</sql-query>
</named-query>
</item-descriptor>The body of the <sql> tag for a stored procedure must use the syntax required by java.sql.CallableStatement. The following requirements apply:
Curly braces must enclose the
CallableStatement.A
CallableStatementtypically has two formats: for procedures, which do not have an explicit return value, and one for functions, which do. Non-Oracle stored procedures in the SQL repository must useproceduresyntax, as in the previous example.Question (
?) marks indicate parameters. In stored procedures, parameters can be one of the following:IN: Values go in the database.OUT: Values come out of the database.INOUT: Values can go in and come out.
The SQL repository supports only
INparameters.OUTandINOUTparameters are valid only for Oracle stored procedures.
A stored procedure must return a java.sql.ResultSet. Most JDBC drivers do so by default, but there are exceptions. Oracle, for example, requires some special tuning, as described in the next section.
Caution: The stored procedure must be read-only; it must not include actions that update the datastore—for example, INSERT or DELETE. Doing so can yield unpredictable results.
Using Stored Procedures with Oracle Databases
An Oracle stored procedure returns a ResultSet only if it is defined to do so (see Returning a JDBC result set from an Oracle stored procedure). The body of the <sql> element must reference the stored procedure with the function syntax,. For example:
{ ? = call myOracleProcedure (?, ?) }
You indicate the returned value through this notation:
? =
Note: You should consider wrapping existing stored procedures with procedures so the results are formatted to conform with Oracle Commerce Platform SQL repository requirements.
