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
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.