JBoss.orgCommunity Documentation

Chapter 19. JDBC Metadata Connector

This connector provides read-only access to the metadata (e.g., catalogs, schemas, table structures) of a relational database. The connector yields a content graph 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.
            + 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, 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 graph name.

Note

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, and HSQLDB. 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.

To use this connector with the ModeShape JCR layer, you must import the JCR node types that this connector uses. These are bundled in the JAR for this connector at the path /org/modeshape/connector/meta/jdbc/nodeTypes.cnd. Please see the Getting Started Guide for detailed examples of how to import custom JCR node types.

The JdbcMetadataSource class provides a number of JavaBean properties that control its behavior:

cachePolicy

Optional property that, if used, defines the cache policy to use for this repository source. When not used, this source will not define a specific duration for caching information.

dataSourceJndiName

The JNDI name of the JDBC DataSource instance that should be used. If not specified, the other driver properties must be set.

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".

driverClassloaderName

Optional property that defines the name of the ModeShape class loader or classpath that should be used to load the JDBC driver class. This is not required if the DataSource is found in JNDI, or if the driver is on the application's classpath.

driverClassName

The name of the JDBC driver class. This is not required if the DataSource is found in JNDI, but is required otherwise.

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".

name

Required property that specifies the name of the repository source, which is used by the RepositoryService when obtaining a RepositoryConnection by name.

nameOfDefaultWorkspace

Optional property that is initialized to an empty string and which defines the name for the workspace that will be used by default if none is specified.

nodeCachePolicy

Optional property that, if used, defines the cache policy to use for caching nodes within the connector.

numberOfConnectionsToAcquireAsNeeded

The number of connections that should be added to the pool when there are not enough to be used. The default is "1".

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.

retryLimit

Optional property that, if used, defines the number of times that any single operation on a RepositoryConnection to this source should be retried following a communication failure. The default value is '0'.

rootNodeUuid

Optional property that, if used, specifies the UUID that should be used for the root node of each workspace. If no value is specified, a new UUID is generated.

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.

One way to configure the JDBC metadata connector is to create JcrConfiguration instance with a repository source that uses the JdbcMetadataSource class. For example:



JcrConfiguration config = ...
config.repositorySource("Meta Store")
      .usingClass(JdbcMetadataSource.class)
      .setDescription("The database source for our content")
      .setProperty("dataSourceJndiName", "java:/MyDataSource")
      .setProperty("nameOfDefaultWorkspace", "default");

Of course, setting other more advanced properties would entail calling setProperty(...) for each. Since almost all of the properties have acceptable default values, however, we don't need to set very many of them.

Another way to configure the JDBC metadata connector is to create JcrConfiguration instance and load an XML configuration file that contains a repository source that uses the JdbcMetadataSource class. For example a file named configRepository.xml can be created with these contents:



<?xml version="1.0" encoding="UTF-8"?>
<configuration xmlns:mode="http://www.modeshape.org/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0">
    <!-- 
    Define the sources for the content.  These sources are directly accessible using the 
    ModeShape-specific Graph API.  In fact, this is how the ModeShape JCR implementation works.  You 
    can think of these as being similar to JDBC DataSource objects, except that they expose 
    graph content via the Graph API instead of records via SQL or JDBC. 
    -->
    <mode:sources jcr:primaryType="nt:unstructured">
        <!-- 
        The 'Meta Store' repository is a JDBC metadata repository with a single default 
        workspace (though others could be created, too).
        -->
        <mode:source jcr:name="Meta Store" 
                    mode:classname="org.modeshape.connector.meta.jdbc.JdbcMetadataSource" 
                    mode:description="The database source for our content"
                    mode:dataSourceJndiName="java:/MyDataSource"
                    mode:defaultworkspaceName="default" >
            <!-- 
            If desired, specify a cache policy that caches items in memory for 5 minutes (300 s).
            This fragment can be left out if the connector should not cache any content.
            -->
            <mode:cachePolicy jcr:name="nodeCachePolicy" 
              mode:classname="org.modeshape.graph.connector.base.cache.InMemoryNodeCache$PathCachePolicy"
              mode:timeToLive="300" />
        </mode:source>
    </mode:sources>
    
    <!-- MIME type detectors and JCR repositories would be defined below --> 
</configuration>
 

The configuration can then be loaded from Java like this:



JcrConfiguration config = new JcrConfiguration().loadFrom("/configRepository.xml");