UsefulUserName UsefulUserName - 3 months ago 19
Java Question

Converting large SQLite tables to XML

I am trying to convert a large (-ish) table into XML. for that purpose I am using DOM Parser, based on how Mkyong does here.

This works fine for smaller tables, but I'm having trouble with a table with 7 columns and about 3.5 million rows. It just takes forever. Could you suggest a better method or point out where I could improve my code?

I am not allowed to use anything but Java and/or SQLite. Maybe i have not searched with the right keywords, but google didn't turn up anything good so far.

public class Export {

public static void export() {
String select = "Select * FROM individuen ORDER BY ID";
DBController dbc = DBController.getInstance();
dbc.initDBConnection();

try {
Statement stmt = DBController.connection.createStatement();
ResultSet rs = stmt.executeQuery(select);
DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docFactory.newDocumentBuilder();

// root elements
Document doc = docBuilder.newDocument();
Element rootElement = doc.createElement("individuen");
doc.appendChild(rootElement);

while (rs.next()) {
Element person = doc.createElement("Person");
rootElement.appendChild(person);

// set attribute to person element
Attr attr = doc.createAttribute("id");
attr.setValue(Integer.toString(rs.getInt(1)));
person.setAttributeNode(attr);

Element geschlecht = doc.createElement("Geschlecht");
geschlecht.appendChild(doc.createTextNode(Integer.toString(rs.getInt(2))));
person.appendChild(geschlecht);

[...]
}

System.out.println("oooooooooooout");
// write the content into xml file
TransformerFactory transformerFactory = TransformerFactory.newInstance();
Transformer transformer = transformerFactory.newTransformer();
DOMSource source = new DOMSource(doc);
StreamResult result = new StreamResult(new File("file.xml"));

// Output to console for testing
// StreamResult result = new StreamResult(System.out);

transformer.transform(source, result);

System.out.println("Als XML exportiert!");

} catch (ParserConfigurationException pce) {
pce.printStackTrace();
} catch (TransformerException tfe) {
tfe.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Answer

Thank you for your tips, I went with SAX/StAX and it's working fine now. Here is part of the code for anybody who might be looking for something similar:

public void export(Population pop) {
    String select = "Select * FROM individuen ORDER BY ID";
    DBController dbc = DBController.getInstance();
    dbc.initDBConnection();

    XMLOutputFactory factory = XMLOutputFactory.newInstance();
    XMLStreamWriter writer;

    try {
        Statement stmt = DBController.connection.createStatement();
        ResultSet rs = stmt.executeQuery(select);
        writer = factory.createXMLStreamWriter(new FileOutputStream("output.xml"));

        // Creating the XML-Header
        writer.writeStartDocument();
        // Root Element
        writer.writeStartElement("individuen");
        while (rs.next()) {

            // Element Person, encompasses the other Elements
            writer.writeStartElement("Peron");
            writer.writeAttribute("ID", rs.getString(1));

            // Element Geschlecht
            writer.writeStartElement("Geschlecht");
            writer.writeCharacters(rs.getString(2));
            writer.writeEndElement();

            // Element Alter
            writer.writeStartElement("Alter");
            writer.writeCharacters(rs.getString(3));
            writer.writeEndElement();

            // Element Alter
            writer.writeStartElement("Familienstand");
            writer.writeCharacters(rs.getString(4));
            writer.writeEndElement();

            // Element PartnerID
            writer.writeStartElement("PartnerID");
            writer.writeCharacters(rs.getString(4));
            writer.writeEndElement();

            // Element Bildungsstand
            writer.writeStartElement("Bildungsstand");
            writer.writeCharacters(rs.getString(5));
            writer.writeEndElement();

            // Closes Element Person
            writer.writeEndElement();
        }
        writer.writeEndDocument();
        writer.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (XMLStreamException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }