hqm
01-21-2003, 07:52 AM
Some people had asked recently about code to convert JDBC queries
into XML.
Here is a very simple example of doing this using manually using JDBC and JDOM. JDOM
is a DOM library for Java that is said to be easier to use than the w3c.org DOM
Java bindings.
JDOM is available from www.jdom.org
This example issues a query "select * from employees" to a database
and then formats the result as an XML document.
For a 'employees' table with columns 'name', 'age', and 'job', and two rows of data, the result might
look like this
<?xml version="1.0" encoding="UTF-8" ?>
<sqldata>
<row>
<name>fred frumble</name>
<age>33</age>
<job>punch press operator</job>
</row>
<row>
<name>freda fertilizer</name>
<age>23</age>
<job>farm hand</job>
</row>
</sqldata>
The example also shows the use of a JDBC connection pool, using a third party library
called jdbcpool.
The code doesn't do any error handling to speak of, it's just to show the
use of JDOM and JDBC together.
/* JDBCTest.java
*
*/
import java.io.*;
import java.text.*;
import java.util.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import org.jdom.*;
import org.jdom.output.*;
import com.bitmechanic.sql.*;
/**
* Example JDBC to XML conversion, talking to Postgres,
*
* Demonstrates use of JDBC connection pool as well.
* See http://www.bitmechanic.com/projects/jdbcpool/ for connection pool library
*
* @author Henry Minsky
*/
public class JDBCTest extends HttpServlet {
private ConnectionPoolManager pool;
private String alias = "jdbcalias";
public void init(ServletConfig config) throws javax.servlet.ServletException {
super.init(config);
// Setup connection pool
try {
//
// Reap stale connections every 2 minutes
pool = new ConnectionPoolManager(120);
pool.addAlias(alias, // pool name
"org.postgresql.Driver", //driver
"jdbc:postgresql:testdb", //url is jdbc:postgresql:DATABASE_NAME
"postgres", // user
"", // password
20, // maxconn
600, // idletimeout
600); // checkout timeout
} catch (Exception e) {
}
}
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException
{
response.setContentType("text/xml");
PrintWriter out = response.getWriter();
String query = "SELECT name, age, job FROM employees";
try {
Document doc = new Document();
Element root = new Element("sqldata");
doc.setRootElement(root);
Connection conn = DriverManager.getConnection(ConnectionPoolManager. URL_PREFIX + alias, null, null);
Statement stmt = conn.createStatement();
ResultSet resultset = stmt.executeQuery(query);
//Get the ResultSet information
ResultSetMetaData resultmetadata = resultset.getMetaData();
//Determine the number of columns in the ResultSet
int numCols = resultmetadata.getColumnCount();
while (resultset.next()) {
List elts = new ArrayList();
for (int i=1; i <= numCols; i++) {
//For each column index, determine the column name
String colName = resultmetadata.getColumnName(i);
//Get the column value
String colVal = resultset.getString(i);
//Output the name and value
Element elt = new Element(colName);
elt.setText(colVal);
elts.add(elt);
}
Element row = new Element("row");
row.setChildren(elts);
root.addContent(row);
}
resultset.close();
stmt.close();
conn.close();
XMLOutputter outputter = new XMLOutputter();
try {
outputter.output(doc, out);
}
catch (IOException e) {
out.println(e);
}
} catch (SQLException e) {
out.println(e);
}
}
Assuming that servlet is installed at the URL shown below, (/examples/servlet/JDBCTest)
then the following laszlo program will load the data and display it:
<canvas width="1000" height="800" debug="true">
<!-- setup HTTP datasource -->
<dataset name="xml" autorequest="true"
src="http://www.beartronics.com:8080/examples/servlet/JDBCTest"/>
<view>
<view fontstyle="bold">
<simplelayout axis="x" spacing="4"/>
<text width="100" name="name" label="name" />
<text width="40" name="age" label="age" />
<text width="200" name="job" label="job" />
</view>
<view datapath="xml:/sqldata/row">
<text bgcolor="#ffcccc" width="100" name="name" datapath="name/text()" />
<text bgcolor="#ffcccc" width="40" name="age" datapath="age/text()" />
<text bgcolor="#ffcccc" width="200" name="job" datapath="job/text()" />
<simplelayout axis="x" spacing="4"/>
</view>
<simplelayout axis="y" spacing="4"/>
</view>
</canvas>
into XML.
Here is a very simple example of doing this using manually using JDBC and JDOM. JDOM
is a DOM library for Java that is said to be easier to use than the w3c.org DOM
Java bindings.
JDOM is available from www.jdom.org
This example issues a query "select * from employees" to a database
and then formats the result as an XML document.
For a 'employees' table with columns 'name', 'age', and 'job', and two rows of data, the result might
look like this
<?xml version="1.0" encoding="UTF-8" ?>
<sqldata>
<row>
<name>fred frumble</name>
<age>33</age>
<job>punch press operator</job>
</row>
<row>
<name>freda fertilizer</name>
<age>23</age>
<job>farm hand</job>
</row>
</sqldata>
The example also shows the use of a JDBC connection pool, using a third party library
called jdbcpool.
The code doesn't do any error handling to speak of, it's just to show the
use of JDOM and JDBC together.
/* JDBCTest.java
*
*/
import java.io.*;
import java.text.*;
import java.util.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import org.jdom.*;
import org.jdom.output.*;
import com.bitmechanic.sql.*;
/**
* Example JDBC to XML conversion, talking to Postgres,
*
* Demonstrates use of JDBC connection pool as well.
* See http://www.bitmechanic.com/projects/jdbcpool/ for connection pool library
*
* @author Henry Minsky
*/
public class JDBCTest extends HttpServlet {
private ConnectionPoolManager pool;
private String alias = "jdbcalias";
public void init(ServletConfig config) throws javax.servlet.ServletException {
super.init(config);
// Setup connection pool
try {
//
// Reap stale connections every 2 minutes
pool = new ConnectionPoolManager(120);
pool.addAlias(alias, // pool name
"org.postgresql.Driver", //driver
"jdbc:postgresql:testdb", //url is jdbc:postgresql:DATABASE_NAME
"postgres", // user
"", // password
20, // maxconn
600, // idletimeout
600); // checkout timeout
} catch (Exception e) {
}
}
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException
{
response.setContentType("text/xml");
PrintWriter out = response.getWriter();
String query = "SELECT name, age, job FROM employees";
try {
Document doc = new Document();
Element root = new Element("sqldata");
doc.setRootElement(root);
Connection conn = DriverManager.getConnection(ConnectionPoolManager. URL_PREFIX + alias, null, null);
Statement stmt = conn.createStatement();
ResultSet resultset = stmt.executeQuery(query);
//Get the ResultSet information
ResultSetMetaData resultmetadata = resultset.getMetaData();
//Determine the number of columns in the ResultSet
int numCols = resultmetadata.getColumnCount();
while (resultset.next()) {
List elts = new ArrayList();
for (int i=1; i <= numCols; i++) {
//For each column index, determine the column name
String colName = resultmetadata.getColumnName(i);
//Get the column value
String colVal = resultset.getString(i);
//Output the name and value
Element elt = new Element(colName);
elt.setText(colVal);
elts.add(elt);
}
Element row = new Element("row");
row.setChildren(elts);
root.addContent(row);
}
resultset.close();
stmt.close();
conn.close();
XMLOutputter outputter = new XMLOutputter();
try {
outputter.output(doc, out);
}
catch (IOException e) {
out.println(e);
}
} catch (SQLException e) {
out.println(e);
}
}
Assuming that servlet is installed at the URL shown below, (/examples/servlet/JDBCTest)
then the following laszlo program will load the data and display it:
<canvas width="1000" height="800" debug="true">
<!-- setup HTTP datasource -->
<dataset name="xml" autorequest="true"
src="http://www.beartronics.com:8080/examples/servlet/JDBCTest"/>
<view>
<view fontstyle="bold">
<simplelayout axis="x" spacing="4"/>
<text width="100" name="name" label="name" />
<text width="40" name="age" label="age" />
<text width="200" name="job" label="job" />
</view>
<view datapath="xml:/sqldata/row">
<text bgcolor="#ffcccc" width="100" name="name" datapath="name/text()" />
<text bgcolor="#ffcccc" width="40" name="age" datapath="age/text()" />
<text bgcolor="#ffcccc" width="200" name="job" datapath="job/text()" />
<simplelayout axis="x" spacing="4"/>
</view>
<simplelayout axis="y" spacing="4"/>
</view>
</canvas>