DEV Community

Cover image for Creating Custom Hibernate Dialect
Jan Cizmar Subscriber for Tolgee

Posted on β€’ Edited on

10 1

Creating Custom Hibernate Dialect

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]})"
        }
      }
    )
  }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Testing time!

Adventure 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)
  }
}
Enter fullscreen mode Exit fullscreen mode

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!

Image description

πŸ’‘ One last tip before you go

Spend less on your side projects

We have created a membership program that helps cap your costs so you can build and experiment for less. And we currently have early-bird pricing which makes it an even better value! πŸ₯

Check out DEV++

Top comments (1)

Collapse
 
csakbab profile image
Csaba β€’

Well, this is not a custom dialect. It is just extending an already existing one.

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay