Metacat

/**

 *        Name: DBWriter.java

 *     Purpose: A Class that reads in an XML text document and

 *              write its contents to a database connection

 *   Copyright: 2000 Regents of the University of California and the

 *              National Center for Ecological Analysis and Synthesis

 *     Authors: Matt Jones

 *     

 *     Version: '$Id$'

 */

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.net.URL;

import java.util.Properties;

import java.sql.*;

import org.xml.sax.ErrorHandler;

import org.xml.sax.InputSource;

import org.xml.sax.SAXException;

import org.xml.sax.SAXParseException;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

import com.sun.xml.tree.XmlDocument;

import com.sun.xml.tree.XmlDocumentBuilder;

import com.sun.xml.tree.SimpleElementFactory;

import com.sun.xml.parser.Resolver;

import com.sun.xml.parser.ValidatingParser;

public class DBWriter

{

    private XmlDocument			doc;

    private Connection			conn = null;

    // Constructer to init the class

    public DBWriter(String argv[]) 

	throws SAXException, SQLException, IOException, ClassNotFoundException {

        // Open a connection to the database

        conn = openDBConnection(

		"oracle.jdbc.driver.OracleDriver",

		"jdbc:oracle:thin:@localhost:test",

		argv[2], argv[3]);

	//

	// Load the document, using the appropriate custom

	// DOM elements.

	//

	XmlDocument	doc = createDocument (

		Resolver.createInputSource (new File (argv [1])),

		new FileInputStream (argv [0]),

		new ErrorPrinter ()

	);

	DBElement		root;

    	root = (DBElement)doc.getDocumentElement();

	root.normalize();

        root.writeXmlToDB(conn, 0);

    }

    public static void main (String argv [])

    {

	try {

	    if (argv.length != 4) {

		System.err.println ("usage: java DBWriter "

		    + "DBElement.props somefile.xml username password");

		System.exit (1);

	    }

            new DBWriter(argv);

	} catch (Throwable t) {

            t.printStackTrace (System.out);

	}

    }

    static private XmlDocument createDocument (

	InputSource	input,

	InputStream	propsStream,

	ErrorHandler	errorHandler

    ) throws SAXException, IOException

    {

	try {

	    XmlDocumentBuilder		builder = new XmlDocumentBuilder ();

	    ValidatingParser		parser = new ValidatingParser ();

	    //

	    // Configure the builder to create the right elements.

	    //

	    {

		Properties		props;

		SimpleElementFactory	factory;

		props = new Properties ();

		props.load (propsStream);

		factory = new SimpleElementFactory ();

		factory.addMapping (props, DBWriter.class.getClassLoader ());

		builder.setElementFactory (factory);

	    }

	    //

	    // Configure the parser

	    //

	    parser.setErrorHandler (errorHandler);

	    parser.setEntityResolver (new Resolver ());

	    parser.setDocumentHandler (builder);

	    parser.parse (input);

	    // Look at how the different versions print themselvs...

	    if (false) {

		builder.getDocument ().write (System.out);

		System.exit (0);

	    }

	    return builder.getDocument ();

	} catch (SAXParseException e) {

	    // already reported

	    throw e;

	} catch (SAXException e) {

	    e.printStackTrace (System.out);

	    throw e;

	} catch (IOException e) {

	    e.printStackTrace (System.out);

	    throw e;

	}

    }

    private Connection openDBConnection(String dbDriver, String connection, 

		String user, String password) 

		throws SQLException, ClassNotFoundException {

      // Load the Oracle JDBC driver

      Class.forName (dbDriver);

      // Connect to the database

      // You can put a database name after the @ sign in the connection URL.

      Connection conn = DriverManager.getConnection(

	connection, user, password);

      return conn;

    }

    static class ErrorPrinter implements ErrorHandler

    {

	private void message (String level, SAXParseException e)

	{

	    System.out.print ("** ");

	    System.out.println (level);

	    System.out.print ("   URI = ");

	    System.out.print (e.getSystemId ());

	    System.out.print (" Line = ");

	    System.out.println (e.getLineNumber ());

	    System.out.print ("   Message = ");

	    System.out.println (e.getMessage ());

	    /*

	    if (e.getException () != null)

		e.getException ().printStackTrace (System.out);

	    else

		e.printStackTrace (System.out);

	    */

	}

	public void error (SAXParseException e)

	{

	    // normally a validity error

	    message ("Error (recoverable)", e);

	}

	public void warning (SAXParseException e)

	{

	    message ("Warning", e);

	}

	public void fatalError (SAXParseException e)

	{

	    message ("FATAL ERROR", e);

	}

    }

}

#

#        Name: DBElement.props

#     Purpose: A set of properties that map element types to

#              the classes that are used to represent that type

#   Copyright: 2000 Regents of the University of California and the

#              National Center for Ecological Analysis and Synthesis

#     Authors: Matt Jones

#     

#     Version: '$Id$'

#

#

# NOTE:  Special tag name "*Element" defines the default class to be used,

# instead of com.sun.xml.tree.ElementNode, when no explicit entry exists.

# The name "*Document" is used similarly for the document itself.

#

*Element	= DBElement

# *Document	= DBDocument

# Note -- we only use the default tags in this application

/**

 *        Name: DBElement.java

 *     Purpose: A Class that represents an XML element and can

 *		write its contents to a database connection

 *   Copyright: 2000 Regents of the University of California and the

 *              National Center for Ecological Analysis and Synthesis

 *     Authors: Matt Jones

 *

 *     Version: '$Id$'

 */

import com.sun.xml.tree.ElementNode;

import org.w3c.dom.DOMException;

import org.w3c.dom.Element;

import org.w3c.dom.Attr;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

import org.w3c.dom.NamedNodeMap;

import org.w3c.dom.ProcessingInstruction;

