Creating Custom Hibernate Dialect

#hibernate #kotlin #java #database

While working with Hibernate's HQL and Criteria Queries I was missing some features of PostgreSQL which would help. I don't want to write native queries for this. Once I use Hibernate I should stick with Hibernate. Right?

One of the features I was missing in Hibernate's default Postgres dialect was similarity function, which returns how two strings are different on 0 to 1 scale. So let's see, how to create our own Hibernate dialect to be able to use similarity function in HQL or Criteria Queries.

The Dialect class

First of all, let's create a class with the dialect. I am using Kotlin btw. 😎

package io.tolgee.dialects.postgres

import org.hibernate.NullPrecedence
import org.hibernate.dialect.PostgreSQL10Dialect
import org.hibernate.dialect.function.SQLFunction
import org.hibernate.engine.spi.Mapping
import org.hibernate.engine.spi.SessionFactoryImplementor
import org.hibernate.type.FloatType
import org.hibernate.type.Type

class CustomPostgreSQLDialect : PostgreSQL10Dialect() {
  init {
    registerFunction(
      "similarity",
      object : SQLFunction {
        override fun hasArguments(): Boolean = true

        override fun hasParenthesesIfNoArguments() = false

        override fun getReturnType(firstArgumentType: Type?, mapping: Mapping?) = FloatType()

        override fun render(
          firstArgumentType: Type,
          arguments: MutableList<Any?>,
          factory: SessionFactoryImplementor
        ): String {
          return "similarity(${arguments[0]}, ${arguments[1]})"
        }
      }
    )
  }
}

Simple, right? In the constructor we just call registerFunction method accepting the function name parameter and object implementing SQLFunction interface, which I am implementing right in the place. Maybe in the future, when my CustomPostgreSQLDialect will grow I would extract this into separate class, but for now I am OK with this.

Since we are implementing interface, there are few methods we have to override. Their names are pretty self-explanatory. It tells Hibernate that

the function has arguments
it has Float return type
when HQL or CriteriaQuery is transformed to SQL it should be rendered like similarity(1st argument, 2nd argument)

The hasParenthesesIfNoArguments doesn't make any sense, because the function cannot work without arguments, so I set it false.

Enabling the dialect

Enabling the dialect is as simple as setting Hibernate prop dialect to dialect's absolute class name.

Since I am using Spring Boot, I have this in my application.yaml.

spring:
  jpa:
    properties:
      hibernate:
        dialect: io.tolgee.dialects.postgres.CustomPostgreSQLDialect

Testing time!

package io.tolgee.dialects.postgres

import io.tolgee.model.UserAccount
import io.tolgee.testing.assertions.Assertions.assertThat
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTests
import org.testng.annotations.Test
import javax.persistence.EntityManager


@SpringBootTest
class CustomPostgreSQLDialectTest: AbstractTransactionalTestNGSpringContextTests() {

  @Autowired
  lateinit var entityManager: EntityManager

  @Test
  fun `similarity function works`(){
    // Hibernate queries doesn't work without FROM clause, so we have 
    // to create a dummy entity to select from
    entityManager.persist(UserAccount(username = "aaa", password = "aaaa", name = "aaaaa"))
    val query = entityManager.createQuery(
      "select similarity('I am so funny!', 'You are so funny!') from UserAccount"
    )
    assertThat(query.singleResult).isEqualTo(0.47619048f)
  }
}

TL;DR

Create your own Hibernate dialect when you need to use advanced features of your database system
It can be done just by extending another dialect and adding functions you need

Tolgee is an open-source solution for software localization. It saves developer's time. Go to Tolgee.io and have fun!