-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
Pulseammo
 Post subject: *BUG* HQL "in" seems to do "not in"
PostPosted: Sun Feb 17, 2008 6:41 pm 
Newbie

Joined: Fri Jan 25, 2008 8:00 am
Posts: 11
I think I've found a bug, skip to the fourth post in this thread please.

Hello all perhaps someone can help me with this:

Say I have a class User which has a Set<String> roles:

Code:
public class User {

   private Set<String> mRoles = new HashSet<String>();
   
   public Set<String> getRoles() {
      return roles;
   }
   
   public void setRoles(Set<String> roles) {
      mRoles = roles;
   }
   
}


What I want to do is find all users with a set of given roles. I thought that this: would solve the problem, i.e. Restrictions.in should search the roles set on User with the roles string array that I passed.

Code:
public List<User> findUsers(String[] roles) {
   return
   session.createCriteria("User")
   .add(Restrictions.in("roles", roles))
   .list()
}


However I get the return of:
java.sql.SQLException: No value specified for parameter 1

The roles array that I am passing definitely has values. How can I search through this set of strings against another array of strings? Can anyone help me?

Thanks in advance.


Last edited by Pulseammo on Mon Feb 18, 2008 6:48 am, edited 5 times in total.

Top
 Profile  
 
Pulseammo
 Post subject:
PostPosted: Sun Feb 17, 2008 6:59 pm 
Newbie

Joined: Fri Jan 25, 2008 8:00 am
Posts: 11
From the FAQ:

Quote:
Hibernate currently does not support joining a collection of components or other value types with Criteria. Use HQL or submit a patch that implements this functionality.


Just found that, I guess that means I have to use HQL instead of a criteria query then.

Ok, to follow this up:

How do I do this in HQL :/.
Top
 Profile  
 
Pulseammo
 Post subject:
PostPosted: Sun Feb 17, 2008 7:23 pm 
Newbie

Joined: Fri Jan 25, 2008 8:00 am
Posts: 11
Ok clearly it should be:

Code:
FROM User user
WHERE 'Role' in elements(user.roles)


Yet even though one of my elements is 'Role' this yields zero returns. At least I'm getting somewhere though ;). (Just thought I'd keep the thread up to day incase anyone else comes across this problem).

Edit:
Executing the SQL generated from the HQL directly against the database using PHP myadmin returns the correct row. . .
Top
 Profile  
 
Pulseammo
 Post subject:
PostPosted: Sun Feb 17, 2008 7:52 pm 
Newbie

Joined: Fri Jan 25, 2008 8:00 am
Posts: 11
Ok, something is seriously wrong here.

If I change "in" to "not in" the correct SQL is generated (for a not in statement), running the "not in" version against my database through PHP myadmin yields zero results, yet using the hibernate tools query builder, it retrieves the correct rows.

It's almost as if the in and not in statements get turned around.

I think this is a bug, anyone else encountered this?
Top
 Profile  
 
Pulseammo
 Post subject:
PostPosted: Mon Feb 18, 2008 6:45 am 
Newbie

Joined: Fri Jan 25, 2008 8:00 am
Posts: 11
A more complete bug report:

Tested with Hibernate 3.2.5 & 3.2.6 + MySQL 5.0.32:

in the following example "RoleNotification" has a Set of Strings (Set<String>) called "notificationRoles".

HQL:

Code:
// Find all role notifications that have Supervisor within their notificationRoles.
"FROM RoleNotification WHERE 'Supervisor' IN elements(notificationRoles)"


AST DUMP:

Code:
10:33:21,687 DEBUG AST:266 - --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'FROM'
    |     \-[RANGE] 'RANGE'
    |        \-[IDENT] 'RoleNotification'
    \-[WHERE] 'WHERE'
       \-[IN] 'in'
          +-[QUOTED_STRING] ''Supervisor''
          \-[IN_LIST] 'inList'
             \-[ELEMENTS] 'elements'
                \-[IDENT] 'notificationRoles'


Code:
10:33:21,750 DEBUG AST:232 - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (pm_notifications_role,pm_notifications,pm_notifications_role_roles)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'rolenotifi0_.notification_id as id13_' {FromElement{explicit,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=pm_notifications_role,tableAlias=rolenotifi0_,origin=null,colums={,className=RoleNotification}}}
    |  \-[SQL_TOKEN] SqlFragment: 'rolenotifi0_1_.time as time13_, rolenotifi0_1_.priority as priority13_, rolenotifi0_1_.message as message13_'
    +-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=1, fromElements=2, fromElementByClassAlias=[], fromElementByTableAlias=[notificati1_, rolenotifi0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[{synthetic-alias}.notificationRoles], impliedElements=[]}
    |  +-[FROM_FRAGMENT] FromElement: 'pm_notifications_role rolenotifi0_ inner join pm_notifications rolenotifi0_1_ on rolenotifi0_.notification_id=rolenotifi0_1_.id' FromElement{explicit,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=pm_notifications_role,tableAlias=rolenotifi0_,origin=null,colums={,className=RoleNotification}}
    |  \-[FROM_FRAGMENT] ImpliedFromElement: '' ImpliedFromElement{implied,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=RoleNotification.notificationRoles,tableName={none},tableAlias=notificati1_,origin=pm_notifications_role rolenotifi0_ inner join pm_notifications rolenotifi0_1_ on rolenotifi0_.notification_id=rolenotifi0_1_.id,colums={,className=null}}
    \-[WHERE] SqlNode: 'WHERE'
       \-[IN] InLogicOperatorNode: 'in'
          +-[QUOTED_STRING] LiteralNode: ''Supervisor''
          \-[IN_LIST] SqlNode: 'inList'
             \-[SQL_TOKEN] CollectionFunction: 'select notificati1_.role_name from pm_notifications_role_roles notificati1_ where rolenotifi0_.notification_id=notificati1_.notification_id' {method=elements,selectColumns=[select notificati1_.role_name from pm_notifications_role_roles notificati1_ where rolenotifi0_.notification_id=notificati1_.notification_id],fromElement=notificati1_}
                \-[DOT] DotNode: '{non-qualified-property-ref}' {propertyName=notificationRoles,dereferenceType=3,propertyPath=notificationRoles,path={synthetic-alias}.notificationRoles,tableAlias=notificati1_,className=null,classAlias=null}
                   +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
                   \-[IDENT] IdentNode: 'notificationRoles' {originalText=notificationRoles}


