blob: 850172d08d5bfe1e504c8ae102540a7cef43b970 [file] [log] [blame]
h1. DataSources (JDBC)
The Apache Karaf DataSources (JDBC) is an optional enterprise feature.
You have to install the following features first:
{code}
karaf@root()> feature:repo-add pax-jdbc
karaf@root()> feature:install pax-jdbc
karaf@root()> feature:install pax-jdbc-config
karaf@root()> feature:install jdbc
{code}
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.
h2. Commands
h3. {{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
The {{jdbc:ds-create}} accepts a set of options and the name argument:
{code}
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
{code}
* 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:
{code}
karaf@root()> jdbc:ds-create -dn derby -dbName test -url jdbc:derby:test test
{code}
We can see that this command created a configuration PID containing the datasource properties.
h2. {{jdbc:ds-delete}}
The {{jdbc:ds-delete}} command deletes a datasource.
{code}
karaf@root()> jdbc:ds-delete test
{code}
h2. {{jdbc:ds-list}}
The {{jdbc:ds-list}} command lists the JDBC datasources:
{code}
karaf@root()> jdbc:ds-list
Name | Product | Version | URL | Status
---------------------------------------
{code}
h2. {{jdbc:ds-info}}
The {{jdbc:ds-info}} command provides details about a JDBC datasource:
{code}
karaf@root()> jdbc:ds-info 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
{code}
h2. {{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:
{code}
karaf@root()> jdbc:execute test "create table person(name varchar(100), nick varchar(100))"
{code}
And we can insert some records in the {{person}} table:
{code}
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')"
{code}
h2. {{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:
{code}
karaf@root()> jdbc:query test "select * from person"
NICK | NAME
--------------------------------
bar | foo
test | test
{code}
h2. {{jdbc:tables}}
The {{jdbc:tables}} command displays all tables available on a given JDBC datasource:
{code}
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 | |
{code}
h2. 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=*}}.
h3. 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.
h3. 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.