1. Introduction
Hello friend! In this post, I would like to show you how to solve a pretty common issue when mapping Postgres jsonb to Jackson JsonNode in Spring Data JPA.
If you are not a regular reader of my blog, then I’m pretty sure that you wanted to map a PostgreSQL jsonb field in your Spring Boot with Spring Data JPA application to Jsonnode and you saw something like that:
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory’ defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`
If that’s the case, then you’ve come to the right place. 😁
2. Quick Setup
Before we learn how to solve the issue with Postgres jsonb mapping to JsonNode, let’s take a minute to reproduce the error.
Note: If you would like to cut straight to the point and see how to implement a mapper, then you can skip this part and navigate directly to chapter 3.
2.1. PostgreSQL Table With jsonb column
Firstly, we need to have a PostgreSQL table with jsonb column:
As we can see, it’s just a dummy table called some_table, which contains 4 example columns.
And to be on the same it page, I created it and populated with the following SQL script:
create table some_table( id serial not null primary key, string_column text not null, integer_column smallint not null, jsonb_column jsonb not null ); insert into some_table(string_column, integer_column, jsonb_column) values ('value-1', 1, '{ "some_field": "some_value"}'), ('value-2', 2, '{ "another_field": "some_value_2"}');
Make a real progress thanks to practical examples, exercises, and quizzes.
- 64 written lessons
- 62 quizzes with a total of 269 questions
- Dedicated Facebook Support Group
- 30-DAYS Refund Guarantee
2.2. Spring Boot and Dependencies
Nextly, let’s create an example Spring Boot project. As always, we can do that with the Spring Initializr page.
Please configure it according to your needs, but do not forget to add the PostgreSQL Driver and Spring Data JPA dependencies. Additionally, we will need to enrich the project with Jackson Databind:
implementation("org.springframework.boot:spring-boot-starter-data-jpa") implementation("com.fasterxml.jackson.core:jackson-databind:2.14.1") runtimeOnly("org.postgresql:postgresql")
2.3. application.yaml
With that being done, let’s specify the connection details:
spring: datasource: url: "jdbc:postgresql://localhost:5432/postgres" username: postgres password: postgres
2.4. Implement Entity
As the next step, let’s open up the project and add the SomeEntity data class:
@Entity @Table(name = "some_table") data class SomeEntity( @Id val id: Long? = null, val stringColumn: String, val integerColumn: Int, val jsonbColumn: JsonNode )
As we can see, we use the @Entity annotation to mark the class as the entity and @Table to explicitly provide Spring with the table name. Moreover, the necessary @Id annotation specifies the primary key of our entity and the jsonbColumn will contain the value of the Postgres jsonb column mapped to JsonNode.
2.5. Create JPA repository
With that being done, let’s add the SomeEntityRepository interface:
interface SomeEntityRepository : CrudRepository<SomeEntity, Long>
This time, the only thing we have to do is to extend the CrudRepository interface, which comes with a basic CRUD operations.
2.6. Create JPA repository
Lastly, let’s add the logic, which will be run when the application starts:
@Component class MyRunner( private val repository: SomeEntityRepository ) : CommandLineRunner { override fun run(vararg args: String?) { repository.findAll() .forEach(::println) } }
As we can see, a repository instance will be injected into this simple component. Moreover, we implement the run method of CommandLineRunner, which instructs Spring Boot to run after the app starts (if you would like to learn more about it, then check out my article about the Spring Boot console app).
Finally, we want to print out all entities, nevertheless, such a stack trace appears to our eyes:
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode` at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1751) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:599) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:521) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1130) ~[spring-context-6.0.2.jar:6.0.2] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:905) ~[spring-context-6.0.2.jar:6.0.2] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:584) ~[spring-context-6.0.2.jar:6.0.2] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:730) ~[spring-boot-3.0.0.jar:3.0.0] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:432) ~[spring-boot-3.0.0.jar:3.0.0] at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-3.0.0.jar:3.0.0] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1302) ~[spring-boot-3.0.0.jar:3.0.0] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1291) ~[spring-boot-3.0.0.jar:3.0.0] at com.codersee.jsonbtojsonnode.JsonbToJsonNodeApplicationKt.main(JsonbToJsonNodeApplication.kt:14) ~[main/:na] Caused by: org.hibernate.type.descriptor.java.spi.JdbcTypeRecommendationException: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode` at org.hibernate.type.descriptor.java.spi.UnknownBasicJavaType.getRecommendedJdbcType(UnknownBasicJavaType.java:37) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.boot.model.process.internal.InferredBasicValueResolver.from(InferredBasicValueResolver.java:222) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.mapping.BasicValue.buildResolution(BasicValue.java:507) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.mapping.BasicValue.resolve(BasicValue.java:315) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.mapping.BasicValue.resolve(BasicValue.java:305) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.lambda$processValueResolvers$4(InFlightMetadataCollectorImpl.java:1767) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at java.base/java.util.ArrayList.removeIf(ArrayList.java:1682) ~[na:na] at java.base/java.util.ArrayList.removeIf(ArrayList.java:1660) ~[na:na] at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processValueResolvers(InFlightMetadataCollectorImpl.java:1766) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processSecondPasses(InFlightMetadataCollectorImpl.java:1752) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:300) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1350) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1421) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final] at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:66) ~[spring-orm-6.0.2.jar:6.0.2] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:376) ~[spring-orm-6.0.2.jar:6.0.2] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-6.0.2.jar:6.0.2] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-6.0.2.jar:6.0.2] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:352) ~[spring-orm-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1797) ~[spring-beans-6.0.2.jar:6.0.2] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1747) ~[spring-beans-6.0.2.jar:6.0.2] ... 15 common frames omitted
3. Map Postgres jsonb to JsonNode
So at this point, we can see that Spring “Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`“.
One of the possible solutions is to implement a custom AttributeConverter and mark it with @Converter annotation:
@Converter(autoApply = true) class MyConverter : AttributeConverter<JsonNode, String> { private val mapper = ObjectMapper() override fun convertToDatabaseColumn(node: JsonNode): String { return node.asText() } override fun convertToEntityAttribute(data: String): JsonNode { return mapper.readTree(data) } }
As we can see, the AttributeConverter interface comes with two methods: convertToDatabaseColumn and convertToEntityAttribute, which will be invoked when mapping between the entity and the database.
Additionally, we have set the autoApply to true (the default is false) to inform the persistence provider that it should use this converter to map between Postgres jsonb and JsonNode. Without it, we would have to explicitly annotate the desired field:
@Convert(converter = MyConverter::class) val jsonbColumn: JsonNode
Of course, the choice is up to you and may vary depending on your project, but most of the time you would like to choose the first solution.
Finally, if we run the application, we should see that everything is working, as expected and logs contain the following lines:
SomeEntity(id=1, stringColumn=value-1, integerColumn=1, jsonbColumn={"some_field":"some_value"}) SomeEntity(id=2, stringColumn=value-2, integerColumn=2, jsonbColumn={"another_field":"some_value_2"})
4. Summary
And that’s all for this short, hands-on article on how to map Postgres jsonb to JsonNode. As always, you can find the source code in this GitHub repository.
As always, happy to hear your thoughts on how would you solve this issue.