How can I use to_char function in the Liferay DynamicQuery API?

Question

Platform is Liferay 7.0, Tomcat, Oracle 12c.

We currently use dynamic query below. In order to tune it for better performance, we need to convert the data_ field which is a CLOB to character.

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil
                    .forClass(ExpandoValue.class, PortalClassLoaderUtil.getClassLoader())
                    .add(PropertyFactoryUtil.forName("columnId").eq(GetterUtil.getLong(column.getColumnId())))
                    .add(PropertyFactoryUtil.forName("data").like(somecharvalue))
                    .add(PropertyFactoryUtil.forName("classNameId")
                            .eq(GetterUtil.getLong(ClassNameLocalServiceUtil.getClassNameId(User.class.getName()))));

At runtime, it results in SQL query below which we need to tune:

SELECT this_.valueId AS valueId90_0_,
this_.companyId AS companyId90_0_,
this_.tableId AS tableId90_0_,
this_.columnId AS columnId90_0_,
this_.rowId_ AS rowId5_90_0_,
this_.classNameId AS classNam6_90_0_,
this_.classPK AS classPK90_0_,
this_.data_ AS data8_90_0_
FROM ExpandoValue this_
WHERE this_.columnId = NNNNN AND this_.data_ LIKE '%CCCC%' AND this_.classNameId = NNNNN

How can I add the to_char(data_) to the SQL query above using DynamicQuery or other API for liferay 7 so it looks like below?

SELECT this_.valueId AS valueId90_0_,
this_.companyId AS companyId90_0_,
this_.tableId AS tableId90_0_,
this_.columnId AS columnId90_0_,
this_.rowId_ AS rowId5_90_0_,
this_.classNameId AS classNam6_90_0_,
this_.classPK AS classPK90_0_,
this_.data_ AS data8_90_0_
FROM ExpandoValue this_
WHERE this_.columnId = NNNNN AND to_char(this_.data_) LIKE '%CCCC%' AND this_.classNameId = NNNNN

Thanks for all the insight!

I could not solve it by using the DynamicQuery. I had to use a direct SQL query to the database to be able to do this. — VC1, May 14 '20 at 16:06

How can I use to_char function in the Liferay DynamicQuery API?

0 Answers0