ClassName |
|
---|---|
Component |
|
The SQLQueryForEach
servlet bean executes a SQL query and renders its output
open parameter once for each element returned by the query. There are several ways to specify a query:
Use the
queryBean
parameter to specify aSQLQuery
bean, which encapsulates the query and the data source.Specify the query directly with the
querySQL
input parameter, and identify the data source either by specifying theconnectionURL
parameter or by specifying thedataSource
andtransactionManager
parameters.
You might find the RQLQueryForEach
bean a better alternative to this bean because it provides enhanced performance and scalability by using ATG’s caching capabilities. See RQLQueryForEach for details.
Input Parameters
queryBean
Component of class atg.service.util.SQLQuery
that encapsulates the query and data source. For example:
<dsp:param name="queryBean" bean="/mySite/MySQLQuery"/>
querySQL
The SQL statement used to query the database. For example:
<dsp:param name="querySQL" value="select * from person
where age=:myAge(INTEGER)"/>
Note that when you specify a SQL parameter that should not be treated as a string or VARCHAR, you should specify the parameter’s SQL data type in parentheses, following the parameter name, as in the above example.
If you specify the querySQL
parameter, you must also supply either the connectionURL
parameter or the dataSource
parameter.
connectionURL
Identifies the database to perform the query against. For example:
<dsp:param name="connectionURL" value="jdbc:atgpool:ConnectionPool"/>
dataSource
Identifies the Java Transaction DataSource to perform the query against. For example:
<dsp:param name="dataSource"
bean="/atg/dynamo/service/jdbc/JTDataSource"/>
If you specify the dataSource
parameter, you must also supply the transactionManager
parameter.
transactionManager
Identifies the Transaction Manager for the DataSource. For example:
<dsp:param name="transactionManager"
bean="/atg/dynamo/transaction/TransactionManager"/>
sortProperties
A string that specifies how to sort the list of items in the output array. Sorting can be performed on properties of JavaBeans, Dynamic Beans, or on Dates, Numbers, or Strings.
To sort JavaBeans, specify the value of sortProperties
as a comma-separated list of property names. The first name specifies the primary sort, the second specifies the secondary sort, and so on. If the first character of each keyword is a +
, this sort is performed in ascending order. If it has a , it is performed in descending order.
Example: To sort an output array of JavaBeans first alphabetically by title
property and second in descending order of the size
property:
<dsp:param name="sortProperties" value="+title,
size"/>
To sort Dates, Numbers, or Strings, specify the value of sortProperties
with either a single +
or a single to indicate ascending or descending order respectively.
Example: To sort an output array of Strings in alphabetical order:
<dsp:param name="sortProperties" value="+"/>
When the items you want sorted are stored in a Map or Dictionary, use a slightly different syntax. Set the value equal to the ascending/descending indicator, an underscore, and then the key or Dictionary name. For example:
<dsp:param name="sortProperties" value="
_id"/>
If this example involved a Map property, id
would be a key of the Map indicated in the array
attribute. All IDs associated with the id
key would be returned in descending order.
When one servlet bean is nested in another and both accept the sortProperties
parameter, the child servlet bean inherits the sortProperties
setting applied by the enclosing servlet bean unless the child specifies another sortProperties
setting or is assigned an empty value:
<dsp:param name="sortProperties" value=""/>
Output Parameters
index
The 0-based index of the returned row.
count
The 1-based number of the returned row.
element
A dynamic bean that has properties for accessing the values returned in the result set. You can access the values either by name or by column index. The name refers to either the column name or the column alias if one is used in the query. To access values by name, use element.name
. To access values by column index, use element.column[i]
where i
is the 0-based index of the returned column.
Open Parameters
The following open parameters control the formatting for the returned results:
outputStart
This parameter is rendered before any output tags if the array is not empty.
outputEnd
This parameter is rendered after all output tags if the array is not empty.
output
This parameter is rendered once for each element in the array.
empty
This optional parameter is rendered if the array contains no elements.
error
This optional parameter can be rendered if there is an error when the query executes. Errors are accessed by this parameter as SQLException
objects.
Example
This example displays the number of votes each mountain received. This is done by counting the number of rows in the MOUNTAINVOTE table where the mountain
column is the same as the mountain.name
parameter.
<dsp:droplet name="/atg/dynamo/droplet/SQLQueryForEach"> <dsp:param name="dataSource" bean="/atg/dynamo/service/jdbc/JTDataSource"/> <dsp:param name="transactionManager" bean="/atg/dynamo/transaction/TransactionManager"/> <dsp:param name="querySQL" value="select count(*) from MOUNTAINVOTE where mountain=:mountain.name"/> <dsp:oparam name="output"> <!-- number="#" prevents us from seeing the decimal of count since some DB's return floating point from the query --> <dsp:valueof param="element.column[0]" number="#"></dsp:valueof> </dsp:oparam> </dsp:droplet>