| // |
| // Licensed under the Apache License, Version 2.0 (the "License"); |
| // you may not use this file except in compliance with the License. |
| // You may obtain a copy of the License at |
| // |
| // http://www.apache.org/licenses/LICENSE-2.0 |
| // |
| // Unless required by applicable law or agreed to in writing, software |
| // distributed under the License is distributed on an "AS IS" BASIS, |
| // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| // See the License for the specific language governing permissions and |
| // limitations under the License. |
| // |
| |
| ==== 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. |
| |
