devxlogo

Dumping Tables

Dumping Tables

Question:
Using JDBC, how can I turn a database table into a series of SQL INSERT statements that can recreate the table?

Answer:
Dumping a database table into a set of insert statements is just amatter of selecting all of its rows and constructing insert statementsbased on their column values.

You can determine the number of columnsin a table by accessing the ResultSetMetaData for a ResultSet withgetMetaData() and then calling ResultSetMetaData.getColumnCount().Getting the values of each row is just a matter of calling getString()with the column number as an argument. The following programdemonstrates the approach:

import java.sql.*;/*** * This program accepts a table name, a JDBC driver name, and a database * connection URL as arguments.  The program connections to the database * and reads all the rows in the table, printing SQL INSERT statements * on standard output representing the data. ***/public final class dump {  public static String DUMP_QUERY = "SELECT * FROM ";  public static final void main(String[] args) {    Connection connection;    Statement statement;    ResultSet result;    ResultSetMetaData metaData;    String table, driver, url;    StringBuffer buffer = new StringBuffer(8192);    int columns;    if(args.length   ");      return;    }    table  = args[0];    driver = args[1];    url    = args[2];    System.setProperty("jdbc.drivers", driver);    try {      connection = DriverManager.getConnection(url);      statement  = connection.createStatement();      result = statement.executeQuery(DUMP_QUERY + table);      metaData = result.getMetaData();       columns = metaData.getColumnCount();      while(result.next()) {        buffer.setLength(0);        buffer.append("INSERT INTO ");        buffer.append(table);        buffer.append(" VALUES (");        for(int i = 1; i 

devx-admin

Share the Post: