Hibernate Criteria Query to use MySQL REPLACE and CONCAT functions

Question

Trying to replicate the following MySQL query in Hibernate using CriteriaBuilder. This query adds first and last name, removes all whitespaces in between and search for results that matches the given string.

select * from users where replace(concat(first_name, last_name), " ", "") like 'jamesbon%';

final CriteriaBuilder criteriaBuilder = getCurrentSession().getCriteriaBuilder();
final CriteriaQuery<UserImpl> userCriteriaQuery = criteriaBuilder.createQuery(UserImpl.class);

final Root<UserImpl> userRoot = userCriteriaQuery.from(UserImpl.class);

// criteriaBuilder.concat(userRoot .get("firstName"), userRoot .get("lastName"))

Answer 1

Concat is available through the builder, so all you really need to add is the replace function.

What you need to do is create a class that implements org.hibernate.boot.spi.MetadataBuilderInitializer and use it to register the functions with Hibernate. Let's say your class is called com.project.hibernate.MetaContrib

package com.project.hibernate;

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.spi.MetadataBuilderInitializer;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StringType;

public class MetaContrib implements MetadataBuilderInitializer {

    @Override
    public void contribute(MetadataBuilder metadataBuilder, StandardServiceRegistry serviceRegistry) {
        metadataBuilder.applySqlFunction("str_replace", new StandardSQLFunction("replace", StringType.INSTANCE));
        metadataBuilder.applySqlFunction("regex_replace", new StandardSQLFunction("REGEXP_REPLACE", StringType.INSTANCE));
    }

}

The next step is to tell Hibernate to load this, by creating a file in the META-INF/services directory in your resources, called org.hibernate.boot.spi.MetadataBuilderInitializer. If such a directory doesn't exist, create it. The file has to contain the full name of the implementing class, and end in a new line.

Finally to use it:

expr1 = criteriaBuilder.concat(userRoot.get("firstName"), userRoot.get("lastName"));
expr2 = criteriaBuilder.function("str_replace", String.class, expr1, " ", "");
expr3 = criteriaBuilder.like(expr2, cb.parameter(String.class, "sv"));
userCriteriaQuery.where(expr3)

return createQuery(userCriteriaQuery)
           .setParameter("sv", "jamesbon%")
           .getResultList();

Detailed explanation:

The CriteriaBuilder creates a JPQL query. Your function expression becomes something like:

... WHERE function('str_replace', concat(u.firstName, u.lastName), ' ', '') LIKE :sv

Which when rendered to a native query will look like:

where replace(concat(u0.first_name, u0.last_name), ' ', '') like :sv

The function was registered under the name str_replace in JPQL, but it can be any name you choose. It's the name you give to the StandardSQLFunction constructor that tells it what the native name is.

Then further down the :sv internally becomes a ?, and when you use setParameter it tells the JDBC driver to safely send the string at that position.

However if you want to remove all whitespace, instead of merely all 0x20 space characters, you should use a regular expression like \s+ with the other function I put in MetaContrib. You can only do so if your MySQL is 8.0.4 or newer, or MariaDB 10.0.8 or newer. That function exists in the MariaDB10Dialect, so if you are using MariaDB, you may not need the MetaContrib class.