java - Why can't I fetch bytea values from my postgres db?

Question

I have a DAO with these methods:

@SqlUpdate("INSERT INTO my_test (ba) VALUES (:ba)")
void insertBytea(@Bind("ba") byte[] ba);

@SqlQuery("SELECT ba from my_test fetch first 1 row only")
byte[] selectBytea();

When I execute the insert method:

    byte[] bytea = new byte[1];
    bytea[0] = 1;
    myDao.insertBytea(bytea);

the value ends up in the database.

So far so good.

But when I retrieve it:

    byte[] bytes = myDao.selectBytea();

.. this happens:

... Caused by: org.postgresql.util.PSQLException: Bad value for type byte : x01 at org.postgresql.jdbc.PgResultSet.getByte(PgResultSet.java:2135) at org.jdbi.v3.core.mapper.PrimitiveMapperFactory.lambda$primitiveMapper$0(PrimitiveMapperFactory.java:64) at org.jdbi.v3.core.mapper.SingleColumnMapper.lambda$new$0(SingleColumnMapper.java:41) at org.jdbi.v3.core.mapper.SingleColumnMapper.map(SingleColumnMapper.java:55) at org.jdbi.v3.core.result.ResultSetResultIterator.next(ResultSetResultIterator.java:83)

I'm not sure what is going on. But when I debug the code, it seems as if the postgres library is has transformed the value from byte array, to string, back to byte array?

...because the values [92, 120, 48, 49] corresponds to the string "x01" which seems to be one of the ways postgres expresses bytea values.

I am using jdbi3 libraries to access the db. I am depending on the artifact postgresql version 42.2.18.

question from:https://stackoverflow.com/questions/65831128/why-cant-i-fetch-bytea-values-from-my-postgres-db

Answer 1

JDBI internal mapper strategy detects byte[] return type as container type and expects the query to return array of byte values. But in your case it is rather a single value returned containing array of bytes. Solution is simple, just add org.jdbi.v3.sqlobject.SingleValue annotation to your method.

From @SingleValue javadoc

Indicate to SqlObject that a type that looks like a container should be treated as a single element.

Your particular example will look like the following:

@SingleValue
@SqlQuery("SELECT ba from my_test fetch first 1 row only")
byte[] selectBytea();