How to check an Apache Camel SQL component resultset (select) is empty?

Question

I'm running a Camel route that reads a flag record from database. If flag is NOT there then insert it and perform some additional stuff. I expect being able to check if resultset from query is empty or not but conditions I use on route (choice) seem to be ignored, so it's working like it's always finding something in the database even if I'm certain it's not (in fact logging ${body} shows empty.

I'm using spring XML DSL and this is the route:

     <from uri="file:/D:/LOCAL/?include=(?i).*.zip&amp;moveFailed=ErrorFiles&amp;move=D:/LOCAL/WRK/"/>  <!--Catch a zip file as trigger for route-->
     <to uri="sql:SELECT LOAD_DATE FROM IMPORT_CUSTOMER_CTRL WHERE LOAD_DATE = CURRENT_DATE?datasource=#customerDS&amp;routeEmptyResultSet=true&amp;outputType=SelectOne"/> <!-- Read a flag record from db -->
     <log message="Query result: ${body}" loggingLevel="INFO"/>
     <choice>
        <when> 
           <simple>${body)} == null</simple>  <!--IF RESULTSET IS EMPTY THEN DO SOMETHING. THIS CONDITION FAILS AND ALWAYS GOES BY OTHERWISE BRANCH-->**strong text**
           <log message="Do something" loggingLevel="INFO"/>

           <!--Insert flag record -->   
           <to uri="sql:INSERT INTO IMPORT_CUSTOMER_CTRL (LOAD_DATE) VALUES(CURRENT_DATE)?dataSource=#customerDS" />  
        </when>
        <otherwise>
           <log message="Flag record already exists, ignoring:${body}" loggingLevel="INFO"/>
        </otherwise>
     </choice>

For the when condition I've tried ${body)} == null and ${body)} == '' and even ${bodyAs(String)} == '' but yet choice behaves as it's always filled and goes by otherwise route. I know cause I always get the "Flag record already exists.." message in log.

What's the correct way to evaluate whether the resultset is empty?

Answer 1

would you post your comment as answer so I can mark it as accepted answer

As explained here above, a generic solution, working whatever the output type (I mean a list as well as a single tuple) is to analyse the various "meta-data" published as headers by Camel : https://camel.apache.org/components/3.17.x/sql-component.html#_message_headers

You should especially have a look at CamelSqlRowCount which, as its name indicates it, will give you information about the number of records returned by your SQL query.

Answer 2

According to the Sql component documentation:

For select operations, the result is an instance of List<Map<String, Object>> type, as returned by the JdbcTemplate.queryForList() method.

So the body is never null - you have to check the content of the returned List object to see what your result set contains.

Below you'll find a complete route that makes several checks for body:

from("direct:mainRoute")
.routeId("MainRoute")
    .process(e ->{
        List<String> list = new ArrayList<String>();
        list.add("foo");
        e.getMessage().setBody(list, List.class);
    })
    .log("MainRoute BEGINS: BODY: ${body}")
    .choice()
        .when(simple("${body} == null"))
            .log("body is null")
        .otherwise()
            .log("body is not null")
    .end()
    .choice()
        .when(simple("${body} is 'java.util.List'"))
            .log("body is a list")
        .otherwise()
            .log("body is not a list")
    .end()
    .choice()
        .when(simple("${body.isEmpty()}"))
            .log("list in body is empty")
        .otherwise()
            .log("list in body is not empty")
    .end()
    .log("MainRoute ENDS: BODY: ${body}")
.end()
;

When the route is run it prints either

MainRoute  INFO  MainRoute BEGINS: BODY: [foo]
MainRoute  INFO  body is not null
MainRoute  INFO  body is a list
MainRoute  INFO  list in body is not empty
MainRoute  INFO  MainRoute ENDS: BODY: [foo]

or

MainRoute  INFO  MainRoute BEGINS: BODY: []
MainRoute  INFO  body is not null
MainRoute  INFO  body is a list
MainRoute  INFO  list in body is empty
MainRoute  INFO  MainRoute ENDS: BODY: []

depending if the List has items or not.