A SQL named query lets you define precisely the SQL statement to be used in executing the query. This can also allow you to make use of database-specific keywords in your query. A SQL named query might be defined in a repository definition file like this:
<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>
There are several things to note here. First, the SQL string contained in the <sql> element must contain the actual SQL column and table names, not the property names defined by the repository. Second, the SQL string must include the ID from the primary table in the item-descriptor. If the ID for an item descriptor is defined as a composite ID (using two or more columns), then all of the columns that make up the composite ID must be selected.
The <returns> element is a comma-separated list of Repository property names that are returned by this query. These are property names so we can know the type of the column when we’re reading values from the returned ResultSet.
The <dependencies> element indicates which properties this query depends on. If any of the properties in the <dependencies> element are changed, then this query should be removed from the query cache. This is typically, if not always, the properties referenced in the WHERE clause of the SQL statement, if any.
The <input-parameter-types> element is a comma-separated list of class names that any parameters in the Query must be an instance of. There should be one value for each parameter, even if the classes used are similar; thus, if there are three String parameters in your Query, the <input-parameter-types> element must contain "java.lang.String, java.lang.String, java.lang.String". This tag serves two purposes. It is used primarily for type checking when a Query is executed. It is also used to indicate how many parameters there are in the Query.
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 and not multi-valued properties (unless they are composite IDs).
The property columns are returned in the order they appear in the <returns> tag. Since users do not have to define an explicit RepositoryPropertyDescriptor for the ID property, the ID property can be omitted from the <returns> element, but it still must exist in the SQL statement as the first column(s) selected.
The <returns> element is optional. Select statements do not have to return any other properties besides the ID. Again, the ID property should only appear in the <returns> element if that item descriptor explicitly defines it with the <property> element. Otherwise, the value in the id-column attribute of the <item-descriptor> tag is used as the name of the ID column.
