XMLMill and JDBC

Using a database to produce .pdf documents has two major facets: selection and output. This chapter describes how to use JDBC to select and manipulate data and to produce pdf documents.

This chapter does not describe XML extensions of database products that some vendors offers.

Example: TestJDBC

This example shows:

• Defining which data to show using a regular SQL select statement.
• Convert the ResultSet to a XML document.
• Send the XML document to the JAXPTransformer instance using PipedReader and PipedWriter instances.

[001] 
[002] 
[003] package com.xmlmilltest;
[004] 
[005] import com.xmlmill.*;
[006] import com.xmlmill.config.Configurator;
[007] 
[008] import java.sql.*;
[009] import java.io.PipedWriter;
[010] import java.io.PipedReader;
[011] import java.io.BufferedWriter;
[012] import java.io.BufferedReader;
[013] import java.io.File;
[014] 
[015] 
[016] public class TestJDBC {
[017] 
[018]     // Connection to the database we will use.
[019]     Connection conn;  
[020]     
[021]     public TestJDBC()   // note more general exception
[022]     {
[023]       // Singleton class
[024]     }    
[025] 
[026]     public static void main(String[] args) 
[027]     throws Throwable {
[028]       JAXPTransformer transform = null;
[029]       PipedWriter writer     = null;
[030]       PipedReader reader     = null;
[031]       BufferedReader br      = null;
[032]       Connection conn        = null;
[033]       ResultSet  resultset   = null;
[034]       File logfile           = null;
[035]       FileLogger logger      = null;
[036]       String     sql;
[037]       
[038]       try {        
[039]         // -------------------------------------
[040]         // Part I : 1. Get database-connection -
[041]         //          2. Create statement        -
[042]         //          3. Get resultset           -
[043]         //          4. Get MetaData set        -
[044]         // -------------------------------------
[045]           // Load the HSQL Database Engine JDBC driver
[046]           // hsqldb.jar should be in the class path or made part of the 
                current jar
[048]           Class.forName("org.hsqldb.jdbcDriver");
[049] 
[050]           // Connect to the database.
[051]           conn = 
                DriverManager.getConnection("jdbc:hsqldb:hsql://localhost", 
[053]                                          "sa",                
[054]                                          "");                 
[055]           // Create a statement
[056]           Statement   statement = conn.createStatement();
[057] 
[058]           // Create a statement
[059]           statement = conn.createStatement();
[060]           sql = "SELECT * FROM phonelist";
[061] 
[062]           // Get a resultset
[063]           resultset = statement.executeQuery(sql);
[064]           
[065]           // Get the Medata used to generate the .xml document
[066]           ResultSetMetaData md = resultset.getMetaData();
[067] 
[068]         // ---------------------------------------------
[069]         // Part II : 1. Create a JAXPTransformer  instance -
[070]         //          2. Set corresponding methods       -
[071]         // ---------------------------------------------        
[072]         
[073]         ////////////////////////////////////////////////////
[074]         // DEFINE FIRST THE LOGGER INSTANCE TO USE        //
[075]         // HERE DEFAULT com.xmlmill.log.FileLogger        //
[076]         // (you can use any class which implements the    //
[077]         // org.apache.commons.logging.Log interface)      //
[078]         ////////////////////////////////////////////////////
[079]         
[080]         // Use the default FileLogger class
[081]         logger = new FileLogger("xmlmill");
[082] 
[083]         // Set logfile
[084]         logger.setFileName("file:/C:/xmlmill3.00/testtransform.txt", 
              false);
[086]       
[087]         // Set the loglevel 
[088]         logger.setLevel("INFO");
[089] 
[090]         // Pass the logger to the XMLMill Configurator, so XMLMill can use 
              it.
[092]         Configurator.setLogger(logger);
[093]     
[094]         //////////////////////////////////////////////
[095]         // INITIALIZE THE CONFIGURATOR              //
[096]         //////////////////////////////////////////////    
[097]          
[098]         // Define a configurator (optional)
[099]         Configurator.configure();
[100]         
[101]         
[102]         //////////////////////////////////////////////
[103]         // CONTINUE                                 //
[104]         //////////////////////////////////////////////       
[105]         
[106]         // Define a tranform object 
[107]         transform = new JAXPTransformer();
[108]                 
[109]         // Define an errorhandler(optional) »
[110]         JAXPErrorHandler ehandler = new JAXPErrorHandler(
[111]                 JAXPErrorHandler.ABORT_ON_ERROR );
[112] 
[113]         // Set the xsl file
[114]         transform.setXSLDocument(new 
              File("C:\\XMLMill140d3\\examples\\phonelist.xsl")); 
[116] 
[117]         // Define where images and other resources resides on the server 
[118]         
              transform.setBaseURL("file:/C:/XMLMill140d3/examples/phonelist.xsl");
[120] 
[121]         // Set the errorhandler
[122]         transform.setErrorHandler(ehandler);
[123]         
[124]         // Set the output folder
[125]         transform.setOutputFolder(new 
              File("C:\\XMLMill140d3\\examples\\output"));
[127]         
[128]         // -----------------------------------------------------
[129]         //  Part III:  1. Define a PipedWriter and PipedReader -        
[130]         //             2. Do the transformation                -
[131]         // -----------------------------------------------------
[132]         // Create a pipe       
[133]         writer = new PipedWriter();
[134]         reader = new PipedReader(writer);
[135]     
[136]         // Start thread going and write into pipe
[137]         new dumpAsXML(writer, md, resultset).start();
[138]     
[139]         // read from other end of pipe
[140]         br = new BufferedReader(reader);
[141] 
[142]         transform.setXMLDocument(br);        
[143]         
[144]         // ... and transform the object to a pdf file
[145]         transform .transform();  
[146]         System.out.println("-- Done --");                                  
              
[148]          
[149]      } finally {
[150]        if (writer != null) {
[151]          writer.close();
[152]          writer = null;
[153]        }  
[154]        if (reader != null) {
[155]          reader.close();
[156]          reader = null;
[157]        } 
[158]        if (conn != null) {
[159]          conn.close();
[160]          conn = null;
[161]        }
[162]        if (br != null) {
[163]          br.close();
[164]          br = null;
[165]        }         
[166]        if (transform != null)
[167]          transform = null;
[168]          
[169]        // Close the logger instance
[170]        logger.finalize();
[171]        logger = null;
[172] 
[173]      }
[174]    }    
[175] }  

