jdbi
Postgres: map bytea[] to byte[][]
jdbi Version
<dependency>
<groupId>org.jdbi</groupId>
<artifactId>jdbi3-postgres</artifactId>
<version>3.12.0</version>
</dependency>
<dependency>
<groupId>org.jdbi</groupId>
<artifactId>jdbi3-core</artifactId>
<version>3.12.0</version>
</dependency>
Driver Version? --> 42.2.12
Java Version? --> openjdk version "11.0.7" 2020-04-14
OS Version? --> Debian 10.4
PostgreSQL Version? --> PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
To Reproduce Steps to reproduce the behaviour: --> Run TestSql.java (JUnit)
Expected behaviour
(byte[][]) rs.getArray("children_bytesarr").getArray() should work, just like (String[]) rs.getArray("children_names").getArray(), but it throws error instead.
The complete ddl, source code is provided below.
Logs The error:
java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class [[B ([Ljava.lang.Object; and [[B are in module java.base of loader 'bootstrap')
at test.TestSql.lambda$1(TestSql.java:116)
at org.jdbi.v3.core.result.ResultSetResultIterator.next(ResultSetResultIterator.java:83)
at org.jdbi.v3.core.result.ResultIterable.one(ResultIterable.java:138)
at test.TestSql.lambda$0(TestSql.java:118)
at org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:341)
at test.TestSql.testArrayArg_onByteA(TestSql.java:84)
Basically i just want to get parent and children aggregated into arrays for each names and bytea fields in a single query. This works in psql, but i was not successful in getting the bytea[] value from the ResultSet.
The models:
-- for tests
CREATE TABLE parent (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE child (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
parent_id int NOT NULL REFERENCES parent(id),
name VARCHAR(512) NOT NULL,
raw BYTEA NOT NULL
);
And the query:
private static final String FIND_PARENT =
"SELECT r.id AS id, r.name, f.children_names, f.children_bytesarr "
+ "FROM parent r "
+ " LEFT JOIN ("
+ " SELECT f.parent_id AS id, "
+ " ARRAY_AGG(f.name) as children_names, ARRAY_AGG(f.raw) as children_bytesarr "
+ " FROM child f "
+ " GROUP BY f.parent_id "
+ " HAVING f.parent_id = :parent_id"
+ " ) f USING (id) "
+ "WHERE r.id = :parent_id";
And here's the related code with resultSet (rs) which triggers the error, i leave the complete code below:
return handle.createQuery(FIND_PARENT)
.bind("parent_id", parentId)
.map((rs, ctx) -> Parent.builder()
.withId(rs.getInt("id"))
.withName(rs.getString("name"))
.withChildrenNames((String[]) rs.getArray("children_names").getArray())
// this triggers error
.withChildrenBytesArr((byte[][]) rs.getArray("children_bytesarr").getArray())
.build())
.one();
And here's the complete JUnit TestSql.java:
package test;
import static org.junit.Assert.assertEquals;
import java.util.Collections;
import java.util.List;
import javax.sql.DataSource;
import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.postgres.PostgresPlugin;
import org.junit.Test;
import com.google.common.collect.Lists;
public class TestSql {
private static class Parent {
private int id;
private String name;
private List<String> childrenNames;
private List<byte[]> childrenBytesList;
private Parent(Builder builder) {
id = builder.id;
name = builder.name;
childrenNames = builder.childrenNames;
childrenBytesList = builder.childrenBytesList;
}
public static Builder builder() {
return new Builder();
}
public static final class Builder {
private int id;
private String name;
private List<String> childrenNames = Collections.emptyList();
private List<byte[]> childrenBytesList = Collections.emptyList();
private Builder() {
}
public Builder withId(int id) {
this.id = id;
return this;
}
public Builder withName(String name) {
this.name = name;
return this;
}
public Builder withChildrenNames(String[] childrenNames) {
this.childrenNames = Lists.newArrayList(childrenNames);
return this;
}
public Builder withChildrenBytesArr(byte[][] childrenBytesArr) {
childrenBytesList = Lists.newArrayList(childrenBytesArr);
return this;
}
public Parent build() {
return new Parent(this);
}
}
}
private static final String FIND_PARENT =
"SELECT r.id AS id, r.name, f.children_names, f.children_bytesarr "
+ "FROM parent r "
+ " LEFT JOIN ("
+ " SELECT f.parent_id AS id, "
+ " ARRAY_AGG(f.name) as children_names, ARRAY_AGG(f.raw) as children_bytesarr "
+ " FROM child f "
+ " GROUP BY f.parent_id "
+ " HAVING f.parent_id = :parent_id"
+ " ) f USING (id) "
+ "WHERE r.id = :parent_id";
@Test
public void testArrayArg_onByteA() {
Jdbi jdbi = Jdbi.create(getDataSource())
.installPlugin(new PostgresPlugin());
Parent parent = jdbi.withHandle(handle -> {
handle.createBatch()
.add("DELETE FROM child")
.add("DELETE FROM parent")
.add("ALTER TABLE parent ALTER COLUMN id RESTART WITH 1")
.add("ALTER TABLE child ALTER COLUMN id RESTART WITH 1")
.execute();
int parentId = handle.createUpdate("INSERT INTO parent (name) VALUES (:name)")
.bind("name", "myparent")
.executeAndReturnGeneratedKeys("id")
.mapTo(Integer.class)
.one();
handle.createUpdate("INSERT INTO child (parent_id, name, raw) VALUES (:parent_id, :name, :raw)")
.bind("parent_id", parentId)
.bind("name", "my 1st child")
.bind("raw", new byte[] { 12, 23 })
.execute();
handle.createUpdate("INSERT INTO child (parent_id, name, raw) VALUES (:parent_id, :name, :raw)")
.bind("parent_id", parentId)
.bind("name", "my 2nd child")
.bind("raw", new byte[] { 88, 99 })
.execute();
return handle.createQuery(FIND_PARENT)
.bind("parent_id", parentId)
.map((rs, ctx) -> Parent.builder()
.withId(rs.getInt("id"))
.withName(rs.getString("name"))
.withChildrenNames((String[]) rs.getArray("children_names").getArray())
.withChildrenBytesArr((byte[][]) rs.getArray("children_bytesarr").getArray())
.build())
.one();
});
assertEquals("myparent", parent.name);
assertEquals(Lists.newArrayList("my 1st child", "my 2nd child"), parent.childrenNames);
assertEquals(Lists.newArrayList(new byte[] { 12, 23 }, new byte[] { 88, 99 }), parent.childrenBytesList);
}
private DataSource getDataSource() {
return ...
}
}
The test will pass if .withChildrenBytesArr((byte[][]) rs.getArray("children_bytesarr").getArray()) is commented.
Please share your thoughts.
Thank you for reporting this. I expect there's no automatic mapping from nested java.sql.Arrays to byte[][] -- we do one dimensional conversion but probably not multi-dimensional.
We can make this possible, but the code likely will never work as is: rs.getArray is implemented by the driver and clearly is not returning something that may be cast to byte[][].
Hello .. I also reported this to pgjdbc issues in here.
It seems that they have added to support but not yet released based on the comment here.
I'm trying to confirm this with them. Thank you for your support.
Yes, let's see what the driver support looks like. Maybe with their fix it just works out of the box with jdbi :crossed_fingers: