An XML-Based JDBC Connector Servlet Framework

1. An XML-Based JDBC Connector Servlet Framework Geng Tang 03/05/2001

2. What is JCS ? • JDBC Connector Servlet

3. Com.jresources.servlets Package • Class JDBCConnectorServlet • package com.jresources.servlets; • import javax.servlet.*; • import javax.servlet.http.*; • import java.io.*; • import com.jresources.jcs.*; • import java.util.Enumeration; • import java.util.Properties; • public class JDBCConnectorServlet extends HttpServlet { • private java.util.Properties initProps = new Properties(); • public void init(ServletConfig config) throws ServletException { • super.init(config); • Enumeration e = config.getInitParameterNames(); • while(e.hasMoreElements()) { • String currParam = (String)e.nextElement(); • initProps.put(currParam, config.getInitParameter(currParam)); • } • }

4. public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { • jcsEngine mainMod = new jcsEngine(); • try { String qryDoc; • if(request.getPathInfo()==null) { • qryDoc = getServletConfig().getServletContext().getRealPath(request.getServletPath()); • }else { • qryDoc = request.getPathTranslated(); • } • mainMod.processRequest(request, this.initProps ,qryDoc); • if(mainMod.getResponseType()==jcsEngine.RESPONSE_REDIRECT) { • response.sendRedirect(mainMod.getResponse()); • } else { • PrintWriter out = response.getWriter(); • response.setContentType(mainMod.getMIMEType()); • out.println(mainMod.getResponse()); • } • }catch(Exception e) { • String errorMsg = "An error occurred while executing the query " + • request.getServletPath() + " with the JCS servlet: " + e.toString(); • this.getServletContext().log(errorMsg, e); • if(mainMod.getErrorPage()=="") { • throw new ServletException(errorMsg, e); • }else { • response.sendRedirect(mainMod.getErrorPage()); • } • } • } • public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { doGet(request, response); • } • }

5. Method in jcsEngine • public void processRequest(HttpServletRequest request, String qryDoc) throws Exception • { • this.processRequest(request, null, qryDoc); • } • /** • * Executes the request with custom properties • */ • public void processRequest(HttpServletRequest request, Properties customProps, String qryDoc) throws Exception • { • //our results • StringBuffer strResults = new StringBuffer(""); • //open the script • String qryContent = FunctionLib.OpenFile(qryDoc); • qryContent = this.insertFormVariables(qryContent, request); • //check for custom properties...if exist, then apply • if(customProps!=null) { • qryContent = this.insertJCSVariables(qryContent, customProps); • } • //process SQLEncode function calls • String[] workingQuery = new String[1]; • workingQuery[0] = qryContent; • this.processSQLEncode(workingQuery, new StringBuffer()); • qryContent = workingQuery[0]; • //parse the query script • jcsQuery objQuery = new jcsQuery(qryContent); • //TODO: reimplement this code as a finite state machine to make it cleaner and more extensible • //set the errorpage for error handling • errorPage = objQuery.getErrorPage(); • //create a string array to hold the template text • String[] workingTemplate = new String[1];

6. //open the template file • if(objQuery.getTemplateDoc()!="") { • workingTemplate[0] = objQuery.getTemplateDoc(); • //process any server-side includes it might have • this.processSSI(workingTemplate, new StringBuffer(), qryDoc, 0); • } • //instantiate and register the JDBC driver • Class.forName(objQuery.getJDBCDriver()); • //connect to the database and create a statement • Connection conn = DriverManager.getConnection(objQuery.getJDBCURL(), objQuery.getUID(), objQuery.getPWD()); • Statement s = conn.createStatement(); • //if it's a "write" query, execute it and redirect to the redirectURL • //if there is no redirect url, add the template to the results stringbuffer • if(objQuery.IsTransaction()) { • s.executeUpdate(objQuery.getSQL()); • //System.out.println(objQuery.getRedirectURL()); • if(objQuery.getRedirectURL()!="") { • conn.close(); //clean up the db connection • this.response = objQuery.getRedirectURL(); • this.responseType = jcsEngine.RESPONSE_REDIRECT; • return; • }else { • strResults.append(workingTemplate[0]); • } • }

7. //otherwise, further parse the template for the jcs:resultset tag... • //execute the query, loop thru the rs, apply the template's display • else{ • if(objQuery.getMaxRows() > -1){ • s.setMaxRows(objQuery.getMaxRows()); • } • ResultSet rs = s.executeQuery(objQuery.getSQL()); • //if there is no template, then send the default • //XML document • if(objQuery.getTemplateDoc().equals("")) { • XMLSerializableResultset xrs = new XMLSerializableResultset(rs); • this.response = xrs.getXML(); • this.MIMEType = "text/xml"; • this.responseType = jcsEngine.RESPONSE_WRITE; • rs.close(); • conn.close(); • return;

8. }else { • jcsTemplateParser template = new jcsTemplateParser(); • template.setTemplate(workingTemplate[0]); • strResults.append(template.getHeader()); • ResultSetMetaData rsMetadata = rs.getMetaData(); • int intFields = rsMetadata.getColumnCount(); • String recordOutput; • String currFieldValue; • String AdjFieldValue; • int recordCount = 0; • while(rs.next()) { • recordOutput = template.getBody(); • for(int i =1; i <= intFields; i++) { • currFieldValue = FunctionLib.EncodeSpecialCharacters(rs.getString(i)); • AdjFieldValue = objQuery.UseHTMLLineBreak()?FunctionLib.HTMLLineBreak(currFieldValue):currFieldValue; • recordOutput = FunctionLib.Replace(recordOutput, "#" + rsMetadata.getColumnName(i) + "#", AdjFieldValue); • } • strResults.append(recordOutput); • recordCount++; • } • if(recordCount == 0) { • strResults.append(objQuery.getEmptyResultsetOutput()); • } • strResults.append(template.getFooter()); • } • rs.close();

