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-create
The jdbc:ds-create command automatically creates a datasource definition file by leveraging pax-jdbc.
The jdbc:ds-create command requires either:
-
the
--driverNamecontaining the JDBC driver name -
or the
--driverClasscontaining the JDBC driver class name
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
nameargument 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
-uoption is optional. It defines the database username. -
the
-urloption is optional. It defines the JDBC URL to access to the database. -
the
-poption 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.
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:
-
nameis the JDBC datasource name -
service.idis the JDBC datasource ID of OSGi service -
productis the database product backend -
urlis the JDBC URL used by the datasource -
versionis 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 thejdbc:createcommand). -
delete(name)deletes a JDBC datasource. -
info(datasource)returns a Map (String/String) of details about a JDBCdatasource. -
tables(datasource)returns a tabular data containing the tables available on a JDBCdatasource. -
execute(datasource, commandexecutes a SQL command on the given JDBCdatasource. -
query(datasource, queryexecutes a SQL query on the given JDBCdatasourceand return the execution result as tabular data.