SQL Executed:

Code:
SELECT rolenotifi0_.notification_id AS id13_, rolenotifi0_1_.time AS time13_, rolenotifi0_1_.priority AS priority13_, rolenotifi0_1_.message AS message13_
FROM pm_notifications_role rolenotifi0_
INNER JOIN pm_notifications rolenotifi0_1_ ON rolenotifi0_.notification_id = rolenotifi0_1_.id
WHERE 'Supervisor'
IN (

SELECT notificati1_.role_name
FROM pm_notifications_role_roles notificati1_
WHERE rolenotifi0_.notification_id = notificati1_.notification_id
)


Correctly returns 1 row through PHP myadmin, yet creates zero objects through hibernate.

Now with the other query ("not in"):

HQL:

Code:
// Find all role notifications that have Supervisor within their notificationRoles.
"FROM RoleNotification WHERE 'Supervisor' NOT IN elements(notificationRoles)"


AST DUMP:

Code:
10:41:32,843 DEBUG AST:266 - --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'FROM'
    |     \-[RANGE] 'RANGE'
    |        \-[IDENT] 'RoleNotification'
    \-[WHERE] 'WHERE'
       \-[NOT_IN] 'not in'
          +-[QUOTED_STRING] ''Supervisor''
          \-[IN_LIST] 'inList'
             \-[ELEMENTS] 'elements'
                \-[IDENT] 'notificationRoles'


Code:
10:41:32,921 DEBUG AST:232 - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (pm_notifications_role,pm_notifications,pm_notifications_role_roles)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'rolenotifi0_.notification_id as id13_' {FromElement{explicit,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=pm_notifications_role,tableAlias=rolenotifi0_,origin=null,colums={,className=RoleNotification}}}
    |  \-[SQL_TOKEN] SqlFragment: 'rolenotifi0_1_.time as time13_, rolenotifi0_1_.priority as priority13_, rolenotifi0_1_.message as message13_'
    +-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=1, fromElements=2, fromElementByClassAlias=[], fromElementByTableAlias=[notificati1_, rolenotifi0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[{synthetic-alias}.notificationRoles], impliedElements=[]}
    |  +-[FROM_FRAGMENT] FromElement: 'pm_notifications_role rolenotifi0_ inner join pm_notifications rolenotifi0_1_ on rolenotifi0_.notification_id=rolenotifi0_1_.id' FromElement{explicit,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=pm_notifications_role,tableAlias=rolenotifi0_,origin=null,colums={,className=RoleNotification}}
    |  \-[FROM_FRAGMENT] ImpliedFromElement: '' ImpliedFromElement{implied,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=RoleNotification.notificationRoles,tableName={none},tableAlias=notificati1_,origin=pm_notifications_role rolenotifi0_ inner join pm_notifications rolenotifi0_1_ on rolenotifi0_.notification_id=rolenotifi0_1_.id,colums={,className=null}}
    \-[WHERE] SqlNode: 'WHERE'
       \-[NOT_IN] InLogicOperatorNode: 'not in'
          +-[QUOTED_STRING] LiteralNode: ''Supervisor''
          \-[IN_LIST] SqlNode: 'inList'
             \-[SQL_TOKEN] CollectionFunction: 'select notificati1_.role_name from pm_notifications_role_roles notificati1_ where rolenotifi0_.notification_id=notificati1_.notification_id' {method=elements,selectColumns=[select notificati1_.role_name from pm_notifications_role_roles notificati1_ where rolenotifi0_.notification_id=notificati1_.notification_id],fromElement=notificati1_}
                \-[DOT] DotNode: '{non-qualified-property-ref}' {propertyName=notificationRoles,dereferenceType=3,propertyPath=notificationRoles,path={synthetic-alias}.notificationRoles,tableAlias=notificati1_,className=null,classAlias=null}
                   +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
                   \-[IDENT] IdentNode: 'notificationRoles' {originalText=notificationRoles}


SQL Executed:
Code:
SELECT rolenotifi0_.notification_id AS id13_, rolenotifi0_1_.time AS time13_, rolenotifi0_1_.priority AS priority13_, rolenotifi0_1_.message AS message13_
FROM pm_notifications_role rolenotifi0_
INNER JOIN pm_notifications rolenotifi0_1_ ON rolenotifi0_.notification_id = rolenotifi0_1_.id
WHERE 'Supervisor' NOT
IN (

SELECT notificati1_.role_name
FROM pm_notifications_role_roles notificati1_
WHERE rolenotifi0_.notification_id = notificati1_.notification_id
)


Correctly returns 0 zeros through PHP myadmin, yet creates the object in hibernate.

Can I *please* get a reply on this, is this a bug, or is my HQL wrong? I'm hoping my work around holds.

Thanks again.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.