NYWC database through JDBC to XML


import java.sql.*;
import java.io.*;

public class BiosToXML {
  
  private static Writer out = new OutputStreamWriter(System.out);

  public static void main(String[] args) {
    
    String userName = "elharo";
    String password = args[0];
    String host = "luna.oit.unc.edu";

    try {
      //Requires JDBC MySQL Driver from http://www.worldserver.com/mm.mysql/
      Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
      
      // Connect to the database
      Connection connection = DriverManager.getConnection(
        "jdbc:mysql://" + host + "/NYWC", userName, password);
                 
      Statement statement = connection.createStatement();
      ResultSet bios = statement.executeQuery(
       "SELECT * FROM composers ORDER BY ComposerLastName, ComposerFirstName");
      
      out.write("<?xml version=\"1.0\"?>\r\n");
      out.write("<Composers>\r\n");
       
      while (bios.next()) {
        
        out.write("  <Composer>\r\n");
        /* primary key */
        out.write("      <first>");
        out.write(bios.getString("ComposerFirstName"));
        out.write("</first>\r\n");
        out.write("      <middle>");
        out.write(bios.getString("ComposerMiddleName"));
        out.write("</middle>\r\n");
        out.write("    <name>\r\n");
        out.write("      <last>");
        out.write(bios.getString("ComposerLastName"));
        out.write("</last>\r\n");
        out.write("    </name>\r\n");
        
        /* These can be null */
        writeElement("biography", bios.getString("ComposerBio"));
        writeElement("email", bios.getString("ComposerEmail"));
        writeElement("url", bios.getString("ComposerURL"));
        writeElement("title", bios.getString("ComposerTitle"));
        
        // Omit these for privacy in this example
/*        writeElement("voice", bios.getString("ComposerVoice"));
        writeElement("fax", bios.getString("ComposerFax"));
        writeElement("street", bios.getString("ComposerStreet"));
        writeElement("suite", bios.getString("ComposerSuite"));  */
        writeElement("state", bios.getString("ComposerState"));
        writeElement("city", bios.getString("ComposerCity"));
        writeElement("country", bios.getString("ComposerCountry"));
        writeElement("zip", bios.getString("ComposerZip"));
        Date expires = bios.getDate("ComposerMembershipExpires");
        String expiresDate = null;
        if (expires != null) expiresDate = expires.toString();
        writeElement("expires", expiresDate);
        
        out.write("  </Composer>\r\n\r\n");
      }         

      out.write("</Composers>\r\n");
      out.flush();
                 
    }
    catch (Exception e) {
      System.err.println("Unable to load driver.");
      System.err.println(e);
      e.printStackTrace();
    }
    
  }
  
  private static void writeElement(String name, String content) 
   throws IOException {
    
    // would be straight-forward to omit null elements
    // rather than making them empty
    out.write('<' + name + '>');
    if (content != null) {
      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("</" + name + ">\r\n");
    
  } 

}

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

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