Hi,
I'm trying to execute query that performs a count(*) on the elements of map attached to a parent object and I'm stuck with not knowing how to write the HQL.
The objects involved look like:
ContentDescriptor
Quote:
.id
.url
.project <!---- foreign key by which results are iltered
.metadata <---- the map to query against
the map contains HTTP header data for each contentdescriptor, including
a field 'Identify Metadata.Content-Type' against which I want to count/groupby.
In standard SQL I would write:
Code:
select cm.mdvalue, count(0)
from contentdescriptor cd, contentmetadata cm
where cd.id = cm.contentDescriptorId
and cm.mdAttribute = 'Identify Metadata.Content-Type'
group by cm.mdvalue
In HQL so far I've got:
Code:
SELECT elements(cd.metadata) FROM com.vamosa.content.ContentDescriptor cd WHERE cd.project.id=:projectId AND 'Identify Metadata.Content-Type' in indices(cd.metadata) GROUP BY elements(cd.metadata)
The question is:
how can I write my query in HQL ?Hibernate version:2.1.6 Mapping documents: Code:
<class
name="com.vamosa.content.ContentDescriptor"
table="contentdescriptor"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="id"
column="id"
type="java.lang.String"
length="32"
>
<generator class="uuid.hex">
</generator>
</id>
<property
name="url"
type="java.lang.String"
update="true"
insert="true"
column="url"
length="860"
/>
<many-to-one
name="project"
class="com.vamosa.projects.Project"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="projectId"
/>
<map
name="metadata"
table="contentMetadata"
lazy="true"
sort="unsorted"
inverse="false"
cascade="delete"
>
<key
column="contentDescriptorId"
/>
<index
column="mdAttribute"
type="string"
length="860"
/>
<element
column="mdValue"
type="string"
length="860"
not-null="false"
unique="false"
/>
</map>
</class>
Code:
14:24:49,437 ERROR [JDBCExceptionReporter] Could not execute query
java.sql.SQLException: ORA-00904: "METADATA2_"."MDVALUE": invalid identifier
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:622)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:180)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:419)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:895)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:451)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:985)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2887)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2928)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:860)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1608)
at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27)
at com.vamosa.query.QueryLibrarianServiceImpl$4.doInHibernate(QueryLibrarianServiceImpl.java:143)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:200)
at com.vamosa.query.QueryLibrarianServiceImpl.executeQuery(QueryLibrarianServiceImpl.java:131)
Name and version of the database you are using: Oracle 10gThe generated SQL (show_sql=true):Code:
select metadata3_.mdValue as x0_0_ from contentdescriptor contentdes0_, contentMetadata metadata3_ where contentdes0_.id=metadata3_.contentDescriptorId and ((contentdes0_.projectId=? )AND('Identify Metadata.Content-Type' in(select metadata1_.mdAttribute from contentMetadata metadata1_ where contentdes0_.id=metadata1_.contentDescriptorId))) group by metadata2_.mdValue
Login
FAQ
Search