java - 将XML文件插入MYSQL数据库

Question

下午好，Java 初学者，我一直在网上学习本教程，该教程提供了此代码以及 XML 文件。它说它基于将 XML 文件插入 MySQL，但不会自动插入字段。所以我所做的是创建一个名为 movie 的数据库，并创建一个名为“about”的表。 XML 中有 4 个字段名称。我尝试在该表中创建 4 个字段，但最终收到错误:

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)

此时的任何信息都会非常有帮助。我尝试研究该错误，但找不到太多相关信息。如果有人愿意运行这个并帮助我正确设置 MySql 表，我将不胜感激。 包xSQLTEST；

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 + "<?xml ...?>" + 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 < fieldnames.size(); i++) {
                                buf.append("?");
                                if(i < (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( "</" + eName + ">" );           // 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 );
                }
        }

<小时/>

<?xml version="1.0" encoding="ISO-8859-15" standalone="no"?>
<DATAPACKET Version="2.0">
        <METADATA>
                <FIELDS>
                        <FIELD FieldName="year" DisplayLabel="year" FieldType="Integer" FieldClass="TField"/>
                        <FIELD FieldName="title" DisplayLabel="title" FieldType="String" FieldClass="TField"/>
                        <FIELD FieldName="movieid" DisplayLabel="movieid" FieldType="String" FieldClass="TField"/>
                        <FIELD FieldName="unsortabletitle" DisplayLabel="unsortabletitle" FieldType="String" FieldClass="TField"/>
                </FIELDS>
        </METADATA>
        <ROWDATA>
                <ROW year="1972" title="Godfather, The" movieid="A0AD59890237CBC738B3EDE134846225" unsortabletitle="The Godfather"/>
                <ROW year="1994" title="Shawshank Redemption, The" movieid="569150D16B224C8A5E2E3D320FC0A6CA" unsortabletitle="The Shawshank Redemption"/>
        </ROWDATA>
</DATAPACKET>

我如何在 MySQL 中设置它:

CREATE TABLE about
(
year int,
title varchar(255),
movieid varchat(255),
unsortabletitle varchar(255)
);

Answer 1

好的，我找到问题的原因了。

看看这段代码:

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 Test 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 {
                        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; // <---- Error if timeTaken is zero
                        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();
                }

        }

        public static void main(String [] args) {
                new Test().run(args);

        }

//      ---- SAX DefaultHandler methods ----

        public void startDocument()
        throws SAXException
        {
                //echoString( sNEWLINE + "<?xml ...?>" + 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")) {
                                // 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")) {
                                            System.out.println(fieldIdx+" (integer) value:"+value);
                                        } else {
                                            System.out.println(fieldIdx+" (string) value:"+value);
                                        }
                                        fieldIdx++;
                                } // end while
                }

        }

        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 < fieldnames.size(); i++) {
                                buf.append("?");
                                if(i < (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( "</" + eName + ">" );           // 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 );
                }
        }
}

它的输出是:

    Field:year
    Field:title
    Field:movieid
    Field:unsortabletitle
    1 (string) value:The Godfather
    2 (string) value:Godfather, The
    3 (integer) value:1972
    4 (string) value:A0AD59890237CBC738B3EDE134846225
    1 (string) value:The Shawshank Redemption
    2 (string) value:Shawshank Redemption, The
    3 (integer) value:1994
    4 (string) value:569150D16B224C8A5E2E3D320FC0A6CA

    Exception in thread "main" java.lang.ArithmeticException: / by zero
        at Test.run(Test.java:49)
        at Test.main(Test.java:70)

这是因为您的代码假设 XML 将按照写入的顺序读取，但事实并非如此。 XML 中的属性没有既定的顺序。因此第一项不一定是年份，因此当您解析它时，您会得到异常。

要修复您的代码，请使用字段名称来确定表中的位置，并且不要相信第一个属性是年份，第二个属性是标题等。