Reading Java Derby Metadata

Find in this post examples on how to read and retrieve a Derby database meta-data information to get list of schemas, tables, stored procedures, …

Get schemas

    DatabaseMetaData dmd = connection.getMetaData();
    ResultSet rs = dmd.getSchemas();
    List<String> schemas = new ArrayList<String>();
    while (rs.next()) {
        schemas.add(rs.getString("TABLE_SCHEM"));
    }
    rs.close();

“TABLE_SCHEM” is not a syntax error.

Get stored procedures

    // get database metadata
    DatabaseMetaData metaData = connection.getMetaData();
    // get procs
    ResultSet rs = metaData.getProcedures(null, null, "%");
    List<String> procs = new ArrayList<String>();
    while (rs.next()) {
        procs.add(rs.getString(3));
    }
    rs.close();

Get schema triggers

    // get triggers
    String query = String.format(
        "select a.TRIGGERNAME from SYS.SYSTRIGGERS a inner join SYS.SYSSCHEMAS b on a.SCHEMAID = b.SCHEMAID where b.SCHEMANAME = '%s' ORDER BY a.TRIGGERNAME",
        "my_schema"
    );
    Statement st = connection.createStatement();
    ResultSet rs = st.executeQuery(query);
    List<String> triggers = new ArrayList<String>();
    while (rs.next()) {
        triggers.add(rs.getString(1));
    }
    rs.close();
    st.close();

Get schema tables

    // get database metadata
    DatabaseMetaData metaData = connection.getMetaData();
    // get columns
    ResultSet rs = metaData.getTables(null, "my_schema", "%", null);
    List<String> tables = new ArrayList<String>();
    while (rs.next()) {
        // 1: none
        // 2: schema
        // 3: table name
        // 4: table type (TABLE, VIEW) 
        tables.add(rs.getString(3));
    }
    rs.close();

or

String query = String.format(
    "select a.TABLENAME from SYS.SYSTABLES a inner join SYS.SYSSCHEMAS b on a.SCHEMAID = b.SCHEMAID where b.SCHEMANAME = '%s' ORDER BY a.TABLENAME",
    "my_schema"
);
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(query);
List<String> tables = new ArrayList<String>();
while (rs.next()) {
    tables.add(rs.getString(1));
}
rs.close();
st.close();

Get table columns

    // get data base metadata
    DatabaseMetaData metaData = connection.getMetaData();
    // get columns
    ResultSet rs = metaData.getColumns(null, "my_schema", "my_table", "%");
    List<String> columns = new ArrayList<String>();
    while (rs.next()) {
        // 1: none
        // 2: schema
        // 3: table name
        // 4: column name
        // 5: length
        // 6: data type (CHAR, VARCHAR, TIMESTAMP, ...)
        columns.add(rs.getString(4));
    }
    rs.close();

Get table indexes

    // get data base metadata
    DatabaseMetaData metaData = connection.getMetaData();
    // get indexes
    ResultSet rs = metaData.getIndexInfo(null, "my_schema", "my_table", false, true);
    List<String> indexes = new ArrayList<String>();
    while (rs.next()) {
        // 1: type (index)
        // 2: schema
        // 3: table
        // 4: TODO
        // 5: 
        // 6: index name
        indexes.add(rs.getString(6));
    }
    rs.close();

Execute a delete/update SQL statement

    Statement stmtDerby = connection.createStatement();
    ResultSet executeQuery = stmtDerby.executeQuery(statement);

Execute a select SQL statement

    Statement stmtDerby = connection.createStatement();
    ResultSet executeQuery = stmtDerby.executeQuery(statement);
    if (executeQuery.next()) {
        // first result
        value1 = executeQuery.getString(1);
        // second result
        value2 = executeQuery.getString(2);
        // ... etc
    }
    stmtDerby.close();
Advertisements

One thought on “Reading Java Derby Metadata”

  1. There’s a small error.
    In example “Get table columns”:

    // 5: length

    should have been

    // 5: int, SQL type from java.sql.Types

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s