Casting Strint to integer in liferay dynamic query

Question

I have been using dynamic query for a project.

Here is an scenario for which I am facing problem.

For a table xyz the column version is stored as varchar (I know it's a poor design, but it's too late to change now) and has values as 9,12.

For the query :

select max(version) 
from xyz 
where something = 'abc';

I am getting the output as 9 instead of 12.

The dynamic query for the same is:

ClassLoader classLoader = PortletBeanLocatorUtil.getBeanLocator(ClpSerializer.getServletContextName()).getClassLoader();

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(xyz.class, classLoader);
                    dynamicQuery.setProjection(ProjectionFactoryUtil.max("version"));
                    dynamicQuery.add(PropertyFactoryUtil.forName("something").eq("abc"));

List<Object> list = xyzLocalServiceUtil.dynamicQuery(dynamicQuery);

The query which is giving the correct value is :

select max(cast(version as signed)) 
from xyz 
where something = 'abc';

Now, I want it to be in the dynamic query, how can I do that?

I am using liferay-6.2-ce

Answer 1

Try using ProjectionFactoryUtil.sqlProjection method. That method allows using functions that are executed by SQL engine.

For example, I am using following code in order to get the max length of a string column called 'content':

Projection maxSizeProjection = ProjectionFactoryUtil.sqlProjection(
        "max(length(content)) as maxSize", new String[] {"maxSize"},
        new Type[] {Type.BIG_DECIMAL});

The same thing can be done with dynamic query criterions using RestrictionsFactoryUtil.sqlRestriction in case you want to use a SQL function in a condition.

In your case try following code:

import com.liferay.portal.kernel.dao.orm.ProjectionFactoryUtil;
import com.liferay.portal.kernel.dao.orm.Type;

...

Projection maxSizeProjection = ProjectionFactoryUtil.sqlProjection(
        "max(cast(version as signed)) as maxVersion",
        new String[] {"maxVersion"}, new Type[] {Type.BIG_DECIMAL});

dynamicQuery.setProjection(maxSizeProjection);