Overview / Connectors / Database Connector / 

Database Connector
You are reading the null version of 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 Database Connector enables the connection of an external database to a Java Content Repository (JCR). The connector supports reading the content of the database; it does not support other functionalities like writing, searching or observation.

This connector is primarily intended to provide JCR access to existing data in an existing legacy database.

Note

The Database Connector is a beta product.

Getting Started

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.


Installing into a CQ5 Application

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.

Installing the Embedded Database (H2)

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.

Connecting the Embedded Database (H2)

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.

Connecting an External Database

In addition to connecting to its own embedded H2 database, the Connector can also connect to any external JDBC-compatible database. To enable the connection you must first install the JDBC driver for the database you want to connect to.

  • If the JDBC driver for your particular database is available as an OSGi bundle, you can deploy it directly using the Felix OSGi managment console.
  • If the JDBC driver is only available as a standard jar file (the most common case), then you must first convert it into an OSGi bundle before deploying it.

Bundling the Driver

We will use MySQL as an example of how to adapt a plain-vanilla jar file driver to work within the OSGi framework of CRX/CQ.

We assume that you have both the Database Connector-enabled CRX or CQ instance and an instance of MySQL installed on the same machine (MySQL can be downloaded here: http://www.mysql.com/).

The MySQL JDBC driver file is

mysql-connector-java-5.1.12-bin.jar

(it can be downloaded here: http://www.mysql.com/downloads/connector/j)

The first step is to copy the driver file to the shared classpath of your application server. If you are using CQSE (which comes with CRX and CQ) the shared classpath is

crx-quickstart/server/lib/container

If you are using another application server, conult the documentaion for that server to find the shared classpath location.

Simply put the driver jar file there. For example, copying the MySQL JDBC to the shared classpath of CQSE wil result in it being located here:

crx-quickstart/server/lib/container/mysql-connector-java-5.1.12-bin.jar

Next, create a manifest file for converting the standard jar into an OSGi compliant jar, or bundle. Our example manifest file will be called mysql-jdbc.mf with the following content.

mysql-jdbc.mf
Bundle-ManifestVersion: 2
Bundle-SymbolicName: com.mysql.jdbc.driver.extension
Bundle-Version: 5.1.12
Fragment-Host: system.bundle; extension:=framework
Bundle-Name: MySQL JDBC Driver Extension
Bundle-Description: Bundle exporting MySQL JDBC Driver
Export-Package: com.mysql

Note that a blank line at the end of the manifest file is required.

The entries in the manifest file are determined as follows:

  • Bundle-ManifestVersion: The value of this entry must be 2.
  • Bundle-SymbolicName: This Java package-style name which is up to you to invent. Keep in mind that the you want the bundle name to be unique among the bundles deployed in your CRX/CQ instance and to be descriptive.
  • Bundle-Version: This information helps make administering bundles easier. Copy the version number from the jar file.
  • Fragment-Host: This is the key entry that defines this bundle as an OSGi extension bundle. The value must be as shown above.
  • Bundle-Name: This is up to you to invent. Choose something informative.
  • Bundle-Description: Again, choose something informative.
  • Export-Package: Use the first two levels of the Bundle-SymbolicName.

Once you have defined your manifest file, convert your existing jar file using the following command:

jar -cfm mysql-connector-java-5.1.12-bin.jar mysql-jdbc.mf

The jar file is now ready to be deployed on your running Database Connector-enabled CRX/CQ instance.

Deploying a Bundled Driver

Assuming that you have a OSGi-bundled driver jar file located in you application server's shared classpath (in CQSE, crx-quickstart/server/lib/container), the next step is to deploy that bundle using the Felix console.

 

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.

JCR Mapping

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

Your comments are welcome!
Did you notice a way we could improve the documentation on this page? Is something unclear or insufficiently explained? Please leave your comments below and we will make the appropriate changes. Comments that have been addressed, by improving the documentation accordingly, will then be removed.

COMMENTS

  • By gregg.king - 5:01 PM on Jul 27, 2010   Reply
    In the section "Connecting an External Database", step 4 says:
    sling.bootdelegation.oracle.driver=oracle.jdbc.driver

    For 5.3, "boot.delegation" has to be changed to system.packages, e.g.
    sling.system.packages.oracle.driver=oracle.jdbc.driver
    • By aheimoz - 2:58 PM on Jul 28, 2010   Reply
      It depends really:

      If the JDBC driver is used by a bundle, which does *not* import the JDBC driver package, then bootdelegation is required.
      When using system packages, the driver is added to the exports of the system bundle and can only be used with imports.

      If the bundle does import the JDBC driver package, it must be added to the system packages instead of bootdelegation.

      Assuming that the bundle using JDBC (the DB pool bundle) does not actually import the JDBC package then bootdelegation is correct.
    • By Kams - 9:53 PM on Nov 02, 2010   Reply
      Hi. I would like to install the connector. Could you please help me in finding the crx2jdbc-quickstart.jar file?

      I have installed the Oracle jdbc driver OSGi bundle using the Felix console and I am able to access the oracle database. But I would like to perform the JCR mapping. Some pointers would help. Thanks.
      • By alvawb - 5:47 PM on Nov 05, 2010   Reply
        Hi,
        The best way to go about this is to contact our customer support team. Visit http://www.day.com/day/en/company/contact_us.html to find out the best person to contact for your region.
      • By satish - 3:12 PM on Jul 11, 2011   Reply
        I want to add mysql database, I am not able to connect mysql database, can any one told how to connect mysql database and what is steps for confuguration and what will be the sample code
        • By ppiegaze - 10:06 PM on Aug 19, 2011   Reply
          Did you follow the directions above, under the heading "Connecting an External Database"? Which part of the procedure are you having trouble with?
          • By satish - 12:30 AM on Jul 10, 2012   Reply
            Yes I have issues, I am using CQ5.4, lot of times I am getting Datasourse not found.
            One time I got connection and even I can get data from database using select query.
            but now its not working..I can not able to understand its behavior.

            Is is possible can we able to insert data in to database from jsp page using CQ5.4?

            Regards,
            Satish
            • By aheimoz - 7:08 AM on Jul 10, 2012   Reply
              Can you verify your implementation to see whether you are actually inserting into your database using datasource, that all the exceptions are logged and you are commiting the changes.

              You can also refer to:
              http://dev.day.com/content/kb/home/cq5/Development/HowToConfigureSling Datasource.html

              Hope that helps.
        • By Diego C - 10:55 PM on Aug 26, 2011   Reply
          Hi

          I have cq5.4. I download the latest version of mysqlconnector 'mysql-connector-java-5.1.16-bin.jar', and i am trying to connect to a mysql database. I just add "mysql-connector-java-5.1.16-bin.jar" in the libs folder of the bundle. But when i get to the line of code describe below:

          Class.forName ("com.mysql.jdbc.Driver").newInstance ();

          It throws me this exception:

          com.mysql.jdbc.Driver not found by ...

          So i follow the step of "Bundling the Driver" and added the generated jar into the libs folder. And still nothing.

          Nevertheless, if i add this jar "mysql-connector-java-3.0.17-ga-bin.jar"(remove the other one) to my libs folder, it works!!

          But i want to use the mysql-connector-java-5.1.16-bin.jar. Any ideas how make this work?
          • By Diego C - 5:43 PM on Aug 30, 2011   Reply
            Hi all

            I solved my problem. The thing is .. i dont know why this solved the problem.

            The problem is with the manifest that comes inside the connector jar of mysql (mysql-connector-java-5.1.16-bin.jar). If i remove this manifest (jar -uMmf MANIFEST.MF mysql-connector-java-5.1.17-bin.jar) and create a manifest like this one:

            Manifest-Version: 1.0
            Ant-Version: Apache Ant 1.6.2
            Created-By: 1.4.2-b28 (Sun Microsystems Inc.)
            (Blank line)

            add the mentioned Manifest to the jar (jar -umf MANIFEST.MF mysql-connector-java-5.1.17-bin.jar); and then add this jar to the libs folder of my bundle. The class is found and i can connect succesfully to the mysql database and retrieve data.
          • By Sachin - 12:07 AM on Mar 22, 2012   Reply
            Hi Author/Mod,

            I believe this article is almost 2years old now.Please confirm if this connector is still in beta.
            If not please share link to download crx2jdbc-quickstart.jar

            Your expedited response will much appreciated

            Thanks
            • By alvawb - 3:00 PM on Apr 24, 2012   Reply
              To download the connector, you'll need to contact the Adobe sales person in your region. They'll also be able to answer any questions you have about the connector.

            ADD A COMMENT

             

            In order to post a comment, you need to sign-in.

            Note: Customers with DayCare user accounts need to create a new account for use on day.com.

            ***
            On this page:
            - Getting Started
            - Installing into a CQ5 Application
            - Installing the Embedded Database (H2)
            - Connecting the Embedded Database (H2)
            - Connecting an External Database
            - Bundling the Driver
            - Deploying a Bundled Driver
            - JCR Mapping
            Jump to Page Comments
            Reviewed by TRUSTe: site privacy statement

            Copyright © 2012 Adobe Systems Incorporated. All rights reserved.

            Use of this website signifies your agreement to the Terms of Use and Online Privacy Policy.