Andrej Koelewijn

JSP 2.0 sql tag to call stored procedures

<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>

<%@ tag dynamic-attributes="param" %>

<%@ 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.replace((char)13,' ').replace((char)10,' '); 

           
            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.startsWith("param")) { 
                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();

9 Responses to “JSP 2.0 sql tag to call stored procedures”

1. Isaiah Says:
   December 4th, 2003 at 4:31 pm

   OK, very good, but really just for small, and non-scalable jsp applications.

2. Jan Says:
   December 4th, 2003 at 9:13 pm

   This is not what JSP’s are meant for. As was said in the previous comment; the chance you end up with a horribly bad performing, non-maintainable application is really big.

3. Andrej Says:
   December 4th, 2003 at 11:12 pm

   As i said, this is for a small internal admin application. Basically everyone using it has it own copy running in tomcat on his laptop. No need to scale. I performs good enough for me. I use it instead of running scripts in sqlplus/toad. It’s more usefull than sqlplus/toad because i can easily navigate between queries/pages, and i’ve added some help text on every page, so other people can easily admin/monitor the real application (batch jobs).

   For me the result is a well performing, very good maintainable application, which can also be used to prototype some ideas, before the real admin application is created (using turbine as mvc framework, and dao’s to access the database).

4. Jonas Says:
   December 6th, 2003 at 1:39 am

   Very intresting. I really like this idea and I start looking into this more myself. I don’t agree about the scalibilty problem. This will scale better. The scalibility problem in webapplication is the common datasourse. Using stored procedures is to use your database much more efficent than all thes millions of small SQL-call all JAVA-developers seem to prefer.

5. Jonas Says:
   December 6th, 2003 at 1:40 am

   Very intresting. I really like this idea and I start looking into this more myself. I don’t agree about the scalibilty problem. This will scale better. The scalibility problem in webapplication is the common datasourse. Using stored procedures is to use your database much more efficent than all thes millions of small SQL-call all JAVA-developers seem to prefer.

6. june Says:
   July 8th, 2004 at 10:56 am

   I never found such a good and informal site. Great kompliment to the owner of this page.

7. JSerge Says:
   September 10th, 2004 at 8:56 am

   Well, the only question is why sql:call tag is not implemented in JSTL?!
   Are the there any (semi)official explanations?

8. Anonymous Says:
   September 26th, 2004 at 2:10 pm

   I think this has great value. There are times when Model 2 architecture is just too heavy and time consuming and Model 1 is necessary, such as prototyping or very simple reporting. (I use it to show datasets in Javadocs by creating a simple JSP page with a displaytag (http://displaytag.sf.net) table and using an iframe in the javadoc to access it.) Using struts for such a task would be silly, but this tag is perfect. Thank you!

9. Mahesh Bhat Says:
   December 9th, 2004 at 1:11 pm

   useful article

Leave a Reply