I'm trying to migrate from hibernate 5 to hibernate 6. Everything works as exptected, except for custom Expressions. In hibernate 5 it was possible to create custom Expression (e.g: RlikeExpression that renders as $1 RLIKE $2 for mysql and regexp_substring ($1, $2) IS NOT NULL for hsql)
My goal is to be able to add a Predicate, the same way as LIKE. (or eventually to create custom orderBy Expressions) All I need is a way to render myself what I need to write in the query. E.G :
criteriaQuery.where(new RLikePredicate(criteriaBuilder, from.get("COLUMN"), "REGEX"));
OR
criteriaQuery.orderBy(new MyOrderExpression(criteriaBuilder, ...)); // this creates a case/when SQL query thanks to the render(RenderingContext) method.
How is it possible to achieve the same in hibernate 6. Here is my RLikePredicate (I know it's not pretty, but it works)
In my HSQLDialect (for junit testing)
public class HSQLIntBooleanDialect extends HSQLDialect {
public HSQLIntBooleanDialect() {
super();
registerFunction("rlike", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "regexp_substring (?1, ?2) "));
}
}
In my MYSQL Dialect (for production)
public class MySQL5IntBooleanDialect extends MySQL5Dialect {
public MySQL5IntBooleanDialect() {
super();
registerFunction("rlike", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "(?1 RLIKE ?2)"));
}
}
And now, the part I'm having problems to migrate :
package fr.cstb.doef.dao.expression;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.HSQLDialect;
import org.hibernate.dialect.MySQL8Dialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.ParameterRegistry;
import org.hibernate.query.criteria.internal.Renderable;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.LiteralExpression;
import org.hibernate.query.criteria.internal.predicate.AbstractSimplePredicate;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Expression;
import java.io.Serializable;
import java.util.function.Function;
@SuppressWarnings({"unused", "WeakerAccess", "UnusedReturnValue"})
public class RLikePredicate extends AbstractSimplePredicate implements Serializable {
private static final Log log = LogFactory.getLog(RLikePredicate.class);
private final Expression<String> matchExpression;
private final Expression<String> pattern;
public RLikePredicate(
CriteriaBuilder criteriaBuilder,
Expression<String> matchExpression,
Expression<String> pattern) {
this(criteriaBuilder, matchExpression, pattern, null);
}
public RLikePredicate(
CriteriaBuilder criteriaBuilder,
Expression<String> matchExpression,
String pattern) {
this(criteriaBuilder, matchExpression, new LiteralExpression<>((CriteriaBuilderImpl) criteriaBuilder, pattern));
}
public RLikePredicate(
CriteriaBuilder criteriaBuilder,
Expression<String> matchExpression,
Expression<String> pattern,
Expression<Character> escapeCharacter) {
super((CriteriaBuilderImpl) criteriaBuilder);
this.matchExpression = matchExpression;
this.pattern = pattern;
}
public RLikePredicate(
CriteriaBuilder criteriaBuilder,
Expression<String> matchExpression,
Expression<String> pattern,
char escapeCharacter) {
this(
criteriaBuilder,
matchExpression,
pattern,
new LiteralExpression<>((CriteriaBuilderImpl) criteriaBuilder, escapeCharacter)
);
}
public RLikePredicate(
CriteriaBuilder criteriaBuilder,
Expression<String> matchExpression,
String pattern,
char escapeCharacter) {
this(
criteriaBuilder,
matchExpression,
new LiteralExpression<>((CriteriaBuilderImpl) criteriaBuilder, pattern),
new LiteralExpression<>((CriteriaBuilderImpl) criteriaBuilder, escapeCharacter)
);
}
public RLikePredicate(
CriteriaBuilder criteriaBuilder,
Expression<String> matchExpression,
String pattern,
Expression<Character> escapeCharacter) {
this(
criteriaBuilder,
matchExpression,
new LiteralExpression<>((CriteriaBuilderImpl) criteriaBuilder, pattern),
escapeCharacter
);
}
public Expression<String> getMatchExpression() {
return matchExpression;
}
public Expression<String> getPattern() {
return pattern;
}
public void registerParameters(ParameterRegistry registry) {
Helper.possibleParameter(getMatchExpression(), registry);
Helper.possibleParameter(getPattern(), registry);
}
@Override
public String render(boolean isNegated, RenderingContext renderingContext) {
StringBuilder buffer = new StringBuilder();
if (renderingContext.getDialect() instanceof HSQLDialect) {
final String operator = isNegated ? " IS NULL" : " IS NOT NULL";
buffer.append("rlike (").append(((Renderable) getMatchExpression()).render(renderingContext))
.append(",")
.append(escapeSqlRegex(renderingContext, String::valueOf)).append(")").append(operator);
} else if (renderingContext.getDialect() instanceof MySQLDialect) {
final String operator = isNegated ? " != 1" : " = 1";
buffer.append("rlike (").append(((Renderable) getMatchExpression()).render(renderingContext))
.append(",")
.append(escapeSqlRegex(renderingContext, String::valueOf)).append(")").append(operator);
} else {
throw new UnsupportedOperationException("Unsupported dialect.");
}
return buffer.toString();
}
private String escapeSqlRegex(RenderingContext renderingContext, Function<Object, String> toStringLiteral) {
if (!(getPattern() instanceof LiteralExpression)) return ((Renderable) getPattern()).render(renderingContext);
Object literal = ((LiteralExpression) getPattern()).getLiteral();
literal = escapeSqlRegex(renderingContext.getDialect(), String.valueOf(literal));
return "'" + toStringLiteral.apply(literal).replace("'", "''") + "'";
}
public static String escapeSqlRegex(Dialect dialect, String regex) {
if (regex == null) return null;
if (dialect instanceof MySQL8Dialect) regex = regex.replace("\\", "\\\\");
return regex;
}
}
I'm expecting a query that shows like alias.COLUMN RLIKE 'REGEX' in mysql and regexp_substring (alias.COLUMN, 'REGEX') IS NOT NULL IN HSQL
FOUND A SOLUTION. Is there an better way ?
I finally came up with that solution. The nodeBuilder.function method returns my rlike function. And transform it to an Predicate. It's not perfect, but I hope it will help someone else.
package fr.cstb.doef.dao.expression;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.query.sqm.NodeBuilder;
import org.hibernate.query.sqm.SemanticQueryWalker;
import org.hibernate.query.sqm.tree.SqmCopyContext;
import org.hibernate.query.sqm.tree.expression.SqmExpression;
import org.hibernate.query.sqm.tree.expression.SqmFunction;
import org.hibernate.query.sqm.tree.predicate.AbstractNegatableSqmPredicate;
import org.hibernate.query.sqm.tree.predicate.SqmNegatablePredicate;
import org.hibernate.query.sqm.tree.predicate.SqmNullnessPredicate;
import java.io.Serializable;
@SuppressWarnings({"unused", "WeakerAccess", "UnusedReturnValue"})
public class RLikePredicate extends AbstractNegatableSqmPredicate implements Serializable {
private static final Log log = LogFactory.getLog(RLikePredicate.class);
private final SqmExpression<?> matchExpression;
private final SqmExpression<?> pattern;
private final SqmFunction<Integer> function;
public RLikePredicate(SqmExpression<?> matchExpression, SqmExpression<?> pattern, boolean negated, NodeBuilder nodeBuilder) {
super(negated, nodeBuilder);
this.matchExpression = matchExpression;
this.pattern = pattern;
function = nodeBuilder.function("rlike", Integer.class, new jakarta.persistence.criteria.Expression[]{matchExpression, pattern});
}
public RLikePredicate(SqmExpression<?> matchExpression, SqmExpression<?> pattern, NodeBuilder nodeBuilder) {
this(matchExpression, pattern, false, nodeBuilder);
}
@Override
public RLikePredicate copy(SqmCopyContext context) {
final RLikePredicate existing = context.getCopy(this);
if (existing != null) return existing;
final RLikePredicate predicate = context.registerCopy(
this,
new RLikePredicate(matchExpression.copy(context), pattern.copy(context), isNegated(), nodeBuilder())
);
copyTo(predicate, context);
return predicate;
}
public SqmExpression<?> getMatchExpression() {
return matchExpression;
}
public SqmExpression<?> getPattern() {
return pattern;
}
@Override
public <T> T accept(SemanticQueryWalker<T> walker) {
if (walker instanceof SqmTranslator<?>) {
Dialect dialect = ((SqmTranslator<?>) walker).getCreationContext().getSessionFactory().getJdbcServices().getDialect();
if (dialect instanceof HSQLDialect) {
return walker.visitIsNullPredicate(new SqmNullnessPredicate(function, !isNegated(), nodeBuilder()));
} else {
return walker.visitComparisonPredicate(new SqmComparisonPredicate(
function,
isNegated() ? ComparisonOperator.NOT_EQUAL : ComparisonOperator.EQUAL,
nodeBuilder().literal(1),
nodeBuilder()
));
}
}
return function.accept(walker);
}
@Override
public void appendHqlString(StringBuilder sb) {
function.appendHqlString(sb);
}
@Override
protected SqmNegatablePredicate createNegatedNode() {
return new RLikePredicate(matchExpression, pattern, !isNegated(), nodeBuilder());
}
}
You'd just have to register a custom function that returns a boolean and then you can use that. Something like this in a FunctionContributor:
functionContributions.getFunctionRegistry().registerPattern(
"rlike",
"?1 rlike ?2",
functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve( StandardBasicTypes.BOOLEAN )
);
and then in JPA Criteria you just use criteriaBuilder.isTrue(criteriaBuilder.function("rlike", Boolean.class, from.get("COLUMN"), criteriaBuilder.literal("REGEX"))))