Sources Contact Advanced Search Tutorials

An Interest In:

Web News this Week

Search Archive

Some of Our Sources

View All Sources

Help Webnuz

Referal links:

January 11, 2022 08:30 am GMT

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.postgresimport org.hibernate.NullPrecedenceimport org.hibernate.dialect.PostgreSQL10Dialectimport org.hibernate.dialect.function.SQLFunctionimport org.hibernate.engine.spi.Mappingimport org.hibernate.engine.spi.SessionFactoryImplementorimport org.hibernate.type.FloatTypeimport org.hibernate.type.Typeclass 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.postgresimport io.tolgee.model.UserAccountimport io.tolgee.testing.assertions.Assertions.assertThatimport org.springframework.beans.factory.annotation.Autowiredimport org.springframework.boot.test.context.SpringBootTestimport org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTestsimport org.testng.annotations.Testimport javax.persistence.EntityManager@SpringBootTestclass 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

Original Link: https://dev.to/tolgee_i18n/creating-custom-hibernate-dialect-1bb9

Share this article:

View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To