Hi,

We are running the following query with HQL (it includes a main table and a set table inlcuded).

select distinct request.id from com.bmc.idm.ctsa.hb8.dbobject.Request as request join request.fields as fields where ((fields.attrName = 'jc_name' and fields.attrValue = 'xyz1') or (fields.attrName = 'user_id' and fields.attrValue like :'abc1') ) group by request.id having count(fields) = 2

We would like to execute select count that will return the count of the entries in the above query. We would appreciate any suggestion for HQL that wil run count for the queury above.
(our work around is to return the list.size() but this workaround has overhaed in performances)

Thanks.

Hibernate version is 3.0.5
This is the hbm file:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.bmc.idm.ctsa.hb8.dbobject">
<class name="Request" table="REQUEST">
<id name="id" column="ID" type="long" unsaved-value="null">
<generator class="native">
<param name="sequence">REQUEST_seq</param>
</generator>
</id>
<version name="version" access="property"/>
<dynamic-component name="attributes">
<property name="requestor" column="REQUESTOR" access="property" type="string" not-null="true" />
<property name="subject" column="SUBJECT" access="property" type="string" not-null="true"/>
<property name="subEntityName" column="SUB_ENTITY" access="property" type="string" not-null="true" />
<property name="submissionDate" column="SUBMISSION_DATE" access="property" type="timestamp"/>
</dynamic-component>

<set name="fields" lazy="true" table="REQUEST_FIELDS" inverse="true" cascade="all-delete-orphan">
<key column="REQUEST_ID"/>
<one-to-many class="RequestField"/>
</set>
</class>

<class name="RequestField" table="REQUEST_FIELDS">
<id name="id" column="ID" type="long" unsaved-value="null">
<generator class="native">
<param name="sequence">REQUEST_FIELDS_seq</param>
</generator>
</id>
<many-to-one name="request" class="Request" not-null="true">
<column name="REQUEST_ID" unique-key="uq"/>
</many-to-one>
<property name="attrName" type="string">
<column name="FIELD_NAME" unique-key="uq"/>
</property>
<property name="attrValue" column="FIELD_VALUE" type="string" length="4000"/>
</class>

You might test this one:


I don't know if it's supported by HQL.

Best regards
Sven

Use the HQL count function:

http://www.hibernate.org/hib_docs/v3/re ... hql-select

It seems to be implemented:
http://www.hibernate.org/hib_docs/v3/re ... ggregation

Best regards
Sven

Hi and Thanks,

The above suggestions don't work for us.
We were trying some count HQL queries, including the query:



It is not helpful since it returns a list with many elemnts, where each element contains the count items in the group. This happens because we running a 'group by' + 'having count' clause.
The size of this list is real count we are looking for.
Other suggestions will be appreciated.

Just leave out the GROUP BY clause. Duplicate entries are filtered using COUNT(DISTINCT...) and the GROUP BY is optional (even when using the HAVING clause). Though I think it's not recommended to use the HAVING clause with a GROUP BY.

Just test, if it satisfies your needs concerning performance.

Best regards
Sven

Sorry, "with = without"

Alternatively, you might use a subselect construction:



Best regards
Sven

If all else fails you can always use native SQL via hibernate, just make sure its portable.

Thank you Sven.
The count query works. I will try to combine it in our code.

Does anyone know how to do this using Criteria objects and DetachedCriteria objects instead of HSQL?