<x:select>

An x:select action is used to execute a query in a relational database. If the query produced a non-empty result set, its rows are cached in memory on the application server machine. One of these rows, initially the first one, is always considered the current row.

Use of this action creates a DBSelect bean which you can access using the variable whose name is specified in the id attribute

JSP actions are provided to make use of the basic function of a DBSelect bean. You can use x:nextRow and x:previousRow to position to other rows in the result set. You can use the the x:updateRow, x:deleteRow, and x:insertRow actions to change rows in the result set without writing additional SQL statements. Using the x:getColumn and x:setColumn actions you can access column values in result sets. If the SQL statement has parameters, you can access them using the x:getParameter and x:parameter actions.

All of the methods of a DBSelect bean can also be executed using scriptlets, and all of its properties can be accessed with the standard jsp:getProperty and jsp:setProperty actions.

 

Syntax

<x:select    id = "name"
        scope = "page|request|session"    
        connectionSpec = "connectionSpec_id"
        connectionSpecRef = "connectionSpecRef"
        maxFieldSize = "numberOfBytes"
        maxRows = "numberOfRows"
        distinctTypesEnabled = "true|false"
        timeOut = "seconds"
        readOnly = "true|false"
        nullToken = "string"
        trace = "true|false" >
    <x:sql>
    </x:sql>
    <x:parameter
        parmName = "name" 
        position = "number" 
        type = "sqltype"
        mode = "in|out|inout" 
        value = "parmValue" />
</x:select>

The x:select action has the following attributes:

id

The name used to identify a DBSelect bean in the specified namespace, and also its scripting variable name. The name specified is case sensitive and must conform to the current scripting language variable naming conventions.

scope

The scope within which the object is defined. The default value is page.

connectionSpec

The identifier of an x:dataSourceSpec or x:driverManagerSpec action. The value is case sensitive and must match a connectionSpec action that is in scope.

The referenced action provides the information needed to make a connection to the database. Omit this attribute if the action occurs inside the body of a x:batch action, in which case the connectionSpec action identified in the x:batch action will be used. If you specify it in both places, it will be flagged as an error.

connectionSpecRef

The reference to a DBConnectionSpec object to be used to obtain the information needed to make a connection.

Either connectionSpec or connectionSpecRef can be specified, but not both.

maxFieldSize

Specifies the maximum size in bytes of any column in the result set. A value of 0 means that there is no maximum. The default value is 0.

maxRows

Specifies the maximum number of rows that can be fetched into the cache. A value of 0 means that there is no maximum. The default value is 0.

distinctTypesEnabled

Whether to enable inserts, updates, and deletes for result sets which contain user-defined (distinct) types. The default value is False.

timeOut

The maximum number of seconds allowed for the statement to execute. The default value is 0, which means no maximum.

readOnly

Specifies whether updates to the data are allowed. The value true means that updates are disallowed even if the database manager would permit them. The value false means that updates are allowed, provided that the database manager permits them. The default value is false.

nullToken

Specifies a case-sensitive String value that will be used to represent the database null value. This attribute is optional. It serves as a default value for the same attribute on any action that gets or sets column or parameter values on this Select. If the attribute is omitted in both places, the default is &empty (&empty is a special value, similar to an HTML character entity, recognized in this attribute as representing an empty String.) See Null values for more on null value representation.

trace

Whether to enable tracing of database actions. The default value is false.

The x:select action can have the following tags in its body:

<x:sql>

This specifies the SQL statement to execute.

<x:parameter>

This specifies a value and meta-data, for a parameter in the SQL statement. There must be a one-to-one correspondence between x:parameter tags and parameters in the SQL statement.

 

Examples

The following action creates a DBSelect bean with the name select1 whose scope is the session. It connects to the database using the information in conn1, and executes a Select statement whose result set contains employee numbers and names from the EMPLOYEE table. The Select contains no parameters (from a database standpoint), but does have user input from the request object substituted into it.

<x:select id="select2" scope="session" connectionSpec="conn1">
    <x:sql>
        SELECT EMPNO, EMPNAME FROM EMPLOYEE 
        WHERE WORKDEPT = ?
    </x:sql>
    <x:parameter position="1" type="VARCHAR"
           value='<%= request.getParameter("department") %>' />
</x:select>
    

The following action creates a DBSelect bean with the name select3. This example is similar to the preceding one, but the parameter is identified by name rather than by position.

<x:select id="select3" scope="session" connectionSpec="conn1">
    <x:sql>
        SELECT EMPNO, EMPNAME FROM EMPLOYEE 
        WHERE WORKDEPT = :dept
    </x:sql>
    <x:parameter parmName="dept" type="VARCHAR"
           value='<%= request.getParameter("department") %>' />
</x:select>

The following actions iterate over the rows in the result set from either of the preceding examples, writing the column values in the request output using the x:getColumn action. See <x:repeat> for details on the x:repeat action and <x:getColumn> for details on the x:getColumn action.

<table border="1" width="300" height="100"><tbody>
    <tr> <td>Employee number:</td> <td>Name:</td> </tr>
    <x:repeat name="select1" over="rows">
        <tr>
            <x:repeat over="columns">
                <td><x:getColumn /></td>
            </x:repeat>
        </tr>
    </x:repeat>

The following actions iterate over the rows in the result set, writing the column values in the request output using the x:getColumn action. See <x:repeat> for details on the x:repeat action and <x:getColumn> for details on the x:getColumn action.

<table border="1" width="300" height="100"><tbody>
    <tr> <td>Employee number:</td> <td>Name:</td> </tr>
    <x:repeat name="select1" over="rows">
        <tr>
            <td><x:getColumn colName="EMPNO"/></td>
            <td><x:getColumn colName="EMPNAME"/></td>
        </tr>
    </x:repeat></tbody></table>