Is it possible to use filters with Custom SQL for loading?

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

Basically I want to do something like:

Code:

<class name="myClass" ...>
    ...
    <filter name="myFilter" 
              condition="AND :myFilterParam = MY_FILTERED_COLUMN"/>
</class>

<sql-query name="organizationEmployments">
    <load-collection alias="empcol" role="Organization.employments"/>
    SELECT {empcol.*}
    FROM EMPLOYMENT empcol
    WHERE EMPLOYER = :id
    {filter: myFilter}
    ORDER BY STARTDATE ASC, EMPLOYEE ASC
</sql-query>

1) If that's not currently posible where would be a good class in hibernate source to look to implement that change. Also what would be your recommendations for syntax as opposed to {filter: myFilter}

2) is there a <hql-query equivalent to <sql-query. If so could you provide a quick example..

BTW remember I'm the guy who regularly contribs to opensource projects :-)

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

To give a more complete example...

Code:

<hibernate-mapping package="xxx.business.domain">
   <filter-def name="secure">
      <filter-param name="accessTypeId type="string"/>
      <filter-param name="typeSpecialityId1" type="integer"/>
      <filter-param name="typeSpecialityId2" type="integer"/>
      <filter-param name="pinActivated" type="string"/>
      <filter-param name="roleId1"  type="long"/>
      <filter-param name="roleId2"  type="long"/>
      <filter-param name="networkPlanId"  type="long"/>
   </filter-def>


   <class
      name="SecureHomeContent"
      table="XXX_SECURE_HOME_CONTENT"
   >
      <id
         name="contentId"
         type="integer"
         column="SECURE_HOME_CONTENT_ID"
      >
         <generator class="vm"/>
      </id>
      <set name="contentItems" 
           inverse="true" 
           lazy="false"
           cascade="all"
       >
         <key column="SECURE_HOME_CONTENT_ID"/>
         <one-to-many class="SecureHomeContentItem"/>
         <loader query-ref="secureContentItemsSql"/>

      </set>
   </class>   
      <sql-query name="secureContentItemsSql">
       <load-collection alias="A" role="SecureHomeContent.contentItems"/>
    
       SELECT {A.*}
              FROM SecureHomeContentItem A, SecureHomeItemRel B
              WHERE {B.primaryKey.contentId} = {A.contentId}
              AND {B.primaryKey.itemId} = {A.itemId}
              AND {A.contentId} = :contentId
              {filter:secure:
                    AND {A.accessTypeId} IN (:accessTypeId, 'Both')
                    AND {B.primaryKey.typeSpecialityId} IN (:typeSpecialityId1, :typeSpecialityId2)
                    AND {A.pinActivated} IN ('N', :pinActivated)
                    AND {B.primaryKey.roleId} IN (:roleId1, :roleId2)
                    AND {B.primaryKey.networkPlanId} IN (:networkPlanId1, :networkPlanId2)
              }
              {filter:notSecure:
                   AND {A.pinActivated} IN ('N', :pinActivated)
              }
   </sql-query>
</hibernate-mapping>

max · **Posted:** Thu Jun 16, 2005 2:31 am

well might not be a bad idea to allow the {filter:blablah} option. (: is probably a bad separator since its used for paramters but anyhow - the idea is ok)

I dont know if you should be allowed to redefine the sql as in your second example, but anyhow - add it to jira and see what happens.

the place to look for how to implement this is in SQLQueryProcessor (from the top of my head, dont have a cvs tree to check against ,)

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

I started implementation... Maybe someone can comment and also help or point me to the place where the filter params can be substituted for the named params...

JIRA is:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-626

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

The remaining problem is that in the queryImpl the parameters get set ok but there is a problem in the parameters that are not filters...

In addition

I called initParameterBookKeeping() before applyFilters and added a new method:

private Set getFilterNamedParameters(String filterString) {
StringTokenizer st = new StringTokenizer(filterString, ParserHelper.HQL_SEPARATORS);
Set result = new HashSet();

while ( st.hasMoreTokens() ) {
String string = st.nextToken();
if( string.startsWith(ParserHelper.HQL_VARIABLE_PREFIX) ) {
result.add( string.substring(1) );
}
}

return result;
}

and in applyFilters wherre filter is != null I added

String filterFragment = hql.substring(hql.indexOf(":", start) + 1, end);
if (addParameters) {
// for each parameter in enabled filter
// add parameter to named parameter list

for (Iterator iter = getFilterNamedParameters(filterFragment).iterator(); iter.hasNext();) {
String filterParameterName = (String) iter.next();
this.setParameter(filterParameterName,session.getFilterParameterValue(filter.getName() + "." + filterParameterName));
}
}

hql.replace(start, end + 2, filterFragment );

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

in NamedQueryCollectionInitializer there are following lines...

Code:

      //TODO: is there a more elegant way than downcasting?
      AbstractQueryImpl query = (AbstractQueryImpl) session.getNamedQuery(queryName); 
      if ( query.getNamedParameters().length>0 ) {
         query.setParameter( 
               query.getNamedParameters()[0], 
               key, 
               persister.getKeyType() 
         );



      }

query.getNamedParameters() is derived from a hashmap

Code:

return (String[]) actualNamedParameters.toArray(new String[actualNamedParameters.size()]);

in org.hibernate.impl.AbstractQueryImpl

so positionally it cannot be used in this way since order is not guaranteed... I ran into problem here.

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

This appears to be bug as well...

in Loader in following code

Code:

         CollectionPersister[] collectionPersisters = getCollectionPersisters();
         for ( int j=0; j<collectionPersisters.length; j++ ) {

if named query assigned to collection returns no rows getCollectionPersisters() returns null causing collectionPersisters.length to result in an exception

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

This appears to be bug as well...

in Loader in following code

Code:

         CollectionPersister[] collectionPersisters = getCollectionPersisters();
         for ( int j=0; j<collectionPersisters.length; j++ ) {

if named query assigned to collection returns no rows getCollectionPersisters() returns null causing collectionPersisters.length to result in an exception