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!
Top comments (1)
Well, this is not a custom dialect. It is just extending an already existing one.