DataSources (JDBC)

The Apache Karaf DataSources (JDBC) is an optional enterprise feature.

You have to install the following features first:

karaf@root()> feature:install jdbc

NB: jdbc feature automatically installs the pax-jdbc-* core features.

Pax JDBC provides ready to use adapters for different databases:

pax-jdbc-derby
pax-jdbc-derbyclient
pax-jdbc-h2
pax-jdbc-mariadb
pax-jdbc-mysql
pax-jdbc-oracle
pax-jdbc-postgresql
pax-jdbc-sqlite
pax-jdbc-mssql

This feature provides an OSGi service to create/delete JDBC datasources in the container and perform database operations (SQL queries).

This JDBC OSGi service can be manipulated programmatically (see the developer guide for details), using the jdbc:* commands, or using the JDBC MBean.

Commands

`jdbc:ds-factories`

The jdbc:ds-factories command lists the available datasource factories, with the available driver.

For instance, once you installed the jdbc feature, you can install pax-jdbc-postgresql feature, providing the PostgreSQL datasource factory:

karaf@root()> feature:install pax-jdbc-postgresql

Then, you can see the factory available with jdbc:ds-factories:

karaf@root()> jdbc:ds-factories
Name                   │ Class                 │ Version │ Registration bundle
───────────────────────┼───────────────────────┼─────────┼───────────────────────────
PostgreSQL JDBC Driver │ org.postgresql.Driver │ 42.2.8  │ org.postgresql.jdbc42 [51]

You can see there the JDBC driver name and class that you can use in the jdbc:ds-create command.

NB: don’t forget to install the pax-jdbc-* features for the database you want to connect:

pax-jdbc-db2
pax-jdbc-teradata
pax-jdbc-derby
pax-jdbc-derbyclient
pax-jdbc-h2
pax-jdbc-hsqldb
pax-jdbc-mariadb
pax-jdbc-oracle
pax-jdbc-postgresql
pax-jdbc-sqlite
pax-jdbc-mssql
pax-jdbc-jtds

`jdbc:ds-create`

The jdbc:ds-create command automatically creates a datasource definition file by leveraging pax-jdbc.

The jdbc:ds-create command requires either:

the --driverName containing the JDBC driver name
or the --driverClass containing the JDBC driver class name

Driver name and driver class can be found with jdbc:ds-factories command.

The jdbc:ds-create accepts a set of options and the name argument:

DESCRIPTION
        jdbc:ds-create

        Create a JDBC datasource config for pax-jdbc-config from a DataSourceFactory

SYNTAX
        jdbc:ds-create [options] name

ARGUMENTS
        name
                The JDBC datasource name

OPTIONS
        -dbName
                Database name to use
        --help
                Display this help message
        -dn, --driverName
                org.osgi.driver.name property of the DataSourceFactory
        -u, --username
                The database username
        -dc, --driverClass
                org.osgi.driver.class property  of the DataSourceFactory
        -p, --password
                The database password
        -url
                The JDBC URL to use

the name argument is required. It’s the name of the datasource. The name is used to identify the datasource, and to create the datasource definition file (deploy/datasource-[name].xml).
the -u option is optional. It defines the database username.
the -url option is optional. It defines the JDBC URL to access to the database.
the -p option is optional. It defines the database password.

For instance, to create an embedded Apache Derby database in Apache Karaf, you can do:

karaf@root()> jdbc:ds-create -dn derby -url "jdbc:derby:test;create=true" test

We can see that this command created a configuration PID containing the datasource properties.

Another example using PostgreSQL driver class name (you can find with jdbc:ds-factories command):

karaf@root()> jdbc:ds-create -dc org.postgresql.Driver -url "jdbc:postgresql://localhost:5432/test" --username user --password passwd test

or using datasource factory name (also provided by jdbc:ds-factories command):

karaf@root()> jdbc:ds-create -dn "PostgreSQL JDBC Driver" -url "jdbc:postgresql://localhost:5432/test" --username user --password passwd test

