Create or obtain an OSGi bundle that that exports the JDBC driver package.
Configure a JDBC data source pool provider.
Obtain a data source object and create the connection in your code.
Some database vendors provide JDBC drivers in an OSGi bundle, for example MySQL. If the JDBC driver for your database is not available as an OSGi bundle, obtain the driver JAR and wrap it in an OSGi bundle. The bundle must export the packages that are required for interacting with the database server. The bundle must also import the packages that it references.
The following example uses the Bundle plugin for Maven to wrap the HSQLDB driver in an OSGi bundle. The POM instructs the plugin to embed the hsqldb.jar file that is identified as a dependency. All org.hsqldb packages are exported.
Wrap the JAR files that contain the packages in an OSGi bundle that exports the packages, and deploy the bundle. Use this solution when the package members are required during code execution.
Knowledge of the source code enables you to decide which solution to use. You can also try either solution and perform testing to validate the solution.
Add a configuration for the JDBC Connections Pool service that uses the JDBC driver to create data source objects. Your application code uses this service to obtain the object and connect to the database.
JDBC Connections Pool (com.day.commons.datasource.jdbcpool.JdbcPoolService) is a factory service. If you require connections that use different properties, for example read-only or read/write access, create multiple configurations.
When working with CQ there are several methods of managing the configuration settings for such services; see Configuring OSGi for full details.
JDBC Driver Class (jdbc.driver.class): The Java class to use that implements the java.sql.Driver interface, for example org.hsqldb.jdbc.JDBCDriver. The data type is String.
JDBC Connection URI (jdbc.connection.uri): The URL of the database to use to create the connection, for example jdbc:hsqldb:hsql//10.36.79.223:9001/mydb. The format of the URL must be valid for use with the getConnection method of the java.sql.DriverManager class. The data type is String.
Username (jdbc.username): The user name to use to authenticate with the database server. The data type is String.
Password (jdbc.password): The password to use for authentication of the user. The data type is String.
Validation Query (jdbc.validation.query): The SQL statement to use to verify that the connection is successful, for example select 1 from INFORMATION_SCHEMA.SYSTEM_USERS. The data type is String.
Readonly By Default (default.readonly): Select this option when you want the connection to provide read-only access. The data type is Boolean.
Autocommit By Default (default.autocommit): Select this option to create separate transactions for each SQL command that is sent to the database, and each transaction is automatically committed. Do not select this option when you are committing transactions explicitly in your code. The data type is Boolean.
Pool Size (pool.size): The number of simultaneous connections to be made available to the database. The data type is Long.
Pool wait (pool.max.wait.msec): The amount of time before a connection request times out. The data type is Long.
Datasource Name (datasource.name): The name of this data source. The data type is String.
Additional Service Properties (datasource.svc.properties): A set of name/value pairs that you want to append to the connection URL. The data type is String.
The JDBC Connections Pool service is a factory. Therefore, if you use a sling:OsgiConfig node to configure the connection service, the name of the node must include the factory service PID followed by -alias. The alias that you use must be unique for all configuration nodess for that PID. An example node name is com.day.commons.datasource.jdbcpool.JdbcPoolService-myhsqldbpool.
In your Java code, use the DataSourcePool service to obtain a javax.sql.DataSource object for the configuration that you created. The DataSourcePool service provides the getDataSource method that returns a DataSource object for a given data source name. As the method argument, use the value of the Datasource Name (or datasource.name) property that you specified for the JDBC Connections Pool configuration.
The following example JSP code obtains an instance of the hsqldbds data source, executes a simple SQL query, and displays the number of results that are returned.
If the getDataSource method throws an exception becuase the datasource is not found, make sure the Connections Pool service configuration is correct. Verify the property names, values, and data types.
To learn how to inject a DataSourcePool into an OSGi bundle, see Injecting a DataSourcePool Service into an Adobe Experience Manager OSGi bundle.