Accessing Databases with HTML5 WebSockets and Apache Camel

As I have said on my previous post, I've been working with several companies with the most diverse use cases and one that really brought my attention was the requirement to access databases using HTML5 WebSockets which seems to be a clever thing to do with the right tools.

So, basically what I've setup for this is represented on the picture below:

Let's go over the implementation details of all of the components described here:

From right to left, the backend datasource I had to use was MySQL that you can download from http://www.mysql.com/downloads/. Then, I used Apache Camel as the backend framework accessing the database and also exposing a TCP-based component (Mina in this case). Moving to the left, we have the Kaazing WebSocket Gateway connecting to the Apache Camel framework and exposing WebSocket connectivity to the web clients (browser-based, mobile-based and desktop thick client applications).

The web-browser sample client is pretty straightforward and with a few lines of code you can get the functionality. There are sample javascript clients shipped with the Kaazing WebSocket Gateway that you can re-use or modify to your needs. The Kaazing WebSocket Gateway documentation set is also helpful and good starting point. Check it out: http://tech.kaazing.com/documentation/html5/3.3/o_dev_js.html

Then the only change you have to make in the Kaazing WebSocket Gateway is in the gateway-config.xml (under $KWG_HOME/conf) to include the service you want to expose to the web clients and where you want to connect to the backend framework with access to the database.

Here is what I have included on my configuration:

<!-- Demo JDBC Backend System Config -->

  <!-- Proxy service --> 

  <service>

<accept>ws://${gateway.hostname}:${gateway.extras.port}/jdbc</accept> 

<type>proxy</type>

<properties>

<connect>tcp://${gateway.hostname}:8888</connect>

</properties>

   </service>

Check the Kaazing WebSocket Gateway documentation to get more familiar with these settings.

Last but not least, the Apache Camel route hosting the Mina endpoint and the database connectivity:

 <camelContext xmlns="http://camel.apache.org/schema/spring">

    <package>com.kaazing.gateway.demo.db</package>

    <route id="WSMinaJDBCSample">

        <from uri="mina:tcp://localhost:8888?timeout=30000"/>

        <log message="RECEIVED: ${body}"/>

        <setBody>

        <simple>select * from customers where customers.email = '${body}'</simple>

        </setBody>

        <to uri="jdbc:myDataSource"/>

        <log message="GENERATED: ${body}"/>

    </route>

</camelContext>

<!-- MySQL datasource -->

  <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="myDataSource">

    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql://localhost:3306/test"/>

    <property name="username" value="root"/>

    <!-- <property name="password" value="XXXXX"/>  -->

  </bean>

The MySQL database setup is very simple and you can use any sample database tables you want. Just make sure you update the Apache Camel route and your environment settings accordingly.

Code will be available soon...

UDPATE:
Sample code is now available at: https://github.com/mjabali/DBOverWS

JMS and Database Integration with Apache ServiceMix, Camel and ActiveMQ

I've been asked to create a simple project to demonstrate how Apache ActiveMQ, Camel and ServiceMix could be used in the same integration context. Then, for the specific customer I was talking to, I decided to create something related to the world they were used to.

This example will process messages as they are delivered on an input queue and I thought that XML would be a good format to also demonstrate how to parse the payload and generate an SQL statement  before hitting the database.

In summary, for each message going to the input queue, we're going to create a record in the database table and then generate a response message on the output queue.

I'm assuming you're already have Apache ServiceMix downloaded and installed in your machine. If not, it's time to download it… I recommend you to just go to http://fusesource.com/downloads/ and download FUSE ESB.

I'm also assuming that you already have Maven installed but if not feel free to download and install it from maven.apache.org

Here are the steps to create this short and simple demonstration:

1) Download and install MySQL database from http://dev.mysql.com/downloads/
You can use the default test database that's shipped with MySQL and the only thing you have to do is to create a table to insert test records. To do that, connect to the database  and run the following SQL statement:

CREATE table PARTNER_METRIC(partner_id int, time_occurred DATE, status_code int, perf_time long);


2) Install the following features in ServiceMix:

features:install camel-jdbc
features:install camel-jms


3) Install commons-dbcp bundle:

