Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3

Mapping documents:
<class name="Child" table="childtable">

<composite-id name="ParentId" class="ParentId" >
<key-property name="DbId" column="dbid" type="long"/>
<key-property name="UserId" column="userid" type="UserIdUserType" />
</composite-id>

<map name="Attributes" lazy="false" table="ChildAttribute" cascade="all" >

<key>
<column name="DbId" not-null="true"/>
<column name="profileid" not-null="true"/>
</key>

<map-key type="string" column="attribute_name" />
<element type="string" column="attribute_value"/>
</map>

</class>
Code between sessionFactory.openSession() and session.close():

I need to query from the map of child attributes. I need to update the attribute values for all those rows where DbId in a particular set of numbers and the attribute name has a specific value.

in sql, would be something like,

update childattribute
set attribute_value = "newValue"
where attribute_name = "attrbute1"
and dbid in (1,2,3,4,5)

The query that I used is as follows.

update ChildAttribute
set Attributes[?] = ?
where ? in indices(Child.Attributes)
and id.DbId in (?, ?)


Full stack trace of any exception that occurs:


org.hibernate.hql.ast.QuerySyntaxError: expecting EQ, found '[' near line 1, column 48 [
update ChildAttribute set Attributes[?] = ?
where ? in indices(Child.Attributes) and id.DbId in (?, ?)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

I tried to find out what the exception meant exactly, but was unable to find anything either in the documentation or HIA. If someone could help me, it would be great. Also, if someone could give some information on using HQL with collection objects,it would be great.


Name and version of the database you are using:
oracle 9i

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hibernate is O(object)-R-M(mapper) so you are supposed to get the object, update necessary attributes and store the object.

If yon need/want to update attributes the 'SQL' way then simply use Hibernates native SQL support http://www.hibernate.org/hib_docs/v3/re ... rysql.html
and risk having incorrect objects in some cache (Session or 2nd level).

thanks for pointing out the pt about objects. gavin - my problem is occuring with writing a query criteria for the map object.

<class name="Child" table="childtable">

<composite-id name="ParentId" class="ParentId" >
<key-property name="DbId" column="dbid" type="long"/>
<key-property name="UserId" column="userid" type="UserIdUserType" />
</composite-id>

<map name="Attributes" lazy="false" table="ChildAttribute" cascade="all" >

<key>
<column name="DbId" not-null="true"/>
<column name="profileid" not-null="true"/>
</key>

<map-key type="string" column="attribute_name" />
<element type="string" column="attribute_value"/>
</map>

</class>

how do i write the query to have the attribute_name match a specific value and attribute_value be a specific value ? I tried the following query. I need to know how to query for values inside a map ?

update ChildAttribute
set Attributes[?] = ?
where ? in indices(Child.Attributes)
and id.DbId in (?, ?)

It gives me the following exception:

org.hibernate.hql.ast.QuerySyntaxError: expecting EQ, found '[' near line 1, column 48 [
update ChildAttribute set Attributes[?] = ?
where ? in indices(Child.Attributes) and id.DbId in (?, ?)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)

There is no support for updating collection of value types using HQL.

ok, so the only way to do it is to retrieve each object and update it? I tried doing that, using the following.

Criteria query = session.createCriteria(ChildMember.class);
query.add(Expression.in("id.ParentId", pIds));
query.add(Expression.ilike("Attributes.attribute_name", "attr1", MatchMode.EXACT));
List result = query.list();
GL.lh("Results are " + result.toString());

It gave me the following exception. I tried Expression.ilike("Attributes[attribute_name]", "attr1") as well - but the same exception again. I have gone through the documentation and HIA, but am not able to find out how to query within collections. attribute_name and attribute_value are both strings. Would really appreciate the help.

thanks a lot,
smsea.

HibernateExceptionAdapter: caught exception: org.hibernate.QueryException: could not resolve property: userAttributes.attribute_name of: ChildMember
08/15/05 15:08:54:862: [main]: org.hibernate.QueryException: could not resolve property: userAttributes.attribute_name of: com.pogo.besl.userassociation.UserAssociationMember
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toColumns(AbstractPropertyMapping.java:63)
at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:31)
at org.hibernate.persister.entity.BasicEntityPersister.toColumns(BasicEntityPersister.java:1086)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:403)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:369)
at org.hibernate.criterion.IlikeExpression.toSqlString(IlikeExpression.java:32)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:314)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:92)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1303)

perhaps
query.add(Expression.isNotNull("Attributes['attr1']" );

I am checking for a match with a certain value, just not null is not enough. sorry about that.

The code

looks like it is supposed to check if we have attribute with name (case independent) 'attr1' - that is that my isNotNull statement will do but in case dependent matter unless you keep null values in the map.

If you are interested in knowing if an element of the collection with a particular name has a particular value then it probably looks like:

query.add(Expression.ilike("Attributes['attrName']", "attrValue", MatchMode.EXACT));