Java Tutorial - Java Scipt :
JDBC Example
JDBC and XML are natural partners in providing open access to an enterprise’s data. JDBC offers standardized Java access to many data stores, and XML is ideal for interchanging data. In our example, we’ll be extracting from legacy JDBC and making it available as XML. This is written as a standalone Java program, but it could easily be an EJB or a Java Web service. The JDBC portion connects with the example database we used in Chapter 9. Our program will connect to the JDBC source, run a userspecified SQL query against it, and present the result set as XML.
Here’s the source code for xml_jdbc_test.java:
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import org.jdom.*;
import org.jdom.output.*;
public class xml_jdbc_test extends JFrame {
private String msDriver;
private String msUrl;
private String msUser;
private String msPassword;
Adding XML Power Tools 467
private JButton moQueryButton;
private JTextArea moQueryArea;
private JTextArea moResultsArea;
private Connection moConnection;
public xml_jdbc_test(
String sDriver,
String sUrl,
String sUser,
String sPassword )
{
super( “XML-JDBC test” );
msDriver = sDriver;
msUrl = sUrl;
msUser = sUser;
msPassword = sPassword;
try {
setupConnection();
}
catch( Exception e ) {
e.printStackTrace();
System.exit( 1 );
}
createGui();
}
protected void setupConnection()
throws Exception
{
Class.forName( msDriver );
moConnection = DriverManager.getConnection( msUrl, msUser, msUrl );
}
protected void createGui()
{
setBounds( 50, 50, 500, 500 );
moQueryArea = new JTextArea();
moResultsArea = new JTextArea();
moQueryButton = new JButton( “Query” );
moQueryButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent oActionEvent )
{
doQuery();
468 Chapter 12
}
}
);
// --- Build the query panel. ---
JPanel oQueryPanel = new JPanel( new BorderLayout() );
oQueryPanel.add( new JScrollPane( moQueryArea ) );
oQueryPanel.add(
new JLabel( “Enter the SQL Query here: “ ),
BorderLayout.NORTH );
// --- Build the results panel. ---
JPanel oResultsPanel = new JPanel( new BorderLayout() );
oResultsPanel.add( new JScrollPane( moResultsArea ) );
oResultsPanel.add(
new JLabel( “XML representation of the query results: “ ),
BorderLayout.NORTH );
// --- Aggregate the text area panels. ---
JPanel oTextAreasPanel = new JPanel( new GridLayout( 2, 1 ) );
oTextAreasPanel.add( oQueryPanel );
oTextAreasPanel.add( oResultsPanel );
// --- Construct the main panel. ---
JPanel oPanel = new JPanel( new BorderLayout() );
oPanel.add( oTextAreasPanel );
oPanel.add( moQueryButton, BorderLayout.SOUTH );
getContentPane().add( oPanel );
setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE );
}
public void doQuery() {
// --- Clear the textareas. ---
String sQuery = moQueryArea.getText();
moResultsArea.setText( “Query: “ + sQuery + “\n\n” );
try {
Statement oStatement = moConnection.createStatement();
ResultSet oResultSet = oStatement.executeQuery( sQuery );
ResultSetMetaData oMetaData = oResultSet.getMetaData();
Document oDocument = new Document(
new Element( “query-results” ) );
Element oQueryElement = new Element( “sql-query” );
oQueryElement.setText( sQuery );
oDocument.getRootElement().addContent( oQueryElement );
Adding XML Power Tools 469
Element oResultSetElement = new Element( “result-set” );
oDocument.getRootElement().addContent( oResultSetElement );
int iRowCount = 0;
while( oResultSet.next() ) {
++iRowCount;
// --- Export each row to XML as it is retrieved.---
// Note: uses base 1 indexing.
Element oRowElement = new Element( “row” );
oResultSetElement.addContent( oRowElement );
oRowElement.setAttribute( “index”, “” + iRowCount );
for( int idxColumn = 1;
idxColumn <= oMetaData.getColumnCount(); ++idxColumn ) {
Element oColumnElement = new Element( “column” );
oColumnElement.setAttribute(
“name”,
oMetaData.getColumnName( idxColumn ) );
oColumnElement.setAttribute(
“type”,
oMetaData.getColumnTypeName( idxColumn ) );
oColumnElement.setAttribute(
“value”,
oResultSet.getString( idxColumn ) );
oRowElement.addContent( oColumnElement );
}
}
oResultSet.close();
oResultSetElement.setAttribute( “count”, “” + iRowCount );
// --- Append the document to the text area. ---
XMLOutputter oOutputter = new XMLOutputter( “ “, true );
moResultsArea.setText(
moResultsArea.getText() +
oOutputter.outputString( oDocument ) );
}
catch( Exception e ) {
moResultsArea.setText(
“Exception encountered. “ +
e.getMessage() );
if( !( e instanceof SQLException ) ) {
e.printStackTrace();
}
}
}
public static void main( String args[] ) {
String sDriver = “org.hsqldb.jdbcDriver”;
470 Chapter 12
String sUrl = “jdbc:hsqldb:d:/openjava/hsqldb/data/hsqltest”;
String sUser = “sa”;
String sPassword = “”;
if( args.length > 0 && args[0] != “” )
sDriver = args[0];
if( args.length > 1 && args[1] != “” )
sUrl = args[1];
if( args.length > 2 && args[2] != “” )
sUser = args[2];
if( args.length > 3 && args[3] != “” )
sPassword = args[3];
System.out.println( “Driver: “ + sDriver );
System.out.println( “URL : “ + sUrl );
System.out.println( “User : “ + sUser );
System.out.println( “Passwd: “ + sPassword );
xml_jdbc_test oTest =
new xml_jdbc_test( sDriver, sUrl, sUser, sPassword );
oTest.show();
}
}
To compile in Windows, use the following command:
javac -classpath .;%JDOM_HOME%\build\jdom.jar xml_jdbc_test.java
In Linux, the command is:
javac -classpath .:$JDOM_HOME/build/jdom.jar xml_jdbc_test.java
You’ll need to have a reference to the JDBC driver .jar file in your classpath to run the example.
To run the example on Windows, use the following command:
java -classpath .;mysql-connector-java-3.0.6-stable.jar;%JDOMHOME%\ build\jdom.jar xml_jdbc_test
In Linux, the command is:
java -classpath .: mysql-connector-java-3.0.6-stable.jar:$JDOM_HOME/build/jdom.jar xml_jdbc_test
The xml_jdbc_test is a Swing program. There are two text areas at the top and a query button at the bottom. SQL queries can be entered into the top text area. The bottom text area shows the result of the SQL query as XML. To run the query, press the “Query” button. The xml_jdbc_test example is a rather academic program. However, it can be used as a basis for more useful programs. It’s possible to extract existing relational database schemas and expose those as XML documents. The XML can be persisted or made available via a servlet. You can also use the mapping the other way, importing data from an XML document and storing it in a JDBC database. The goal of this example is give you an idea of how the two technologies can work together. [NR1]
