Reading Tables From OracleDB Using Camel

Question

Hi I am new to Service mix.Can anyone pointing me in right direction how to read the tables from oracle Db and post that table into a local files.(Here i want to use only blueprint.XML content which i can directly deploy in service mix deploy folder).

Answer 1

Here is an example:

public class JdbcReadFromOracleRoute extends RouteBuilder {
    @Override
    public void configure() throws Exception {
        from("sql:select full_name from common.company where code='1'?delay=3000&dataSource=oracleDataSource&outputType=SelectOne")
                .routeId("testOracleRead").autoStartup(true)
        .log(LoggingLevel.INFO, "JDBC oracle : ${body}");
        .to("file://c:/test/test.txt")
    }
}

And you can use pax-jdbc (https://ops4j1.jira.com/wiki/spaces/PAXJDBC/overview) to create datasource (https://ops4j1.jira.com/wiki/display/PAXJDBC/Oracle+driver+adapter).

Once you got the table's record, you can create the file contents using the template engine (e.g. velocity (http://camel.apache.org/velocity.html)) or otherwise and then save the file by the component file (http://camel.apache.org/file2.html).

Answer 2

A simple way is to use Camel's SQL Component.

   <route id="foo">
       <from uri="sql:select * from foo"/>
   </route>

The returned data will be an List of Maps in the Exchange Body that you can then easily iterate through using Camel's Splitter EIP as the next step after the database select:

<split>
   <simple>${body}</simple>
</split>

There are a number of ways you could build your file within the <split>. Individual field values can be accessed using simple with ${body[FIELD_NAME_HERE]}

However keep in mind that the exchange is new for each split iteration, so you can't assemble your file contents in anything within the exchange, like a header or property. Instead, I'd probably use an aggregation strategy with the split, known as a Composed Message Processor. That way you can build your file in any format you want, row by row, within the old exchange body. Then after the </split> end, simply use Camel's File component to save the exchange body to a file.

Another option is to execute a Processor after your SQL statement, and the List of Maps in the exchange body will be available to your Java code to iterate through and build your file if you're more comfortable doing it that way. Camel's splitter also has overhead, so if performance is an issue and you're processing thousands of records, this may be the best option.