Java Buzz Forum - JSP 2.0 sql tag to call stored procedures

As i mentioned some time ago, i've created a small jsp application using jstl. I found that the sql tags part of jstl really enable you to quickly create small web applications. Ofcource this architecture isn't really fit for large websites, but for small internal apps, and for prototyping it's ok.

One thing i missed was a tag to call a stored procedure, preferable in a separate thread as i needed to start some long running stored procedures. I couldn't find any taglibs which offered this.

But with jsp 2.0 it's really easy to implement new tags. You create a .tag file under WEB-INF/tags/. A tag file is basically a jsp fragment. I created the following file: WEB-INF/tags/sql/call.tag. So now i can use it as follows:

<sql2:call dataSource="jdbc/db">
<jsp:attribute name="sql">
    begin
      my_package_pkg.my_long_running_sp(?);
    end;
</jsp:attribute>
<jsp:attribute name="param1">
    <c:out value="${param.my_param}"/>
</jsp:attribute>
</sql2:call>

<%@ attribute name="sql" %>
<%@ attribute name="dataSource" %>

final java.util.Map params =
    (java.util.Map) pageContext.getAttribute("param");
final javax.servlet.jsp.JspWriter _out = out;
final javax.servlet.ServletContext _app = application;

java.lang.Thread thread = new Thread() {
    public void run() {
        try {
            javax.naming.Context ctx = new javax.naming.InitialContext();
            if (ctx == null)
                throw new Exception("No Context");
            javax.sql.DataSource ds =
                (javax.sql.DataSource) ctx.lookup(
                    "java:comp/env/" + dataSource);

            if (ds != null) {
                java.sql.Connection conn = ds.getConnection();
                try {

                    if (conn != null) {
                        java.sql.CallableStatement stmt =
                           conn.prepareCall(sql);

                        java.util.Map p = params;
                        java.util.Set keys = p.keySet();
                        java.util.Iterator keyIter = keys.iterator();
                        int pc = 1;
                        while (keyIter.hasNext()) {
                            String key = (String) keyIter.next();
                            if (key.equals("param1")) {
                               stmt.setString(pc, (String) p.get(key));
                               pc++;
                            }
                        }

                        stmt.execute();
                        conn.close();
                        conn = null;
                    }
                } finally {
                    if (conn != null) {
                        conn.close();
                    }
                }
            }
        } catch (Exception e) {
            try {
                _app.log("call.tag: Error: " + e.getMessage());
            } catch (Exception e2) {
            }
        }
    }
}; //thread

thread.start();


	Web Artima.com