9. } • //end sql check • conn.close(); • this.MIMEType = objQuery.getMIMEType(); • this.responseType = jcsEngine.RESPONSE_WRITE; • //make sure we fill out any form or cgi variables in the template... • String tmpResponse = insertFormVariables(strResults.toString(),request); • //check for custom properties...if exist, then apply • if(customProps!=null) { • tmpResponse = this.insertJCSVariables(tmpResponse, customProps); • } • this.response=tmpResponse; • return; • }

10. Some important files • jcs_taglib.tld • web.xml

11. jcs_taglib.tld • <?xml version="1.0" encoding="ISO-8859-1" ?> • <!DOCTYPE taglib • PUBLIC "-//Sun Microsystems, Inc.//DTD JSP Tag Library 1.1//EN" • "http://java.sun.com/j2ee/dtds/web-jsptaglibrary_1_1.dtd"> • <!-- a tab library descriptor --> • <taglib> • <tlibversion>1.0</tlibversion> • <jspversion>1.1</jspversion> • <shortname>jcs</shortname> • <uri></uri> • <info> • A custom JSP tag implementation of the JResources.com DB Connector servlet • </info> • <tag> • <name>query_object</name> • <tagclass>com.jresources.jcs.jcsCustomTag</tagclass> • <teiclass>com.jresources.jcs.jcsCustomTagOutput</teiclass> • <info>A custom JSP tag implementation of the JResources.com DB Connector servlet</info> • <attribute> • <name>url</name> • <required>true</required> • </attribute> • <attribute> • <name>outputvariable</name> • <required>true</required> • </attribute> • </tag> • </taglib>

12. Web.xml • <?xml version="1.0" encoding="ISO-8859-1"?> • <!DOCTYPE web-app • PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN" • "http://java.sun.com/j2ee/dtds/web-app_2.2.dtd"> • <web-app> • <display-name>JResources.com Database Connector Servlet</display-name> • <description> • The JResources.com Database Connector Servlet is a Java • servlet that provides an XML-based interface to JDBC data • sources. With the JResources.com DB connector servlet, • web developers now have an easy way to query and modify • JDBC databases without writing a line of Java code. • </description> • <context-param> • <param-name>jdbc_url</param-name> • <param-value>jdbc:odbc:guestbook</param-value> • <description> • The default URL for the guestbook app's database • </description> • </context-param>

13. (continued) • <context-param> • <param-name>output</param-name> • <param-value> • This is an example of using JCS from a JSP with custom • properties read from the web application's context parameters. • In Tomcat, these are set up in the WEB-INF/web.xml file • </param-value> • <description> • Sample output for the guestbook app • </description> • </context-param> • <servlet> • <servlet-name> • JCS • </servlet-name> • <servlet-class> • com.jresources.servlets.JDBCConnectorServlet • </servlet-class> • <init-param> • <param-name> • defaultDriver • </param-name> • <param-value> • sun.jdbc.odbc.JdbcOdbcDriver • </param-value> • </init-param> • </servlet>

14. (continued) • <servlet-mapping> • <servlet-name> • JCS • </servlet-name> • <url-pattern> • *.jcs • </url-pattern> • </servlet-mapping> • <taglib> • <taglib-uri> • http://www.jresources.com/jcs/jcs_taglib.tld • </taglib-uri> • <taglib-location> • /WEB-INF/jcs_taglib.tld • </taglib-location> • </taglib> • </web-app>

