title: Inserting XML file into MYSQL database
body: <p>Good afternoon, Java beginner here and I have been working with this tutorial online and the tutorial supplied this code along with an XML file. It said that it was based off of inserting the XML file into MySQL but doesn't auto insert the fields. So what I have done is created a database named movie, and have created a table named 'about'. So coming from the XML there are 4 field names. I've tried creating the 4 fields inside of that table but I end up getting the error:</p>
  
  <pre><code>Field:year
  Field:title
  Field:movieid
  Field:unsortabletitle
  INSERT INTO about VALUES (?,?,?,?)
  java.sql.SQLException: Incorrect integer value: 'The Godfather' for column 'year' at row 1
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
      at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1347)
      at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:958)
      at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1494)
      at xSQLTEST.Inserter.startElement(Inserter.java:138)
      at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl.parse(Unknown Source)
      at javax.xml.parsers.SAXParser.parse(Unknown Source)
      at xSQLTEST.Inserter.run(Inserter.java:60)
      at xSQLTEST.Inserter.main(Inserter.java:87)
  java.sql.SQLException: Incorrect integer value: 'The Shawshank Redemption' for column 'year' at row 1
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
      at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1347)
      at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:958)
      at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1494)
      at xSQLTEST.Inserter.startElement(Inserter.java:138)
      at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
      at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
      at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl.parse(Unknown Source)
      at javax.xml.parsers.SAXParser.parse(Unknown Source)
      at xSQLTEST.Inserter.run(Inserter.java:60)
      at xSQLTEST.Inserter.main(Inserter.java:87)
  Exception in thread "main" java.lang.ArithmeticException: / by zero
      at xSQLTEST.Inserter.run(Inserter.java:63)
      at xSQLTEST.Inserter.main(Inserter.java:87)
  </code></pre>
  
  <p>Any Information at this point would be very helpful. I have tried researching the error, and I couldn't find much information on it. If anyone is willing to run this and help me set up MySql table correctly I would greatly appreciate it.
      package xSQLTEST;</p>
  
  <pre><code>import java.io.File;
  import java.io.IOException;
  import java.io.OutputStreamWriter;
  import java.io.Writer;
  import java.sql.PreparedStatement;
  import java.util.HashMap;
  import java.util.Iterator;
  
  import java.sql.DriverManager;
  import java.sql.Connection;
  import java.sql.SQLException;
  
  import javax.xml.parsers.ParserConfigurationException;
  import javax.xml.parsers.SAXParser;
  import javax.xml.parsers.SAXParserFactory;
  
  import org.xml.sax.Attributes;
  import org.xml.sax.SAXException;
  import org.xml.sax.helpers.DefaultHandler;
  
  public class Inserter extends DefaultHandler {
  
          HashMap fieldnames = new HashMap();
          HashMap values = new HashMap();
          String tableName = "movie";
          Connection c = null;
          PreparedStatement ps = null;
          static final   String       sNEWLINE   = System.getProperty( "line.separator" );
          static private Writer       out        = null;
          private        StringBuffer textBuffer = null;
          int     numErrors = 0;
          long numInserts = 0;
  
  
          public void run(String [] args) {
                  SAXParser saxParser;
  
  
                  tableName = "about";
                  String file = "C:\\insertxml.xml";
  
  
                  try {
                          try {
                                  Class.forName("com.mysql.jdbc.Driver");
                          } catch (ClassNotFoundException cnfe) {
                                  System.out.println("Couldn't find the driver!");
                                  System.out.println("Let's print a stack trace, and exit.");
                                  cnfe.printStackTrace();
                                  System.exit(1);
                          }
  
                          c = DriverManager.getConnection("jdbc:mysql://localhost:3306/movies",
                                          "root", "Password!");
                          saxParser = SAXParserFactory.newInstance().newSAXParser();
                          long timePre = System.currentTimeMillis();
                          saxParser.parse( new File( file ), this );
                          long timeAfter = System.currentTimeMillis();
                          long timeTaken = (timeAfter - timePre)/1000;
                          long insertsPerSecond = numInserts/timeTaken;
                          System.out.println("Errors: " + numErrors);
                          System.out.println("Inserts: " + numInserts);
                          System.out.println("Inserts per second: " + insertsPerSecond);
                          System.out.println("Seconds taken: " + (System.currentTimeMillis() - timePre)/1000);
  
  
                  } catch (ParserConfigurationException e) {
                          // TODO Auto-generated catch block
                          e.printStackTrace();
                  } catch (SAXException e) {
                          // TODO Auto-generated catch block
                          e.printStackTrace();
                  } catch (IOException e) {
                          // TODO Auto-generated catch block
                          e.printStackTrace();
                  } catch (SQLException e) {
                          // TODO Auto-generated catch block
                          e.printStackTrace();
                  }
  
          }
  
          public static void main(String [] args) {
                  new Inserter().run(args);
  
          }
  
  //      ---- SAX DefaultHandler methods ----
  
          public void startDocument()
          throws SAXException
          {
                  //echoString( sNEWLINE + "&lt;?xml ...?&gt;" + sNEWLINE + sNEWLINE );
          }
  
          public void endDocument()
          throws SAXException
          {
                  echoString( sNEWLINE );
          }
  
          public void startElement( String namespaceURI,
                          String localName,   // local name
                          String qName,       // qualified name
                          Attributes attrs )  throws SAXException {
                  echoTextBuffer();
                  String eName = ( "".equals( localName ) ) ? qName : localName;
  
                  if(eName.equalsIgnoreCase("FIELD")) {
                          fieldnames.put(attrs.getValue("FieldName"), attrs.getValue("FieldType"));
                          System.out.println("Field:" + attrs.getValue("FieldName"));
                  } else if(eName.equalsIgnoreCase("ROW")) {
                          try {
                                  // for each field prepare statement
                                  Iterator itFields = fieldnames.keySet().iterator();
                                  int fieldIdx = 1;
                                  while(itFields.hasNext()) {
                                          String fieldName = itFields.next().toString();
                                          String type = fieldnames.get(fieldName).toString();
                                          String value = attrs.getValue(fieldName);
  
                                          if(type.equalsIgnoreCase("Integer")) {
                                                  try {
                                                          ps.setInt(fieldIdx, Integer.parseInt(value));
                                                  } catch (NumberFormatException e) {
                                                          e.printStackTrace();
                                                          numErrors++;
                                                  }
                                          } else {
                                                  ps.setString(fieldIdx, value);
                                          }
                                          fieldIdx++;
                                  } // end while
  
                                  ps.execute();
                                  if(numInserts % 50000 == 0) {
                                          System.out.println(numInserts + " inserts made.");
                                  }
                                  numInserts++;
  
                                  ps.clearParameters();
                          } catch (SQLException e) {
                                  numErrors++;
                                  e.printStackTrace();
                          }
                  }
  
          }
  
          public void endElement( String namespaceURI,
                          String localName,     // local name
                          String qName )        // qualified name
          throws SAXException
          {
  
                  String eName = ( "".equals( localName ) ) ? qName : localName;
                  if(eName.equalsIgnoreCase("METADATA")) {
                          // build sql
                          StringBuffer buf = new StringBuffer();
                          for(int i = 0; i &lt; fieldnames.size(); i++) {
                                  buf.append("?");
                                  if(i &lt; (fieldnames.size()-1)) {
                                          buf.append(",");
                                  }
                          }
  
  
                          try {
                                  ps = c.prepareStatement("INSERT INTO " + tableName + " VALUES (" + buf.toString() + ")");
                                  System.out.println("INSERT INTO " + tableName + " VALUES (" + buf.toString() + ")");
                          } catch (SQLException e) {
                                  // TODO Auto-generated catch block
                                  e.printStackTrace();
                          }
  
                  }
  
  
  //              echoTextBuffer();
  
  //              echoString( "&lt;/" + eName + "&gt;" );           // element name
          }
  
          public void characters( char[] buf, int offset, int len )
          throws SAXException
          {
  //              String s = new String( buf, offset, len );
  //              if( textBuffer == null )
  //              textBuffer = new StringBuffer( s );
  //              else
  //              textBuffer.append( s );
          }
  
          // ---- Helper methods ----
  
          // Display text accumulated in the character buffer
          private void echoTextBuffer()
          throws SAXException
          {
                  if( textBuffer == null )  return;
                  echoString( textBuffer.toString() );
                  textBuffer = null;
          }
  
          // Wrap I/O exceptions in SAX exceptions, to
          // suit handler signature requirements
          private void echoString( String s )
          throws SAXException
          {
                  try {
                          if( null == out )
                                  out = new OutputStreamWriter( System.out, "UTF8" );
                          out.write( s );
                          out.flush();
                  } catch( IOException ex ) {
                          throw new SAXException( "I/O error", ex );
                  }
          }
  </code></pre>
  
  <hr>
  
  <pre><code>&lt;?xml version="1.0" encoding="ISO-8859-15" standalone="no"?&gt;
  &lt;DATAPACKET Version="2.0"&gt;
          &lt;METADATA&gt;
                  &lt;FIELDS&gt;
                          &lt;FIELD FieldName="year" DisplayLabel="year" FieldType="Integer" FieldClass="TField"/&gt;
                          &lt;FIELD FieldName="title" DisplayLabel="title" FieldType="String" FieldClass="TField"/&gt;
                          &lt;FIELD FieldName="movieid" DisplayLabel="movieid" FieldType="String" FieldClass="TField"/&gt;
                          &lt;FIELD FieldName="unsortabletitle" DisplayLabel="unsortabletitle" FieldType="String" FieldClass="TField"/&gt;
                  &lt;/FIELDS&gt;
          &lt;/METADATA&gt;
          &lt;ROWDATA&gt;
                  &lt;ROW year="1972" title="Godfather, The" movieid="A0AD59890237CBC738B3EDE134846225" unsortabletitle="The Godfather"/&gt;
                  &lt;ROW year="1994" title="Shawshank Redemption, The" movieid="569150D16B224C8A5E2E3D320FC0A6CA" unsortabletitle="The Shawshank Redemption"/&gt;
          &lt;/ROWDATA&gt;
  &lt;/DATAPACKET&gt;
  </code></pre>
  
  <p>How I set it up in MySQL:</p>
  
  <pre><code>CREATE TABLE about
  (
  year int,
  title varchar(255),
  movieid varchat(255),
  unsortabletitle varchar(255)
  );
  </code></pre>