SpringBoot+Kotlin+Postgres and JSONB: "org.hibernate.MappingException: No Dialect mapping for JDBC type"

Question

I have been consulting a number of approaches/posts/stackoverflow questions in order to deal with the following error (full stack trace) when running a Kotlin/SpringBoot application:

2020-04-22 18:33:56.823 ERROR 46345 --- [  restartedMain] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 2118910070
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1803)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1108)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
    at app.ApplicationKt.main(Application.kt:13)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 2118910070
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:403)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1862)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1799)
    ... 21 common frames omitted
Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2118910070
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:71)
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:103)
    at org.hibernate.dialect.Dialect.getTypeName(Dialect.java:369)
    at org.hibernate.mapping.Column.getSqlType(Column.java:238)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateColumnType(AbstractSchemaValidator.java:156)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateTable(AbstractSchemaValidator.java:143)
    at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:42)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:89)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:68)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:192)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:320)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1249)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391)
    ... 25 common frames omitted

The issue lies in mapping the PostgreSQL’s JSONB data type with Hibernate.

The 2 approaches I have extensively tried and debugged are the following:

1. Implementing a Custom Hibernate Mapping and creating a custom UserType for JSONB. References: here, here, here and here
2. Use Hibernate types. References are here, here and here

I have attempted profusely at both, but with no luck whatsoever and I am keen to understand where I am going wrong and what I have been missing.

Approach 1

My Entity:

@Entity
@TypeDef(name = "JsonUserType", typeClass = JsonUserType::class)
@Table(name = "entity")
data class MyEntity(
  @Column(nullable = false)
  val id: UUID,
  @Column(nullable = false)
  @Enumerated(value = EnumType.STRING)
  @Column(nullable = false)
  val type: Type,
  @Type(type = "JsonUserType")
  @Column(columnDefinition = "jsonb")
  @Basic(fetch = FetchType.LAZY)
  var event_data: Event
) : SomeEntity<UUID>(), SomeOtherStuff {
  override fun getName(): String {
    return id
  }
}
 
 
enum class Type(val value: String) {
  TYPE1("Type1"),
  TYPE2("Type2")
}

My PoJO:

