<%-- store your connection info in the web.xml file --%> <sql:connection id="conn1"> <sql:url initParameter="dbURL"/> <sql:driver initParameter="mysqlDriver"/> <sql:userId initParameter="dbUserId"/> <sql:password initParameter="dbPassword"/> </sql:connection> 2. 使用數據源
Tag 參考 connection Availability: 1.0 Get a java.sql.Connection object from the DriverManager or a DataSource.
Tag Body JSP Restrictions None Attributes Name Required Runtime EXPression Evaluation Availability id yes no 1.0 Name of the resulting Connection attribute.
dataSource no no 1.0 Name of an existing page attribute that contains a DataSource object.
jndiName no no 1.0 Name used to find a datasource via jndi.
Variables Name Scope Availability id attribute value End of tag to end of page 1.0
Properties Name Get Set Availability catalog yes yes 1.0 Set the catalog for this connection.
closed yes no 1.0 False if the connection is open, true if it is not.
readOnly yes yes 1.0 True if the connection has read-only permission.
<%-- open a database connection --%> <sql:connection id="conn1" dataSource="ds1"> <%-- optional --%> <sql:userId>root</sql:userId> <%-- optional --%> <sql:password>notVerySecure</sql:password> </sql:connection>
Method 3: using a jndi named DataSource
<%-- open a database connection --%> <sql:connection id="conn1" jndiName="java:/comp/jdbc/test"/>
url Availability: 1.0 Sets the database URL of the enclosing connection tag.
Tag Body JSP Restrictions Use inside a connection tag. Attributes Name Required Runtime Expression Evaluation Availability initParameter no no 1.0 Optional attribute, indicating the name of an init parameter
Variables None Examples
<%-- example 1: using the tag body --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> </sql:connection> <%-- example 2: using an init parameter --%> <sql:connection id="conn1"> <sql:url initParameter="dbURL"/> </sql:connection>
jndiName Availability: 1.0 Sets the JNDI named JDBC DataSource of the enclosing connection tag.
Tag Body JSP Restrictions Use inside a connection tag. Attributes Name Required Runtime Expression Evaluation Availability initParameter no no 1.0 Optional attribute, indicating the name of an init parameter
Variables None Examples
<%-- example 1: using the tag body --%> <sql:connection id="conn1"> <sql:jndiName>java:/comp/jdbc/test</sql:jndiName> </sql:connection> <%-- example 2: using an init parameter --%> <sql:connection id="conn1"> <sql:jndiName initParameter="jndiDataSource"/> </sql:connection>
driver Availability: 1.0 Sets the driver class name for the connection tag.
Tag Body JSP Restrictions Use inside a connection tag. Attributes Name Required Runtime Expression Evaluation Availability initParameter no no 1.0 Optional attribute, indicating the name of an init parameter.
Variables None Examples
<%-- example 1: using the tag body --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> </sql:connection> <%-- example 2: using an init parameter --%> <sql:connection id="conn1"> <sql:url initParameter="dbURL"/> <sql:driver initParameter="dbDriver"/> </sql:connection>
userId Availability: 1.0 Sets the user id for the connection tag.
Tag Body JSP Restrictions Use inside a connection tag. Attributes Name Required Runtime Expression Evaluation Availability initParameter no no 1.0 Optional attribute, indicating the name of an init parameter.
Variables None Examples
<%-- example 1: using the tag body --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> <sql:userId>root</sql:userId> </sql:connection> <%-- example 2: using an init parameter --%> <sql:connection id="conn1"> <sql:url initParameter="dbURL"/> <sql:userId initParameter="dbUserId"/> </sql:connection>
password Availability: 1.0 Sets the password for the connection tag.
Tag Body JSP Restrictions Use inside a connection tag. Attributes Name Required Runtime Expression Evaluation Availability initParameter no no 1.0 Optional attribute, indicating the name of an init parameter.
Variables None Examples
<%-- example 1: using the tag body --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> <sql:userId>root</sql:userId> <sql:password>notVerySecure</sql:password> </sql:connection> <%-- example 2: using an init parameter --%> <sql:connection id="conn1"> <sql:url initParameter="dbURL"/> <sql:userId initParameter="dbUserId"/> <sql:password initParameter="dbPassword"/> </sql:connection>
closeConnection Availability: 1.0 Close the specified connection. The "conn" attribute is the name of a connection object in the page context.
Tag Body empty Restrictions None Attributes Name Required Runtime Expression Evaluation Availability conn yes no 1.0 Id of the connection you want to close.
Variables None Examples
<%-- open a database connection --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> <sql:userId>root</sql:userId> <sql:password>notVerySecure</sql:password> </sql:connection> <%-- statement tags go here --%> <sql:closeConnection conn="conn1"/>
statement Availability: 1.0 Create and execute a database query.
Tag Body JSP Restrictions None Attributes Name Required Runtime Expression Evaluation Availability id yes no 1.0 Script variable id for use with standard jsp:getProperty tag.
conn yes no 1.0 id of the connection to use
Variables Name Scope Availability id attribute value Nested within tag 1.0
Properties Name Get Set Availability fetchSize yes yes 1.0 the number of rows that should be fetched from the database when more rows are needed
maxRows yes yes 1.0 the maximum number of rows that a ResultSet object can contain (handy!)
queryTimeout yes yes 1.0 the number of seconds the driver will wait for a Statement object to execute
Examples
<%-- insert a row into the database --%> <sql:statement id="stmt1" conn="conn1"> <%-- set the SQL query --%> <sql:query> insert into test_books (id, name) values (3, ´<sql:escapeSql><%= request.getParameter("book_title") %></sql:escapeSql>´) </sql:query> <%-- execute the query --%> <sql:execute/> </sql:statement>
escapeSql Availability: 1.0 Replaces each single quote in the tag body with a pair of single quotes.
Tag Body JSP Restrictions Use inside a query tag. Attributes None Variables None Examples
<%-- insert a row into the database --%> <sql:statement id="stmt1" conn="conn1"> <%-- set the SQL query --%> <sql:query> insert into test_books (id, name) values (3, ´<sql:escapeSql><%=request.getParameter("book_title")%></sql:escapeSql>´) </sql:query> <%-- execute the query --%> <sql:execute/> </sql:statement>
query Availability: 1.0 Set a query for a statement or preparedStatement tag
Tag Body JSP Restrictions Use inside a statement or preparedStatement tag. Attributes None Variables None Examples
<%-- insert a row into the database --%> <sql:statement id="stmt1" conn="conn1"> <%-- set the SQL query --%> <sql:query> insert into test_books (id, name) values (3, ´<sql:escapeSql><%=request.getParameter("book_title")%></sql:escapeSql>´) </sql:query> <%-- execute the query --%> <sql:execute/> </sql:statement>
execute Availability: 1.0 Executes an insert, update or delete for a statement or preparedStatement tag
Tag Body JSP Restrictions Use inside a statement or preparedStatement tag. Attributes None Variables None Examples
<%-- insert a row into the database --%> <sql:statement id="stmt1" conn="conn1"> <%-- set the SQL query --%> <sql:query> insert into test_books (id, name) values (3, ´<sql:escapeSql><%=request.getParameter("book_title")%></sql:escapeSql>´) </sql:query> <%-- execute the query --%> <sql:execute/> </sql:statement>
preparedStatement Availability: 1.0 Create and execute a tokenized database query
Tag Body JSP Restrictions The scipt variable is not available until after the query tag is called. Attributes Name Required Runtime Expression Evaluation Availability id yes no 1.0 Script variable id
conn yes no 1.0 id of the connection to use
Variables Name Scope Availability id attribute value Nested within tag 1.0
Properties Name Get Set Availability fetchSize yes yes 1.0 the number of rows that should be fetched from the database when more rows are needed
maxRows yes yes 1.0 the maximum number of rows that a ResultSet object can contain (handy!)
queryTimeout yes yes 1.0 the number of seconds the driver will wait for a Statement object to execute
Examples
<%-- insert a row into the database --%> <sql:preparedStatement id="stmt1" conn="conn1"> <sql:query> insert into test_books (id, name) values (?, ?) </sql:query> <sql:execute> <sql:setColumn position="1">3</sql:setColumn> <sql:setColumn position="2"><%=request.getParameter("book_title")%></sql:setColumn> </sql:execute> </sql:preparedStatement>
setColumn Availability: 1.0 Set a field in a preparedStatement. Set the value as a String inside the tag body.
Tag Body JSP Restrictions Use within the preparedStatement tag Attributes Name Required Runtime Expression Evaluation Availability position yes no 1.0 Column position
Variables None Examples
<%-- use the tag body --%> <sql:setColumn position="1"><%= someValue %></sql:setColumn>
resultSet Availability: 1.0 JSP tag resulset, executes the query and loops through the results for the enclosing statement or preparedstatement tag. The body of this tag is executed once per row in the resultset. The optional "loop" attribute, which default to true, specifies whether to execute the tag body once per row "true", or to simply assign the ResultSet to the page attribute specified by "id".
Tag Body JSP Restrictions If a name attribute is not supplied, use within a statement or preparedStatement and after a query. If a name attribute is supplied, there are no restrictions. Attributes Name Required Runtime Expression Evaluation Availability id yes no 1.0 Script variable id
loop no no 1.0 True: execute the tag body once per row in the result set, automatically advancing the rows. False: execute the tag body once.
name no no 1.0 Name of an attribute containing a ResultSet object. If you pull a ResultSet object from an attribute, it is not necessary to place this tag inside of a statement.
scope no no 1.0 Scope (page, request, session, or application) to search for the ResultSet attribute indicated in the "name" attribute. If this is not supplied, we use the default findAttribute() behaviour.
Variables Name Scope Availability id attribute value Nested within tag 1.0
Properties Name Get Set Availability fetchSize yes yes 1.0 the number of rows that should be fetched from the database when more rows are needed
Examples
<%-- open a database query --%> <table> <sql:statement id="stmt1" conn="conn1"> <sql:query> select id, name, description from test_books order by 1 </sql:query> <%-- loop through the rows of your query --%> <sql:resultSet id="rset2"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3"/> <sql:wasNull>[no description]</sql:wasNull></td> </tr> </sql:resultSet> </sql:statement> </table>
wasNull Availability: 1.0 Executes its body if the last getColumn tag received a null value from the database. You must be inside a resultset tag and there must be a previous getColumn tag, or an error will be generated.
Tag Body JSP Restrictions Must be used following a getColumn tag. Attributes None Variables None Examples
<%-- open a database query --%> <table> <sql:statement id="stmt1" conn="conn1"> <sql:query> select id, name, description from test_books order by 1 </sql:query> <%-- loop through the rows of your query --%> <sql:resultSet id="rset2"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3"/> <sql:wasNull>[no description]</sql:wasNull></td> </tr> </sql:resultSet> </sql:statement>
wasNotNull Availability: 1.0 Executes its body if the last getColumn tag did not encounter a null value from the database.
Tag Body JSP Restrictions Must be used following a getColumn tag. Attributes None Variables None Examples
<%-- open a database query --%> <table> <sql:statement id="stmt1" conn="conn1"> <sql:query> select id, name, description from test_books order by 1 </sql:query> <%-- loop through the rows of your query --%> <sql:resultSet id="rset2"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3" to="description"/> <sql:wasNotNull>Description: <%= pageContext.getAttribute("description") %></sql:wasNotNull></td> </tr> </sql:resultSet> </sql:statement>
getColumn Availability: 1.0 Gets the value, as a String, of a coulmn in the enclosing resultset. The column number is set via the "position" attribute. You can optionally set the value, as a String, to a serlvet attribute instead of the tag body with the "to" attribute. The scope of the servlet attribute is specified by the "scope" XML attribute (default = page).
Tag Body empty Restrictions Use within the resultSet tag Attributes Name Required Runtime Expression Evaluation Availability position no no 1.0 Column position
colName no no 1.0 Column name
to no no 1.0 Optionally assign the String to an attribute rather than the JSP output.
scope no no 1.0 Optionally change the scope of the attribute designated in "to" (default = page).
Variables None Examples
<%-- output to the JSP directly --%> <sql:getColumn position="1"/>
getNumber Availability: 1.0 Similar to getColumn, but provides more precise control over number formatting. The "format" attribute can be either a pattern as accepted by the DecimalFormat constrUCtor or a style: "CURRENCY", "PERCENT" or "NUMBER". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.
Tag Body empty Restrictions Use within the resultSet tag Attributes Name Required Runtime Expression Evaluation Availability position no no 1.0 Column position
colName no no 1.0 Column name
to no no 1.0 Optionally assign the String to an attribute rather than the JSP output.
scope no no 1.0 Optionally change the scope of the attribute designated in "to" (default = page).
locale no yes 1.0 Format according to a particular locale.
format no yes 1.0 Specify a format for the number.
Variables None Examples
<%-- format a database value as English currency --%> <sql:getNumber colName="id" format="CURRENCY" locale="en_GB"/>
getTime Availability: 1.0 Similar to getColumn, but provides more precise control over java.sql.Time formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.
Tag Body empty Restrictions Use within the resultSet tag Attributes Name Required Runtime Expression Evaluation Availability position no no 1.0 Column position
colName no no 1.0 Column name
to no no 1.0 Optionally assign the String to an attribute rather than the JSP output.
scope no no 1.0 Optionally change the scope of the attribute designated in "to" (default = page).
locale no yes 1.0 Format according to a particular locale.
format no yes 1.0 Specify a format for the time.
Variables None Examples
<sql:getTime colName="time"/>
getTimestamp Availability: 1.0 Similar to getColumn, but provides more precise control over java.sql.Timestamp formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_". If neither the format nor locale attribute is set, output should be identical to getColumn.
Tag Body empty Restrictions Use within the resultSet tag Attributes Name Required Runtime Expression Evaluation Availability position no no 1.0 Column position
colName no no 1.0 Column name
to no no 1.0 Optionally assign the String to an attribute rather than the JSP output.
scope no no 1.0 Optionally change the scope of the attribute designated in "to" (default = page).
locale no yes 1.0 Format according to a particular locale.
format no yes 1.0 Specify a format for the timestamp.
Variables None Examples
<sql:getTimestamp colName="time"/>
getDate Availability: 1.0 Similar to getColumn, but provides more precise control over java.sql.Date formatting. The "format" attribute can be either a pattern as accepted by SimpleDateFormat or a style: "FULL", "LONG", "MEDIUM" or "SHORT". It is required. The "locale" attribute can have one to three components as accepted by the Locale constructor: language, country and variant. They are separated by "_".
Tag Body empty Restrictions Use within the resultSet tag Attributes Name Required Runtime Expression Evaluation Availability position no no 1.0 Column position
colName no no 1.0 Column name
to no no 1.0 Optionally assign the String to an attribute rather than the JSP output.
scope no no 1.0 Optionally change the scope of the attribute designated in "to" (default = page).
locale no yes 1.0 Format according to a particular locale.
format no yes 1.0 Specify a format for the date.
Variables None Examples
<sql:getDate colName="time" format="FULL"/>
wasEmpty Availability: 1.0 Executes its body if the last ResultSet tag received 0 rows from the database. You must be after a ResultSet tag, or an error will be generated.
Tag Body JSP Restrictions Use after a ResultSet tag. Attributes None Variables None Examples
<%-- showing the contents of the table --%> <table> <tr><th>id</th><th>name</th><th>description</th></tr> <sql:preparedStatement id="stmt6" conn="conn1"> <sql:query> select id, name, description from test_books </sql:query> <sql:resultSet id="rset4"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3" to="description"/></td> </tr> </sql:resultSet> <tr> <td colspan="3"> <%-- show different text, depending on whether or not any rows were retrieved --%> <sql:wasEmpty>No rows retrieved.</sql:wasEmpty> <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty> </td> </tr> </sql:preparedStatement>
wasNotEmpty Availability: 1.0 Executes its body if the last ResultSet tag received more than 0 rows from the database. You must be after a ResultSet tag, or an error will be generated.
Tag Body JSP Restrictions Use after a ResultSet tag. Attributes None Variables None Examples
<%-- showing the contents of the table --%> <table> <tr><th>id</th><th>name</th><th>description</th></tr> <sql:preparedStatement id="stmt6" conn="conn1"> <sql:query> select id, name, description from test_books </sql:query> <sql:resultSet id="rset4"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3" to="description"/></td> </tr> </sql:resultSet> <tr> <td colspan="3"> <%-- show different text, depending on whether or not any rows were retrieved --%> <sql:wasEmpty>No rows retrieved.</sql:wasEmpty> <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty> </td> </tr> </sql:preparedStatement>
rowCount Availability: 1.0 Prints out the number of rows retrieved from the database. It can be used inside a ResultSet tag to provide a running count of rows retreived, or after the ResultSet tag to display the total number. Using the tag before the ResultSet will produce an error.
Tag Body empty Restrictions Use inside or after a ResultSet tag (not before). Attributes None Variables None Examples
<%-- showing the contents of the table --%> <table> <tr><th>id</th><th>name</th><th>description</th></tr> <sql:preparedStatement id="stmt6" conn="conn1"> <sql:query> select id, name, description from test_books </sql:query> <sql:resultSet id="rset4"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3" to="description"/></td> </tr> </sql:resultSet> <tr> <td colspan="3"> <%-- show different text, depending on whether or not any rows were retrieved --%> <sql:wasEmpty>No rows retrieved.</sql:wasEmpty> <sql:wasNotEmpty><sql:rowCount/> rows retrieved.</sql:wasNotEmpty> </td> </tr> </sql:preparedStatement>
Examples See the example application DBTags-examples.war for examples of the usage of the tags from this custom tag library.
Java Docs Java programmers can view the java class documentation for this tag library as javadocs.
Revision History Review the complete revision history of this tag library.
Developers´ Notes Last updated: 08/14/2001 <div id="developers-notes"> On the radar screen Here´s a list of what´s cooking with DBTags. If you´ve made a suggestion or contributed a patch that you think we´ve missed, send a note to taglibs-user@jakarta.apache.org.
To-do:
Add support for RowSets. (considering several contributions) [Update: Preliminary support has been added, see the history for details]
Under consideration:
ResultSet/RowSet "paging". (Ciot submitted some code to do this, which we plan to review and see if it´s a sufficiently general solution.)
On the back burner:
Connection management. There has been some discussion lately on if/how to terminate connections upon a JSP page error without making the usage too clunky. I think we´re still waiting for that spark of inspiration.
