DBDump.java

  1: // JDBC Table Dump program.  This application opens a database, selects all the
  2: // records in a table, and using the meta-data returned prints a report (dump).
  3: // By changing the SQL, this program can produce an arbitrary report on a join
  4: // on a database.  The database and table must be supplied on the command line.
  5: // Not all databases support meta-data, especially if the resultSet is empty.
  6: // The metadata also contains info on column widths, which should be used to
  7: // line up columns correctly.  (Omitted here for clarity.)
  8: //
  9: // There is no standard SQL to list the databases (the SQL standard
 10: // uses the term schema) available on some server.  However there
 11: // is a standard SQL query to list the schemas in a database server (the
 12: // SQL standard uses the term catalog), but some DBMSes don't support
 13: // schemas, or don't follow the standard (e.g., DB2 and Oracle).  Use:
 14: //   SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
 15: //
 16: // The standard SQL to list the tables in a DB/schema is:
 17: //  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 18: //     WHERE TABLE_SCHEMA = 'name'
 19: // but not all DBMSes support it.  Oracle uses "SELECT * FROM TAB",
 20: // and DB2 uses "SYSCAT" instead of "INFORMATION_SCHEMA".)
 21: //
 22: // Using INFORMATION_SCHEMA it is possible to describe (list the columns
 23: // and their types and constraints) any table, but not all DBMSes
 24: // support this.  For Oracle use "DESCRIBE tablename" and for DB2
 25: // use "DESCRIBE TABLE tablename".
 26: //
 27: // To create an embedded Derby DB to use, run "ij" in a suitable directory
 28: // and enter this information:
 29: /*
 30:    CONNECT 'jdbc:derby:AddressBook;create=true';
 31: 
 32:    CREATE TABLE addresses (
 33:       id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1000),
 34:       lname VARCHAR(40) NOT NULL,
 35:       fname VARCHAR(40) NOT NULL,
 36:       phone VARCHAR(14),
 37:       notes varchar(256),
 38:       primary key (ID) );
 39: 
 40:    INSERT INTO addresses (lname, fname, phone, notes)
 41:       VALUES ('Pollock', 'Wayne', '253-7213', 'Professor'),
 42:       ('Piffl', 'Hymie', NULL, 'Fake student name'),
 43:       ('Jojo', 'Mojo', NULL, 'Super-villain');
 44:    EXIT;
 45: */
 46: // To run with this DB, use: java DBDump jdbc:derby:AddressBook addresses
 47: //
 48: // Written 2019 by Wayne Pollock, Tampa Florida USA.
 49: 
 50: import java.sql.*;
 51: 
 52: public class DBDump {
 53: 
 54: public static void main ( String [] args ) {
 55:    if ( args.length != 2 ) {
 56:       System.out.println(
 57:          "**** Usage: java DBDump <URL> <table>" );
 58:       return;
 59:    }
 60:    String URL = args[0];
 61:    String table = args[1];
 62:    String username = "";
 63:    String password = "";  // Bad practice!
 64:    String SQL_Query = "SELECT * FROM " + table;  // Security flaw!
 65: 
 66:    Connection con = null;
 67:    Statement stmt = null;
 68:    try {
 69:        con = DriverManager.getConnection( URL, username, password );
 70:        stmt = con.createStatement();
 71:    } catch ( Exception e ) {
 72:        System.err.println( "**** Cannot open connection to " + URL + "!" );
 73:    }
 74: 
 75:    try {
 76:        ResultSet results = stmt.executeQuery( SQL_Query );
 77: 
 78:        // Collect meta-data:
 79:        ResultSetMetaData meta = results.getMetaData();
 80:        int numColumns = meta.getColumnCount();
 81: 
 82:        // Display results:
 83:        System.out.println( "\n\t\t\t--- " + table + " ---\n" );
 84:        for ( int i = 1; i <= numColumns; ++i )
 85:            System.out.printf( "%-12s", meta.getColumnLabel( i ) );
 86:        System.out.println();
 87: 
 88:        while ( results.next() )     // Fetch next row, quit when no rows left.
 89:        {   for ( int i = 1; i <= numColumns; ++i )
 90:            {   String val = results.getString( i );
 91:                if ( val == null )
 92:                    val = "(null)";
 93:                System.out.printf( "%-12s", val );
 94:            }
 95:            System.out.println();
 96:        }
 97:        con.close();
 98:    }
 99:    catch ( Exception e ) {
100:       e.printStackTrace();
101:    }
102: }  // End of Main
103: }  // End of DBDump