DataSources (JDBC)

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

You have to install the jdbc feature first:

karaf@root()> feature:install jdbc

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:create`

The jdbc:create command automatically creates a datasource definition file in the Apache Karaf deploy folder.

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

karaf@root()> jdbc:create --help
DESCRIPTION
        jdbc:create

        Create a JDBC datasource

SYNTAX
        jdbc:create [options] name

ARGUMENTS
        name
                The JDBC datasource name

OPTIONS
        -u, --username
                The database username
        -v, --version
                The version of the driver to use
        -t, --type
                The JDBC datasource type (generic, MySQL, Oracle, Postgres, H2, HSQL, Derby, MSSQL)
        -url
                The JDBC URL to use
        -p, --password
                The database password
        -i, --install-bundles
                Try to install the bundles providing the JDBC driver
        -d, --driver
                The classname of the JDBC driver to use. NB: this option is used only the type generic
        --help
                Display this help message

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 -v option is optional. It "forces" a given JDBC driver version (only used with the -i option).
the -t option is required. It defines the JDBC datasource type. Accepted values are: MySQL, Oracle, Postgres, Derby, H2, HSQL, MSSQL, Generic. Generic is a generic configuration file using DBCP to create a pooled datasource. When using generic, it’s up to you to install the JDBC driver and configure the deploy/datasource-[name].xml datasource file.
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.
the -d option is optional. It defines the JDBC driver classname to use (only used with the generic type).
the -i option is optional. If specified, the command will try to automatically install the OSGi bundles providing the JDBC driver (depending of the datasource type specified by the -t option).

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

karaf@root()> jdbc:create -t derby -u test -i test

We can note that the Derby bundle has been installed automatically, and the datasource has been created:

karaf@root()> la
...
87 | Active   |  80 | 10.8.2000002.1181258  | Apache Derby 10.8
88 | Active   |  80 | 0.0.0                 | datasource-test.xml

We can see the deploy/datasource-test.xml datasource file.

`jdbc:delete`

The jdbc:delete command deletes a datasource by removing the deploy/datasource-[name].xml datasource file:

karaf@root()> jdbc:delete test

Note	The `jdbc:delete` does not uninstall the JDBC driver bundles and does not remove the files created by the JDBC driver (or the database in case of embedded database). It’s up to you to remove it.

`jdbc:datasources`

The jdbc:datasources command lists the JDBC datasources:

karaf@root()> jdbc:datasources
Name       | Product      | Version              | URL
------------------------------------------------------------------
/jdbc/test | Apache Derby | 10.8.2.2 - (1181258) | jdbc:derby:test

`jdbc:info`

The jdbc:info command provides details about a JDBC datasource:

karaf@root()> jdbc:info /jdbc/test
Property       | Value
--------------------------------------------------
driver.version | 10.8.2.2 - (1181258)
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 /jdbc/test "create table person(name varchar(100), nick varchar(100))"

And we can insert some records in the person table:

karaf@root()> jdbc:execute /jdbc/test "insert into person(name, nick) values('foo','bar')"
karaf@root()> jdbc:execute /jdbc/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 /jdbc/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 /jdbc/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
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.