MariaDB查询LIKE子句附近语法错误 JPA自定义函数调用异常

问题现象 #

编写JPA数据查询逻辑时抛出SQL语法错误，错误位置指向LIKE子句附近，MariaDB返回的原始报错信息如下：

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') like '%:#{#criteria.nameWithCompany}' or concat(lower(replace), '/', replac...' at line 1

从报错输出的SQL片段可以看到，生成的SQL中replace函数未携带任何入参，直接以lower(replace)的形式输出，是触发语法错误的直接特征。

关联代码片段 #

异常查询片段 #

(:#{#criteria.nameWithCompany} is null " +
            "                       or (lower(function('replace',u.fullName,' ','')) like :#{#criteria.nameWithCompany}) " +
            "                       or (concat(lower(function('replace',u.fullName,' ','')), '/', " +
            "                function('replace', lower(c.companyName), ' ', '')) like :#{#criteria.nameWithCompany})) 

自定义函数注册代码 #

由于JPA原生未内置replace等函数，自行实现了自定义方言类用于注册扩展SQL函数，代码如下：

public class MySQLUTF8InnoDBDialect implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("group_concat_with_distinct", new SQLFunctionTemplate(StringType.INSTANCE, "group_concat(distinct ?1)"));
        metadataBuilder.applySqlFunction("replace", new SQLFunctionTemplate(StringType.INSTANCE, "replace"));
        metadataBuilder.applySqlFunction("char_length", new SQLFunctionTemplate(StringType.INSTANCE, "char_length"));

    }
}

根因定位 #

自定义SQL函数的注册模板配置错误：
注册group_concat_with_distinct时，模板串通过?1标记了参数占位位置，但replace、char_length的模板仅填写了裸函数名，没有配置对应数量的参数占位符，Hibernate生成SQL时不会将调用时传入的参数拼接到函数名后，最终输出的SQL缺失函数入参，直接在函数名后跟随右括号触发语法错误，和报错中打印的异常SQL片段完全匹配。

修复方案 #

可选择以下任意一种方案修复：

• 修正自定义函数模板配置：按照函数实际参数数量补充占位符，replace为三参数函数，模板需写为replace(?1, ?2, ?3)；char_length为单参数函数，且返回值为整数类型，模板需写为char_length(?1)，返回类型调整为IntegerType，修正后的注册代码如下：

metadataBuilder.applySqlFunction("group_concat_with_distinct", new SQLFunctionTemplate(StringType.INSTANCE, "group_concat(distinct ?1)"));
metadataBuilder.applySqlFunction("replace", new SQLFunctionTemplate(StringType.INSTANCE, "replace(?1, ?2, ?3)"));
metadataBuilder.applySqlFunction("char_length", new SQLFunctionTemplate(IntegerType.INSTANCE, "char_length(?1)"));

• 移除冗余的内置函数注册：Hibernate 5.2及以上版本已经原生支持replace、char_length这类标准SQL函数，不需要手动注册，直接删除自定义类中这两个函数的注册逻辑即可，避免自定义模板配置错误引发异常。

内容的提问来源于stack exchange，提问作者Elen Mouradian