Database dump with Java

I need to update a database that is created by PHP. The problem is that I am not a PHP coder, but a Java coder, and I need to use some other Java libraries to get the job done. So how can find out exactly which tables to update and how? It would take me weeks to search the PHP code, and I still wouldn't be sure if I got it right.

The first step is to install a clean application on my computer. There is no user data in the database, so if I perform commands like creating a user etc in the web application, I can look at what changed in the database. I'm sure that could be done in MySQL, but I'm not an expert on that either. When the only tool you have is a hammer, everything looks like a nail. So, I'll use Java for that to.

So, I wrote a small Java application that produces exactly the output that I need. It reads metadata from the database to find all tables and columns, lists that metadata and the content of all the rows.

Here it is:

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

public class DumpDatabase {
   private Connection conn;
   private PrintWriter out;

   public static void main(String[] args) {
      try {
         DumpDatabase app = new DumpDatabase();
         String timestamp = new SimpleDateFormat("MMdd-hhmmss")
.format(new Date());
         String fileName = "dumpdatabase-" + timestamp + ".txt";
         System.out.println("Creating " + fileName);
         app.dumpDatabase(fileName);
         System.out.println("Done.");
      } catch(Exception e) {
         e.printStackTrace();
         System.exit(1);
      }
   }

   public DumpDatabase() throws IOException, 
ClassNotFoundException, SQLException {
      // Get database connection from hibernate.properties.
      // Or hard-code your own JDBC connection if desired.
      InputStream in = getClass().getResourceAsStream(
"/hibernate.properties");
      Properties properties = new Properties();
      properties.load(in);
      String driver = properties.getProperty(
"hibernate.connection.driver_class");
      String url = properties.getProperty(
"hibernate.connection.url");
      String user = properties.getProperty(
"hibernate.connection.username");
      String password = properties.getProperty(
"hibernate.connection.password");

      Class.forName(driver);
      conn = DriverManager.getConnection(url, user, password);
   }
   
   public void dumpDatabase(String fileName) 
throws FileNotFoundException, SQLException {
      out = new PrintWriter(fileName);
      listAll();
      out.close();
      conn.close();
   }

   public void listAll() throws SQLException {
      DatabaseMetaData metadata = conn.getMetaData();
      String[] types = { "TABLE" };
      ResultSet rs = metadata.getTables(
null, null, null, types);
      while(rs.next()) {
         String tableName = rs.getString("TABLE_NAME");
         listTable(tableName);
      }
   }

   private void listTable(String tableName) throws SQLException  {
      PreparedStatement statement = conn.prepareStatement(
            "select * from " + tableName + " a");
      out.println("----" + tableName + "----");
      int rowNo = 0;
      ResultSet rs = statement.executeQuery();
      while(rs.next()) {
         if (rowNo == 0)
            printTableColumns(rs);
         printResultRow(rs);
         rowNo++;
      }
   }

   private void printTableColumns(ResultSet rs) 
throws SQLException {
      ResultSetMetaData metaData = rs.getMetaData();
      for(int i = 0; i < metaData.getColumnCount(); i++) {
         int col = i + 1;
         out.println(metaData.getColumnName(col) + " "
               + metaData.getColumnTypeName(col) + " " + "("
               + metaData.getPrecision(col) + ")");
      }
      out.println("");
   }

   private void printResultRow(ResultSet rs) throws SQLException {
      ResultSetMetaData metaData = rs.getMetaData();
      for(int i = 0; i < metaData.getColumnCount(); i++) {
         String column = metaData.getColumnName(i + 1);
         try {
            String value = rs.getString(column);
            if (value != null && !value.equals("null") 
                  && !value.equals("") && !value.equals("0"))
               out.print(column + ": " + value + ", ");
         }
         catch(SQLException e) {
            out.print(column + ": " + e.getMessage());
         }
      }
      out.println("");
   }
}

The application reads the JDBC properties from an hibernate.properties file, for example like this:

hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://localhost/users
hibernate.connection.username=<username>
hibernate.connection.password=<password>
hibernate.show_sql=true

It creates a file like dumpdatabase-01-23-10-30-38.txt:

----tb_role----
----tb_user----
user_id BIGINT (20)
username VARCHAR (255)
password VARCHAR (255)
first_name VARCHAR (255)
last_name VARCHAR (255)
email VARCHAR (255)

----tb_user_role----

Then, I create a user and run the program again to get a new dump dumpdatabase-01-23-10-32-05.txt:

----tb_role----
----tb_user----
user_id BIGINT (20)
username VARCHAR (255)
password VARCHAR (255)
first_name VARCHAR (255)
last_name VARCHAR (255)
email VARCHAR (255)

user_id: 1, username: albert, password: pw, first_name: albert, 
last_name: albertson, email: albert@dot.com, 
----tb_user_role----

Of course, in the real application the dumps are thousands of lines, but with Eclipse "Compare with each other", it' still easy to see what happened, so I know exactly what my Java code needs to do.