osgi:install -s mvn:org.apache.servicemix.bundles/org.apache.servicemix.bundles.commons-dbcp/1.2.2_6


4) When you do this you will receive a bundle id, e.g. 229.  Enable dynamic imports on this bundle so that it can pick up the appropriate database driver.

dev:dynamic-import 229


5) Using ServiceMix hot deploy feature to deploy the MySQL JDBC driver:

cp /Applications/MySQL/mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar  /Users/mjabali/Fuse/apache-servicemix-4.4.1-fuse-00-08/deploy/


Then you can verify that the driver gets deployed correctly with the following command:

karaf@root> list |grep -i mysql


and you should get a message similar to the following:

[ 227] [Active     ] [            ] [       ] [   60] Sun Microsystems' JDBC Driver for MySQL (5.1.18)


6) Download the source files of this sample and extract them on the directory of your choice. So, let's review the JMS and Database connection settings  in the following resource files:

$PROJECT_HOME/src/test/resources/sample/RiderAutoPartsPartnerTest.xml
$PROJECT_HOME/src/main/resources/META-INF/spring/beans.xml file

These are the same file albeit named differently. One is used by the bundle and the other by our test case.

We'll be also using the default Apache ActiveMQ instance for the messaging aspect of this tutorial.
To check whether the ActiveMQ is installed, up and running you can run the following command:

karaf@root> osgi:list |grep activemq-broker


and you should see an output similar to this

[  66] [Active     ] [Created     ] [       ] [   50] activemq-broker.xml (0.0.0)


You can see the default broker's definition within the activemq-broker.xml file under $SERVICEMIX_HOME/etc/activemq-broker.xml

The database source definition is on the beans.xml file and you may want to adjust it to reflect the settings on your environment. Here is a copy of the definitions I have on my machine:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">


    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql://localhost/test"/>

    <property name="username" value="root"/>

    <property name="password" value="XXXX"/>

</bean>

7) Let's compile the code then… Run the following command on the root directory of the project

mvn clean install -Dmaven.test.skip=true


If the code builds up successfully then you're ready to deploy on ServiceMix.

8) To deploy the bundle into ServiceMix, run the following command:

karaf@root>osgi:install mvn:com.fusesource.fusebyexample/camel-jms-dbase/1.0-SNAPSHOT


A confirmation that the bundle was deployed successfully should be returned to you and a message similar to the follow one should be displayed:

Bundle ID: 228


Then, you are ready to start the bundle and verify if has started correctly. Follow the instructions below to do that:

To start the bundle:

karaf@root>osgi:start <bundle ID>


To verify the bundle has started correctly:

karaf@root>osgi:list |grep <bundle ID>


and then a message like the following should be displayed:

[ 228] [Active     ] [            ] [Started] [   60] Camel JMS Database Example (1.0.0.SNAPSHOT)


You can also immediately turn on the trace logging capability for Camel and get additional output in the ServiceMix log facility. To enable the trace logging just run:

karaf@root>set TRACE org.apache.camel


and then to see the ServiceMix log, run:

karaf@root>log:display


You should be able to see messages with the TRACE identifier similar to the below:

11:46:17,095 | TRACE | tnerRequestQueue | JmsMessageListenerContainer      | ?                                   ? | 94 - org.springframework.jms - 3.0.5.RELEASE | Consumer [ActiveMQMessageConsumer { value=ID:titan.local-54477-1319240594120-6:1:1:1, started=true }] of session [PooledSession { ActiveMQSession {id=ID:titan.local-54477-1319240594120-6:1:1,started=true} }] did not receive a message


9) To test this sample project all you have to do is to send a sample message (there is one included in the $PROJECT_HOME/src/test/resources/sample directory) to the ActiveMQ queue that the Camel route is listening for. You can use any approach you like but writing a JMS Test client is pretty simple and there are tons of examples on the Internet and also available in the ActiveMQ sample directory. You can also use tools like HermesJMS which adds a little UI for you so you don't need to create a test program.

Either way, after sending a message to the partnerRequestQueue (default queue name used on this tutorial) you should see a couple of messages in the ServiceMix console like those below:

