Connecting to a database with a webService (cxf) deployed (jbi) in Servicemix

Introduction

Apache ServiceMix at the moment doesn’t have a JBI component for connecting to a database (servicemiix-jdbc is in the roadmap): in the meanwhile you have to use servicemix-cxf or servicemix-bean.

This example (cxf-wsdl-first-jdbc.zip, cxf-wsdl-first.pdf)  is built on top of the example cxf-wsdl-first and can be deployed in apache servicemix 4.0 as JBI sa package.

Installing smx4

Download from “http://servicemix.apache.org/SMX4/download.html

Unzip it

cd /usr/local

tar xvfz apache-servicemix.tar.gz

Starting smx4

cd /usr/local/apache-servicemix-4.0.0/

./bin/servicemix

Creating WS

cd /usr/local/apache-servicemix-4.0.0/examples

cp -a cxf-wsdl-first cxf-wsdl-first-jdbc

Edit file “wsdl-first-cxfse-su/pom.xml”

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.0.8</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>2.5.6</version>
</dependency>

Edit file “wsdl-first-cxfse-su/src/main/resources/xbean.xml”

<bean id="moodleDB"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/moodle" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<cxfse:endpoint>
<cxfse:pojo>
<bean class="org.apache.servicemix.samples.wsdl_first.PersonImpl" >
<property name="dataSource" ref="moodleDB"/>
</bean>
</cxfse:pojo>
</cxfse:endpoint>

Edit file “wsdl-first-cxfse-su/src/main/java/org/apache/servicemix/samples/wsdl_first/PersonImpl.java”

import java.util.*;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
..
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
 this.dataSource = dataSource;
 this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void getPerson(Holder<String> personId, Holder<String> ssn, Holder<String> name)
throws UnknownPersonFault
{
org.apache.servicemix.samples.wsdl_first.types.UnknownPersonFault fault =
  new org.apache.servicemix.samples.wsdl_first.types.UnknownPersonFault();
fault.setPersonId(personId.value);
if (personId.value == null || personId.value.length() == 0) {
   throw new UnknownPersonFault(null, fault);
}
List result = this.jdbcTemplate.queryForList("select description, lastname from mdl_user where username = ?",
   new Object[]{personId.value});
if(result.size() != 1)
 throw new UnknownPersonFault(null, fault);
 Map record=(Map)result.get(0);
 name.value = record.get("lastname").toString();
 ssn.value = record.get("description").toString();
}

Deplying the WS

mvn clean instal

cp wsdl-first-cxf-sa/target/wsdl-first-cxf-sa-4.0.0.zip /usr/local/apache-servicemix-4.0.0/deploy/

Testing WS

Open with your browser the file “file:///usr/local/apache-servicemix-4.0.0/examples/cxf-wsdl-first-jdbc/client.html”