DBTags 自定义标签库用于读写SQL数据库。
JSP 需求
这个需要一个支持JSP规范1.2版本以上的servlet引擎。
虽然它也可以工作于某些如tomcat这样的JSP 1.1 版引擎，但是不能工作在如Weblogic 这样的引擎上。它是根据JSP 1.2 规范设计的，需要<jsp:getProperty ... /> 标签:
jsp:setProperty 和 jsp:getProperty 中的name属性的值是通过pageContext 对象的findAttribute()方法得到的对象.JSP 1.1 规范不要求这个行为而tomcat碰巧支持而Weblogic 不支持。也有相当直接的方法为Weblogic 的用户写一个自定义标签效仿这个行为。已经有现成的范例可以从 这里得到。
DBTags 需求
DBTags 库支持数据源，而这不是Java 2 标准版的一部分。为了能使用数据库，要么使用J2EE,或者下载JDBC 2.0 Optional API 。
使用下面步骤使你的web应用可以使用这个标签库:
拷贝标签库的描述文件dbtags.tld 到你的web应用的 /WEB-INF 子目录下拷贝标签库的 JAR 文件到应用的 /WEB-INF/lib 子目录下。在/WEB-INF/web.xml 下增加如下内容:: <taglib> <taglib-uri>http://jakarta.apache.org/taglibs/dbtags</taglib-uri> <taglib-location>/WEB-INF/dbtags.tld</taglib-location> </taglib>在你的JSP 页面中使用这个标签库，在每页的顶部直接加上如下内容:
<%@标签lib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>"sql" 是你希望使用的标签前缀，你可以将它改为你喜欢使用的值。
下面是一个打印表中的书名的JSP页面源文件:
<%@标签lib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %> <%-- open a database connection --%> <sql:connection id="conn1"> <sql:url>jdbc:mysql://localhost/test</sql:url> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> </sql:connection> <%-- 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> <%-- close a database connection --%> <sql:closeConnection conn="conn1"/>下面是DBTags标签库的总体描述，标签的某些细节，例如connection, statement, resultSet, 和 preparedStatement 标签的所有可能的属性,在这里没有讨论。 Tag Reference 列出了所有的细节。
打开连接
有三种方式打开一个数据库连接:
1. 使用数据库 URL
connection标签可以接受一个数据库URL通过Driver Manager获得一个Connection:
<%-- open a database connection --%> <sql:connection id="conn1"> <%-- required --%> <sql:url>jdbc:mysql://localhost/test</sql:url> <%-- optional --%> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> <%-- optional --%> <sql:userId>root</sql:userId> <%-- optional --%> <sql:password>notVerySecure</sql:password> </sql:connection>"id"属性是必须的。在结束标签后，一个java.sql.Connection 对象被加为一个pageContext属性，可以被包括statement, preparedStatement, 和 closeConnection的其它的标签使用。
不在标签体内包含数据库URL, 驱动器名,用户id,或者口令，你可以使用可选属性"initParameter":
<%-- 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. 使用数据源
connection也可以接受一个指向Servlet属性的javax.sql.DataSource对象的引用。(这个属性是通过PageContext的findAttribute()方法得到的。):
<%-- open a database connection --%> <sql:connection id="conn1" dataSource="ds1"> <%-- optional --%> <sql:userId>root</sql:userId> <%-- optional --%> <sql:password>notVerySecure</sql:password> </sql:connection>3. 使用JNDI命名的JDBC数据源
Connection也可以接受一个使用JNDI命名的JDBC数据源。
<%-- open a database connection --%> <sql:connection id="conn1" jndiName="java:/comp/jdbc/test"/>关闭连接
将一个connection的引用传递到"closeConnection" 标签关闭一个连接:
<%-- 除非你使用自己的连接池，否则总应该关闭连接 --%> <sql:closeConnection conn="conn1"/>"Statements"是向数据库提交查询的一种方法。(另一个是使用 "preparedStatement"。) 基于statement查询的语法对任何知道SQL的人都是不陌生的。为了查询数据库，打开一个"statement"标签，传递给它一个sql "query", 然后要么对inserts, updates, 和 deletes "execute"申明，或者调用resultSet 标签在一个select申明的结果上循环执行。下面是一个简单的insert:
<%-- 向数据库插入一行 --%> <sql:statement id="stmt1" conn="conn1"> <%-- 设置SQL查询 --%> <sql:query> insert into test_books (id, name) values (3, '<sql:escapeSql><%=request.getParameter("book_title")%></sql:escapeSql>') </sql:query> <%-- 执行查询 --%> <sql:execute/> </sql:statement>转义SQL
"escapeSql"标签用在一个SQL查询里面转义输入的值里面可能的单引号。
错误处理
缺省情况下，SQL查询的执行导致的错误（例如主键violations（违例），残缺的SQL申明）将导致JSP页面的失败，你可以选择性的设置"execute"标签的"ignoreErrors"属性为"true"，这将使SQL错误打印到标准输出而不会终止页面:
<sql:statement id="stmt1" conn="conn1"> <%-- 这个SQL查询是残缺的 --%> <sql:query>delete * from test_books</sql:query> <%-- 查询将失败，但是页面会继续 --%> <sql:execute ignoreErrors="true"/> </sql:statement>空白处理
所有的statement和preparedStatement自动的去除空白。
"Prepared statements"是产生SQL查询的比较高级的形式。它不是直接将值插入SQL申明中，而是在需要设置值得地方放入一个'?'符号，然后使用一组独立的标签实际设置那些值。下面是statement中使用的范例的preparedstatement版本:
<%-- 向数据库插入一行 --%> <sql:preparedStatement id="stmt1" conn="conn1"> <%-- 设置SQL查询。注意"name"值上缺少引号 --%> <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>prepared statements的一个优点就是你不需要在文本上执行sql转义。然而，记住标准的statements对于那些没有连接池和prepared statements的数据库和驱动器在性能上更好。
setColumn标签
你可以将prepared statements的setColumn标签放置在execute或者 resultset标签的前面, 或者是execute标签的里面。execute标签永远不会输出它的内容（body），因此将setColumn标签放置在里面可以防止不必要的空白。
Resultset是一个select申明的结果。resultSet标签自动循环，每次一行。使用 "getColumn"标签从每行中提取值然后要么显示他们，要么将他们存为字符串:
<%--在一个HTML表格里面打印行 --%> <table> <sql:statement id="stmt1" conn="conn1"> <sql:query> select id, name, description from test_books order by 1 </sql: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"和"wasNotNull"标签
"wasNull"标签只有在前面的"getColumn"标签遇到一个数据库中的空值（null）时执行它的体中的内容。你只能在一个resultset内并且"getColumn"标签已经被执行时使用"wasNull"标签。"wasNotNull"标签在它前面的getColumn标签没有产生一个空值（null）使执行它的体中的内容。参看Tag参考获得范例。
"getColumn"标签
getColumn标签执行两个中的一个功能。你可以:
直接向JSP输出列值（缺省行为）
<%-- 向JSP输出值 --%> <sql:getColumn position="1"/>, 或者
将值作为一个String对象写为页面的一个属性，通过"to"属性。如果你愿意，你也可以为"scope"属性分配一个不同于"page"的值。如果数据库中该列的值为null, getColumn标签 将不会创建属性。下面是一个使用getColumn标签产生一个整型的请求属性:
<%-- 注意请求的属性将是一个String --%> <sql:getColumn position="1" to="someId" scope="request"/>"getNumber"标签
如果你想对数字格式有更多的控制，使用getNumber标签。
"format"属性可以是DecimalFormat构造方法可以接受的模式或者是下面的类型: "CURRENCY", "PERCENT" 或者 "NUMBER"。
"locale"属性可以有一到三个部分，也就是Locale构造方法可以接受的形式: 语言, 国家 和 变量。它们使用"_"分割。例如:
<%-- 格式化数据库值为英国货币形式 --%> <sql:getNumber colName="id" format="CURRENCY" locale="en_GB"/> 如果format和locale属性都没有设置，输出将和getColumn一样。
time标签
有几个标签是设计用来显示时间相关的数据的: getTime, getTimestamp 和 getDate。
"format"属性可以是被SimpleDateFormat接受的形式或者是一个类型: "FULL", "LONG", "MEDIUM" 或 "SHORT"。这个属性是可选的。
"locale"属性可以有一到三个部分，也就是Locale构造方法可以接受的形式: 语言, 国家 和 变量。它们使用"_"分割。
禁止循环
缺省情况下resultset标签对ResultSet中的每行循环执行。通过设置可选属性"loop"为"false"就可以禁止这个特性然后手工操作ResultSet对象或者将它传递给另外的自定义标签。
<sql:statement id="stmt1" conn="conn1"> <sql:query> select id, name, description from test_books order by 1 </sql:query> <%-- 禁止resultset标签的循环 --%> <sql:resultSet id="rset2" loop="false"> <% ResultSet rset = (ResultSet) pageContext.getAttribute("rset2"); // 手工操作 %> </sql:resultSet> </sql:statement>使用RowSets
你也可以用一个RowSet对象使用resultSet标签。通过设置选项"name",resultSet标签将查找一个ResultSet对象(包括RowSets)并将它以该名字存储在page, request, 或者session上下文上。通过设置可选属性"scope",你可以指定上下文来包含你的ResultSet/RowSet。注意当你从一个属性中读取一个ResultSet/RowSet，resultSet标签可以不在statement标签内。
<%-- 循环执行ResultSet/RowSet的每行,无论它来自何处 --%> <sql:resultSet id="rset1" name="rsetAtt"> <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>"wasEmpty"和"wasNotEmpty"标签
"wasEmpty"标签只有在上一个ResultSet标签从数据库中得到0行时执行它的体内的内容。它必须放在一个resultSet标签后否则将出错。"wasNotEmpty"标签在上一个ResultSet从数据库中得到了多于 0 行时执行体内的内容。参看Tag参考得到使用范例。
"rowCount"标签
"rowCount"标签打印数据库返回的行数。可以在ResultSet标签内使用它提供一个运行计数，或者在ResultSet标签后面使用打印总数。参看Tag 参考得到使用范例。在ResultSet前使用该标签将产生一个错误。
| connection | 从DriverManager或者DataSource得到一个java.sql.Connection对象。 |
| url | 在封闭的connection标签中设置数据库URL。 |
| jndiName | 在封闭的connection标签中设置JNDI命名的JDBC数据源。 |
| driver | 在封闭的connection标签中设置驱动器类名。 |
| userId | 设置connection标签的用户名。 |
| password | 设置connection标签的用户名口令。 |
| closeConnection | 关闭指定的连接。"conn"属性是该页上下文上的一个connection对象。 |
| statement | 创建并执行一个数据库查询。 |
| escapeSql | 将标签体中的每个单引号替换成一对单引号。 |
| query | 为一个statement或者preparedStatement标签设置一个查询。 |
| execute | 为statement或者preparedStatement标签执行一个insert, update 或者 delete。 |
| preparedStatement | 创建并执行一个记号化的数据库查询。 |
| setColumn | 设置preparedStatement中的一个字段。将值设置为标签体内的一个字符串。 |
| resultSet | 标签resulset执行查询并循环执行封闭的statement或者preparedstatement标签中的结果。这个标签体的内容在resultset的每行上执行。可选属性"loop"（缺省为true）指定是否在每行上执行标签体，或者只是简单的将ResultSet分配给页面的一个用"id"指定的属性。 |
| wasNull | 如果前面的getColumn标签从数据库得到一个null值时执行标签体。必须在一个resultset标签内而且前面有一个getColumn标签,否则将产生一个错误。 |
| wasNotNull | 如果上一个getColumn标签从数据库得到的不是一个null值执行标签体。 |
| getColumn | 在封闭的resultset内得到字段值，作为一个字符串。字段索引通过"position"属性设置，使用"to"属性将该值设置为一个serlvet属性而不是一个标签体。servlet属性的范围使用"scope"属性指定(缺省为page)。 |
| getNumber | 和getColumn相似，但是对数字格式提供更精确的控制。 "format"属性可以是DecimalFormat构造函数可以接受的形式或者是一个类型: "CURRENCY","PERCENT" 或 "NUMBER"。 "locale"属性可以有一到三个部分，也就是Locale构造方法可以接受的形式: 语言, 国家 和 变量。它们使用"_"分割。 如果format和locale属性都没有设置，输出将和getColumn一样。 |
| getTime | 和getColumn类似，但是对java.sql.Time格式提供更精确的控制。 "format"属性可以是被SimpleDateFormat接受的形式或者是一个类型: "FULL", "LONG", "MEDIUM" 或 "SHORT"。这个属性是可选的。 "locale"属性可以有一到三个部分，也就是Locale构造方法可以接受的形式: 语言, 国家 和 变量。它们使用"_"分割。 如果format和locale属性都没有设置，输出将和getColumn一样。 |
| getTimestamp | 和getColumn类似，但是对java.sql.Timestamp格式提供更精确的控制。 "format"属性可以是被SimpleDateFormat接受的形式或者是一个类型: "FULL", "LONG", "MEDIUM" 或 "SHORT"。这个属性是可选的。 "locale"属性可以有一到三个部分，也就是Locale构造方法可以接受的形式: 语言, 国家 和 变量。它们使用"_"分割。 如果format和locale属性都没有设置，输出将和getColumn一样。 |
| getDate | 和getColumn类似，但是对java.sql.Date格式提供更精确的控制。 "format"属性可以是被SimpleDateFormat接受的形式或者是一个类型: "FULL", "LONG", "MEDIUM" 或 "SHORT"。这个属性是可选的。 "locale"属性可以有一到三个部分，也就是Locale构造方法可以接受的形式: 语言, 国家 和 变量。它们使用"_"分割。 |
| wasEmpty | 上一个ResultSet标签从数据库得到0行执行标签体。必须在ResultSet标签后使用否则将产生一个错误。 |
| wasNotEmpty | 上一个ResultSet标签从数据库得到多于0行执行标签体。必须在ResultSet标签后使用否则将产生一个错误。 |
| rowCount | "rowCount"标签打印数据库返回的行数。可以在ResultSet标签内使用它提供一个运行计数，或者在ResultSet标签后面使用打印总数。在ResultSet前使用该标签将产生一个错误。 |
| 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. | |||||
| Examples | Method 1: using the DriverManager | ||||
| <%-- open a database connection --%> <sql:connection id="conn1"> <%-- required --%> <sql:url>jdbc:mysql://localhost/test</sql:url> <%-- optional --%> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> <%-- optional --%> <sql:userId>root</sql:userId> <%-- optional --%> <sql:password>notVerySecure</sql:password> </sql:connection> | |||||
| Method 2: using a DataSource | |||||
| <%-- 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> | |||||
See the example application DBTags-examples.war for examples of the usage of the tags from this custom tag library.
Java programmers can view the java class documentation for this tag library as javadocs.
Review the complete revision history of this tag library.
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.