Apache derby in-memory db schema dump with Hibernate 4+

Question

I'm using Apache Derby 10.9.x with Hibernate Entitymanager 4.1.9.Final in unit tests. The Derby db schema gets generated from JPA annotated entities. There is only a persistence.xml config file. I'd like to dump the generated Derby db schema during/before/after a unit test. What's the programmatic way to do that?

Solution:

    // should not be required because Hibernate already started Derby:
    //Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

    try (Connection conn = DriverManager.getConnection ("jdbc:derby:memory:unit-testing;")) {
        String cat = null;
        String schema = "ROOT";

        DatabaseMetaData md = conn.getMetaData();
        ResultSet rs = md.getTableTypes();
        ResultSetUtils.dump(rs);
        rs = md.getTables(cat, schema, null, new String[]{"TABLE"});
        ResultSetUtils.dump(rs);
        rs = md.getColumns(cat, schema, null, null);
        ResultSetUtils.dump(rs);
    }

public class ResultSetUtils {

private static final Logger logger = Logger.getLogger(ResultSetUtils.class.getName());
private static final String COL_SEPARATOR = ";";

public static int getColForLabel(ResultSet rs, String labelname) throws SQLException {
    ResultSetMetaData rsmd = rs.getMetaData();

    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        if (labelname.equals(rsmd.getColumnLabel(i))) {
            return i;
        }
    }

    throw new SQLException("Invalid label name " + labelname);
}

public static void dump(ResultSet rs) throws SQLException {

    // the order of the rows in a cursor
    // are implementation dependent unless you use the SQL ORDER statement
    ResultSetMetaData meta = rs.getMetaData();
    int colmax = meta.getColumnCount();
    int i;
    Object o;

    StringBuilder sb = new StringBuilder(512);
    for (i = 0; i < colmax; ++i) {
        if(i>0) {
            sb.append(COL_SEPARATOR);
        }
        String s = meta.getColumnName(i + 1);
        sb.append((s == null) ? "NULL" : s);
        s = meta.getColumnTypeName(i + 1);
        sb.append((s == null) ? "(NULL)" : "("+s+")");
    }
    logger.info(sb.toString());


    // the result set is a cursor into the data.  You can only
    // point to one row at a time
    // assume we are pointing to BEFORE the first row
    // rs.next() points to next row and returns true
    // or false if there is no next row, which breaks the loop
    for (; rs.next();) {
        sb = new StringBuilder(512);
        for (i = 0; i < colmax; ++i) {
            if(i>0) {
                sb.append(COL_SEPARATOR);
            }
            o = rs.getObject(i + 1);    // Is SQL the first column is indexed
            sb.append((o == null) ? "NULL" : o.toString());
        }

        logger.info(sb.toString());
    }
}

}

Answer 1

A programmatic way to access the schema is to use the DatabaseMetaData class: http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html

Start with the getTables() method, and print out the information that is returned.