Liferay自定义SQL使用IN运算符 [英] Liferay custom-sql using IN operator

问题描述

我正在使用Liferay 6.1，Tomcat和MySQL.我有一个用于列表portlet的定制SQL语句. custom-sql使用两个参数:一个groupIds数组和一个结果限制.

I am using Liferay 6.1, Tomcat and MySQL. I have a custom-sql sentence for a list portlet. The custom-sql uses two parameters: an array of groupIds and a result limit.

SELECT
count(articleId) as count,
...
FROM comments
WHERE groupId IN (?)
GROUP BY articleId
ORDER BY count DESC 
LIMIT 0, ?

我的FinderImpl类具有此方法:

My FinderImpl class has this method:

 public List<Comment> findByMostCommented(String groupIds, long maxItems) {

    Session session = null;
    session = openSession();

    String sql = CustomSQLUtil.get(FIND_MOST_COMMENTS);

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity("Comment", CommentImpl.class);

    QueryPos queryPos = QueryPos.getInstance(query);
    queryPos.add(groupIds);
    queryPos.add(maxItems);

    List<Comment> queryResult = query.list();

    return queryResult;
}

这将返回0个结果.如果我删除WHERE IN()，它将起作用.

This returns 0 results. If I remove the WHERE IN(), it works.

IN是有效的运算符吗?如果没有，如何在不同的组中进行搜索?

Is IN a valid operator? If not, how can search within different groups?

推荐答案

hibernate也许正在引用您的groupId字符串(大概是"1,2,3,4"的形式，当hibernate将其转换为sql时，它会在它周围加上引号你吗?

Perhaps hibernate is quoting your string of groupIds (presumably it is in the form of "1,2,3,4" and when hibernate translates this to sql it is putting quotes around it for you?

您可能想尝试类似的方法(来自Liferay本身):

You may want to try something like this (from Liferay itself):

String sql = CustomSQLUtil.get(FIND_BY_C_C);

sql = StringUtil.replace(sql, "[$GROUP_IDS$]", groupIds);

在SQL中用([$GROUP_IDS$])代替(?)

这篇关于Liferay自定义SQL使用IN运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！