Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

4.5. Using Repositories with JDBC in EAP

4.5.1. JDBC in EAP

The hierarchical database provides a JDBC-compliant API that allows clients to connect and query a repository via JDBC. The hierarchical database comes pre-packaged with a org.modeshape.jdbc module. This module contains a java.sql.Driver implementation that allows JDBC clients to connect to existing repositories.

4.5.2. Configure a Datasource and Driver

You can access a hierarchical database repository via JDBC to configure a datasource and a driver inside of JBoss EAP. The following example shows a configuration snippet from a JBoss EAP standalone.xml file, which exposes via JDBC, the workspace "extra" from a repository named "artifacts": 

<datasource jndi-name="java:/datasources/ModeShapeDS" enabled="true" use-java-context="true" pool-name="ModeShapeDS">
  <connection-url>jdbc:jcr:jndi:jcr?repositoryName=artifacts</connection-url>
  <driver>modeshape</driver>
  <connection-property name="workspace">extra</connection-property>
  <security>
   <user-name>admin</user-name>
   <password>admin</password>
  </security>
</datasource>
 
<drivers>
  <driver name="modeshape" module="org.modeshape.jdbc">
     <driver-class>org.modeshape.jdbc.LocalJcrDriver</driver-class>
  </driver>
</drivers>
Configuring the hierarchical database JDBC driver requires the following attributes:

Table 4.3. JDBC driver attributes

name A symbolic name for the JDBC driver for the datasource.
module The JBoss EAP module name containing the JDBC driver implementation.
driver-class The fully qualified class name of the java.sql.Driver implementation.
For each repository you want to access, you need to configure a DataSource in the JBoss EAP configuration file. In the example above, the following attributes are defined:

Table 4.4. JDBC driver attributes

jndi-name The name under which the datasource should be registered in JNDI by JBoss EAP. Currently, JBoss EAP only allows datasources to be registered under a name beginning either with java:/ or java:jboss/.
connection-url A JNDI URL that points the hierarchical database to an existing repository. The format of this URL is: jdbc:jcr:jndi:jcr:?repositoryName=
driver The name of the JDBC driver.
security The username and password that is passed to the connection, when attempting to access a repository. Inside JBoss EAP, these are taken from the modeshape-security domain.
connection-property Any additional properties which can be passed to the connection. For example, to access a specific workspace of a repository, the workspace property can be defined.

4.5.3. Access Datasource from JNDI and Execute Queries

Once you configure a datasource and start the application server, you can access the datasource from JNDI and execute queries against the configured repository. Here is an example: 

@Resource( mappedName = "datasources/ModeShapeDS" )
private DataSource modeshapeDS;
 
....
 
Connection connection = modeshapeDS.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT [jcr:primaryType], [jcr:mixinTypes], [jcr:path], [jcr:name] FROM [nt:unstructured] ORDER BY [jcr:path]");
For executing queries, use JCR-SQL2 query language. However, as you can not expose JCR Nodes directly via JDBC, the only way to return the path and score information is through additional columns in the result. But doing so is not compatible with JDBC applications that dynamically build queries based upon database metadata. Such applications require the columns to be properly described in database metadata, and the columns need to be used within queries. The hierarchical database attempts to solve these issues by directly supporting a number of "pseudo-columns" within JCR-SQL2 queries, wherever columns can be used. These "pseudo-columns" include:
  • jcr:score: This is a column of type DOUBLE that represents the full-text search score of the node, which is a measure of the node's relevance to the full-text search expression. The hierarchical database computes the scores for all queries, though the score for rows in queries that do not include a full-text search criteria may not be reliable.
  • jcr:path: This is a column of type PATH that represents the normalized path of a node, including same-name siblings. This is the same as what would be returned by the getPath() method of http://www.day.com/maven/javax.jcr/javadocs/jcr-2.0/javax/jcr/Node.htmlNode. Examples of paths include "/jcr:system" and "/foo/bar[3]".
  • jcr:name: This is a column of type NAME that represents the node name in its namespace-qualified form using namespace prefixes and excluding same-name-sibling indexes. Examples of node names include "jcr:system", "jcr:content", "ex:UserData", and "bar".
  • mode:localName: This is a column of type STRING that represents the local name of the node, which excludes the namespace prefix and same-name-sibling index. As an example, the local name of the "jcr:system" node is "system", while the local name of the "ex:UserData[3]" node is "UserData".
  • mode:depth: This is a column of type LONG that represents the depth of a node, which corresponds exactly to the number of path segments within the path. For example, the depth of the root node is 0, whereas the depth of the "/jcr:system/jcr:nodeTypes" node is 2.
These columns are exposed in the database metadata allowing potential clients to detect and use them.