Cannot use setArray JDBC (integer) with H2 Database

Question

I want to put an array of integers in my jdbc query with H2 database.

Integer[] list = new Integer[]{1,2,3};
            String query = "SELECT EXAMPLE FROM DATA WHERE EXAMPLE IN (?)";
            PreparedStatement ps = GestionBDD.getConexionBD().prepareStatement(query);
            Array array = GestionBDD.getConexionBD().createArrayOf("int", list);

            ps.setArray(1, array);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                // DO THINGS 
            }

But doest not work, I am getting this exception : org.h2.jdbc.JdbcSQLException: Data conversion error converting "(1,2,3)"; SQL statement:

I am using H2 database. Can you help me please?

Check, it may help you. https://stackoverflow.com/questions/37693849/h2-in-memory-database-error-data-conversion-error-converting-when-using-uuid-a?rq=1 — Sambit, May 14 '19 at 17:17
Is that way: CREATE TABLE IF NOT EXISTS DATA (EXAMPLE int auto_increment) — Traif, May 14 '19 at 17:24
can you try `GestionBDD.getConexionBD().createArrayOf("INTEGER", list);` — Jayesh, May 14 '19 at 17:33
I tried too, but doesn't work. It seems that is not the error — Traif, May 14 '19 at 17:41
See [this answer](https://stackoverflow.com/a/3724272/2144390) for how to do it with H2. — Gord Thompson, May 14 '19 at 18:39
See also https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — Mark Rotteveel, May 29 '19 at 13:44

LHA · Accepted Answer · 2021-10-01T16:12:23.893

Using my JDBC utilities to work with JDBC IN array parameters easily:

1. If Maven, add the below dependency into your pom.xml

    <dependency>
      <groupId>com.appslandia</groupId>
      <artifactId>appslandia-common</artifactId>
      <version>7.9</version>
    </dependency>

2. If no maven, download this jar file

3. Fix your code ( Works all DBMS guaranteed )

static final Sql EXAMPLE_SQL 
          = new Sql("SELECT EXAMPLE FROM DATA WHERE EXAMPLE IN :example_array");

StatementImpl stat = new StatementImpl(GestionBDD.getConexionBD(), EXAMPLE_SQL);

stat.setIntArray("example_array", new int[] {1, 2, 3} );

ResultSet rs = stat.executeQuery();

while (rs.next()) {
    // DO THINGS 
}

rs.close(); stat.close();

4. Named parameters / Null parameters

final Sql sampleSql 
       = new Sql("SELECT * FROM Table WHERE a=:int_a AND b=:str_b)

StatementImpl stat = new StatementImpl(conn, sampleSql);

stat.setInt("int_a", int_value);
stat.setInt2("int_a", null_value);
stat.setString("str_b", str_value);
// ...

5. Some Notes

Parameter name supported. No more ? in your query
Parameter in Sql need to start with : (Like JPA parameters)
IN Array parameter in Sql must be in this syntax IN :param_name

Cannot use setArray JDBC (integer) with H2 Database

1 Answers1

Linked