Dealing with "No Dialect mapping for JDBC type" while working with JSON properties

[Geane Norm]

Hi,

I have been working with a Kotlin/SpringBoot application and I have ran across the following error:

"No Dialect mapping for JDBC type"

In simple terms, my app includes the following:

- Entity class where one of the field is of JSON type (more details to follow)
- Model entity
- an Interface called Event whose implementation is then in two classes (signifying different event types/tokens). This is where I think the issue is as I am trying to map a field to an interface, but more to follow in terms of details.

I have consulted a variety of sources with different approaches:

https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/
https://stackoverflow.com/questions/15974474/mapping-postgresql-json-column-to-hibernate-value-type
https://stackoverflow.com/questions/21630370/no-dialect-mapping-for-jdbc-type-2003
https://stackoverflow.com/questions/59967180/how-to-create-custom-jsonb-mapper-for-postgresql-and-hibernate-in-kotlin

I have ended up creating a custom jsonb mapper along these lines:

https://stackoverflow.com/questions/59967180/how-to-create-custom-jsonb-mapper-for-postgresql-and-hibernate-in-kotlin
https://github.com/thjanssen/HibernateJSONBSupport/tree/master/PostgresJSONB/src/main/java/org/thoughts/on/java/model

In a nutshell, the solution involves:

- Register custom PostgreSQL95Dialect which has jsonb type
- Implement Hibernate's UserTypeinterface with custom mapping
- Annotate Entity with @TypeDef of custom implementation

Here is my actual implementation:

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 = "json")
  @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")
}

The implementation of the Event interface looks like this:

data class EventConcrete(
  val content: String,
  val flag: Boolean
) : Event, Serializable {
  override fun getTypeName(): Type {
    return Type.TYPE1
  }
}

MyJsonCustomType is my custom implementation of Hibernate's UserType interface with custom mapping. I am not posting all, but just an excerpt:

open class JsonUserType : UserType {

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

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

My Event interface is as follow:

interface Event {
  fun getTypeName(): Type
}

While the Dto/model looks like this:

@JsonInclude(JsonInclude.Include.NON_NULL)
open class MyModel(
  @ApiModelProperty(value = "Id")
  open val Id: UUID,
  @ApiModelProperty(value = "type")
  open val type: Type,
  @ApiModelProperty(value = "event)
  open val event: Event
) : AbstractDtoEntity {

 //equals, hashcode and toString methods

}

And here is the custom PostgreSQL95Dialect implementation:

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

I am not getting "No Dialect mapping for JDBC type" anylonger, however I am not getting the following:

"Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: property mapping has wrong number of columns:"


Can you help? Where am I going wrong? is the issue down to the fact that I am passing in the entity and dto the interface itself and not the concrete class?

[Dave Tolls]

Can you post the full exception?
That will give the details of the issue...it should have the class name and the field it's having issues with.

[Geane Norm]

Dave Tolls wrote:Can you post the full exception?
That will give the details of the issue...it should have the class name and the field it's having issues with.

Dave Tolls wrote:Can you post the full exception?
That will give the details of the issue...it should have the class name and the field it's having issues with.

Hi,
thank you for your reply. In the meantime, I have changed my approach and went for this:

https://vladmihalcea.com/how-to-map-json-collections-using-jpa-and-hibernate/

after a conversation here: https://stackoverflow.com/questions/59967180/how-to-create-custom-jsonb-mapper-for-postgresql-and-hibernate-in-kotlin

in a nutshell, I have removed the custom type implementation.

Here is my implementation:

Entity

@Entity
@TypeDef(
  name = "jsonb-node",
  typeClass = JsonNodeBinaryType::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-node")
  @Column(columnDefinition = "jsonb")
  @Basic(fetch = FetchType.LAZY)
  var event_data: GenericEvent
) : SomeEntity<UUID>(), SomeOtherStuff {
  override fun getName(): String {
    return id
  }
}
 
 
enum class Type(val value: String) {
  TYPE1("Type1"),
  TYPE2("Type2")
}

Model

@JsonInclude(JsonInclude.Include.NON_NULL)
open class MyModel(
  @ApiModelProperty(value = "Id")
  open val Id: UUID,
  @ApiModelProperty(value = "type")
  open val type: Type,
  @ApiModelProperty(value = "event")
  open val event: GenericEvent
) : AbstractDtoEntity {
 
 //equals, hashcode and toString methods
 }

Custom dialect extension

class CustomPostgreSQLDialect : PostgreSQL95Dialect {
  constructor() : super() {
    this.registerHibernateType(Types.OTHER, JsonNodeBinaryType::class.javaObjectType.name)
  }
}

please note that the same error is shown when passing simply "jsnob" instead of JsonNodeBinaryType::class.javaObjectType.name.

That custom class is then referenced in the application.yml:

jpa.properties.hibernate.dialect: org.myapp.util.CustomPostgreSQLDialect

Here is the full stacktrace:

2020-04-21 14:09:13.793 ERROR 41235 --- [  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: 769917233
	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: 769917233
	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: 769917233
	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

I seem to have followed all the steps and still I am getting the same No Dialect issue. Am i missing anything?

Thank you

[Geane Norm]

I am still struggling with the issue and I do not seem to have found a solution.

I have modified my custom Dialect implementation:

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)
  }
}

but I am still getting the very same error. From https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/, it is given multiple options to address the issue and I went for "Mapping the JDBC Types.OTHER to JsonNodeBinaryType at the Dialect level", but shall I also modify my repository? And if so, how? Shall I also do it at the NativeQuery level?

Thank you