使用hibernate (或SQL查询)在键值表中搜索

Question

我有两张桌子：

• 内容(id、数据、标题、大小)
• ContentProperties(id，key，value，fk_content_id)

每个内容记录可以有许多属性(一对多)。我想使用hibernate检索具有以下条件的内容记录(它是伪代码)：

(content.title == "article") 
AND
if(content.contentProperties.key == "author")
{
    content.contentProperties.value = "david"
} 
AND
if(content.contentProperties.key == "pages")
{
    content.content.contentProperties.value <= "150"
    content.content.contentProperties.value >= "050"
}

它的SQL查询是什么？我如何使用hibernate api来做到这一点？提前谢谢。

Answer 1

我用Restrictions.sqlRestriction创建了键值条件：

private Criterion createKeyValueSqlCriteria(String key, String operator, Object value)
{
    if(operator.equals("like"))
    {
        value = "%" + ((String)value) + "%";
    }
    Object[] valueArray = {key, value};
    Type[] typeArray = {StringType.INSTANCE, getValueType(value)};

    String query = "exists ( select 1 from CONTENT_PROPERTY cp " + 
                   "where cp.FK_CONTENT_ID = {alias}.CONTENT_ID " +
                   "and cp.KEY= ? and cp.VALUE " + operator + " ? )";

    Criterion criterion = Restrictions.sqlRestriction(query,valueArray,typeArray);
    return criterion;
}

private Type getValueType(Object value)
{
    if(value instanceof String)
    {
         return StringType.INSTANCE;
    }
    else if (value instanceof Long)
    {
         return LongType.INSTANCE;
    }
    else if (value instanceof Double)
    {
         return DoubleType.INSTANCE;
    }
    else if (value instanceof Boolean)
    {
         return BooleanType.INSTANCE;
    }
    else if (value instanceof Date)
    {
         return DateType.INSTANCE;
    }
    else
    {
        return null;
    }
}