-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Is it possible to use filters with Custom SQL for loading?
PostPosted: Wed Jun 15, 2005 7:18 pm 
Regular
Regular

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 :-)


Top
 Profile  
 
 Post subject: To give a more complete example...
PostPosted: Wed Jun 15, 2005 7:34 pm 
Regular
Regular

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>


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 16, 2005 2:31 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
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 ,)

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject: I started implementation... and created JIRA
PostPosted: Thu Jun 16, 2005 1:18 pm 
Regular
Regular

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


Top
 Profile  
 
 Post subject: Here are additional changes...
PostPosted: Thu Jun 16, 2005 5:18 pm 
Regular
Regular

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 );


Top
 Profile  
 
 Post subject: I think there is a bug in hibernate
PostPosted: Thu Jun 16, 2005 8:27 pm 
Regular
Regular

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.


Top
 Profile  
 
 Post subject: Another bug
PostPosted: Thu Jun 16, 2005 9:25 pm 
Regular
Regular

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


Top
 Profile  
 
 Post subject: Another bug
PostPosted: Thu Jun 16, 2005 9:25 pm 
Regular
Regular

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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.