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?

You can analyse the **header** `CamelSqlRowCount` (Integer) and see if the count is zero. See https://camel.apache.org/components/3.17.x/sql-component.html#_message_headers — TacheDeChoco, May 31 '22 at 14:39
Thanks @TacheDeChoco, that makes sense, I'll try that. I think that might as well work as the answer to question, would you post your comment as answer so I can mark it as accepted answer? — Dandev, Jun 01 '22 at 01:04
You have a typo: `${body)}` has extra `)`. The correct syntax is `${body}`. — user272735, Jun 01 '22 at 05:59

user272735 · Answer 1 · 2022-06-01T06:19:09.187

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.

Thanks @user272735, in fact first I tried without outputType=SelectOne so it returned a List. But then upon splitting list, behaviour was similar. As there's really no elements in list it would fail anyway when applying same sort of conditions. Should apply something else than split to evaluate for an empty list? — Dandev, Jun 01 '22 at 01:02
@Dandev Using `CamelSqlRowCount` header as pointed out by @TacheDeChoco might be a better approach in your case. — user272735, Jun 01 '22 at 06:24

score 0 · Accepted Answer · answered Jun 01 '22 at 07:28

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.

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

2 Answers2