Apache Camel example to insert a row in a table

Question

I want to insert exchange.body to a database table for one of the condition of my route.

• Is there any example/tutorial of camel-jdbc component to insert message body?
• Can I import the SQL statement itself and pass exchange.body to it?

I looked at http://camel.apache.org/jdbc.html example, but could not understand it.

Here Spring example is confusing for me. I didn't get why is it setting the body as SQL query and again importing some query from the class path. (There is no insert query example mentioned here.)

Answer 1

If you want to insert using the same statement (changing the parameters only) - use SQL component.

If you want to insert using arbitrary SQL statement into the component - use JDBC component.

SQL component usage:

from("direct:start").to("sql:insert into table foo (c1, c1) values ('#','#')");

com.google.common.collect.Lists;
producerTemplate.sendBody("direct:start", Lists.newArrayList("value1","value2"));

JDBC component usage:

from("direct:start").to("jdbc:dataSource");

producerTemplate.sendBody("direct:start", "insert into table foo (c1, c1) values ('value1','value2')");

Answer 2

You probably need to do some restructure of your payload before inserting it anyway, so there should probably be no issue to do a transformation using whatever method in Camel to set the body to the appropriate INSERT statement.

The important thing is what kind of payload structure your incoming message have. In the basic case - it's a string - it should be fairly simple

// In a Java bean/processor before the JDBC endpoint.
// Update: make sure to sanitize the payload from SQL injections if it contains user inputs or external data not generated by trusted sources.
exchange.getIn().setBody("INSERT INTO MYTABLE VALUES('" + exchange.getIn().getBody(String.class) + "', 'fixedValue', 1.0, 42)");

In case your message contains complex data structures, this code will of course be more complex, but it's pretty much the same way regular application will generate SQL queries.

The classpath example you are refering to

 <jdbc:embedded-database id="testdb" type="DERBY">
        <jdbc:script location="classpath:sql/init.sql"/>
 </jdbc:embedded-database>

Simply shows how to test the JDBC component by starting a Database server embedded (Apache Derby) and populate it with some initial data (the sql/init.sql file). This part is not really part of the core jdbc component, but simply in the documentation to get up and running a sample without needing to configure a DB server and setup the JDBC connection properties.

That said, you might want to use the SQL component for more complex scenarios.