Java and Database Connectivity: Slide 116

Moving to XML
Sample Java class

import java.io.*;       // to read things from the user
import java.sql.*;      // to talk to the database
import uk.co.weft.domutil.*;    // things to convert elements to namespaces
import uk.co.weft.dbutil.*; // things to store namespaces in databases
import org.w3c.dom.*;       // interrogates a DOM tree...
import org.apache.xerces.dom.*; // using Apache's DOM implementation
import org.apache.xalan.xslt.*; // Apache's XSL processor
import org.apache.xerces.parsers.DOMParser;
                // and Apache's XML parser


public class ParseExample
{
    static Context connectionContext = new Context();
                // a context to hold database
                // connection details

    /** walk down a document tree looking for nodes we recognise */
    public static void walk( Node node)
    throws SQLException, DataStoreException
    {
    if ( node.getNodeType() == Node.ELEMENT_NODE)
        {
        Element elt = ( Element) node;

        System.out.println( "Considering element of type " +
                    elt.getTagName());

        if ( elt.getTagName().equals( "workshop"))
            handleWorkshop( elt);
        else
            {
            NodeList children = elt.getChildNodes();

            for ( int i = 0; i < children.getLength(); i++)
                walk( children.item( i));
                // recurse down through the children
            }
        }
    }


    /** handle a workshop element; extract its attribute (and
     *  actually, it's text-only child) values, and store them in the
     *  database. Then look for attendees.*/
    protected static void handleWorkshop( Element elt) 
    throws SQLException, DataStoreException
    {
    Object key = null;

    Context c = ( Context)connectionContext.clone(); 
                // construct a new namespace with just
                // the database connection details in
                // it
    ContextElement.populateContext( elt, c);
                // fill it with values from the element

    TableDescriptor workshopDescriptor = 
        TableDescriptor.getDescriptor( "WORKSHOP", "Workshop", c);
                // get a descriptor on the WORKSHOP table

    Contexts rows = workshopDescriptor.match( c);
                // try to match that against what's
                // already in the table

    if ( rows != null && rows.size() > 0)
        {           // there was a match
        key = ( ( Context)rows.get( 0)).getValueAsInteger( "Workshop");
                // get its primary key value
        System.out.println( "Found workshop " + key.toString());
        }
    else
        {
        key = workshopDescriptor.store( c);
                // store it and get its primary key value
        System.out.println( "Created workshop " + key.toString());
        }

    NodeList children = elt.getChildNodes();

    for ( int i = 0; i < children.getLength(); i++)
        {           // look through the children for my attendees
        Node child = children.item( i);

        if ( child.getNodeType() == Node.ELEMENT_NODE &&
             ( ( Element) child).getTagName().equals( "attendee"))
            {
            handleAttendee( ( Element)child, key);
            }
        }
    }

    /** handle an attendee element by finding or storing it in the
     *  database, and fixing up the link table */
    protected static void handleAttendee( Element elt, Object workshopKey)
    throws SQLException, DataStoreException
    {
    Object attendeeKey = null;

    Context c = ( Context)connectionContext.clone(); 
                // construct a new namespace with just
                // the database connection details in
                // it
    ContextElement.populateContext( elt, c);
                // fill it with values from the element

    TableDescriptor attendeeDescriptor = 
        TableDescriptor.getDescriptor( "ATTENDEE", "Attendee", c);
                // get a descriptor on the ATTENDEE table

    Contexts rows = attendeeDescriptor.match( c);
                // try to match that against what's
                // already in the table

    if ( rows != null && rows.size() > 0)
        {           // there was a match
        attendeeKey = 
            ( ( Context)rows.get( 0)).getValueAsInteger( "Attendee");
                // get its primary key value
        System.out.println( "Found attendee " + 
                    attendeeKey.toString());
        }
    else
        {
        attendeeKey = attendeeDescriptor.store( c);
                // store it and get its primary key value
        System.out.println( "Created attendee " + 
                    attendeeKey.toString());
        }

    String q = "insert into ATTENDANCE ( Attendee, Workshop) values ("
        + attendeeKey.toString() + ", " + workshopKey.toString() + ")";

    Connection conn = c.getConnection();
    Statement s = conn.createStatement();
                // set up a database connection

    s.executeUpdate( q);    // run the statement
    System.out.println( "Inserted link into link table");

    s.close();      // close it...
    c.releaseConnection( conn);
                // and release it back into the pool
    }

    /** prompt the user for input; if we get any, return it */
    protected static String maybeGetFromUser( BufferedReader in, String prompt,
                       String val) throws IOException
    {
    System.out.print( prompt + " ] ");

    String s = in.readLine();

    if ( s != null || s.length() == 0)
        val = s.trim();
    
    return val;
    }

    /** start me up... */
    public static void main(String args[]) 
    {
    BufferedReader in = new 
        BufferedReader( new InputStreamReader( System.in));

                // get from the user the name of the
                // database driver to use
    try
        {
        Class.forName( 
              maybeGetFromUser( in, "Database Driver", 
                    "sun.jdbc.odbc.JdbcOdbcDriver"));

                // get from the user the details
                // needed to connect to the database
        connectionContext.put( "db_url", 
                   maybeGetFromUser( in, "Database URL", 
                         "jdbc:odbc:workshop"));
        connectionContext.put( "db_username", 
                   maybeGetFromUser( in, "Database Username", 
                         "nobody"));
        connectionContext.put( "db_password", 
                   maybeGetFromUser( in, "Database Password", 
                         "doesntmatter"));


        DOMParser p = new DOMParser();
            
        p.parse( maybeGetFromUser( in, "URL of XML to handle", 
                         "file:workshop.xml"));

        walk( p.getDocument().getDocumentElement());

        System.exit( 0); // all satisfactory
        }
    catch ( Exception e)
        {
        System.out.println( "Failed: " + e.getClass().getName() +
                    ": " +e.getMessage());
        System.exit( 1); // whoops
        }
    }
}