javapostgresqlapache-camelspring-jdbcistream-iterator

Apache Camel with SQL component for inserts with batch=true throws error with multiple tables


I am using Apache camel with sql component for performing sql operations with Postgresql. I have already tried successfully inserting multiple rows in a single table as a batch using batch=true option and providing the Iterator in the message body. To keep the example simple with Student as table name and having 2 columns name & age, below is the code snippet displaying the relevant part:

from("direct:batch_insert_single_table")
...
.process(ex -> {
    log.info("batch insert for single table");
    final var iterator = IntStream.range(0, 5000).boxed()
            .map(x -> {
                final var query = new HashMap<String, Object>();
                Integer counter = x.intValue();
                String name = "abc_" + counter;
                query.put("name", name);
                query.put("age", counter);               
                return query;
            }).iterator();
    ex.getMessage().setBody(iterator);
})
.to("sqlComponent:INSERT INTO student (name, age) VALUES (:#name, :#age);?batch=true")
...
;

This overall takes 10 seconds for 5000 records.

However, when I use the same approach for inserting as a batch on multiple different tables, I get an error:

Here is the code that is not working:

from("direct:batch_insert_multiple_tables")
...
.process(ex -> {
    log.info("batch insert for multiple tables");
    final var iterator = IntStream.range(0, 3).boxed()
            .map(x -> {
                final var query = new HashMap<String, Object>();
                Integer counter = x.intValue();
                String name = "abc_" + counter;
                query.put("table", "test" + counter);
                query.put("name", "name");
                query.put("age", counter);               
                return query;
            }).iterator();
    ex.getMessage().setBody(iterator);
})
.to("sqlComponent:INSERT INTO :#table (name,age) VALUES (:#name,:#age);?batch=true")
...
;

The tables test0, test1 & test2 are already existing. The exception thrown is:

Failed delivery for (MessageId: A0D98C12BAD769F-0000000000000000 on ExchangeId: A0D98C12BAD769F-0000000000000000). Exhausted after delivery attempt: 1 caught: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
Position: 13

Plz suggest if I am doing something wrong or my approach is simply not supported by Apache Camel.

NOTE: I am using the latest version apache camel & Postgre.

Regards, GSN


Solution

  • You cannot use a parameter for a table name, column name nor any other identifier in PostgreSQL. You either have to use a dynamically generated SQL statement (that is, a statement you construct in your Java code; take special care of SQL injection) or two SQL statements.