Dumping Tables

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

You can determine the number of columns in a table by accessing the ResultSetMetaData for a ResultSet with getMetaData() 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 program demonstrates 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 < 3) {
      System.err.println("Usage: dump  ");
      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 <= columns; i++) {
          String str;
          str = result.getString(i);
          if(str == null)
            buffer.append("NULL");
          else {
            buffer.append('\'');
            buffer.append(str);
            buffer.append('\'');
          }
          if(i < columns)
            buffer.append(", ");
          else
            buffer.append(");");
        }
        System.out.println(buffer.toString());
      }
    } catch(SQLException e) {
      e.printStackTrace();
      return;
    }
  }
}



														
DevX Pro
														
 
														

															

																

																	

																		

																			
																				
																			
																		
																		

																			Submit a Tip
																		
																		

																			
																				
																			
																		
																		

																			Browse "XML" Tips
																		
																		

																			
																				
																			
																		
																		

																			 Browse All Tips