data class Event(
  val someContent: String,
  val someBoolean: Boolean
) : Serializable { //equals, hashcode etc are omitted }

My Custom Hibernate dialect:

class CustomPostgreSQLDialect : PostgreSQL95Dialect {
  constructor() : super() {
    this.registerColumnType(Types.JAVA_OBJECT, "jsonb")
  }
}

My custom type (abstract class)

abstract class JsonDataUserType : UserType {

  override fun sqlTypes(): IntArray? {
    return intArrayOf(Types.JAVA_OBJECT)
  }

  override fun equals(value1: Any?, value2: Any?): Boolean {
    return value1 == value2
  }

  override fun hashCode(value1: Any?): Int {
    return value1!!.hashCode()
  }

  override fun assemble(value1: Serializable?, value2: Any?): Any {
    return deepCopy(value1)
  }

  override fun disassemble(value1: Any?): Serializable {
    return deepCopy(value1) as Serializable
  }

  override fun deepCopy(p0: Any?): Any {
    return try {
      val bos = ByteArrayOutputStream()
      val oos = ObjectOutputStream(bos)
      oos.writeObject(p0)
      oos.flush()
      oos.close()
      bos.close()
      val bais = ByteArrayInputStream(bos.toByteArray())
      ObjectInputStream(bais).readObject()
    } catch (ex: ClassNotFoundException) {
      throw HibernateException(ex)
    } catch (ex: IOException) {
      throw HibernateException(ex)
    }
  }

  override fun replace(p0: Any?, p1: Any?, p2: Any?): Any {
    return deepCopy(p0)
  }

  override fun nullSafeSet(p0: PreparedStatement?, p1: Any?, p2: Int, p3: SharedSessionContractImplementor?) {
    if (p1 == null) {
      p0?.setNull(p2, Types.OTHER)
      return
    }
    try {
      val mapper = ObjectMapper()
      val w = StringWriter()
      mapper.writeValue(w, p1)
      w.flush()
      p0?.setObject(p2, w.toString(), Types.OTHER)
    } catch (ex: java.lang.Exception) {
      throw RuntimeException("Failed to convert Jsonb to String: " + ex.message, ex)
    }
  }
  override fun nullSafeGet(p0: ResultSet?, p1: Array<out String>?, p2: SharedSessionContractImplementor?, p3: Any?): Any {
    val cellContent = p0?.getString(p1?.get(0))
    return try {
      val mapper = ObjectMapper()
      mapper.readValue(cellContent?.toByteArray(charset("UTF-8")), returnedClass())
    } catch (ex: Exception) {
      throw RuntimeException("Failed to convert String to Jsonb: " + ex.message, ex)
    }
  }

  override fun isMutable(): Boolean {
    return true
  }

}

Such class was taken from this Stackoverflow question

My concrete class:

class JsonType : JsonDataUserType() {
    override fun returnedClass(): Class<Event> {
      return Event::class.java
    }
}

My application.yml jpa hibernate properties

jpa.properties.database.database-platform: org.hibernate.dialect.PostgreSQL95Dialect
jpa.properties.hibernate.dialect: org.myapp.util.CustomPostgreSQLDialect

Approach 2

Hibernate properties are exactly the same as well as the PoJo class, no custom mapper is included.

Entity

@Entity
@TypeDef(
  name = "jsonb",
  typeClass = JsonBinaryType::class
)
@Table(name = "entity")
data class MyEntity(
  @Column(nullable = false)
  val id: UUID,
  @Column(nullable = false)
  @Enumerated(value = EnumType.STRING)
  @Column(nullable = false)
  val type: Type,
  @Type(type = "jsonb")
  @Column(columnDefinition = "jsonb")
  @Basic(fetch = FetchType.LAZY)
  var event_data: Event
) : SomeEntity<UUID>(), SomeOtherStuff {
  override fun getName(): String {
    return id
  }
}
  
  
enum class Type(val value: String) {
  TYPE1("Type1"),
  TYPE2("Type2")
}

Custom Dialect (using hibernate types):

class CustomPostgreSQLDialect : PostgreSQL95Dialect {
  constructor() : super() {
    this.registerHibernateType(Types.OTHER, JsonNodeBinaryType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonStringType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonBinaryType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonNodeBinaryType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonNodeStringType::class.java.name)
  }
}

Please note that I also tried using only:

this.registerHibernateType(Types.OTHER, "jsonb")

as well as having all this in my entity or the base entity it extended from (no change for that matter):

@TypeDefs({
    @TypeDef(name = "string-array", typeClass = StringArrayType.class),
    @TypeDef(name = "int-array", typeClass = IntArrayType.class),
    @TypeDef(name = "json", typeClass = JsonStringType.class),
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class),
    @TypeDef(name = "jsonb-node", typeClass = JsonNodeBinaryType.class),
    @TypeDef(name = "json-node", typeClass = JsonNodeStringType.class),
})

Is there anything evidently wrong I am doing in both approaches? I cannot get it working and, not sure if in any way relevant, the numerical value after No Dialect mapping for JDBC type: is always different. I am adding this as I have seen some ids being relevant for certain categories of errors.

Can you help?

Thank you

EDIT: I wanted to provide more info regarding jpa, postgres and hibernate versions. I am currently working with the following:

1. postgres:10-alpine

2. PostgreSQL JDBC Driver JDBC 4.2 » 42.2.8

3. org.springframework.boot:spring-boot-starter-data-jpa:2.2.1.RELEASE

4. org.hibernate:hibernate-core:5.4.8.Final

   Is there any particular versioning issues among them?

EDIT 2 I have been trying to successfully use hibernate-types (Approach 2 as explained above). I have made the following change according to the Postgres version (10):

class CustomPostgreSQLDialect : PostgreSQL10Dialect {
  constructor() : super() {
    this.registerHibernateType(Types.OTHER, StringArrayType::class.java.name)
    this.registerHibernateType(Types.OTHER, IntArrayType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonStringType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonBinaryType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonNodeBinaryType::class.java.name)
    this.registerHibernateType(Types.OTHER, JsonNodeStringType::class.java.name)
  }
}

Then in my entity I have

@TypeDefs({
        @TypeDef(name = "string-array", typeClass = StringArrayType.class),
        @TypeDef(name = "int-array", typeClass = IntArrayType.class),
        @TypeDef(name = "json", typeClass = JsonStringType.class),
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})

and

 @Type(type = "jsonb")
 @Column(columnDefinition = "jsonb")
 @Basic(fetch = FetchType.LAZY)
 var event_data: Event

I have then debugged the get method in TypeNames where the error is coming from:

public String get(final int typeCode) throws MappingException {
        final Integer integer = Integer.valueOf( typeCode );
        final String result = defaults.get( integer );
        if ( result == null ) {
            throw new MappingException( "No Dialect mapping for JDBC type: " + typeCode );
        }
        return result;
    }