`jdbc:ds-delete`

The jdbc:ds-delete command deletes a datasource.

karaf@root()> jdbc:ds-delete test

`jdbc:ds-list`

The jdbc:ds-list command lists the JDBC datasources:

karaf@root()> jdbc:ds-list
Name │ Service Id │ Product        │ Version              │ URL             │ Status
─────┼────────────┼────────────────┼──────────────────────┼─────────────────┼───────
test │ 112        │ Apache Derby   │ 10.8.2.2 - (1181258) │ jdbc:derby:test │ OK

`jdbc:ds-info`

The jdbc:ds-info command provides details about a JDBC datasource. The data source may be specified using name or service.id:

karaf@root()> jdbc:ds-info test
Property       | Value
--------------------------------------------------
driver.version | 10.8.2.2 - (1181258)
service.id     | 112
username       | APP
db.version     | 10.8.2.2 - (1181258)
db.product     | Apache Derby
driver.name    | Apache Derby Embedded JDBC Driver
url            | jdbc:derby:test

`jdbc:execute`

The jdbc:execute command executes a SQL query that doesn’t return any result on a given JDBC datasource.

Typically, you can use the jdbc:execute command to create tables, insert values into tables, etc.

For instance, we can create a person table on our test datasource:

karaf@root()> jdbc:execute test "create table person(name varchar(100), nick varchar(100))"

And we can insert some records in the person table:

karaf@root()> jdbc:execute test "insert into person(name, nick) values('foo','bar')"
karaf@root()> jdbc:execute test "insert into person(name, nick) values('test','test')"

`jdbc:query`

The jdbc:query command is similar to the jdbc:execute one but it displays the query result.

For instance, to display the content of the person table, we can do:

karaf@root()> jdbc:query test "select * from person"
NICK       | NAME
--------------------------------
bar        | foo
test       | test

`jdbc:tables`

The jdbc:tables command displays all tables available on a given JDBC datasource:

karaf@root()> jdbc:tables test
REF_GENERATION | TYPE_NAME | TABLE_NAME       | TYPE_CAT | REMARKS | TYPE_SCHEM | TABLE_TYPE   | TABLE_SCHEM | TABLE_CAT | SELF_REFERENCING_COL_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------
               |           | SYSALIASES       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCHECKS        |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCOLPERMS      |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCOLUMNS       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCONGLOMERATES |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCONSTRAINTS   |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSDEPENDS       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSFILES         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSFOREIGNKEYS   |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSKEYS          |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSPERMS         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSROLES         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSROUTINEPERMS  |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSCHEMAS       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSEQUENCES     |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSTATEMENTS    |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSTATISTICS    |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSTABLEPERMS    |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSTABLES        |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSTRIGGERS      |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSVIEWS         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSDUMMY1        |          |         |            | SYSTEM TABLE | SYSIBM      |           |
               |           | PERSON           |          |         |            | TABLE        | APP         |           |

JMX JDBC MBean

The JMX JDBC MBean provides the JDBC datasources, and the operations to manipulate datasources and database.

The object name to use is org.apache.karaf:type=jdbc,name=*.

Attributes

The Datasources attribute provides a tabular data of all JDBC datasource, containing:

name is the JDBC datasource name
service.id is the JDBC datasource ID of OSGi service
product is the database product backend
url is the JDBC URL used by the datasource
version is the database version backend.

Operations

create(name, type, jdbcDriverClassName, version, url, user, password, installBundles) creates a JDBC datasource (the arguments correspond to the options of the jdbc:create command).
delete(name) deletes a JDBC datasource.
info(datasource) returns a Map (String/String) of details about a JDBC datasource.
tables(datasource) returns a tabular data containing the tables available on a JDBC datasource.
execute(datasource, command executes a SQL command on the given JDBC datasource.
query(datasource, query executes a SQL query on the given JDBC datasource and return the execution result as tabular data.