Constructs a query of an SQL database and renders its output parameter once for each of a specified range of the rows returned by the database query.

Required Input Parameters

You must specify one of the following sets of 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)"/>

When you specify an SQL parameter that should not be treated as a string or VARCHAR, 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"/>

Optional Input Parameters

howMany

The number of array elements to display. If howMany is greater than the number of array elements, SQLQueryRange renders the number of elements available.

start

The initial array element, where a setting of 1 (the default) specifies the array’s first element. If start is greater than the number of elements in the array, the empty parameter (if specified) is rendered.

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.

The following example sorts 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, prepend the sortProperties value with a plus + or minus – sign to specify ascending or descending sort order.

The following example sorts an output array of Strings in alphabetical order:

<dsp:param name="sortProperties" value="+"/>

When the items to sort are stored in a Map or Dictionary, use the following syntax:

{+|-}_name

where name is the key or Dictionary name.

If this example involved a Map property, id is a key of the Map indicated in the array attribute. All IDs associated with the id key are returned in descending order.

A nested servlet bean inherits the parent servlet bean’s sortProperties setting, unless the nested servlet bean has its own sortProperties setting. For example, the following setting negates any parent sortProperties setting:

<dsp:param name="sortProperties" value=""/>

Output Parameters

index

The zero-based index of the returned row.

count

The one-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 zero-based index of the returned column.

hasPrev

Set to true before any output parameters is rendered. It indicates whether there are any array items before the current array set.

prevStart

Set before any output parameters are rendered, and only if hasPrev is true, 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

Set before any output parameters are rendered, and only if hasPrev is true, indicates the (one-based) count of the last element in the previous array set.

prevHowMany

Set before any output parameters are rendered, and only if hasPrev is true, indicates the number of elements in the previous array set.

hasNext

Set to true before any output parameters are rendered, indicates whether there are any array items after the current array set.

nextStart

Set before any output parameters are rendered, and only if hasNext is true, indicates the value of start that should be used to display the next array set.

nextEnd

Set before any output parameters are rendered, and only if hasNext is true, indicates the (one-based) count of the last element in the next array set.

nextHowMany

Set before any output parameters are rendered, and only if hasNext is true, indicates the number of elements in the next array set.

Open Parameters

outputStart

Rendered before any output tags if the array is not empty.

outputEnd

Rendered after all output tags if the array is not empty.

output

Rendered once for each array element.

empty

Rendered if the array contains no elements.

error

Rendered if there is an error when the query executes. Errors are accessed by this parameter as SQLException objects.

Usage Notes

SQLQueryRange executes an SQL query and renders its output open parameter for a selected subset of the elements returned by the query. You can specify a query in two ways:

Example

SQLQueryRange 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.

The following 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 and Previous 3 and Next 5 links (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>