Coming in Karaf 3.0.0: new enterprise JDBC feature

December 16, 2013 Posted by jbonofre

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 Driver
url            | 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 Schneider
jbonofre   | 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.

About jbonofre

ASF Member, PMC for Apache Karaf, PMC for Apache ServiceMix, PMC for Apache ACE, PMC for Apache Syncope, Committer for Apache ActiveMQ, Committer for Apache Archiva, Committer for Apache Camel, Contributor for Apache Falcon Twitter: jbonofre IRC: jbonofre on #servicemix,#karaf,#camel,#cxf on Freenode

Comments are closed.