An SQL named query lets you define precisely the SQL statement to use in executing the query. This can also allow you to make use of database-specific keywords in your query. An SQL named query might be defined in a repository definition file like this:

<item-descriptor name=...>
      select id,first_name,last_name from dps_user WHERE login=?

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), all columns in 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 property names let you know the type of the column when reading values from the returned ResultSet.

The <dependencies> element indicates which properties this query depends on. If any properties in the <dependencies> element are changed, 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:

The property columns are returned in the order they appear 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 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-name attribute of the <table> tag is used as the name of the ID column.

loading table of contents...