Skip to main content

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.

Comments

Hi "The Pessimistic Programmer" ,

I used your program for one of my assignments, but i got following result... :(


Creating dumpdatabase-0408-091230.txt
java.lang.NullPointerException
at DumpDatabase.listAll(DumpDatabase.java:57)
at DumpDatabase.dumpDatabase(DumpDatabase.java:51)
at DumpDatabase.main(DumpDatabase.java:28)
Java Result: 1

I think this NullPointer is raised @ reading metadata line.. I don't have any knowledge on this area.. Can you help out??
Unknown said…
Hi,

I cannot really help much, but it seems like conn is null. This is set by:
conn = DriverManager.getConnection(url, user, password);

So the url, user or password parameters must be wrong. Please look for correct values in the documentation for your database or JDBC driver.

/Lars
hi Lars,

Thanx for the fast reply.. :) And i think you are correct.. It can be "conn". I ll look in to it and try to figure out.. If i do i ll post the reply for sure.. :)

Actually I have hard coded those values (url, username, password) for JDBC. Those values are tested and working.

Thanx again for the wonderful program and prompt reply... Keep up the good work.. :)
Marco Collautti said…
Sooooo thank you!!!
It works!!
How can i restore the database from the dump?
Isuru said…
I'm using a very simple code in one of my applications. I would like your comments on my code. http://isuru.diyatha.com/java-db-dump/

Popular posts from this blog

The problem with use cases

The greatest benefit I get from use cases is that they focus on the user. Use cases help me to think about what the user wants to do instead of only focusing on implementation details. The biggest problem I have with use cases is that they are not structured. They are basically free text. For instance, if we have a use case Withdraw money from ATM, we may define that it has a precondition that Open account is performed, but we don't get any help from the method to see that. What happens if someone later changes the Open account use case or defines a Close account use case? How do we find which other uses cases that need to be modified? We can look through the old use case diagrams and find dependencies, but I can almost guarrantee that these dependencies have not been maintained after they were initially created. The solution to this is to connect the use cases to an object model. I don't mean a use-case realization with view and controller objects like ATM_Screen and ATM

The Pessimistic Programmer

I decided to change the title of this blog to "The Pessimistic Programmer". Why? Am I a depressed person that thinks nothing will work? No, I am an optimist in life. Something good is going to happen today :-) But in programming, something will surely go wrong. I don't actually view this as pessimism, but as realism. I want to be prepared for the worst that can possibly happen. Hope for the best, prepare for the worst. But my wife explained to me that pessimists always say that they are just being realistic. So, I might as well face it: I am a pessimist. I think a good programmer needs to be pessimistic; always thinking about what can go wrong and how to prevent it. I don't say that I am a good programmer myself. No, I make far too many mistakes for that. But I have learnt how to manage my mistakes with testing and double checking. Über-programmers can manage well without being pessimistic. They have total overview of the code and all consequences of changes. But I

Use examples to make your code easier to understand

Programmers are used to abstract thinking. To program is to generalize: A method is a general specification of what to do during execution. A class is a general specification of objects. A superclass is a generalization of several classes. Altough our minds are capable of abstract thinking, concrete thinking is much easier, and concrete examples are the foundation for abstractions. For instance, when we were children, our parents didn't try to teach us about cars by explaining to us cars are and what they can do. Instead, they just pointed at a car that was driving by and said ”Look, a car!” When they had done that a number of times, we knew what a car was. Another example is prejudice. We all have prejudices, because this is the way our minds work. If we have met a few people from Denmark in our lives, and those people were friendly, we ”know” that Danes are friendly. And this works even stronger for negative prejudices. My point is that we learn by examples. Einstein said t