Handling Multiple Elements within Record

[dan486]

Hi!

I have been working on parsing an XML file and have run into a problem. I am using Java SAX, and while parsing, I am feeding data to an oracle database.

So, here is my problem:

In the XML file I have, there are sometimes many of the same tags within a record, for example, one line of the xml might read:

<actor><stagename>Julia Roberts</stagename><relationtype><relationname>Hugh Grant</relationname></relationtype><dateofBirth/><relationtype><relationname>someotherguy</relationname>
</relationtype></actor>

What I have been doing up to now is parsing the data such that if the qualified name is actor, then it prepares to insert a record into the database. The code utilizes SAX to get all the records parsed.

import java.io.*;
 
import java.sql.*;
 
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
 
import javax.xml.parsers.SAXParserFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
 
public class Actors extends DefaultHandler
{
    static Connection conn;
    static Statement insertActorStatement;
 
    public static void main(String argv[]) throws SQLException
    {
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection
        ("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "ASDF",*****");
        insertActorStatement = conn.createStatement();
 
        // Use an instance of ourselves as the SAX event handler
        DefaultHandler handler = new Actors();
        // Use the default (non-validating) parser
        SAXParserFactory factory = SAXParserFactory.newInstance();
        try {
            // Parse the input
            SAXParser saxParser = factory.newSAXParser();
            saxParser.parse( new File(argv[0]), handler);
 
        } catch (Throwable t) {
            t.printStackTrace();
        }
        System.exit(0);
    }
 
 
    int count = 0;
    String insertActorString = null;
 
    //===========================================================
    // SAX DocumentHandler methods
    //===========================================================
 
    public void startDocument() throws SAXException
    {
    }
 
    public void endDocument() throws SAXException
    {  }
 
    boolean isActor = false;
    boolean isStagename = false;
    boolean isFamilyname = false;
    boolean isDob = false;
    boolean isStudio = false;
    boolean isRelationships = false;
    //    boolean isNotes = false;
 
 
    public void startElement(String namespaceURI,
                             String lName, // local name
                             String qName, // qualified name
                             Attributes attrs) throws SAXException
    {
 
    if ( qName.equals("actor") ) {
            this.isActor = true;
            this.insertActorString =
                    "INSERT INTO Actors(StageName, FamilyName, DateOfBirth, Studio, Relationships) " +
                    "VALUES (stagenameval, familynameval, dobval, studioval, relationshipsval)";
            System.out.print(++count + " ");
        }
        if ( qName.equals("stagename") ) this.isStagename = true;
        if ( qName.equals("familyname") ) this.isFamilyname = true;
        if ( qName.equals("dob") ) this.isDob = true;
        if ( qName.equals("studio") ) this.isStudio = true;
        if ( qName.equals("relationships") ) this.isRelationships= true;
    //    if ( qName.equals("notes") ) this.isNotes = true;
 
    }
 
    public void endElement(String namespaceURI,
                           String sName, // simple name
                           String qName  // qualified name
                          ) throws SAXException
    {
        if ( qName.equals("stagename") ) this.isStagename = false;
        if ( qName.equals("familyname") ) this.isFamilyname = false;
        if ( qName.equals("dob") ) this.isDob = false;
        if ( qName.equals("studio") ) this.isStudio = false;
        if ( qName.equals("relationships") ) this.isRelationships= false;
    //        if ( qName.equals("notes") ) this.isNotes = false;
 
 
        if ( qName.equals("actor") ) {
 
            //Replace any missing field value by NULL
            this.insertActorString = this.insertActorString.replaceAll("stagenameval", "NULL");
            this.insertActorString = this.insertActorString.replaceAll("familynameval", "NULL");
            this.insertActorString = this.insertActorString.replaceAll("dobval", "NULL");
            this.insertActorString = this.insertActorString.replaceAll("studioval", "NULL");
            this.insertActorString = this.insertActorString.replaceAll("relationshipsval", "NULL");
        //  this.insertActorString = this.insertActorString.replaceAll("notesval", "NULL");
 
            System.out.println(this.insertActorString);
            try {
                insertActorStatement.executeUpdate(this.insertActorString);
            }
            catch (SQLException ex) {
                System.out.println("execution**************************: " + ex);
                System.exit(1);
            }
 
        }
    }
 
 
    public void characters(char buf[], int offset, int len) throws SAXException
    {
        if ( this.isStagename == true ) {
            String s = new String(buf, offset, len);
            this.insertActorString = this.insertActorString.replaceAll("stagenameval",
                                                            "'" + s.replaceAll("'","''") + "'");
        }
 
        if ( this.isFamilyname == true ) {
            String s = new String(buf, offset, len);
            this.insertActorString = this.insertActorString.replaceAll("familynameval",
                                            "'" + s.replaceAll("'", "''") + "'");
        }
 
        if ( this.isDob == true ) {
            String s = new String(buf, offset, len);
            this.insertActorString = this.insertActorString.replaceAll("dobval",
                                                                "'" + s.trim() + "'");
        }
 
        if ( this.isStudio == true ) {
            String s = new String(buf, offset, len);
            this.insertActorString = this.insertActorString.replaceAll("studioval",
                                                                "'" + s.replaceAll("'","''") + "'");
        }
 
        if ( this.isRelationships == true ) {
            String s = new String(buf, offset, len);
            this.insertActorString = this.insertActorString.replaceAll("relationshipsval",
                                            "'" + s.replaceAll("'", "''") + "'");
        }
    //        if ( this.isNotes == true ) {
    //         String s = new String(buf, offset, len);
    //         this.insertActorString = this.insertActorString.replaceAll("notesval",
    //                                        "'" + s.replaceAll("'","''") + "'");
        //}
    }
 
} //end of class
 

So what I would like to be able to do, is for situations where there is more than one relationship for an actor, I would like to insert a new Actors touple into my database for each one. The specific problem that I am having is that i'm not sure how I would insert the touple because the way my code is set up currently, it's only one touple per instance of actor.

If you could point me in the right direction I'd be grateful.
Thanks
Dan