Spring JDBC in OSGi

Spring JDBC provides an excellent alternative to the dreadful Java JDBC API for accessing a database. However, using it in an OSGi container isn't so easy, as there are some pitfalls to avoid. I will try to describe here a solution to this problem.

Test Database

First let's create a test database. We are going to use MySQL. Open a MySQL command prompt and type the following:

CREATE DATABASE `repository`;

USE `repository`;

CREATE TABLE `items` (
  `ItemId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY (`ItemId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


insert  into `items`(`ItemId`,`Name`) values (1,'Name 1'),(2,'Name 2'),(3,'Name 3');

Spring-JDBC bundle

Now we are going to create a test bundle to access the data stored in the above table. The following pom.xml contains the appropriate configuration and the required dependencies. Notice that we need spring-core, spring-jdbc and spring-tx (transaction) bundles. These are the necessary compile time dependencies. There are more runtime dependencies that we need to install  in the OSGi container. The exact list of dependencies will be presented later.

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  
    <modelVersion>4.0.0</modelVersion>
    <groupId>aniketos</groupId>
    <artifactId>springjdbc-test-impl</artifactId>
    <packaging>bundle</packaging>
    <version>1.0.0</version>
    <name>springjdbc-test</name>
    <url>http://maven.apache.org</url>
  
    <properties>
        <spring.version>3.1.1.RELEASE</spring.version>
        
  <bundle.require.bundle>org.springframework.jdbc</bundle.require.bundle>
  <bundle.import.package>
javax.sql,
org.springframework.core;version="[2.5.6,3.1.2)",
org.springframework.jdbc;version="[2.5.6,3.1.2)",
org.springframework.transaction;version="[2.5.6,3.1.2)"      
  </bundle.import.package>
  <bundle.export.package></bundle.export.package>      
        <bundle.dynamicimport.package>*</bundle.dynamicimport.package>
    </properties>
  
    <build>  
        <plugins>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <version>2.1.0</version>
                <extensions>true</extensions>
                <configuration>
     <instructions>
      <Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>
      <Bundle-Activator>gr.atc.aniketos.springjdbc.MainActivator</Bundle-Activator>
      <Bundle-Name>${project.artifactId}</Bundle-Name>
      <Bundle-Description>A bundle that demonstrates the use of SPRING JDBC in an OSGi environment</Bundle-Description>
      <Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>
      <Require-Bundle>${bundle.require.bundle}</Require-Bundle>
      <Import-Package>${bundle.import.package}</Import-Package>
      <Export-Package>${bundle.export.package}</Export-Package>
                        <DynamicImport-Package>${bundle.dynamicimport.package}</DynamicImport-Package>
     </instructions>
    </configuration>              
            </plugin>
            
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>          
        </plugins>
    </build>
  
    <dependencies>      
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>org.osgi.core</artifactId>
            <version>4.3.0</version>
        </dependency>  
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-core</artifactId>
   <version>${spring.version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-tx</artifactId>
   <version>${spring.version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>${spring.version}</version>
  </dependency>      
    </dependencies>
  
</project>

The Java code is simple as we only want to demonstrate that the access to the database is succesfull.

public class MainActivator implements BundleActivator {

    public void start(BundleContext context) throws Exception {
        System.out.println("Started...");
        
     String databaseUrl = "jdbc:mysql://localhost:3306/repository";
     String databaseUser = "root";
     String databasePassword = "";

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
         System.out.println(ex.getMessage());
            throw new IllegalStateException(
                    "Could not load JDBC driver class", ex);
        }

     DriverManagerDataSource dataSource = new DriverManagerDataSource();
     dataSource.setUrl(databaseUrl);
     dataSource.setUsername(databaseUser);
     dataSource.setPassword(databasePassword);

     JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        
        Collection<String> names = jdbcTemplate.query(
                          "SELECT `items`.`Name` FROM `items`",
                          new Object [0], new ServiceNameMapper());

        for(String name: names) {
            System.out.println(name);
        }
    }

    private final class ServiceNameMapper implements RowMapper<String> {
     public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString("Name");
        }
    }  
    
    public void stop(BundleContext context) throws Exception {

    }
}

mysql-springjdbc-fragment bundle

In order to test the above bundle you of course need to install a MySQL driver to your OSGi container. However this isn't enough. The same class loader needs to be used from both Spring-JDBC and the driver. This isn't the case though, as these two are in different bundles and have separate class loaders. The solution is to use a fragment bundle. This can be an empty bundle that all it does is to attach the MySQL driver to Spring-JDBC, so that these two can share the same classloaders. This pom.xml creates the fragment bundle:

<build>
  <plugins>
    <plugin>
      <groupId>org.apache.felix</groupId>
      <artifactId>maven-bundle-plugin</artifactId>
      <version>2.1.0</version>
      <extensions>true</extensions>
      <configuration>
        <instructions>
          <Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>
          <Fragment-Host>org.springframework.jdbc</Fragment-Host>
          <Import-Package>com.mysql.jdbc</Import-Package>
        </instructions>
      </configuration>      
    </plugin>
  </plugins>
</build>

Warning: Maven (bnd tool internally) doesn't let you build a completely empty jar. Just place a text file in the META-INF folder to overcome this.


Running everything in Apache Karaf

Now let's see how can we easily install everything in Apache Karaf. We are going to make use of Apache Karaf features. In order for this to work Maven needs to be installed and the M2_HOME variable properly set.

Karaf features allows you to group a bundle and its dependencies and install everything in one go. All bundles are defined in a features.xml file. Since Karaf supports the mvn protocol, we can use mvn URLs. We can use both remote and local repositories.

<features>
  <feature name="springjdbc_test" version="1.0">  
    <bundle>mvn:org.apache.commons/com.springsource.org.apache.commons.logging/1.1.1</bundle>
    <bundle>mvn:org.springframework/spring-core/3.1.1.RELEASE</bundle>
    <bundle>mvn:org.springframework/spring-asm/3.1.1.RELEASE</bundle>
    <bundle>mvn:org.springframework/spring-beans/3.1.1.RELEASE</bundle>
    <bundle>mvn:org.springframework/spring-context/3.1.1.RELEASE</bundle>
    <bundle>mvn:org.springframework/spring-tx/3.1.1.RELEASE</bundle>
    <bundle>mvn:org.springframework/spring-jdbc/3.1.1.RELEASE</bundle>
    
    <bundle>mvn:aniketos/mysql-springjdbc-fragment/1.0.0</bundle>
    <bundle>mvn:com.mysql.jdbc/com.springsource.com.mysql.jdbc/5.1.6</bundle>  
    <bundle>mvn:aniketos/springjdbc-test-impl/1.0.0</bundle>  
  </feature>
</features>

You can easily install the springjdbc_test feature by copying the above features.xml file in your file system and then typing in Apache Karaf:

karaf@root> features:addURl file:///path/to/features.xml
karaf@root> features:install springjdbc_test

However we can also create a Maven project to hold the features.xml file. The features.xml needs to go under src/main/resources. The pom.xml for building the project is the following:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>aniketos</groupId>
  <artifactId>springjdbc-feature</artifactId>
  <version>1.0.0</version>
  <packaging>pom</packaging>
  <name>springjdbc-feature</name>
  
  <build>
    <resources>
      <resource>
        <directory>src/main/resources</directory>
        <filtering>true</filtering>
      </resource>
    </resources>
    <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-resources-plugin</artifactId>
      <executions>
       <execution>
        <id>filter</id>
        <phase>generate-resources</phase>
        <goals>
          <goal>resources</goal>
        </goals>
       </execution>
      </executions>
    </plugin>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>build-helper-maven-plugin</artifactId>
        <executions>
          <execution>
            <id>attach-artifacts</id>
            <phase>package</phase>
            <goals>
              <goal>attach-artifact</goal>
            </goals>
            <configuration>
              <artifacts>
                <artifact>
                  <file>target/classes/features.xml</file>
                  <type>xml</type>
                </artifact>
              </artifacts>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>

</project>

Now the feature can be install by adding a URL with the mvn protocol:

karaf@root> features:addURl mvn:aniketos/springjdbc-feature/1.0.0/xml
karaf@root> features:install springjdbc_test

Summary

In this post we presented how Spring-JDBC can be used in an OSGi environment. A fragment bundle that will attach the SQL driver to the Spring-JDBC bundle is needed. The client bundle only needs to import spring-jdbc and javax.sql packages. However, a dynamic import is also necessary.

We also showed how the Apache Karaf features can be used to easily install a bundle and its dependencies.

The code of this example is available in GitHub.

ANIKETOS newsletter

Stay informed on our latest news!

Login

Only for users who has an user and a password sent by the administrator.