Easiest way to transform mysql table "as is" into xml document ?

Question

Interested how to do it programmatically, not export table data once (there are tools for sure).

easiest way to transform mysql table "as is" into xml document ?

table contains say 2 rows: id name 1 "John Travolta" 2 "Mary Lynch"

ideally we'd need a class that's gonna eat login/passw/host of a database and

  GoodClass goodClass = new GoodClass(login, passw, host);
  String xml = goodClass.getTableData(nameOfTable);

 // xml : <table><row><num>1</num><id>1</id><name>John Travolta</name></row><row><num>2</num><id>2</id><name>Mary Lynch</name></row></table>

Answer 1

Break the problem into pieces:

1. Query for the data you want.
2. Marshal the ResultSet into an XML stream.

Security should be a separate concern.

Answer 2

The easiest way to do this would be to use JDBC to get a connection to your db (using the login info you're passing in), and then you'd either use the DatabaseMetaData class to get the columns of the table (which would be the tags like "" or "") (or you could use ResultSetMetaData after querying the table), and then just a "select * from " will get your data, and a simple while-loop using ResultSet.next() as your conditional will get your data.

I'd post a code snippet, but this kind of sounds like a homework problem, and if so, I wouldn't want to take away from a learning experience.

Answer 3

If you wanted to go overboard, you could use hibernate to get your database results as Java objects and then use JAXB or JiBX to marshal the objects to XML. But the easiest way is to connect to your database, execute a query, loop over the resultset and build the XML. Example code is shown below:

public class DataExtractor{

   private final String login, passwd, host;

   public DataExtractor(String login, String passwd, String host){
       this.login = login;
       this.passwd = passwd;
       this.host = host;
   }

   public String getTableData(String tableName) throws SQLException, ClassNotFoundException {
       Connection con = null;
       Statement st = null;
       ResultSet rs = null;
       try {
           Class.forName("com.mysql.jdbc.Driver");
           con = DriverManager.getConnection("jdbc:mysql:///" + host, login, passwd);

           st = con.createStatement();
           rs = st.executeQuery("select * from " + tableName);

           ResultSetMetaData rsmd = rs.getMetaData();
           int colCount = rsmd.getColumnCount();

           StringBuilder b = new StringBuilder("<table>\n");

           int num = 1;
           while (rs.next()) {
               b.append("<row>");
               b.append("<num>").append(num++).append("</num>");
               for (int i = 1; i <= colCount; i++) {
                   String columnName = rsmd.getColumnName(i);
                   b.append('<').append(columnName).append('>');
                   b.append(rs.getObject(i));
                   b.append("</").append(columnName).append('>');
               }
               b.append("</row>\n");
           }
           b.append("</table>");
           return b.toString();
       } catch (SQLException e) {
           throw e;
       } catch (ClassNotFoundException e) {
           throw e;
       } finally {
           if (rs != null)
               try {
                   rs.close();
               } catch (SQLException e) {
               }
           if (st != null)
               try {
                   st.close();
               } catch (SQLException e) {
               }
           if (con != null)
               try {
                   con.close();
               } catch (SQLException e) {
               }
       }
   }
}