[Tiny] Spring Data JDBC: When a Map Pretends to Be a String
#spring#springboot#kotlin#postgres
Petr Filaretov

Petr Filaretov @pfilaretov42

About: Passionate software engineer who is always striving for excellence.

Joined:
Sep 1, 2022

[Tiny] Spring Data JDBC: When a Map Pretends to Be a String

Publish Date: May 20
0 0

When using Spring Data JDBC and PostgreSQL, we may need to store key-value pairs in a JSONB column.

Let's take an example from one of my previous posts and modify it so that the extras field does not have nested objects, but rather simple key-value pairs:

{
  "friend": {
    "fullName": "Anthony Edward Stark",
    "alias": "Iron Man",
    "extras": {
      "publisher": "Marvel Comics",
      "createdBy": "Stan Lee"
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

We could use the Map<String, String?>? type for the extras field in the entity...

@Table("friends")
class FriendsEntity(
    //...
    val extras: Map<String, String?>?,
) : Persistable<UUID> {
    //...
}
Enter fullscreen mode Exit fullscreen mode

...and the JSONB type for the database column:

create table friends
(
    -- ...
    extras JSONB
);
Enter fullscreen mode Exit fullscreen mode

Now, if we try to create an entity in the database, we get an error:

DEBUG o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO "friends" ("alias", "extras", "full_name", "id", "superpower") VALUES (?, ?, ?, ?, ?)]
TRACE o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [Iron Man], value class [java.lang.String], SQL type 12
TRACE o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [{"publisher":"Marvel Comics","createdBy":"Stan Lee"}], value class [org.postgresql.util.PGobject], SQL type 12
...
ERROR o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute InsertRoot{entity=dev.pfilaretov42.spring.data.jdbc.jsonb.entity.FriendsEntity@2f97ec4e, idValueSource=PROVIDED}] with root cause
org.postgresql.util.PSQLException: ERROR: column "extras" is of type jsonb but expression is of type character varying
Enter fullscreen mode Exit fullscreen mode

For some reason, StatementCreatorUtils treats a Map as a String:

value class [org.postgresql.util.PGobject], SQL type 12
Enter fullscreen mode Exit fullscreen mode

To fix this, we have to change the map value type from String? to Any?:

@Table("friends")
class FriendsEntity(
    //...
    val extras: Map<String, Any?>?,
) : Persistable<UUID> {
  //...
}
Enter fullscreen mode Exit fullscreen mode

Please let me know in the comments if you know how to fix this another way and use Map<String, String?>?.

The full source code is available on GitHub: pfilaretov42/spring-data-jdbc-jsonb.

Dream your code, code your dream.

Comments 0 total

    Add comment