Talend ESB: query a database directly in the mediation perspective
When exposing a database as a REST or SOAP service, lot of users use:
- the integration perspective to create a service, but they don’t leverage Camel
- the mediation perspective to create a route containing a cTalendJob
However, there’s an easy alternative.
Using the mediation perspective, we can use directly a datasource exposed in the runtime (Karaf) as an OSGi service, and directly use Camel components.
The advantages of this approach are:
- The same DataSource is shared by different routes and services. It means that we can use a PooledDataSource and optimize the connections to the database.
- We don’t use any Talend job, and directly leverage Camel native components.
Route design in the studio
We create a route in the mediation perspective of the studio.
First, in the Spring tab, we add the DataSource OSGi service lookup. To do so, we add the
spring-osgi namespace and use the
<beans .... xmlns:osgi="http://www.springframework.org/schema/osgi" xsi:schemaLocation=" ... http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi.xsd ..."> <osgi:reference id="demoDS" interface="javax.sql.DataSource" filter="(osgi.jndi.service.name=jdbc/demo)"/></beans>
You can use the same mechanism to load a JMS connection factory: you can reference a JMS ConnectionFactory OSGi service, and use the
camel-jms component in a
cMessagingEndpoint, or use
cJMS component with an empty custom
Now, we can start the actual design of our route.
As we want to expose a REST service, the route starts with a CXFRS endpoint.
The CXFRS endpoint property is set to
"/demo". It’s not an abolute URL: I recommend to use a relative URL as it will bind relatively to the CXF servlet in the runtime, and so leverage CXF and Jetty configuration of the runtime.
We also create an API mapping
getMembers producing a JSON output.
NB: the methodName (here
getMembers) is available as header. You can use a Content Base Router just after the CXFRS endpoint to route the different methods/REST action to different sub-routes/endpoints. Here, as we have only one method/action (
getMembers), I directly route to an unique endpoint.
We now add a
cMessagingEndpoint to use the
camel-sql component. In the URI, we set the SQL query and the datasource reference:
"sql:select * from members?dataSource=demoDS"
dataSource property corresponds to the reference id as defined in the Spring tab.
And in the advanced settings, we define the sql component:
The SQL endpoint populate the body of the in message with the query result, as a
List. I transform this as JSON using a marshaler (which use
camel-xstream by default).
For that, I add a
cJavaDSLProceddor which does:
For the demo, I directly marshal the list of map as JSON. If you want to have more control in the generated JSON, you can use a
cProcessor before the
cJavaDSLProcessor (marshaler). In this cProcessor, you create a simple instance of a POJO which will be marshaled as JSON, generating the JSON as you want.
Our route is now ready, let’s create a kar file.
We are now ready to deploy in the runtime (Karaf).
Deployment in the runtime
Let’s start a runtime:
First, we install the
jdbc feature to easily create a datasource:
karaf@trun> features:install jdbc
Now, we can create the demo datasource (as expected in our route):
karaf@trun> jdbc:create -i -t derby demo
We now have the demo datasource ready:
karaf@trun> jdbc:datasources Name Product Version URL Status jdbc/demoxa, 418 Apache Derby 10.8.2.2 - (1181258) jdbc:derby:demo OK jdbc/demo, 419 Apache Derby 10.8.2.2 - (1181258) jdbc:derby:demo OK
We create the members table using the
karaf@trun> jdbc:execute jdbc/demo "create table members(id int, name varchar(256))"
Now, let’s insert a record in the members table:
karaf@trun> jdbc:execute jdbc/demo "insert into members values(1, 'jbonofre')"
As our route uses JSON marshaler, we need the
karaf@trun> features:install camel-xstream
We are now ready to deploy our route. We drop the kar file in the deploy folder. We can see in the log:
2015-08-03 10:02:09,895 | INFO | pool-10-thread-1 | OsgiSpringCamelContext | e.camel.impl.DefaultCamelContext 1673 | 170 - org.apache.camel.camel-core - 2.13.2 | Apache Camel 2.13.2 (CamelContext: BlogDataSourceRoute-ctx) started in 0.302 seconds
If we access to
http://localhost:8040/services and see our REST service:
And the generated WADL:
Using a browse, you can use the REST service accessing
http://localhost:8040/services/demo/. We have the JSON generated containing the database data:
When you want to manipulate a database, you don’t have to use a Talend Service or a cTalendJob. Camel provides components:
Using the DataSource as an OSGi service, you can share an unique datasource from different routes, services, applications, and bundles, leveraging the pooling.
It’s an efficient way to leverage some runtime feature, with design in the studio.