The previous example has three Parts

Part I:	This part makes a connection with a database and defines a ResultSet containing the data to be processed.
Part II:	A JAXPTransformer instance is defined and the corresponding methods are defined.
Part III:	This part defines PipedReader and PipedWriter instances, converts the raw data in the ResultSet to xml elements and then pipes this data to the JAXPTransformer instance using a separate thread (see the dumpAsXML class below).

[001] 
[002] class dumpAsXML extends Thread {
[003]   private PipedWriter out ;
[004]   private ResultSetMetaData md ;
[005]   private ResultSet rs ;
[006]     
[007]   public dumpAsXML(PipedWriter out, ResultSetMetaData md, ResultSet rs) {
[008]     this.out = out;
[009]     this.md  = md;
[010]     this.rs  = rs;
[011]   }
[012]     
[013]   public void run() {
[014]     // write into one end of pipe
[015]     BufferedWriter bw = null;
[016]        
[017]     try {
[018]       bw = new  BufferedWriter(out);
[019]          
[020]       dump(bw, "<?xml version='1.0' encoding='UTF-8' standalone='no'?>");
[021]       //dump(pr, "<" + md.getTableName(1)+"s>");
[022] 
[023]       dump(bw, "\t<"+ md.getTableName(1).toLowerCase()+">");
[024]       while (rs.next()) {
[025]         dump(bw,"<record>");
[026]         for ( int i = 1; i<= md.getColumnCount(); i++) {
[027]           dump(bw, "\t\t<" + md.getColumnName(i).toLowerCase()+">");
[028]           dump(bw, "<![CDATA[" + rs.getString(i) + "]]>" );
[029]           dump(bw, "</" + md.getColumnName(i).toLowerCase() +">"); 
[030]         }
[031]         dump(bw,"</record>");
[032]         dump(bw, " ");
[033]       }
[034]       
[035]       dump(bw, "\t</" +md.getTableName(1).toLowerCase()+">");
[036]       //dump(pr,"</" + md.getTableName(1)+"s>");
[037]       bw.flush();  
[038]       bw.close();
[039]     }  catch (Exception e) {
[040]       System.err.println(e);
[041]     } finally {
[042]       if (bw != null) {
[043]         try {
[044]           bw.flush();  
[045]           bw.close();  
[046]         } catch (Exception e) {
[047]           //
[048]         }
[049]       }
[050]       bw = null;
[051]     }
[052]   }
[053]      
[054]   private void dump(BufferedWriter bw, String s) 
[055]   throws java.io.IOException {
[056]     bw.write(s.trim().toCharArray(), 0, s.trim().length()) ;  
[057]     System.err.println(s.trim());
[058]   }     
[059] }    
[060]         

The dumpAsXML class extends the Thread class. This class is responsible for converting the raw data in the ResultSet to xml elements and then piping this data to the PipedWrite instance.