and this is what I am getting:

defaults = {HashMap@12093}  size = 27
     {Integer@12124} -1 -> "text"
     {Integer@12126} 1 -> "char(1)"
     {Integer@12128} -2 -> "bytea"
     {Integer@12130} 2 -> "numeric($p, $s)"
     {Integer@12132} -3 -> "bytea"
     {Integer@12133} -4 -> "bytea"
     {Integer@12134} 4 -> "int4"
     {Integer@12136} -5 -> "int8"
     {Integer@12138} -6 -> "int2"
     {Integer@12140} 5 -> "int2"
     {Integer@12141} -7 -> "bool"
     {Integer@12143} 6 -> "float4"
     {Integer@12145} 7 -> "real"
     {Integer@12147} 8 -> "float8"
     {Integer@12149} -9 -> "nvarchar($l)"
     {Integer@12151} 12 -> "varchar($l)"
     {Integer@12153} -15 -> "nchar($l)"
     {Integer@12155} -16 -> "nvarchar($l)"
     {Integer@12156} 16 -> "boolean"
     {Integer@12158} 2000 -> "json"
     {Integer@12160} 2004 -> "oid"
     {Integer@12162} 2005 -> "text"
     {Integer@12163} 1111 -> "uuid"
     {Integer@12165} 91 -> "date"
     {Integer@12167} 2011 -> "nclob"
     {Integer@12169} 92 -> "time"
     {Integer@12171} 93 -> "timestamp"

No jsonb can be found and when I debug my custom dialect, I am getting the following:

{Integer@10846} 1111 -> "com.vladmihalcea.hibernate.type.json.JsonStringType"
 key = {Integer@10846} 1111
 value = "com.vladmihalcea.hibernate.type.json.JsonStringType"

Why is that? Why am I not getting the jsonb type?

Answer 1

I propose my solution in a pull-request

The idea is to change Entity to:

import com.example.demo.pojo.SamplePojo
import com.vladmihalcea.hibernate.type.json.JsonBinaryType
import com.vladmihalcea.hibernate.type.json.JsonStringType
import org.hibernate.annotations.Type
import org.hibernate.annotations.TypeDef
import org.hibernate.annotations.TypeDefs
import javax.persistence.*

@Entity
@Table(name = "tests")
@TypeDefs(
        TypeDef(name = "json", typeClass = JsonStringType::class),
        TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)
)
data class SampleEntity (
    @Id @GeneratedValue
    val id: Long?,
    val name: String?,

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    var data: Map<String, Any>?
) {

    /**
     * Dependently on use-case this can be done differently:
     * https://stackoverflow.com/questions/37873995/how-to-create-empty-constructor-for-data-class-in-kotlin-android
     */
    constructor(): this(null, null, null)
}

1. Each entity should either have a default constructor or have defaults for all its parameters
2. Instead of saving POJO, save as Map<String, Any> type

Since we have a full-control what will be in POJO in business logic the only missing piece will be to convert POJO to Map and Map to POJO

SamplePojo implementation

data class SamplePojo(
        val payload: String,
        val flag: Boolean
)  {
    constructor(map: Map<String, Any>) : this(map["payload"] as String, map["flag"] as Boolean)

    fun toMap() : Map<String, Any> {
        return mapOf("payload" to payload, "flag" to flag)
    }
}

This is rather a workaround but it allows us to work with any depth-level structures.

P.S. I noticed that you use Serializer and redefined equals, toString, hashCode. You don't need this if using data class.

UPDATE:

If you need a more flexible structure than Map<String, Any>, you can use JsonNode. Code example

Entity:

import com.fasterxml.jackson.databind.JsonNode
import com.vladmihalcea.hibernate.type.json.JsonBinaryType
import com.vladmihalcea.hibernate.type.json.JsonStringType
import org.hibernate.annotations.Type
import org.hibernate.annotations.TypeDef
import org.hibernate.annotations.TypeDefs
import javax.persistence.*

@Entity
@Table(name = "tests")
@TypeDefs(
        TypeDef(name = "json", typeClass = JsonStringType::class),
        TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)
)
data class SampleJsonNodeEntity (
        @Id @GeneratedValue
        val id: Long?,
        val name: String?,

        @Type(type = "jsonb")
        @Column(columnDefinition = "jsonb")
        var data: JsonNode?
) {

    /**
     * Dependently on use-case this can be done differently:
     * https://stackoverflow.com/questions/37873995/how-to-create-empty-constructor-for-data-class-in-kotlin-android
     */
    constructor(): this(null, null, null)
}

