Ch.16 JDBC (Java DataBase Connectivity)

1. Ch.16 JDBC(Java DataBase Connectivity) PIKE Lab. 석사 2학기 이 은 정

2. CONTENTS • Introduction • Needed Software • Generating XML from JDBC • Using XML for distributed JDBC App. • Summary

3. INTRODUCTION(1/2) • Challenge • To create JDBC Applications that allow platform_neutral, device_independent access to data • By XML-enabling the JDBC application • XML with J2EE + Standard Internet Protocol(HTTP) • Enterprise applications that provide universal data access to JDBC data sources

4. INTRODUCTION(2/2) • In this chapter… • Some examples of how to integrate XML with JDBC in J2EE applications that are device-and –platform independent universal data access • There’re 2 scenarios… • Generating XML from a JDBC data source • A XML gateway architecture for JDBC • Using XML to update a JDBC data source • How to use WebRowSets to distributed JDBC applications

5. Needed Software • JDK1.3 - http://java.sun.com • Xalan XSLT Processor for Java - http://xml.apache.org • Java packages to implement the WebRowSet framework • sun.jdbc.rowset Package • javax.sql Package : JDBC 2.0 standard extension API • Tomcat 3.1 : java servlet API - http://jakarta.apache.org • A JDBC data source & Driver(Oracle, SQL Server…)

6. Generating XML from JDBC • Create an architecture that allows you to extract data from a JDBC data source • Serialize the JDBC result set to XML • Send it to the requesting client, using XSLT as appropriate to create output that targets a particular device

7. Generating XML from JDBC • A simple XML Gateway Architecture for JDBC • JDBC2XML class : control access to the JDBC data source • Execute a SQL statement against the specified JDBC data source • Serialize the returned JDBC result set as XML • Return the XML document to the calling client • Reused by two java servlets • XMLDataGateway : a generic XML-over-HTTP interface to JDBC data sources for XML –enabled applications • JDBC2HTML : return JDBC result sets as a HTML to web browsers using the specified XSL stylesheet like a filter

8. XML-enabled Application Web browser HTTP GET or POST XML over HTTP HTTP GET or POST HTMLover HTTP Delegated to XMLDataGateway Servleet XSL Stylesheet JDBC2HTML Servlet XSLT In-process call XML serialized resultset XML serialized resultset In-process call JDBC2XML class JDBC JDBC Data Source

9. Creating & Using the Architecture • Develop the JDBC2XML class • Develop the XMLDataGateway servlet • Query a JDBC data source using the XMLDataGateway servlet • Develop the JDBC2HTML servlet • Write an XSL stylesheet that defines HTML presentation logic for our <resultset/> XML schema • Query a JDBC data source using the JDBC2HTML servlet

