Coming in Karaf 3.0.0: new enterprise JDBC feature

Some weeks (months ;)) ago, my colleague Christian (Schneider) did a good job by creating some useful commands to manipulate databases directly in Karaf.

We discussed together where to put those commands. We decided to submit a patch at ServiceMix because we didn’t really think about Karaf 3.0.0 at that time.

Finally, I decided to refactore those commands as a even more “useful” Karaf feature and prepare it for Karaf 3.0.0.

JDBC feature

By refactoring, I mean that it’s no more only commands: I did a complete JDBC features, providing a OSGi service, a set of commands and a MBean.
The different modules are provided by the jdbc feature.

Like most of other enterprise features, the jdbc feature is not installed by default. To enable it, you have to install the jdbc feature first:

karaf@root()> feature:install jdbc

This feature provides:

  • a JdbcService OSGi service
  • a set of jdbc:* commands
  • a JDBC MBean (org.apache.karaf:type=jdbc,name=*)

The OSGi service provides a set of operation to create a datasource, execute SQL queries on a datasource, get details about a datasource, etc.

The commands and MBean manipulate the OSGi service.

Commands

The first command that you can do is jdbc:create.

This command automatically create a JDBC datasource file in the deploy folder. It can also try to automatically install the bundles providing the JDBC driver.

The jdbc:create command requires the datasource name and type. The type can be: generic (DBCP), Derby, Oracle, MySQL, Postgres, H2, HSQL.

For instance, if you want to create an embedded Apache Derby database and the corresponding datasource, you can do:

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

The -t derby option defines a datasource of type derby. The -u test option defines the datasource username. The -i option indicates to try to install the bundles providing the Derby JDBC driver. Finally test is the datasource name.

Now, we can see several things.

First, the command automatically installed the JDBC driver and created the datasource:

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 datasource blueprint file created in the deploy folder:

/opt/apache-karaf/target/apache-karaf-3.0.0$ ls -l deploy/total 8-rw-r--r-- 1 jbonofre jbonofre 841 Dec 16 14:10 datasource-test.xml

The datasource-test.xml file has been created using a set of templates depending of the datasource type that we provided.
The content is a blueprint XML definition:

<?xml version="1.0" encoding="UTF-8"?><blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"           default-activation="eager">    <bean id="dataSource" class="org.apache.derby.jdbc.EmbeddedXADataSource">        <property name="databaseName" value="test"/>        <property name="createDatabase" value="create" />    </bean>    <service ref="dataSource" interface="javax.sql.DataSource">        <service-properties>            <entry key="osgi.jndi.service.name" value="/jdbc/test"/>        </service-properties>    </service>    <service ref="dataSource" interface="javax.sql.XADataSource">        <service-properties>            <entry key="osgi.jndi.service.name" value="/jdbc/testxa"/>        </service-properties>    </service></blueprint>

You can use the jdbc:delete command to delete an existing datasource.

The jdbc:datasources command provide the list of available JDBC datasources:

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

If you want to have more details about a JDBC datasource, you can use the jdbc:info command:

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 Driverurl            | jdbc:derby:test

You can execute SQL commands and queries on a given JDBC datasource.

For instance, we can create a table directly in our Derby database using the jdbc:execute command:

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

The jdbc:tables command displays all tables available on a given datasource. In our case, we can see our person table:

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         |           |

Now, also using the jdbc:execute command, we can insert some records in our 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('Christian Schneider','cschneider')"karaf@root()> jdbc:execute /jdbc/test "insert into person(name,nick) values('JB Onofre','jbonofre')"

The jdbc:query command executes SQL queries returning result. For instance, we can select the records in the person table:

karaf@root()> jdbc:query /jdbc/test "select * from person"NICK       | NAME               --------------------------------bar        | foo                cschneider | Christian Schneiderjbonofre   | JB Onofre

JDBC MBean and JDBC OSGi Service

All actions that we did using the jdbc:* commands can be performed using the JDBC MBean.

More over, if you want to perform JDBC operations or manipulates datasources programmatically, you can use the org.apache.karaf.jdbc.JdbcService OSGi service.

Next one: jms

Following the same idea, I prepared a new jms feature providing OSGi service, commands and MBean to manipulate JMS connection factories, get information on the broker, send/consume messages, etc.

Comments

Post a Comment

Popular posts from this blog

Exposing Apache Karaf configurations with Apache Arrow Flight

Image
This blog post shows how to use Apache Arrow and Arrow Flight in Apache Karaf runtime. The code base is available here: https://github.com/jbonofre/karaf-arrow . Apache Arrow ? Apache Arrow is composed by: The Arrow format (specification). Format implementations with different languages (C, Java, Go, Rust, ...). Extra subprojects like Flight, Flight SQL, ADBC, nanoarrow, ... Apache Arrow format is a in-memory columnar format, super efficient for scanning and iterating a large chunks of data. In traditional memory buffer, we focus on "row". Apache Arrow format "groups" data per column, which is much more efficient on modern CPUs/GPUs. The Apache Arrow format is basically a specification. We have several implementations for this specification: C C++ C# Go Java Python Ruby Rust As the purpose of this blog post is to show Apache Arrow in Apache Karaf, we will use Arrow Java. In addition, Apache Arrow also provides several...
Read more

Getting started with Apache Karaf Minho

Image
Apache Karaf Minho Apache Karaf Minho is a new runtime, completely created from scratch. The objectives are: super easy to use and run ligtning fast, including GraalVM support Kubernetes compliant to be easily used on the cloud helping to optimize cloud costs by supporting applications colocation providing turnkey services allowing you to create Minho applications in a minute Minho is composed by: minho-boot is the core of Minho. It contains the runtime launcher and the core services (service registry, lifecycle service, configuration service, ...). minho-* services provided additional functionnalities in the runtime. You can use these services to create your own services, it's also where you will find the services managing sepcific kind of applications, allowing colocation. minho-tooling optionally provide convenient tools to create your own runtime. It includes minho-maven-plugin for instance. In this blog post, we will take a quick tour on A...
Read more

Using Apache Karaf with Kubernetes

Image
In a previous blog post ( http://blog.nanthrax.net/?p=893 ), I introduced the “new” docker support in Karaf and the use of Karaf static distribution. This post follows the previous one by illustrating how to use Karaf docker image with a Kubernetes cluster. Preparing Karaf static docker image As in the previous blog post, we are preparing a Karaf static distribution based docker image. For this blog post, I’m using the example provided in Karaf: https://github.com/apache/karaf/tree/master/examples/karaf-docker-example/karaf-docker-example-static-dist . I’m just building the distribution as usual: karaf-docker-example-static-dist$ mvn clean install Preparing kubernetes testing cluster For this blog, I’m using a minikube installed locally. The installation is pretty simple: Download minikube from https://storage.googleapis.com/minikube/releases/latest/minikube-linux-amd64 Rename minikube-linux-amd64 as minikube Copy minikube in your path, for instance /us...
Read more