Change Entity in Repository:

import com.example.demo.entity.SampleJsonNodeEntity
import org.springframework.data.jpa.repository.JpaRepository

interface SampleJsonNodeRepository: JpaRepository<SampleJsonNodeEntity, Long> {
}

Tests for both approaches:

import com.example.demo.DbTestInitializer
import com.example.demo.entity.SampleJsonNodeEntity
import com.example.demo.entity.SampleMapEntity
import com.example.demo.pojo.SamplePojo
import com.fasterxml.jackson.module.kotlin.jacksonObjectMapper
import junit.framework.Assert.assertEquals
import junit.framework.Assert.assertNotNull
import org.junit.Before
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.ContextConfiguration
import org.springframework.test.context.junit4.SpringRunner


@RunWith(SpringRunner::class)
@SpringBootTest
@ContextConfiguration(initializers = [DbTestInitializer::class])
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class SampleRepositoryTest {

    @Autowired
    lateinit var sampleMapRepository: SampleMapRepository

    @Autowired
    lateinit var sampleJsonNodeRepository: SampleJsonNodeRepository

    lateinit var dto: SamplePojo
    lateinit var mapEntity: SampleMapEntity
    lateinit var jsonNodeEntity: SampleJsonNodeEntity

    @Before
    fun setUp() {
        dto = SamplePojo("Test", true)
        mapEntity = SampleMapEntity(null,
                "POJO1",
                dto.toMap()
        )

        jsonNodeEntity = SampleJsonNodeEntity(null,
            "POJO2",
                jacksonObjectMapper().valueToTree(dto)
        )
    }

    @Test
    fun createMapPojo() {
        val id = sampleMapRepository.save(mapEntity).id!!
        assertNotNull(sampleMapRepository.getOne(id))
        assertEquals(sampleMapRepository.getOne(id).data?.let { SamplePojo(it) }, dto)
    }

    @Test
    fun createJsonNodePojo() {
        val id = sampleJsonNodeRepository.save(jsonNodeEntity).id!!
        assertNotNull(sampleJsonNodeRepository.getOne(id))
        assertEquals(jacksonObjectMapper().treeToValue(sampleJsonNodeRepository.getOne(id).data, SamplePojo::class.java), dto)
    }

}

Answer 2

Extending with an example, sorry I know I am a bit late

in your pom.xml:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.4.3</version>
</dependency>

Then I have my entity named Day:

import com.vladmihalcea.hibernate.type.json.JsonBinaryType;

@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
@Data
@Entity
public class Day {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "DayId")
   private Integer id;
   private Integer day;
   private Integer month;
   private Integer year;

   @Type(type = "jsonb")
   @Column(columnDefinition = "jsonb")
   private List<Activity> activities;

   @Type(type = "jsonb")
   @Column(columnDefinition = "jsonb")
   private Notification notification;

}

Activity and Notification JSONB's class:

@Data
@JsonIgnoreProperties(ignoreUnknown = true)
public class Activity implements Serializable {

   private String name;
   private String emoji;
   private Integer durationInSeconds;
   private Boolean highPriority;

   public Activity (){}
}

@Data
@JsonIgnoreProperties(ignoreUnknown = true)
public class Notification implements Serializable {

    private String email;
    private String mobile;

    public Notification (){}
}

Our repository:

@Repository
public interface DayRepository extends CrudRepository<Day, Integer> {

}

Our service:

public interface DayService{
    Day saveArbitraryDay();
}

@Service
@Transactional
public DayServiceImpl implements DayService{

    private DayRepository repository;

    public DayServiceImpl(DayRepository repository){
         this.repository = repository;
    }

    @Override
    public Day saveArbitraryDay(){
         Day day = new Day();
         day.setDay(16);
         day.setMonth(04);
         day.setYear(1991);

         //Set the jsonb objects
         //You can use custom constructors whatever
         Notification notification = new Notification();
         notification.setEmail("contoso@hotmail.com");
         day.setNotification(notification);

         //Now putting activities
         List<Activity> activities = new ArrayList<>();

         Activity actOne = new Activity();
         actOne.setName("Breakfast");
         actOne.setEmoji("");
         actOne.setDurationInSeconds(9000);
         actOne.setHighPriority(true);

         Activity actTwo = new Activity();
         actTwo.setName("Shopping");
         actTwo.setEmoji("");

         activities.add(actOne);
         activities.add(actTwo);

         day.setActivities(activities)

         return repository.save(day);
    }
}

And that is pretty much I think, in case you wanna digging more into types with hibernate check out this link