JDBC Metadata Connector

This connector provides read-only access to the metadata (e.g., catalogs, schemas, table structures and foreign keys) of a relational database. The connector yields a hierarchy of nodes that looks like this:

/ (root node)
    + <catalog name> - one node for each accessible catalog in the database.
        + <schema name> - one node for each accessible schema in the catalog.
            + tables - a single node that is the parent of all tables in the schema.
            |   + <table name> - one node for each table in the schema.
            |      + <column name> - one node for each column in the table.
            |      + foreignKeys - a single node that is the parent of all the foreign keys imported by the table
            |          + <foreign key name> - one node for each imported foreign key
            + procedures - a single node that is the parent of all procedures in the schema.
                + <procedure name> - one node for each procedure in the schema.

The root, table, column, foreign key and procedure nodes contain additional properties that correspond to the metadata provide by the DatabaseMetaData class. In databases that do not support catalogs or schemas (or allow the empty string as a valid catalog or schema name, the value of the defaultCatalogName and/or defaultSchemaName properties will be used instead when determining the node name.

This connector has currently been tested successfully against Oracle 10g, Oracle 11g, Microsoft SQL Server 2008 (with the Microsoft JDBC driver), IBM DB2 v9, Sybase ASE 15, MySQL 5 (with the InnoDB engine), PostgreSQL 8, HSQLDB and H2. As JDBC driver implementations of the DatabaseMetaData interface tend to vary widely, other databases may or may not work with the default MetadataCollector implementation.

As one example, the metadataCollectorClassName property must be set to org.modeshape.connector.meta.jdbc.SqlServerMetadataConnector if the Microsoft JDBC driver is used. This is to work around a known bug where that driver returns a list of users from a call to DatabaseMetaData.getSchemas() instead of a list of schemas.

The org.modeshape.connector.meta.jdbc.JdbcMetadataConnector class provides a number of JavaBean properties that control its behavior:

Property	Description
`dataSourceJndiName`	The JNDI name of the JDBC DataSource instance that should be used. If not specified, the other driver properties must be set.
`password`	The password that should be used when creating JDBC connections using the JDBC driver class. This is not required if the DataSource is found in JNDI.
`url`	The URL that should be used when creating JDBC connections using the JDBC driver class. This is not required if the DataSource is found in JNDI.
`username`	The username that should be used when creating JDBC connections using the JDBC driver class. This is not required if the DataSource is found in JNDI.
`driverClassName`	The name of the JDBC driver class. This is not required if the DataSource is found in JNDI, but is required otherwise.
`defaultCatalogName`	Optional property that defines the name to use for the catalog name if the database does not support catalogs or the database has a catalog with the empty string as a name. The default value is "default".
`defaultSchemaName`	Optional property that defines the name to use for the schema name if the database does not support schemas or the database has a schema with the empty string as a name. The default value is "default".
`idleTimeInSecondsBeforeTestingConnections`	Optional property that defines the number of seconds after a connection remains in the pool that the connection should be tested to ensure it is still valid. The default is 180 seconds (or 3 minutes).
`maximumConnectionsInPool`	Optional property that defines the maximum number of connections that may be in the connection pool. The default is "5".
`maximumConnectionIdleTimeInSeconds`	Optional property that defines the maximum number of seconds that a connection should remain in the pool before being closed. The default is "600" seconds (or 10 minutes).
`maximumSizeOfStatementCache`	Optional property that defines the maximum number of statements that should be cached. The default value is "100", but statement caching can be disabled by setting to "0".
`metadataCollectorClassName`	Advanced optional property that defines the name of a custom class to use for metadata collection, which is typically needed for JDBC drivers that don't properly support the standard DatabaseMetaData methods. The specified class must implement the `MetadataCollector` interface and must have a public no-argument constructor. If an empty string (or null) value is specified for this property, a default `MetadataCollector` implementation will be used that relies on the driver's DatabaseMetaData.
`minimumConnectionsInPool`	Optional property that defines the minimum number of connections that will be kept in the connection pool. The default is "0".
`numberOfConnectionsToAcquireAsNeeded`	The number of connections that should be added to the pool when there are not enough to be used. The default is "1".
`cacheable`	Optional property that if a node returned by this connector should be cached in the workspace cache or not. By default all nodes are cached, but you can set it to `false` for the connector to always read the latest version of the nodes

The connector can either be configured from a standalone JSON configuration file:

{
    "name" : "Federated repository which uses a JDBC Metadata External Source",
    "jndiName" : "java:/testRepo",
    "workspaces" : {
        "predefined" : ["ws1", "ws2"]
    },
    "externalSources" : {
        "jdbc-meta" : {
            "classname" : "org.modeshape.connector.meta.jdbc.JdbcMetadataConnector",
            "dataSourceJndiName" : "java:/testDS",
            "maximumConnectionsInPool" : "${dataSource.maximumConnectionsInPool}",
            "minimumConnectionsInPool" : "${dataSource.minimumConnectionsInPool}",
            "maximumConnectionIdleTimeInSeconds" : "${dataSource.maximumConnectionIdleTimeInSeconds}",
            "maximumSizeOfStatementCache" : "${dataSource.maximumSizeOfStatementCache}",
            "numberOfConnectionsToAcquireAsNeeded" : "${dataSource.numberOfConnectionsToAcquireAsNeeded}",
            "retryLimit" : "${dataSource.retryLimit}"
        }
    }
}

or when deployed in EAP, via the ModeShape EAP subsystem:

<source name="jdbc-metadata" 
        classname="org.modeshape.connector.meta.jdbc.JdbcMetadataConnector" 
        module="org.modeshape.connector.jdbc.metadata" 
        dataSourceJndiName="java:jboss/datasources/ExampleDS">
  <projection>default:/ModeShapeTestDb => /</projection>
</source>

JBoss Community Archive (Read Only)

ModeShape 5

JDBC Metadata Connector