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”