Friday, September 19, 2008

Output/Print DB/Database Schema to String in Java

The following is an example of a simple way in Java to get the table names and column names from a schema using JDBC:
import java.sql.*;

/**
 * @author Gary S. Weaver
 */
public class DBUtil {

    public static String getSchemaDescription(String driverClassname, String connectionString, String username, String password)
            throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        String catalog = null;
        String tableNamePattern = "%";
        String[] types = null;
        return getSchemaDescription(driverClassname, connectionString, username, password, catalog, tableNamePattern, types);
    }

    public static String getSchemaDescription(String driverClassname, String connectionString, String username, String password, String catalog, String tableNamePattern, String[] types)
            throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Connection conn = null;
        StringBuffer sb = new StringBuffer();

        try {
            Driver d = (Driver) Class.forName(driverClassname).newInstance();
            conn = DriverManager.getConnection(connectionString, username, password);
            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet metadataRS = metadata.getSchemas();
            while (metadataRS.next()) {
                String schemaPattern = metadataRS.getString(1);
                sb.append("\n");
                sb.append("'");
                sb.append(schemaPattern);
                sb.append("':\n");
                sb.append("****************\n");
                ResultSet tables = metadata.getTables(catalog, schemaPattern, tableNamePattern, types);
                while (tables.next()) {
                    String tableName = tables.getString(3);
                    sb.append(tableName);
                    sb.append(" (");
                    ResultSet columns = metadata.getColumns(null, null, tableName, null);
                    boolean delim = false;
                    while (columns.next()) {
                        String columnName = columns.getString(4);
                        if (delim) {
                            sb.append(", ");
                        }
                        sb.append(columnName);
                        delim = true;
                    }

                    sb.append(")\n");
                }
            }
        }
        finally {
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (Throwable t) {
                    t.printStackTrace();
                }
            }
        }

        return sb.toString();
    }
}
For example, you might call this from within a unit test:
    public void printDbSchema() {

        try {
            // ... code to start DB goes here
            String schemaAsString = DBUtil.getSchemaDescription("org.h2.Driver", "jdbc:h2:./tmph2database", "sa", "");
            System.err.println(schemaAsString);
        }
        catch (Throwable t) {
            t.printStackTrace();
        }
        finally {
            // ... code to stop DB goes here
        }
    }

No comments: