Customizing the escape character for like operations (~ and ~~)

[agrison]

Hello,

I would like to discuss the possibility of having a spring property injected into both LikeOperationExpressionProcessor and InsensitiveLikeOperationExpressionProcessor to be able to control which character is used for escaping things with the like predicate on the CriteriaBuilder.

My application is using Oracle as a main database (and spring-filter works well), but for testing I am using H2 in Oracle compatibility mode (because I cannot have all the niceties of test-containers, and besides I like to test my filtering in JPA tests to be sure of what I provide to my users).

The thing is that since the last versions of Hibernate, the like predicates are broken on H2 using Oracle compatibility mode, because it will issue a ESCAPE '' filter by default, which will then make the query returning nothing, see HHH-16277 and h2 issue 3745,

While if the query was shaped with a ESCAPE '^' for example then it would work. So all this just to say I do have a need to control the escape character, because currently none is used and then I fall into this hibernate/h2#oracle pitfall which is pretty annoying.

Something like this works, and I'd like to know if you would be interested in a PR, or if you have any better idea:

public interface LikeExpressionProcessor {
    default Predicate like(CriteriaBuilder builder, Expression<String> left,
                           Expression<String> pattern, @Nullable Character escapeCharacter) {
        if (escapeCharacter == null) {
            return builder.like(left, pattern); // this is the current way of doing
        } else {
            return builder.like(left, pattern, escapeCharacter); // providing a specific escape character
        }
    }
}

@Component
public class InsensitiveLikeOperationExpressionProcessor implements
        FilterInfixOperationProcessor<FilterExpressionTransformer, Expression<?>>,
        LikeExpressionProcessor {
    @Value("${spring-filter.jpa-like-escape-character:#{null}}")
    private Character escapeCharacter;

    // ...

    @SuppressWarnings("unchecked")
    @Override
    public Expression<?> process(FilterExpressionTransformer transformer, InfixOperationNode source) {
        transformer.registerTargetType(source, Boolean.class);
        transformer.registerTargetType(source.getLeft(), String.class);
        transformer.registerTargetType(source.getRight(), String.class);

        return like(transformer.getCriteriaBuilder(), // here we call like from above interface
                transformer.getCriteriaBuilder()
                        .upper((Expression<String>) transformer.transform(source.getLeft())),
                transformer.getCriteriaBuilder()
                        .upper(likeOperationExpressionProcessor.getLikePatternExpression(transformer,
                                source.getRight())),
                escapeCharacter);
    }
}

// same for the LikeOperationExpressionProcessor

This way for those not interested they don't have to do anything, it continues to work as it currently does, but for those wanting a specific escape character then they can just set the following spring-filter.jpa-like-escape-character=^ in their application.properties and can benefit from this nice library even in H2/Oracle world.

Let me know what you think or in any other better way to do it.

[agrison]

Hello @torshid, any feedback on this ?

[torshid]

Thanks for this issue. I've committed something similar to what you've shared here. Going to release a new version soon.

[agrison]

Thank you that is good to know, I will take a look once it is released, so I can remove that custom code from my codebase.