Code to generate XML data from a JDBC query
//---------------------------------------------------------------------------||
// xmlfromdb2.java ||
// ||
// This file shows how to generate XML data from a JDBC query. For this ||
// code to work, you need to have DB2 installed, along with the XML Extender ||
// and the sample database built in the XML Extender tutorial. If you use ||
// another database, or another database server, you'll need to change the ||
// JDBC initialization code appropriately. ||
// ||
// You'll also need to change the DbOwner, DbUserid, and DbPasswd variables ||
// to the values for your system. ||
//---------------------------------------------------------------------------||
// Written 17 February 2000 by Doug Tidwell. ||
//---------------------------------------------------------------------------||
// ||
// (C) Copyright IBM Corp. 1999 All rights reserved. ||
// ||
// US Government Users Restricted Rights Use, duplication or ||
// disclosure restricted by GSA ADP Schedule Contract with IBM Corp. ||
// ||
// The program is provided "as is" without any warranty express or ||
// implied, including the warranty of non-infringement and the implied ||
// warranties of merchantibility and fitness for a particular purpose. ||
// IBM will not be liable for any damages suffered by you as a result ||
// of using the Program. In no event will IBM be liable for any ||
// special, indirect or consequential damages or lost profits even if ||
// IBM has been advised of the possibility of their occurrence. IBM ||
// will not be liable for any third party claims against you. ||
// ||
//---------------------------------------------------------------------------||
// Import base Java classes
import java.io.*;
import java.net.*;
import java.util.*;
import java.lang.*;
import java.awt.*;
import java.sql.*;
import java.util.*;
import java.text.*;
// Import servlet classes
import javax.servlet.*;
import javax.servlet.http.*;
// Import classes from IBM's XML4J parser
import com.ibm.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;
/**
* This class illustrates how to parse an XML document and use the resulting
* document tree. The document tree conforms to the W3C's DOM standard.
*/
public class xmlfromdb2 extends HttpServlet
{
static String url=null;
// Strings to hold password info
static String DbOwner = null;
static String DbUserid = null;
static String DbPasswd = null;
// Strings to build the JDBC URL
static String Db = "db2";
static String DbName = "sales_db";
static String JDBCDriver = "COM.ibm.db2.jdbc.app.DB2Driver";
static String DbSource ="jdbc:" + Db + ":" + DbName;
// JDBC connections
static int [] Constat = {0,0};
static Connection con0;
static Connection con1;
private static boolean dbconnect = false;
static
{
// When the first instance of this class is created, we register
// the driver with DriverManager.
try
{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
}
catch (Exception e)
{
System.out.println("Can't get the driver!"); e.printStackTrace();
}
}
// We'll connect to the database during init()
public void init(ServletConfig cfig)
{
// / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / //
// Be sure to change these three strings appropriately, or the //
// servlet won't work. //
// / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / //
DbUserid = "xxxxxxxx";
DbPasswd = "xxxxxxxx";
DbOwner = "xxxxxxxx";
// URL is jdbc:db2:sales_db
url = "jdbc:" + Db + ":" + DbName;
try
{
// if webserver and DB on same machine and using same userid and password,
// connect with default id/password
if (DbUserid.equals ("null"))
{
con0 = DriverManager.getConnection(url);
con1 = DriverManager.getConnection(url);
}
// execute these lines if webserver and DB not on same machine
// connect with specific id/password
else
{
con0 = DriverManager.getConnection(url, DbUserid, DbPasswd);
con1 = DriverManager.getConnection(url, DbUserid, DbPasswd);
}
con0.setAutoCommit(false);
con1.setAutoCommit(false);
if ((con0 != null) && (con1 != null))
dbconnect = true;
}
catch( Exception e )
{
e.printStackTrace();
}
if (con0 == null)
System.out.println("con0 is null");
if (con1 == null)
System.out.println("con1 is null");
}
// service() is where the real work gets done
public void service(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
PrintWriter out = res.getWriter();
// We hardcoded the SQL statement here; this would be more
// sophisticated if we qualified the query based on user input.
String query = "select order from " + DbOwner + ".sales_order_view";
res.setContentType("text/xml");
try
{
ConInfo index = new ConInfo();
Connection con = getCon(index);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Now we create an XML document. The first thing we
// need to do is write the XML header. Notice also that
// we use the flights document type definition (DTD) to
// tell the XML parser what tags we're using and how the
// tags relate to each other.
out.print("<?xml version=\"1.0\" ?>\n");
out.print("<orders>\n");
DOMParser parser = new DOMParser();
// Display the result set. We take each row and convert the
// fields into the tags we're using. rs.next() returns
// false when there are no more rows.
while (rs.next())
{
String nextOrder = rs.getString(1).trim();
Document doc = null;
StringReader sr = new StringReader(nextOrder);
InputSource iSrc = new InputSource(sr);
try
{
parser.parse(iSrc);
doc = parser.getDocument();
}
catch (Exception e)
{
System.err.println("Sorry, an error occurred: " + e);
}
if (doc != null)
printDOMTree(doc, out);
}
rs.close();
stmt.close();
putCon(index.index);
}
catch (Exception e) {e.printStackTrace();}
out.print("</orders>\n");
}
/** Prints the specified node, recursively. */
public void printDOMTree(Node node, PrintWriter out)
{
int type = node.getNodeType();
switch (type)
{
// print the document element
case Node.DOCUMENT_NODE:
{
printDOMTree(((Document)node).getDocumentElement(), out);
break;
}
// print element with attributes
case Node.ELEMENT_NODE:
{
out.print("<");
out.print(node.getNodeName());
NamedNodeMap attrs = node.getAttributes();
for (int i = 0; i < attrs.getLength(); i++)
{
Node attr = attrs.item(i);
out.print(" " + attr.getNodeName() +
"=\"" + attr.getNodeValue() +
"\"");
}
out.print(">");
NodeList children = node.getChildNodes();
if (children != null)
{
int len = children.getLength();
for (int i = 0; i < len; i++)
printDOMTree(children.item(i), out);
}
break;
}
// handle entity reference nodes
case Node.ENTITY_REFERENCE_NODE:
{
out.print("&");
out.print(node.getNodeName());
out.print(";");
break;
}
// print cdata sections
case Node.CDATA_SECTION_NODE:
{
out.print("<![CDATA[");
out.print(node.getNodeValue());
out.print("]
7]>");
break;
}
// print text
case Node.TEXT_NODE:
{
out.print(node.getNodeValue());
break;
}
// print processing instruction
case Node.PROCESSING_INSTRUCTION_NODE:
{
out.print("</?");
out.print(node.getNodeName());
String data = node.getNodeValue();
{
out.print(" ");
out.print(data);
}
out.print("?>");
break;
}
}
if (type == Node.ELEMENT_NODE)
{
out.print("</");
out.print(node.getNodeName());
out.print('>');
}
} // printDOMTree(Node, PrintWriter)
// get a database connection for a statement to use
private synchronized Connection getCon(ConInfo index)
{
int i = 0;
if (Constat[0] == 0)
{
index.index = 0;
Constat[0]=1;
return (con0);
}
else if (Constat[1] == 0)
{
index.index = 1;
Constat[1]=1;
return (con1);
}
else
{
index.index = -1;
return(null);
}
}
// release the database connection so it can be reused
private synchronized void putCon(int index)
{
Constat[index] = 0;
}
} //end of class parseXML
class ConInfo
{
int index;
ConInfo()
{
this.index = 0;
}
} //end class ConInfo
// ftp://go.cygnus.com/pub/sourceware.cygnus.com/pub/cygwin/latest/full-split/
|