15. Guestbook application • Create a SELECT query • Adding and modifying data • Generating XML output from query • Using JCS from within a JSP • Using XSLT with JCS

18. guestbook.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql>select * from guestbook order by date</jcs:sql> • <jcs:template> • <![CDATA[ • <jcs:include> • templates\header.inc • </jcs:include> • <p><b>Guestbook Entries</b> (as of #system.date#)</p> • <table> • <tr> • <td> • <b>Visitor</b> • </td> • <td> • <b>Date</b> • </td> • </tr> • <jcs:resultset> • <tr> • <td> • <a href="guestbook_view_entry.jcs?ID=#ID#">#fname# #lname#</a> • </td> • <td> • <i>#date#</i> • </td> • </tr> • </jcs:resultset> • </table>

19. (Continued) • <jcs:include> • templates\footer.inc • </jcs:include> • ]]> • </jcs:template> • <jcs:empty_resultset_output> • <![CDATA[ • <tr> • <td colspan="2"> • <font color="red">There are no entries in the guestbook.</font> • </td> • </tr> • ]]> • </jcs:empty_resultset_output> • <jcs:line_break_character>HTML</jcs:line_break_character> • </jcs:query>

21. guestbook_xml_default.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql>select * from guestbook order by date</jcs:sql> • </jcs:query>

23. guestbook_xml.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:mime_type>text/xml</jcs:mime_type> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql>select * from guestbook order by date</jcs:sql> • <jcs:template> • <![CDATA[ • <?xml version="1.0"?> • <guestbook date="#system.date#"> • <jcs:resultset> • <entry id="#ID#"> • <visitor fname="#fname#" lname="#lname#"/> • <comments> • #comments# • </comments> • <date>#date#</date> • <host>#host#</host> • </entry> • </jcs:resultset> • </guestbook> • ]]> • </jcs:template> • </jcs:query>

25. guestbook.jsp • <jsp:useBean id="jcs" scope="request" class="com.jresources.jcs.jcsEngine" /> • <% • try{ • jcs.processRequest(request, getServletConfig().getServletContext().getRealPath("/samples/guestbook/guestbook.jcs")); • } • catch(Exception e){} • if(jcs.getResponseType()==com.jresources.jcs.jcsEngine.RESPONSE_REDIRECT) { • response.sendRedirect(jcs.getResponse()); • }else { • %> • <%=jcs.getResponse()%> • <% • } • %>

27. guestbook_tag.jsp • <%@ taglib uri="http://www.jresources.com/jcs/jcs_taglib.tld" prefix="jcs" %> • <jcs:query_object url="/samples/guestbook/guestbook.jcs" outputvariable="guestbook" /> • <%=guestbook%>

29. guestbook_custom.jsp • <jsp:useBean id="jcs" scope="request" class="com.jresources.jcs.jcsEngine" /> • <% • java.util.Properties prop = new java.util.Properties(); • prop.put("jdbc_url", "jdbc:odbc:guestbook"); • prop.put("output", "This is an example of using JCS from a JSP with custom properties passed from the JSP script to the JCS engine"); • try{ • jcs.processRequest(request, prop, getServletConfig().getServletContext().getRealPath("/samples/guestbook/jsp.guestbook.jcs")); • }catch(Exception e){} • if(jcs.getResponseType()==com.jresources.jcs.jcsEngine.RESPONSE_REDIRECT) { • response.sendRedirect(jcs.getResponse()); • }else { • %> • <%=jcs.getResponse()%> • <% • } • %>

30. Jsp.guestbook.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>#jcs.jdbc_url#</jcs:jdbc_url> • <jcs:sql>select * from guestbook order by date</jcs:sql> • <jcs:template> • <![CDATA[ • <jcs:include> • templates/header.inc • </jcs:include> • <p><b>Guestbook Entries</b> (as of #system.date#)</p> • <table> • <tr> • <td> • <b>Visitor</b> • </td> • <td> • <b>Date</b> • </td> • </tr>

31. <jcs:resultset> • <tr> • <td> • <a href="guestbook_view_entry.jcs?ID=#ID#">#fname# #lname#</a> • </td> • <td> • <i>#date#</i> • </td> • </tr> • </jcs:resultset> • </table> • <p><b>#jcs.output#</b></p> • <jcs:include> • templates/footer.inc • </jcs:include> • ]]> • </jcs:template> • <jcs:line_break_character>HTML</jcs:line_break_character> • </jcs:query>

33. guestbook_xsl.jsp • <%@ page import="org.xml.sax.SAXException,org.apache.xalan.xslt.XSLTProcessorFactory,org.apache.xalan.xslt.XSLTInputSource,org.apache.xalan.xslt.XSLTResultTarget,org.apache.xalan.xslt.XSLTProcessor" %> • <%@ taglib uri="http://www.jresources.com/jcs/jcs_taglib.tld" prefix="jcs" %> • <jcs:query_object url="/samples/guestbook/guestbook_xml.jcs" outputvariable="guestbook" /> • <% • /* • This sample uses Xalan 1.1. Download it from http://xml.apache.org. Ensure that xalan.jar and xerces.jar are in Tomcat's classpath. • */ • try{ • XSLTProcessor processor = XSLTProcessorFactory.getProcessor(); • processor.process(new XSLTInputSource(new java.io.StringReader(guestbook.toString())), new XSLTInputSource("file:///" + getServletConfig().getServletContext().getRealPath("/samples/guestbook/guestbook.xsl")), new XSLTResultTarget(out)); • }catch(Exception e) { • %> • An error occurred processing your operation...<%=e.toString()%> • <% • } • %>

35. guestbook_tag_custom.jsp • <%@ taglib uri="http://www.jresources.com/jcs/jcs_taglib.tld" prefix="jcs" %> • <jcs:query_object url="/samples/guestbook/jsp.guestbook.jcs" outputvariable="guestbook" /> • <%=guestbook%>

37. guestbook_submit.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql>select * from guestbook order by date</jcs:sql> • <jcs:template> • <![CDATA[ • <jcs:include> • templates\header.inc • </jcs:include> • <b>Sign the Guestbook:</b> • <form method="POST" action="guestbook_add.jcs"> • <p>Your first name: <input type="text" name="fname" size="20"></p> • <p>Your last name:&nbsp; <input type="text" name="lname" size="20"></p> • <p>Your comments:&nbsp; <textarea rows="5" name="comments" cols="30"></textarea></p> • <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" • name="B2"></p> • </form>

38. (continued) • <hr size="1"> • <p><b>Other Guestbook Entries</b> (as of #system.date#)</p> • <table> • <tr> • <td> • <b>Visitor</b> • </td> • <td> • <b>Date</b> • </td> • </tr> • <jcs:resultset> • <tr> • <td> • <a href="guestbook_view_entry.jcs?ID=#ID#">#fname# #lname#</a> • </td> • <td> • <i>#date#</i> • </td> • </tr> • </jcs:resultset> • </table> • <jcs:include> • templates\footer.inc • </jcs:include> • ]]> • </jcs:template> • <jcs:line_break_character>HTML</jcs:line_break_character> • </jcs:query>

39. guestbook_add.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql> • INSERT INTO guestbook ("ID", "fname", "lname", "comments", "host", "date") VALUES ('#system.UID#','<jcs:encode_sql>#form.fname#</jcs:encode_sql>','<jcs:encode_sql>#form.lname#</jcs:encode_sql>', '<jcs:encode_sql>#form.comments#</jcs:encode_sql>', '#CGI.REMOTE_ADDR#', Now()) • </jcs:sql> • <jcs:redirect_url>guestbook.jcs</jcs:redirect_url> • <jcs:error_page>error_page.htm</jcs:error_page> • </jcs:query>

42. guestbook_delete.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql> • delete from guestbook where ID = '#form.ID#' • </jcs:sql> • <jcs:redirect_url>guestbook.jcs</jcs:redirect_url> • <jcs:error_page>error_page.htm</jcs:error_page> • </jcs:query>

43. guestbook_view_entry.jcs • <?xml version="1.0"?> • <jcs:query xmlns:jcs="http://www.jresources.com/xml/jcs/1.0"> • <jcs:jdbc_url>jdbc:odbc:guestbook</jcs:jdbc_url> • <jcs:sql>select * from guestbook where ID = '#form.ID#'</jcs:sql> • <jcs:template> • <![CDATA[ • <jcs:include> • templates\header.inc • </jcs:include> • <jcs:resultset> • <p><b>#fname# #lname#'s Guestbook Entry</b></p> • <p><b>Comments: </b>#comments#</p> • <p><b>Date entered: </b>#date#</p> • <p><b>Originating Host: </b>#host#</p> • <p><a href="guestbook_delete.jcs?ID=#ID#">Delete this entry</a></p> • </jcs:resultset> • <jcs:include> • templates\footer.inc • </jcs:include> • ]]> • </jcs:template> • <jcs:line_break_character>HTML</jcs:line_break_character> • </jcs:query>

45. Summary • Reusable • access the record in relational database via JDBC without Java • rapid creation of XML data server • portable

46. Questions ?