10. The JDBC2XML Class • package com.jresources.jdbc; • import java.sql.*; • import java.util.*; • public class JDBC2XML • { • public JDBC2XML() { • super(); • } • Include the functionality • to query a JDBC Data • source • to return the Results of • that query as a well-formed • XML document

11. public String execute(String driver, String url, String uid, String pwd, String sql) { String output = new String(); try { //instantiate and register the JDBC driver Class.forName(driver); //connect to the database and create a statement Connection conn = DriverManager.getConnection(url, uid, pwd); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery(sql); output = writeXML(rs); rs.close(); conn.close(); }catch(Exception e) { output = "<error>" + encodeXML(e.toString()) + "</error>"; } return output; } String writeXML(ResultSet rs) { StringBuffer strResults = new StringBuffer("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\r\n<resultset>\r\n"); try { ResultSetMetaData rsMetadata = rs.getMetaData(); int intFields = rsMetadata.getColumnCount(); strResults.append("<metadata>\r\n"); for(int h =1; h <= intFields; h++) { strResults.append("<field name=\"" + rsMetadata.getColumnName(h) + "\" datatype=\"" + rsMetadata.getColumnTypeName(h) + "\"/>\r\n"); } strResults.append("</metadata>\r\n<records>\r\n"); while(rs.next()) { strResults.append("<record>\r\n"); for(int i =1; i <= intFields; i++) { strResults.append("<field name=\"" + rsMetadata.getColumnName(i) + "\">" + encodeXML(rs.getString(i)) + "</field>\r\n"); } strResults.append("</record>\r\n"); } }catch(Exception e) {} strResults.append("</records>\r\n</resultset>"); return strResults.toString(); } public String SQLEncode(String content) { return Replace(content, "\'", "\'\'"); } /** * Applies XML encoding rules to special characters */ String encodeXML(String sData) { String[] before = {"&","<",">","\"", "\'"}; String[] after = {"&amp;","&lt;","&gt;","&quot;", "&apos;"}; if(sData!=null) { for(int i=0;i<before.length;i++) { sData = Replace(sData, before[i], after[i]); } }else {sData="";} return sData; } String Replace(String content, String oldWord, String newWord) { int position = content.indexOf(oldWord); while (position > -1) { content = content.substring(0,position) + newWord + content.substring(position+oldWord.length()); position = content.indexOf(oldWord,position+newWord.length()); } return content; } <?xml version="1.0" encoding="ISO-8859-1"?> <resultset> <metadata> <field name="field name goes here" datatype="field's datatype goes here"/> </metadata> <records> <record> <field name="field name goes here"> field's value goes here </field> </record> </records> </resultset>

12. The XMLDataGateway Servlet <form action=“/jdbcxml/servlet/XMLDataGateway" method="POST"> <table border="0"> <tr> <td align="right"><font face="Arial">JDBC Driver: </font></td> <td><font face="Arial"> <input type="text" size="50" name="driver" value="sun.jdbc.odbc.JdbcOdbcDriver"></td> </tr> <tr> <td align="right"><font face="Arial">JDBC URL: </font></td> <td><font face="Arial"> <input type="text" size="50" name="jdbcurl" value="jdbc:odbc:northwind"></td> </tr> <tr> <td align="right"><font face="Arial">Userid:</font></td> <td><font face="Arial"><input type="text" size="50" name="uid"></font></td> </tr> <tr> <td align="right"><font face="Arial">password </font></td> <td><font face="Arial"><input type="password" size="50" name="pwd"></font></td> </tr> <tr> <td align="right"><font face="Arial">SQL Statement:</font></td> <td><textarea name="sql" rows="10" cols="50"></textarea></td> </tr> <tr> <td align="right"><input type="submit"></td> <td>&nbsp;</td> </tr> </table> </form> package com.jresources.jdbc; import javax.servlet.*; import javax.servlet.http.*; import java.io.*; public class XMLDataGateway extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("text/xml"); PrintWriter out = response.getWriter(); JDBC2XML searchObj = new JDBC2XML(); out.println(searchObj.execute(request.getParameter("driver"), request.getParameter("jdbcurl"), request.getParameter("uid"), request.getParameter("pwd"), request.getParameter("sql"))); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { doGet(request, response); } }

13. Using XMLDataGAteway from a Web Browser

15. The JDBC2HTML Servlet public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { String qryDoc; if(request.getPathInfo()==null) { qryDoc = getServletConfig().getServletContext().getRealPath( request.getServletPath()); }else { qryDoc = request.getPathTranslated();} response.setContentType("text/html"); PrintWriter out = response.getWriter(); JDBC2XML searchObj = new JDBC2XML(); String output = searchObj.execute(request.getParameter("driver"), request.getParameter("jdbcurl"), request.getParameter("uid"), request.getParameter("pwd"), request.getParameter("sql")); try { XSLTProcessor processor = XSLTProcessorFactory.getProcessor(); processor.process(new XSLTInputSource(new java.io.StringReader(output)), new XSLTInputSource("file:///" + qryDoc), new XSLTResultTarget(out)); }catch(SAXException se) { throw new ServletException(se); //out.println(se.toString()+", " + se.getMessage() + ", " + qryDoc); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { doGet(request, response); } } import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import org.xml.sax.SAXException; import org.apache.xalan.xslt.*; public class JDBC2HTML extends HttpServlet {

16. Writing an XSL stylesheet <xsl:template match="metadata"> <tr bgcolor="#FFD700"> <xsl:apply-templates/> </tr> </xsl:template> <xsl:template match="metadata/field"> <td><b><xsl:value-of select="@name"/></b></td> <xsl:apply-templates/> </xsl:template> <xsl:template match="records"> <xsl:apply-templates/> </xsl:template> <xsl:template match="record"> <tr> <xsl:for-each select="field"> <td><xsl:value-of select="."/></td> </xsl:for-each> </tr> </xsl:template> <xsl:template match="error"> <html> <head> <title> A JDBC Resultset in HTML Table Format </title> </head> <body> Your request caused the following error: <xsl:value-of select="."/> </body> </html> </xsl:template> </xsl:stylesheet> <xsl:template match="resultset"> <html> <head> <title> A JDBC Resultset in HTML Table Format </title> </head> <body> <h1 align="center"> A JDBC Resultset in HTML Table Format </h1> <table border="1" cellspacing="0" cellpadding="5" align="center"> <xsl:apply-templates select="metadata"/> <xsl:apply-templates select="//resultset/records"/> </table> </body> </html> </xsl:template> <?xml version='1.0'?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <xsl:apply-templates/> </xsl:template>

17. Using XML for distributed JDBC Applications • Rowset Interface • Provide a framework for writing classes that encapsulate a persistent set of rows • sun.jdbc.rowset.WebRowSet Class • A rowset implementation that can serialize the data, metadata, and properties of a JDBC result set to XML • Use the information in the XML document to re-establish the connection and update the original data source

18. A distributed JDBC applications using WebRowSet • WebRowSetFetchServlet class • Provide an interface for fetching a WebRowSet via HTTP GET or POST • WebRowSetUpdateservlet class • Used to update the underlying JDBC data source • WebRowSetHTTPClient application • Used to execute a query against a JDBC data source & return the WebRowSet • Add a record to the WebRowSet

19. Fetching a Rowset via HTTP package com.jresources.jdbc; import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import javax.sql.*; import sun.jdbc.rowset.*; import java.sql.*; public class WebRowSetFetchServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { . . . . . . . . .(omit) } public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { doGet(request, response); } }

20. Performing a Batch Update via HTTP public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.sendError(403); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("text/plain"); PrintWriter out = response.getWriter(); try { WebRowSet wrs = new WebRowSet(); wrs.readXml(request.getReader()); wrs.acceptChanges(); out.println("The transaction succeeded"); }catch(Exception e) { out.println("The transaction failed with the following error: " + e.getMessage()); } } package com.jresources.jdbc; import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import javax.sql.*; import sun.jdbc.rowset.*; import java.sql.*; public class WebRowSetUpdateServlet extends HttpServlet { }

21. Inserting, Updating, Deletingdata at the Client • executeSearch() method • Encapsulate an HTTP GET request to WebRowSetFetchServlet • updateDataSource() method • Encapsulate an HTTP POST request to WebRowSetUpdateServlet • main() method • The application’s entry point

22. Web Application Deployment Description <?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>Professional XML Databases: Chapter 16 (JDBC)</display-name> <description> This web app contains the sample code for Professional XML Databases: Chapter 16 (JDBC) </description> <servlet> <servlet-name> JDBC2HTML </servlet-name> <servlet-class> com.jresources.jdbc.JDBC2HTML </servlet-class> </servlet> <servlet> <servlet-name> XMLDataGateway </servlet-name> <servlet-class> com.jresources.jdbc.XMLDataGateway </servlet-class> </servlet> <servlet> <servlet-name> WebRowSetFetchServlet </servlet-name> <servlet-class> com.jresources.jdbc.WebRowSetFetchServlet </servlet-class> </servlet> <servlet> <servlet-name> WebRowSetUpdateServlet </servlet-name> <servlet-class> com.jresources.jdbc.WebRowSetUpdateServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name> JDBC2HTML </servlet-name> <url-pattern> *.xsl </url-pattern> </servlet-mapping> </web-app>

23. Building Application • Compile the Java Classes • javac -d %myClasspath% -classpath %CLASSPATH% %basepath%\*.java • Package the Bytecode into a JAR • jar cvf %jarpath%\jdbcxml.jar -C %myClasspath%\ . • Package the Application into a WAR • jar cvf %warpath%\jdbcxml.war -C %apppath%\ .

24. Summary • How can use JDBC and XML to create universal data access gateways for J2EE applications • How to construct a simple XML gateway architecture for JDBC that allows to execute SQL statements and return the result-set as well-formed XML data structures • How to build a distributed JDBC application that allows to interact with a JDBC data source over the Internet using HTTP