Programming Tutorials

Using JDBC to extract data from a database and output to an XML document

By: Nicholas Chase in JDBC Tutorials on 2007-04-05  

Here's an example Java program that uses JDBC to extract data from a database and output to an XML document:

import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamWriter;

public class JdbcToXml {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "username";
        String password = "password";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        XMLOutputFactory factory = XMLOutputFactory.newFactory();
        XMLStreamWriter writer = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM employees");

            writer = factory.createXMLStreamWriter(new FileWriter("employees.xml"));

            writer.writeStartDocument();
            writer.writeStartElement("employees");

            while (rs.next()) {
                writer.writeStartElement("employee");

                writer.writeStartElement("id");
                writer.writeCharacters(rs.getString("id"));
                writer.writeEndElement();

                writer.writeStartElement("name");
                writer.writeCharacters(rs.getString("name"));
                writer.writeEndElement();

                writer.writeStartElement("salary");
                writer.writeCharacters(rs.getString("salary"));
                writer.writeEndElement();

                writer.writeEndElement();
            }

            writer.writeEndElement();
            writer.writeEndDocument();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

This program uses the JDBC API to connect to a MySQL database, execute a SELECT statement to retrieve data from the "employees" table, and then use the StAX API to write the data to an XML file named "employees.xml". The program also handles any exceptions and closes the resources (such as the database connection) in a finally block.