Search in key-value table with hibernate (or SQL query)

Question

I have two tables:

• Content(id,data,title,size)
• ContentProperties(id,key,value,fk_content_id)

Each content record can have many properties (one to many). I want to use hibernate to retrieve content records with these conditions (it's pseudo code):

(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"
}

What is its SQL query? How can i do that with hibernate api? Thanks in advance.

Answer 1

I created Key-Value criteria with 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;
    }
}