An XML Based JDBC Connector Servlet Framework By Narasimhan Rengaswamy What is a JDBC Connector Servlet JCS A Java servlet that provides an XML based scripting interface to JDBC data sources Allows JSP and servlet developers to achieve a separation between server side presentation logic and their data access SQL logic Brief Idea about JDBC An API used to write database clients from applets to servlets EJBs connect to a relational database execute SQL statements and process the results extracted Database connectivity is not just connecting to database and executing statements but also optimizing network resources using connection pooling and distributed transactions e g WebLogic Enterprise CORBA Java and WebLogic Enterprise EJB applications Servlet A Java servlet contains the application code that executes on the server in response to an event A servlet receives events from the web application page performs the appropriate actions and then subsequently outputs data dynamically to the application s presentation layout model by invoking a Java Server Page JSP Servlets Contd Are Http request handlers which provide full access to Java APIs and endow web programmers with a powerful tool for creating dynamic Web pages A servlet is a Java component that can be plugged into a Java enabled web server to provide custom services A little about XML XML the Extensible Markup Language is the universal syntax for describing and structuring data independent from application logic XML is a method for putting structured data in a text file Case Study Two Sections 1 Implementing the JDBC Connector Servlet 2 Using the JDBC Connector Servlet Implementing the JDBC Connector Servlet Determine Framework s Functional Requirements Design a High Level Architecture Design the tool s scripting Framework Design and develop the Java classes that implement the scripting framework Design and develop other artifacts to deploy framework to J2EE containers Contd Package the framework s binaries for deployment to servlet engines and application servers that support J2EE Deploy JCS to a J2EE web container Framework s Functional Requirements JCS should provide mechanism to Query a Database and return the result back to the browser The output can be a XML document Allow to view in a WAP device in a WML format JCS must be able to execute INSERT UPDATE and DELETE statements Contd Must allow HTML form CGI and custom variables in JCS query files Allow HTTP GET or POST between the HTML form and a JCS Query Support SSI Server Side Includes e g Library files Contd Must accommodate default content to send to the browser e g if the select statements does not return the result Support Exception and redirecting of pages when error occurs Should allow JCS queries within JSP applications by providing a bean interface and custom tag Must provide tags to generate unique Ids e g primary key The JCS Architecture Web Clients HTTP Web Server Apache IIS Appropriate Connector Servlet Engine Tomcat various J2EE App Servers JDBC Connector Servlet Java Server Pages jcs query JSP Custom Tag jcs Engine Relational Databases Contd Step 1 Remote Browser requests URL for the XML based queries Step 2 Servlet Engine dispatch it to JDBC Connector Servlet Step 3 JDBC Connector Servlet Instantiates JCS engine and parse the query and get back the result JCS Scripting Framework JCS provides a scripting framework to write web based JDBC queries JDBC queries are XML documents with special tags containing database connection parameters SQL code and output Design of this XML schema is done by first creating a jcs namespace Snapshot of a JCS Query xml version 1 0 jcs query xmlns jcs http www jresources com xml jcs 1 0 jcs mime type Your output s MIME Type jcs mime type jcs jdbc url The URL of the JDBC data source jcs jdbc url jcs jdbc uid Your userid jcs jdbc uid jcs jdbc pwd Your password jcs jdbc pwd jcs jdbc driver Your JDBC driver jcs jdbc driver contd jcs sql Your SQL statement Here s an example SELECT FROM guestbook WHERE ID FORM id jcs sql jcs error page The page to redirect to in the event an exception is thrown jcs error page jcs redirect url The page to redirect to after successfully executing an INSERT update or DELETE statement jcs redirect url jcs maxrows The maximum number of rows to be returned in the query jcs maxrows Contd jcs template This is your output template CDATA jcs include include file reference goes here jcs include HTML markup goes here jcs resultset HTML markup goes here Use FIELD NAME to insert the contents of a particular field HTML markup goes here jcs resultset HTML markup goes here jcs template JCS Query Tags Database connectivity parameter tags The SQL tag Output definition tags Special function tags Template specific tags Database Connectivity Tags Contains parameters to connect to what database which JDBC driver to use and support authentication Tag names jcs jdbc driver jcs jdbc url jcs jdbc uid jcs jdbc pwd The SQL Tag Contains jcs sql tag Output Definition Tags Jcs mime type Jcs redirect url Jcs template Jcs empty resultset output Jcs error page Jcs maxrows Special function tags Jcs encode sql Doubles up single quote characters to replace them with two consecutive single quote characters Jcs line break character to handle carriage return and line feed characters in data retrieved from database currently supports HTML only but future enhancement would be an XHTML Template specific tags jcs result set loops through the rows of data returned from the query jcs include creates a server side include reference within a JCS output template Variable Types JCS supports variable types to access dynamic data Variable names are case sensitive Form Variables Resultset Field Variables CGI Variables System Variables Custom Variables Variables take the form form variablename Form Variables Variables passed by client to the server via HTTP GET and POST method Sample JCS query form handler named 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 form fname form lname form lname form comments CGI REMOTE ADDR NOW jcs sql Resultset Field Variables It takes the form fieldname Used in output templates to reference values of a given record s fields Sample code jcs include templates header inc jcs include jcs resultset p b fname lname s Guestbook Entry b p p b Comments b comments