karaf@root>  **** toSql returning: INSERT INTO PARTNER_METRIC (partner_id, time_occurred, status_code, perf_time) VALUES ('123', '200911150815', '200', '9876')

 **** fromSql returning: Sample message to be returned on reply queue


You can verify that the values were inserted into the MySQL database running a simple query like:

select * from PARTNER_METRIC;


It is also recommended to check the ServiceMix log to make sure there are no exceptions there.

The sample code for this tutorial is available on GitHub: https://github.com/mjabali/JMS-DB-Sample

I also would like to thank my colleague Susan Javurek for putting an extra effort on this and helping out with the sample code.

UPDATE: I also would like to say a big thanks to Claus Ibsen (@davsclaus) for reminding me that all the manual steps related to OSGi bundles installation could be automated using the feature capability that consists in deploy multiple related bundles into a larger unit of deployment.

Enjoy the ride...

File Batch Splitter with Apache Camel and FuseSource Camel IDE

The Splitter from the EIP patterns allows you split a message into a number of pieces and process them individually.

To implement that EIP pattern using Apache Camel and FuseSource Camel IDE is very simple. Here is what you need:

1) Eclipse IDE (Eclipse Helios 3.6.2)
2) Apache Maven (3.0.3)
3) FuseSource Camel IDE (http://fusesource.com/products/fuse-ide-camel/)

After you install and configure the tools mentioned above, start creating a new Fuse IDE Project.


Select the Camel Archetype and type the Group Id, Artifact Id and Package name for your project then click Finish:

At this point you should have a sample Camel project and artifacts available for you. The default Camel project gives you another EIP, the Content-Based Router (http://camel.apache.org/content-based-router.html). You can open the camelContext.xml under the META-INF/spring directory and remove the Camel components available there since we're going to implement a different EIP (right-click and then Delete).

To start creating the File Batch Splitter sample project, right-click in the canvas of the camelContext.xml file and select Add > Route. We're now ready to start building our process. Right-click inside the Route rectangle and select Add > Endpoints > Endpoint.

Then, right-click again and select Add > Routing > Split. Finally, right-click and select Add > Endpoints > Endpoint. Now, click on the first created Endpoint and draw a line to the Split component. Do the same thing between the Split component and the second Endpoint.

You can also click on the Automatic Layout icon available in the Eclipse top menu bar to automatically adjust your route diagram.

Now, you have added all necessary components and should have something similar to the following picture.

You can now configure each component to execute the tasks needed to conclude the File Batch Splitter sample project. Basically, we'll consume files from one directory in the file system (an XML file with multiple records), split each record in the XML file into a new file in the file system.

To do that, click on the first endpoint and go to the Properties tab where you can see the details of each component. In the Uri field, type: file:src/data?noop=true

Highlight the Split component and type the condition you want to generate new records from the input file. In this case, we're going to use the following: //club/person as a XPath expression.

And then, highlight the final endpoint and type the following in the Uri field: file:target/output?fileName=${date:now:yyyyMMddHHmmssSSSSS}.xml
We're using a dynamic file name based on the current date to avoid conflicts and/or overriding the original files. So, every record gets a new file name when Camel creates the file.

Before we execute our Camel project, we need an input file under the src/data directory. Here is the content of a sample file to use:


<?xml version="1.0" encoding="UTF-8"?>
<club>

    <person user="marcelo">

        <firstName>Marcelo</firstName>

        <lastName>Jabali</lastName>

        <city>San Diego</city>

    </person>

    <person user="scott">

        <firstName>Scott</firstName>

        <lastName>Cranton</lastName>

        <city>Boston</city>

    </person>

</club>

The step before we execute the project is to configure how to run it. There are a couple different ways to run it but basically you can configure everything in Eclipse. Just go to Run > Run Configurations and then under Local Camel Context you can create a new configuration just pointing to the camel-Context.xml file. Click Apply and then Run.

If everything was configured correctly you should be able to see two XML files under the src/output directory containing a person record each (don't forget to refresh the view).

You also have the option to execute that project through Maven on the command line. Just navigate to the root directory of the project and type:

>mvn camel:run

That does the same thing that we executed under the Eclipse IDE.

If you want to download the files and play with the samples the project files are available on GitHub (https://github.com/FuseByExample/FileBatchSplitter)

Enjoy the ride...