Extract database tables in XML

Question

I am trying to create a Java class where it will connect to a MySQL database and will extract the data from the tables.

I followed this tutorial. But the problem is I do not know the data coming from the tables as :

We have more than 1 table and
We are dealing with a quit big table.

The connection is established but I cannot write data to the XML file.

My code so far for the connection part is:

public class ExtractTo {
    static String driverName = "com.mysql.jdbc.Driver";
    static String connectURL = "jdbc:mysql://localhost/[database_name]?";
   // static String user="root";
    //static String password="";
    static Statement stmt=null;
    static Connection db = null; 
    static ResultSet rslt=null;
    private static String SQLquery="SELECT * FROM [TABLE_NAME]";
    static ResultSetMetaData resultmetadata = null;
    static Document dataDoc=null;
    static Document Doc=null;
 public static void main(String [] args) {
    try {

       Class.forName(driverName).newInstance();
       db = DriverManager.getConnection(connectURL+"user=root&password=");

       stmt=db.createStatement();
       rslt=stmt.executeQuery(SQLquery);
    } [all the catch] ...
}

I don't see any line trying to iterate the result and creating an XML, that's probably why. If you don't know how to write an XML, you should first find a guide to follow. — AxelH, Nov 20 '17 at 12:25
@assembler If you want to make an edit, you'll need to fix all the things that are wrong the post. Not only is deleting "Thanks" on its own worth an edit, you also ignored the much more prevalent problem, which was that the code highlighting in this post is totally messed up. If you edit, do it properly please — Neuron, Nov 20 '17 at 12:30
@AxelH yes, it was meant to be a comment, thanks for pointing it out :'D — Neuron, Nov 20 '17 at 12:31
@AxelH Hi. The code that I used to extract to xml is in a different method and it's the same as in the link posted. I thought it would be better not to imprort all the methods as it will be a mess and as explained above this option does not quite helps me. — Mike M. Kaspin, Nov 20 '17 at 12:32
@MikeM.Kaspin maybe but I don't click links... so at least post a [mcve] explaining what is not working with that. DId you try the XML generation separatly ? Debugging would help. — AxelH, Nov 20 '17 at 12:33
@AxelH Of course I did. I understand that you do not want to click links. As explained, the problem is that almost every option for XML exporting requires the knowledge of the database schema.I do not have this option for the reasons mentioned above. Thanks for your recommendations. — Mike M. Kaspin, Nov 20 '17 at 12:36
I invite you to do some research on [`ResultSet.getMetaData()`](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getMetaData--). Like [here](https://stackoverflow.com/questions/9201128/how-to-map-a-resultset-with-unknown-amount-of-columns-to-a-list-and-display-it-i) or [here](https://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset). You will find the answer to one problem (unknow table content) — AxelH, Nov 20 '17 at 12:39

score 0 · Answer 1 · answered Nov 20 '17 at 13:22

I will post my own answer in case someone has the same question as I did.

My final code is:

 try {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
       DocumentBuilder builder        = factory.newDocumentBuilder();
       Document doc  = builder.newDocument();
    File f = new File(enter_path_here);
    FileWriter writer = new FileWriter(f); 
   Class.forName(driverName).newInstance();
   db = DriverManager.getConnection(connectURL+"user=root&password=");

   Element results = doc.createElement("Final");
   doc.appendChild(results);    
   stmt=db.createStatement();
   rslt=stmt.executeQuery(SQLquery);
   resultmetadata=rslt.getMetaData();

   while(rslt.next()) {
       Element row = doc.createElement("Information");
         results.appendChild(row);

         for(int i=1;i<=resultmetadata.getColumnCount();i++) {
           String name=resultmetadata.getColumnName(i);
           Object value= rslt.getObject(i);
           Element node=  doc.createElement(name);
           node.appendChild(doc.createTextNode(value == null ? "" : value.toString()));
           row.appendChild(node);
       }

   }
   StreamResult result= new StreamResult(writer);
   DOMSource source = new DOMSource(doc);
   TransformerFactory transformerFactory = TransformerFactory.newInstance();
   Transformer transformer = transformerFactory.newTransformer();
   transformer.transform(source, result);

} catch (ClassNotFoundException e) {
   System.out.println("Error creating class: "+e.getMessage());
} catch (SQLException e) {
   System.out.println("Error creating connection: "+e.getMessage());
} catch (InstantiationException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (IllegalAccessException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (ParserConfigurationException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (TransformerConfigurationException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (TransformerException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
finally {
    System.out.println("Closing Database Connection...");
    try {
        rslt.close();
        stmt.close();
        db.close();
    }catch(SQLException e){
        System.out.println("Can't close connection!" + e);
    }
}

}

The above code will create a .xml file where it will the format above :

<?xml version="1.0" encoding="UTF-8" standalone="no"?><Final><Information><TABLE_ID>321231</TABLE_ID></Information></Final>

Hope it helps!

Extract database tables in XML

1 Answers1