如何在Liferay 7.0 DynamicQuery中使用to_char优化Oracle查询

我之前在Liferay 7.0+Oracle 12c的环境里刚好解决过一模一样的问题——要把CLOB类型的data_字段用to_char()转换后再做模糊查询，优化原生SQL的执行效率。其实不用复杂的自定义SQL，直接借助Liferay的RestrictionsFactoryUtil就能实现需求，具体修改如下：

修改后的DynamicQuery代码 #

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil
    .forClass(ExpandoValue.class, PortalClassLoaderUtil.getClassLoader())
    .add(PropertyFactoryUtil.forName("columnId").eq(GetterUtil.getLong(column.getColumnId())))
    // 替换原有like条件，用原生SQL限制实现to_char转换
    .add(RestrictionsFactoryUtil.sqlRestriction(
        "to_char(this_.data_) LIKE ?", 
        "%" + somecharvalue + "%", 
        Type.STRING
    ))
    .add(PropertyFactoryUtil.forName("classNameId")
        .eq(GetterUtil.getLong(ClassNameLocalServiceUtil.getClassNameId(User.class.getName()))));

关键说明 #

1. 替换原有的like限制：原来用PropertyFactoryUtil.forName("data").like(...)只能生成直接对data_字段的like查询，没法嵌套数据库函数。而RestrictionsFactoryUtil.sqlRestriction()允许我们插入原生SQL片段，刚好满足给字段套to_char()的需求。
2. 参数化查询：这里用?作为占位符，再传入带通配符的参数，既实现了模糊查询，又避免了SQL注入的风险，比直接拼接SQL字符串更安全。
3. 适配Oracle特性：因为你的环境固定是Oracle 12c，所以直接用to_char()没问题；如果以后需要兼容其他数据库，可以通过PortalDatabaseUtil.getDBType()判断当前数据库类型，再分支处理不同的转换函数（比如MySQL用CAST(data_ AS CHAR)）。

这样修改后，DynamicQuery生成的SQL就会自动带上to_char(this_.data_) LIKE '%CCCC%'的条件，和你想要的优化后的SQL完全一致。

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