postgresql - 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)
  @

Answer 1

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