import org.w3c.dom.Text;

import java.sql.*;

import java.io.IOException;

public class DBElement extends ElementNode {

    public DBElement () {

      super();

    };

    /**

     * Writes this element and all of its children out to the 

     * given database connection 

     */

    public void writeXmlToDB(Connection conn, int parentID) 

		throws IOException {

        int maxid=0;

        int newid=0;

        Statement stmt;

        try {

          stmt = conn.createStatement();

          stmt.execute("SELECT MAX(nodeid) FROM xml_elements");

          try {

            ResultSet rs = stmt.getResultSet();

            try {

              boolean tableHasRows = rs.next();

              if (tableHasRows) {

                try {

                  maxid = rs.getInt(1);

                } catch (SQLException e) {

                  System.out.println("Error with getInt: " + e.getMessage());

                }

              }

            } catch (SQLException e) {

              System.out.println("Error with next: " + e.getMessage());

            }

          } catch (SQLException e) {

            System.out.println("Error with getrset: " + e.getMessage());

          }

          stmt.close();

        } catch (SQLException e) {

          System.out.println("Error getting id: " + e.getMessage());

        }

        // assign a new ID number

        newid = maxid + 1;

        try {

          PreparedStatement pstmt;

          if (parentID != 0) {

            pstmt = conn.prepareStatement(

                "INSERT INTO xml_elements(nodeid, nodename, parentnodeid) " + 

		"VALUES (?, ?, ?)");

          } else {

            pstmt = conn.prepareStatement(

                "INSERT INTO xml_elements(nodeid, nodename) " + 

		"VALUES (?, ?)");

          }

          // Bind the values to the query

          pstmt.setInt(1, newid); // The first ? is for NODEID

          pstmt.setString(2, getTagName());// The second ? is for NODENAME

          if (parentID != 0) {

            pstmt.setInt(3, parentID);

          }

          // Do the insertion

          pstmt.execute();

          pstmt.close();

        } catch (SQLException e) {

          System.out.println(e.getMessage());

        }

        System.out.println("ID: " + newid + "\tTag: " + getTagName());

        // Now save this node's attributes

	writeAttributesToDB(conn, newid);

        // Now save this node's children

	if (hasChildNodes()) {

            try {

	      writeChildrenXmlToDB(conn, newid);

            } catch (SQLException e) {

              System.out.println(e.getMessage());

            }

        }

    }

    public void writeChildrenXmlToDB(Connection conn, int parentID) 

		throws IOException, SQLException {

      NodeList children = getChildNodes();

      if (children != null) {

        for (int i = 0; i < children.getLength(); i++) {

          if (children.item(i) instanceof ElementNode) {

            ((DBElement)children.item(i)).writeXmlToDB(conn, parentID);

          } else if (children.item(i) instanceof Text) {

             // Write the text to the now created parent node

             PreparedStatement pstmt = conn.prepareStatement(

                "UPDATE xml_elements SET nodedata = ? " + 

		"WHERE nodeid = ?");

             pstmt.setString(1, ((Text)children.item(i)).getNodeValue()); 

             pstmt.setInt(2, parentID); 

             pstmt.execute();

             pstmt.close();

          } else {

            System.out.println("    Other Node Type skipped.");

          }

        }

      }

    }

    /**

     * Writes this element's attributes out to the 

     * given database connection 

     */

    public void writeAttributesToDB(Connection conn, int nodeid) 

		throws IOException {

        NamedNodeMap attributes = getAttributes();

        if (attributes != null) {

            for (int i=0; i < attributes.getLength(); i++) {

               String name = ((Attr)attributes.item(i)).getName();

               String value = ((Attr)attributes.item(i)).getValue();

               System.out.println("    Saving attribute: " + name + "=" + value);

               int maxid=0;

               int newid=0;

               Statement stmt;

               try {

                 stmt = conn.createStatement();

                 stmt.execute("SELECT MAX(attributeid) FROM xml_attributes");

                 try {

                   ResultSet rs = stmt.getResultSet();

                   try {

                     boolean tableHasRows = rs.next();

                     if (tableHasRows) {

                       try {

                         maxid = rs.getInt(1);

                       } catch (SQLException e) {

                         System.out.println("Error with getInt: " + e.getMessage());

                       }

              }

                   } catch (SQLException e) {

                     System.out.println("Error with next: " + e.getMessage());

                   }

                 } catch (SQLException e) {

                   System.out.println("Error with getrset: " + e.getMessage());

                 }

                 stmt.close();

               } catch (SQLException e) {

                 System.out.println("Error getting id: " + e.getMessage());

               }

               // assign a new ID number

               newid = maxid + 1;

               try {

                 PreparedStatement pstmt;

                 pstmt = conn.prepareStatement(

                  "INSERT INTO xml_attributes(attributeid, nodeid, " +

                  "attributenumber, attributename, attributevalue) " +

		   "VALUES (?, ?, ?, ?, ?)");

                 // Bind the values to the query

                 pstmt.setInt(1, newid); 		// ATTRIBUTEID

                 pstmt.setInt(2, nodeid);		// NODEID

                 pstmt.setInt(3, i+1); 			// ATTRIBUTENUMBER

                 pstmt.setString(4, name);		// ATTRIBUTENAME

                 pstmt.setString(5, value);		// ATTRIBUTEVALUE

                 // Do the insertion

                 pstmt.execute();

                 pstmt.close();

               } catch (SQLException e) {

                 System.out.println(e.getMessage());

               }

            }

        }

    }

    // used by JTree to display this node

    public String toString ()

    {

	StringBuffer	value = new StringBuffer ();

	value.append ('<');

	value.append (getTagName ());

	value.append (getAttributes ().toString ());

	value.append ('>');

	return value.toString ();

    }

}