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

It's not lack of content...

We all have that excuse of no time for blogging or lack of interesting content for not updating our tech-y blogs but in fact there are tons of interesting things that I'm working on right now like a JDBC over WebSocket project which seems to be a really nice thing to do and also an end-to-end prototype that we at Kaazing put together for an airline company.

Basically, the airline use case goes from backend JMS-based systems to mobile devices over WebSocket using Kaazing WebSocket Gateway JMS Edition notifying the users about gate changes and flight cancellations. You gotta see this in action! It's super cool... Since it has some proprietary systems I won't be able to show you all parts but I've been working on a generic version of it where you can install all the pieces in your machine and run the whole thing locally. Ping me if you want to take a look on that.

The other project that I'm working on is the JDBC over WebSocket which basically uses Kaazing WebSocket Gateway HTML5 Edition to connect to an Apache Camel route with a Mina component and JDBC access to a MySQL database. Everything is pretty straightforward and I'll be writing a tutorial on that in the near future. But, if you need any additional info or sample code just let me know and I'll walk you thru whatever is needed to get you going...

Stay tuned!