ClassName |
|
---|---|
Component |
|
The SQLQueryRange
servlet bean executes a SQL query and renders its output
open parameter for a selected subset of the elements 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.
Input Parameters
queryBean
Component of class atg.service.util.SQLQuery
that encapsulates the query and data source. For example:
<dsp:param name="queryBean" beanvalue="/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:
<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"/>
start
Specifies which element of the array to start with. Setting start=1
renders the array beginning with its first element. If start
is greater than the number of elements in the array and the empty
output parameter is supplied in your implementation, the empty
parameter is rendered.
howMany
Specifies the number of items in the array set to display. If the combination of start
and howMany
point past the end of the array, the ATG platform stops rendering after the end of the array is reached.
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.
hasPrev
This parameter is set to true before any of the output parameters is rendered. It indicates whether there are any array items before the current array set.
prevStart
This parameter is set before any of the output parameters are rendered, and only if hasPrev
is true. It indicates the value of start that should be used to display the previous array set. You can use this parameter to create a link or form submission that displays the previous elements of the array.
prevEnd
This parameter is set before any of the output parameters are rendered, and only if hasPrev
is true. It indicates the (1-based) count of the last element in the previous array set.
prevHowMany
This parameter is set before any of the output parameters are rendered, and only if hasPrev
is true. It indicates the number of elements in the previous array set.
hasNext
This parameter is set to true before any of the output parameters are rendered. It indicates whether there are any array items after the current array set.
nextStart
This parameter is set before any of the output parameters are rendered, and only if hasNext
is true. It indicates the value of start
that should be used to display the next array set.
nextEnd
This parameter is set before any of the output parameters are rendered, and only if hasNext
is true. It indicates the (1-based) count of the last element in the next array set.
nextHowMany
This parameter is set before any of the output parameters are rendered, and only if hasNext
is true. It indicates the number of elements in the next array set.
Open Parameters
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 SQLQueryRange
servlet bean queries a database that includes entries for people listed in a table named SKIER. It renders the skiers in the table, five at a time, as specified by the howMany
parameter.
This example uses Switch
servlet beans, together with the hasPrev
and hasNext
parameters, to render links to pages that display entries from the SKIER table that are before or after the five entries specified by the range. So, if the startIndex
parameter is 4, this example displays skiers 4 – 9, together with links that read Previous 3
and Next 5
(assuming the SKIER table has at least 14 rows).
<dsp:droplet name="SQLQueryRange"> <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 * from SKIER order by name"/> <dsp:param name="start" value="startIndex 1"/> <dsp:param name="howMany" value="5"/> <!-- SQLQueryRange renders this "output" parameter once for each row in the SKIER table, 5 skiers at a time. The startIndex parameter is passed in as an HTML query parameter using the "prevStart" and "nextStart" parameters set by SQLQueryRange. --> <dsp:oparam name="outputStart"> <dsp:droplet name="Switch"> <dsp:param name="value" param="hasPrev"/> <dsp:oparam name="true"> <tr><td colspan=5> <dsp:a href="listPeople.jsp"> <dsp:param name="startIndex" param="prevStart"/> Previous <dsp:valueof param="prevHowMany"></dsp:valueof> </dsp:a> </td></tr> </dsp:oparam> </dsp:droplet> </dsp:oparam> <dsp:oparam name="output"> <dsp:include src="displayPerson.jsp"> <dsp:param name="person" param="element"/> </dsp:include> </dsp:oparam> <dsp:oparam name="outputEnd"> <dsp:droplet name="Switch"> <dsp:param name="value" param="hasNext"/> <dsp:oparam name="true"> <tr><td colspan=5> <dsp:a href="listPeople.jsp"> <dsp:param name="startIndex" param="nextStart"/> Next <dsp:valueof param="nextHowMany"></dsp:valueof> </dsp:a> </td></tr> </dsp:oparam> </dsp:droplet> </dsp:oparam> </dsp:droplet>