I recently downloaded the DBTags library and quickly wrote the following
JSP:
<%@ taglib uri="dbtags" prefix="sql" %>
<sql:connection id="connection" dataSource="com.webmiles.tomcat.database" />
<sql:preparedStatement id="statement" conn="connection">
<sql:query>select sysdate from dual</sql:query>
<sql:resultSet id="results">
The time is: <sql:getDate position="1" format="FULL"/>
</sql:resultSet>
</sql:preparedStatement>
<sql:closeConnection conn="connection"/>
However, upon further reflection, it seems like a bad design choice to have
separate 'connection' and 'closeConnection' tags since there is no way to
guarantee that the connection will be closed (not only that, but it's also
more error prone as the user may forget to include the closeConnection tag).
The closeConnection tag *must* be enclosed in a finally block to prevent
resource leaks in the presence of exceptions. When I tried this (using
tomcat), I got the following error.
<%@ taglib uri="dbtags" prefix="sql" %>
<sql:connection id="connection" dataSource="com.webmiles.tomcat.database" />
<% try { %>
<sql:preparedStatement id="statement" conn="connection">
<sql:query>select sysdate from dual</sql:query>
<sql:resultSet id="results">
The time is: <sql:getDate position="1" format="FULL"/>
</sql:resultSet>
</sql:preparedStatement>
<% } finally { %>
<sql:closeConnection conn="connection"/>
<% } %>
Internal Servlet Error:
javax.servlet.ServletException: (class: _0002fdbtags_0002ejspdbtags_jsp_4,
method: _jspService signature:
(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletRespo
nse;)V) Incompatible object argument for function call
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:508)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at
org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at
org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:79
7)
at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpC
onnectionHandler.java:213)
at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
Root cause:
java.lang.VerifyError: (class: _0002fdbtags_0002ejspdbtags_jsp_4, method:
_jspService signature:
(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletRespo
nse;)V) Incompatible object argument for function call
at java.lang.Class.newInstance0(Native Method)
at java.lang.Class.newInstance(Class.java:237)
at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.instantiateServlet(Js
pServlet.java:204)
at
org.apache.jasper.servlet.JspServlet.doLoadJSP(JspServlet.java:617)
at
org.apache.jasper.servlet.JasperLoader12.loadJSP(JasperLoader12.java:146)
at org.apache.jasper.servlet.JspServlet.loadJSP(JspServlet.java:542)
at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.loadIfNecessary(JspSe
rvlet.java:258)
at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.ja
va:268)
at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at
org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at
org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:79
7)
at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpC
onnectionHandler.java:213)
at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
Wouldn't it make much more sense to enclose everything in the connection tag
and not have a separate closeConnection? This way the connection could be
automatically closed when the connection tag ended (why would you *ever* not
want to close the connection?) This should happen in the release() method
of the connection tag. From the JSP 1.2 pfd 2 pg 164, "The page compiler
guarantees that release() will be invoked on the Tag handler before the
handler is released to the GC". This means that release() is called in a
finally block and there will be no connection leaks.
The resulting jsp would look like this:
<%@ taglib uri="dbtags" prefix="sql" %>
<sql:connection id="connection" dataSource="com.webmiles.tomcat.database">
<sql:preparedStatement id="statement" conn="connection">
<sql:query>select sysdate from dual</sql:query>
<sql:resultSet id="results">
The time is: <sql:getDate position="1" format="FULL"/>
</sql:resultSet>
</sql:preparedStatement>
</sql:connection>
Just as concise with no possibility for resource leaks!
Sean Rohead
