Polling any Database table with JBoss ESB 4.9 and Apache Camel - Part 1

Introduction


Poll a database table is very common use case when you are integrating systems and applications, according EIPs(http://www.enterpriseintegrationpatterns.com/): Shared Databse is one of Integration Styles available in the industry.

JBoss ESB 4.9 : Support for Apache Camel 

IMHO, this is the greatest improvement in JBoss ESB from the last 3 years,  we community owe this job to David Ward, which pushed this implementation in that newest Community JBoss ESB release.  David wrote about getting started with Apache Camel and JBoss ESB here: http://community.jboss.org/wiki/CamelGateway. If you need some basic overview about Apache Camel, I recommend you take a look on Camel website:  http://camel.apache.org/.

Where is the benefits of Apache Camel in JBoss ESB?

First of all, we have a plenty of new gateways components available, such component's list is huge, you can see them here. In Apache Camel we have to be aware about the difference between the components, basically there are:

• Consumers - Which can be message listeners from the URI protocols
• Producers - Which can post a message into the protocol URI

There are components that commonly can be both, but you might see some components that can be only Consumers or only Producers. It depends of the protocols nature or any other related aspect. 

For that demo, I decided use the JPA Component, as which can be both a producer and consumer.

Demo Use Case

We have many customers asking how they could integrate legacy applications, or even applications written in different platforms, such as .Net or PHP, for this matter we have one thing in common: The Database can be shared between many applications. I am not talking about ETL or even Data Services Federation, at this point I want to just get a row from some table and consider it a "Message", as which I can do anything I want using JBoss ESB Actions.

To turn my demo more realistic I am using the very well known opensource CRM: SugarCRM, which has a table called: Lead, which keeps every leads from the system stored. See Image 1 to see my local SugarCRM instance running:

Image 1 - Sugar CRM with my Leads

The SugarCRM stores the data into any relational database, in my case I am using MySql.

The ESB Project

In my ESB Project, I just have to add an Entity class that will reference the Lead Table, in that case I created the following table:

@Entity
@Table(name="leads")
@NamedQuery(name = "NewLeadsQuery", query = "select x from Lead x where x.status='New'")

public class Lead implements Serializable {
 private static final long serialVersionUID = 1L;

 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 @Column(unique=true, nullable=false, length=36)
 private String id;

    @Lob()
 @Column(name="account_description")
 private String accountDescription;

   .....

In that Entity you can see that it is a very basic JPA Entity, which contains a named query that will return every Lead that the property status is "New", in other words: Every single Lead will be dispatched to JBoss ESB as a Message, and now you are asking yourself: How ? See the next topic.

Declaring a Camel Gateway in JBoss ESB

What is more important is to know the components that you will interact with, and notice that each component has its dependency, in my case with JPA, I need to add into my project lib folder the camel-jpa-VERSION.jar, which is my component library, I have to do that, because AKAIK just the core camel libraries are in JBoss ESB classpath. Here is my jboss-esb.xml config portion for it:

There is a point that I'd like to call your attention: The & symbol as it is declared in XML you have to work with the & representation replacing that symbol

Now, you are able to create any Service in JBoss ESB and several actions to interact with your Message.

In my final release of that demo, I am using JBoss BRMS/Guvnor, that will receive the Lead row as an Object and will decide if it is a Lead that have to be worked by a Sales Rep or an Inside Sales Rep, according some fields on that "Lead fact"

In my upcoming new things in that demo I will have 2 consoles: 1 written using Swing which will listening a JMS Queue for the Inside Sales, which supposedly are inside the company's office, and if it is a lead for an Outside Sales Rep, it will go to an Infinispan Cache, and it will be accessible through an WebSocket HTML 5 Client.

Last Tip about JPA Component

Once you consume a row to the database, maybe you need to change at least the column that makes the row available to be polled, in our case "status=new", So, you can "mark" that row with a new Status, or invoke any other operation, just adding the @Consumed annotation in any method in the JPA Entity.

// Changing 2 column's values in the database, so a polled row, will not be consumed more than one time
@Consumed
 public void checked(){
  
  this.setStatus("EAI-Partner"); 
  this.setStatusDescription("This Lead was Forwarded to a Partner");
  
 }

Next Steps

Partially I have the demo done, I will move that to my Mac, where I can do fancier screencasts, and than you will be able to see it working.