Database Connector

This documentation covers the Day JCR Connector for Databases. The connector belongs to Day’s CRX connectors family. CRX is Day's Content Repository Extreme, fully implementing the Content Repository API for Java Technology (JCR).

The connector enables to connect an external database to a Java Content Repository (JCR). The connector only supports reading the content of the database; it does not support other functionalities like writing, searching or observation.

To get started with the connector:

1. Make sure you have at least Java 5 installed.
2. Download the connector Quickstart distribution .jar file.
3. Copy a valid license.properties file in the same directory as the .jar file.
4. Double-click the file or start it from the command line (for example: java -jar crx2jdbc-quickstart.jar).
5. When the connector is up and running, your browser shows the connector welcome screen after a short while.
   
   
   

It is possible to install the connector within an existing CQ5 instance, enabling for example an existing application to access the database.

Installing the connector in a CQ5 instance consists in extracting the relevant files from the connector package and installing them into the existing CQ5 instance, configuring the connector, and configuring Sling. Proceed as follows:

1. Unpack the connector jar file: start the jar file from the command line with the option -unpack; for example: java -jar crx2jdbc-quickstart.jar -unpack
   
2. Acquire the content package crx2jdbc-initial-content located on your file system at <connector-install-dir>/crx-quickstart/repository/install
   
3. Install the crx2jdbc-initial-content content package on the existing CQ5 server by using the Package Manager.
   
4. In the Felix console, in the Configuration tab (for example at http://localhost:4502/system/console/configMgr), select the Day JCR Connector for JDBC. Set the URL of the database server and click Save.
   
   
5. Create a CQ5 user with the same credentials as a database user.
   
6. In the Felix console, in the Configuration tab, select the Apache Sling Request Authenticator configuration and switch off the Anonymous Access. Click Save.
   
7. Stop the CQ5 server.
   
8. Restart the CQ5 server.

An H2 database is embedded into the connector.

To install it:

1. Stop the connector.
   
2. Unarchive the crx2jdbc-initial-content.jar located in the file system at crx-quickstart/repository/install
3. Double-click the h2-1.1.114.jar file at jcr_root/libs/connector/crx2jdbc/install
   The login page of the H2 database opens up in your browser.
4. Set admin as User Name and admin as Password. Click Connect.
   The user interface of the H2 database is displayed.
5. Create some test data (see below).
6. Click Disconnect as the connector and the database cannot run simultaneously.

Note: repeat the procedure to add/delete data in the H2 database.

To add data to the database:

1. Double-click the h2-1.1.114.jar file to start the database.
2. In the login screen, set admin as User Name and admin as Password. Click Connect.
   The UI of the H2 database is displayed.
   
3. In the edit window, add the following commands:
   drop table address;
   create table address(id int primary key, first_name varchar(255), last_name varchar(255));
   insert into address values(1, 'Felix', 'Doe');
   insert into address values(2, 'Joanne', 'Stone');
   insert into address values(3, 'Silverster', 'Stallone');
   insert into address values(4, 'Harrison', 'Ford');
   
4. Click Run.
5. Click Clear.
6. To view the datas, select the table in the left navigation and click Run.
   
   
7. Click Disconnect.

To connect the embedded database (H2):

1. Stop the H2 database.
2. Start the connector.
3. In the Felix console, in the Configuration tab (for example at http://localhost:4502/system/console/configMgr), select the Day JCR Connector for JDBC. Set jdbc:h2:~/test as the URL of the database server and click Save.
4. Stop the connector.
5. Re-start the connector.
6. Login with admin and admin as Username and Password (same credentials as the one used to access the H2 database).
7. In CRX Explorer navigate to /mnt/jdbc to view the data of the database mapped to the JCR repository.
   
   

Note: There is no need to install a jdbc driver for the H2 database as it is embedded in the connector.

To connect an external database you only need to install the jdbc driver of the external database and to set the URL of the database. The driver can either be installed with boot-delegation as described in the following procedure or as an OSGI service (as for example the driver for the H2 database).

1. Stop the connector.
   
2. Get the jdbc driver jar file of the external database.
3. Copy the jdbc driver jar file in the file system into <connector-install-dir>/crx-quickstart/server/lib/container
4. Edit the file <connector-install-dir>/crx-quickstart/launchpad/sling.properties and add the following setting:
   sling.bootdelegation.oracle.driver=oracle.jdbc.driver
   Save the file.
   
5. Start the connector.
6. In the Felix console, in the Configuration tab (for example at http://localhost:4502/system/console/configMgr), select the Day JCR Connector for JDBC. Set the URL of the database and click Save.
7. Stop the connector.
8. Re-start the connector.
9. In CRX Explorer, create a user with the same credentials as the one needed to access the database.
   
10. Log into the CRX server with the credentials that have been set in the former step.
    
11. In CRX Explorer, navigate to /mnt/jdbc to see the data of the database mapped to the repository.
    
    

The connector maps tables to nodes, with sub-nodes representing table's rows and properties representing the columns.