Creating Custom Hibernate Dialect
Tags: hibernate, kotlin, java, database
Published: 2022-01-11 08:30:41

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. 😎
```kotlin
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`.
```yaml
spring:
  jpa:
    properties:
      hibernate:
        dialect: io.tolgee.dialects.postgres.CustomPostgreSQLDialect
```

## Testing time!
![Adventure Time](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/knfk67a2dz68haopdqgo.gif)
 
```kotlin
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](https://tolgee.io) and have fun!

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/idu23asnj6dsz885l25x.jpeg)