Generic database through JDBC to InputSource


import java.sql.*;
import java.io.*;
import org.xml.sax.*;
import org.apache.xerces.parsers.*; 
import org.apache.xml.serialize.*; 


public class SQLDatabaseToInputSource {
  
  public InputSource sendQuery(String query) throws IOException {
    
    PipedWriter out = new PipedWriter();
    Reader reader = new PipedReader(out);
    Thread t = new DatabaseAccessThread(out, query);   
    t.start();
    return new InputSource(reader);
    
  } 
  
  private String userName;
  private String password;  // should be a char[] for security
  private String host;
  private String database;                                     
  private String driver;  
                                    
  public SQLDatabaseToInputSource(
   String host, String database, String userName, String password, String driver) 
   throws IOException {
    
    this.driver = driver;
    this.host = host;
    this.database = database;
    this.userName = userName;
    this.password = password;
    
  }
  
  
  class DatabaseAccessThread extends Thread {
  
    private Writer out;
    private String query;
    
    DatabaseAccessThread(Writer out, String query) {
      this.out = out;
      this.query = query;
    }

    public void run() {
      
      try {
        
        Class.forName(driver).newInstance(); 
        
        // Connect to the database
        Connection connection = DriverManager.getConnection(
          "jdbc:mysql://" + host + "/" + database, userName, password);
                   
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        
        ResultSetMetaData metadata = rs.getMetaData();
        int numColumns = metadata.getColumnCount();
        
        
        String[] names = new String[numColumns+1]; 
        String[] types = new String[numColumns+1];

        for (int i = 1; i < numColumns; i++) {
          names[i] = metadata.getColumnName(i);
          types[i] = metadata.getColumnTypeName(i);
        }        
        
        out.write("<?xml version=\"1.0\"?>\r\n");
        out.write("<ResultSet>\r\n");
         
        while (rs.next()) {
          
          out.write("  <Row>\r\n");
          for (int i = 1; i <= numColumns; i++) {
            String data = rs.getString(i);
            if (data != null) writeElement("Field", data, types[i], names[i]);
          }
          
          out.write("  </Row>\r\n\r\n");
          
        }         
  
        out.write("</ResultSet>\r\n");
        out.flush();
                   
      }
      catch (Exception e) {
        System.err.println(e);
        e.printStackTrace();
      }
      
    }
    
    private void writeElement(
     String elementName, String content, String type, String fieldName) 
     throws IOException {
      
      // would be straight-forward to omit null elements
      // rather than making them empty
      out.write('<' + elementName + " type=\"" + type + "\" name=\"" + fieldName + "\">");
      char[] characters = content.toCharArray();
      for (int i = 0; i < characters.length; i++) {
        switch (characters[i]) {
          case '&':
            out.write("&amp;");
            break;
          case '<':
            out.write("&lt;");
            break;
          default:
            out.write(characters[i]); 
        }
      }
      out.write("</" + elementName + ">\r\n");
      
    } 
  
  } // end DatabaseAccess
  
  public static void main(String[] args) {
    
   try {
     SQLDatabaseToInputSource btis 
      = new SQLDatabaseToInputSource("luna.oit.unc.edu", 
      "NYWC", "elharo", args[0], "org.gjt.mm.mysql.Driver");

     InputSource in = btis.sendQuery(
      "SELECT * FROM composers ORDER BY ComposerLastName, ComposerFirstName");
     
     // read the document...
     XMLReader parser = new SAXParser();
     OutputFormat format = new OutputFormat("xml", "ISO-8859-1", true);
     parser.setContentHandler(new XMLSerializer(System.out, format));
     parser.parse(in);

   } 
   catch (ArrayIndexOutOfBoundsException e) {
     System.out.println("Usage: java SQLDatabaseToInputSource password");
   }
   catch (Exception e) {
     System.err.println(e);
     e.printStackTrace();
   }
   
  }
  

}

View results in Browser
Previous | Next | Top | Cafe con Leche

Copyright 2000 Elliotte Rusty Harold
elharo@metalab.unc.edu
Last Modified March 13, 2000