Clickhouse Batch Execution not inserting new rows

Question

I have just installed a clickhouse server locally, with package clickhouse-server linux package. I created some java code to insert N rows in table and it works well via JDBC. However to improve, performance I am now implementing it using batch instead of separate INSERTS, but the code below is not working and the executeBatch function is returning an array with length 0.

For testing purposes i created the following code:

    public static void main(String[] args) throws PulsarClientException, SQLException {

       Connection con = null;
       String connectionString = "jdbc:clickhouse://localhost:8123/bank";

        try {
            con = DriverManager.getConnection(connectionString);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
     
        String query = "INSERT INTO bank.test (numero,palavra) VALUES (3,'girassol');";

        Statement stmt = con.createStatement();

        con.setAutoCommit(false);      
        try {
            stmt.addBatch(query);
            stmt.addBatch(query);
            stmt.executeBatch();

            con.commit()
        
        }catch (Exception e)
        {
            e.printStackTrace();
        }
        con.close();

    }

The stmt.executeBatch(); returns 0 elements and nothing is inserted onto the clickhouse server. I tested the same code on a Postgres DB just by replacing the connectionstring and it worked as expected. Is there any configuration that I am missing on clickhouse?

everything is wrong here. CH does not support transactions. It's not a batch insert in terms of Clickhouse. Clickhouse does not return a number of inserted rows (it always 0). — Denny Crane, Mar 10 '21 at 19:01
example for CH https://github.com/ClickHouse/clickhouse-jdbc/blob/master/src/test/java/ru/yandex/clickhouse/integration/BatchInsertsTest.java#L168 — Denny Crane, Mar 10 '21 at 19:01
Yup turns out all I had to change is use preparedstatements instead of statemtns just like in the documentation — João Ramiro, Mar 12 '21 at 12:27

score 0 · Answer 1 · answered Oct 20 '21 at 13:36

Try preprocessing to insert data:

// Setting Preprocessing Strings
String query = "INSERT INTO bank.test (numero,palavra) VALUES (?,?);";

then, insert each parameter, and add the parameters to the batch, and submit the parameters:

PreparedStatement stmt = con.preparedStatement(query);
// setting first parameter
stmt.setInt(1, 3);
// setting second parameter
stmt.setString(2, "girassol");
stmt.addBatch();
stmt.executeBatch();

con.commit();

Clickhouse Batch Execution not inserting new rows

1 Answers1