-->
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.  [ 1 post ] 
Author Message
 Post subject: HQL Select ... where array = array
PostPosted: Thu Apr 19, 2007 12:38 am 
Newbie

Joined: Mon Mar 26, 2007 7:50 am
Posts: 18
Hi All,
I ask my self how to shoot a query where in the Where-part of the select statement the column I want to select is an array and I want the select statement to give me all the User Objects where the authorities column match.

SELECT main.org.jboss.portlet.milestone.spring.security.dto.User from main.org.jboss.portlet.milestone.spring.security.dto.User where authorities = auth

auth is an array of type AccessLevel

I am using the Spring Framework an there the getHibernateTemplate().find()

Hibernate version:
3.2
Mapping documents:
Code:
<class name="main.org.jboss.portlet.milestone.spring.security.dto.User"
      table="user">
      <id column="id" name="id" type="long">
         <generator class="increment" />
      </id>
      <property name="firstname" column="firstname" type="string" not-null="true"/>
      <property name="lastname" column="lastname" type="string" not-null="true"/>
      <property name="username" column="username" type="string" not-null="true"/>
      <property name="email" column="email" type="string" not-null="true"/>
      <property name="password" column="password" type="string" not-null="true"/>
      <property name="expired" column="expired" type="boolean" />
      <property name="locked" column="locked" type="boolean" />
      <property name="enabled" column="enabled" type="boolean" />
      <property name="credentialsExpired" column="credentialsExpired" type="boolean" />
      <array name="authorities" cascade="all">
         <key column="uid" />
         <index column="idx" />
         <one-to-many class="main.org.jboss.portlet.milestone.spring.dto.AccessLevel"/>
      </array>
</class>

<class name="main.org.jboss.portlet.milestone.spring.dto.AccessLevel"
      table="accesslevel">
      <id column="id" name="id" unsaved-value="0"><!-- Hier type nicht setzen -->
         <generator class="increment" />
      </id>
      <property name="authority" column="authority" type="string" not-null="true"/>
   </class>



so when i Try this:

Code:
AccessLevel[] al =  new AccessLevel[1];
      al[0] = new AccessLevel(role, null);
      List userInLevel = getHibernateTemplate().
         find("from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ", al);


I get the following error

Code:
2007-04-19 10:00:51,859 DEBUG [main.org.jboss.portlet.milestone.jsf.bean.AccessLevelBean] - Entering AccessLevelBean.getUserList
2007-04-19 10:00:51,859 DEBUG [main.org.jboss.portlet.milestone.jsf.bean.AccessLevelBean] - userList is null, requesting userList from DB...
2007-04-19 10:00:51,859 DEBUG [main.org.jboss.portlet.milestone.jsf.facade.FacadeService] - receiving mileStoneService...
2007-04-19 10:00:51,859 DEBUG [main.org.jboss.portlet.milestone.spring.persistence.AccessLevelManager] - Entering AccessLevelManager.getUserInAccessLevel(String role)
2007-04-19 10:00:51,859 DEBUG [main.org.jboss.portlet.milestone.spring.dto.AccessLevel] - Entring AccessLevel Constructor(...)
2007-04-19 10:00:51,859 DEBUG [org.hibernate.impl.SessionImpl] - opened session at timestamp: 11769570518
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] - parse() - HQL: from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ?
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.AST] - --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'from'
    |     \-[RANGE] 'RANGE'
    |        +-[DOT] '.'
    |        |  +-[DOT] '.'
    |        |  |  +-[DOT] '.'
    |        |  |  |  +-[DOT] '.'
    |        |  |  |  |  +-[DOT] '.'
    |        |  |  |  |  |  +-[DOT] '.'
    |        |  |  |  |  |  |  +-[DOT] '.'
    |        |  |  |  |  |  |  |  +-[DOT] '.'
    |        |  |  |  |  |  |  |  |  +-[IDENT] 'main'
    |        |  |  |  |  |  |  |  |  \-[IDENT] 'org'
    |        |  |  |  |  |  |  |  \-[IDENT] 'jboss'
    |        |  |  |  |  |  |  \-[IDENT] 'portlet'
    |        |  |  |  |  |  \-[IDENT] 'milestone'
    |        |  |  |  |  \-[IDENT] 'spring'
    |        |  |  |  \-[IDENT] 'security'
    |        |  |  \-[IDENT] 'dto'
    |        |  \-[IDENT] 'User'
    |        \-[ALIAS] 'user'
    \-[WHERE] 'where'
       \-[EQ] '='
          +-[DOT] '.'
          |  +-[IDENT] 'user'
          |  \-[IDENT] 'authorities'
          \-[PARAM] '?'

2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.ErrorCounter] - throwQueryException() : no errors
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.antlr.HqlSqlBaseWalker] - select << begin [level=1, statement=select]
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.FromElement] - FromClause{level=1} :  main.org.jboss.portlet.milestone.spring.security.dto.User (user) -> user0_
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.FromReferenceNode] - Resolved :  user -> user0_.id
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.DotNode] - getDataType() : authorities -> org.hibernate.type.ArrayType(main.org.jboss.portlet.milestone.spring.security.dto.User.authorities)
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.FromElementFactory] - createEntityAssociation() : One to many - path = user.authorities role = main.org.jboss.portlet.milestone.spring.security.dto.User.authorities associatedEntityName = main.org.jboss.portlet.milestone.spring.dto.AccessLevel
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.FromElement] - FromClause{level=1} :  main.org.jboss.portlet.milestone.spring.dto.AccessLevel (no alias) -> authoritie1_
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.FromClause] - addJoinByPathMap() : user.authorities -> accesslevel authoritie1_
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.DotNode] - dereferenceCollection() : Created new FROM element for user.authorities : accesslevel authoritie1_
2007-04-19 10:00:51,875 DEBUG [org.hibernate.hql.ast.tree.FromReferenceNode] - Resolved :  user.authorities -> .
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.antlr.HqlSqlBaseWalker] - select : finishing up [level=1, statement=select]
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.HqlSqlWalker] - processQuery() :  ( SELECT ( FromClause{level=1} user user0_ accesslevel authoritie1_ ) ( where ( = ( . user0_.id authorities ) ? ) ) )
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.HqlSqlWalker] - Derived SELECT clause created.
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.util.JoinProcessor] - Using FROM fragment [user user0_]
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.util.SyntheticAndFactory] - Using WHERE fragment [user0_.id=authoritie1_.uid]
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.antlr.HqlSqlBaseWalker] - select >> end [level=1, statement=select]
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.AST] - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (accesslevel,user)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'user0_.id as id1_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=user,role=null,tableName=user,tableAlias=user0_,origin=null,colums={,className=main.org.jboss.portlet.milestone.spring.security.dto.User}}}
    |  \-[SQL_TOKEN] SqlFragment: 'user0_.firstname as firstname1_, user0_.lastname as lastname1_, user0_.username as username1_, user0_.email as email1_, user0_.password as password1_, user0_.expired as expired1_, user0_.locked as locked1_, user0_.enabled as enabled1_, user0_.credentialsExpired as credent10_1_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[user], fromElementByTableAlias=[authoritie1_, user0_], fromElementsByPath=[user.authorities], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  +-[FROM_FRAGMENT] FromElement: 'user user0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=user,role=null,tableName=user,tableAlias=user0_,origin=null,colums={,className=main.org.jboss.portlet.milestone.spring.security.dto.User}}
    |  \-[FROM_FRAGMENT] ImpliedFromElement: 'accesslevel authoritie1_' ImpliedFromElement{implied,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=main.org.jboss.portlet.milestone.spring.security.dto.User.authorities,tableName=accesslevel,tableAlias=authoritie1_,origin=user user0_,colums={user0_.id ,className=main.org.jboss.portlet.milestone.spring.dto.AccessLevel}}
    \-[WHERE] SqlNode: 'where'
       +-[THETA_JOINS] SqlNode: '{theta joins}'
       |  \-[SQL_TOKEN] SqlFragment: 'user0_.id=authoritie1_.uid'
       \-[EQ] BinaryLogicOperatorNode: '='
          +-[DOT] DotNode: '.' {propertyName=authorities,dereferenceType=3,propertyPath=authorities,path=user.authorities,tableAlias=authoritie1_,className=main.org.jboss.portlet.milestone.spring.dto.AccessLevel,classAlias=null}
          |  +-[ALIAS_REF] IdentNode: 'user0_.id' {alias=user, className=main.org.jboss.portlet.milestone.spring.security.dto.User, tableAlias=user0_}
          |  \-[IDENT] IdentNode: 'authorities' {originalText=authorities}
          \-[PARAM] ParameterNode: '?' {ordinal=0, expectedType=org.hibernate.type.ArrayType(main.org.jboss.portlet.milestone.spring.security.dto.User.authorities)}

2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.ErrorCounter] - throwQueryException() : no errors
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] - HQL: from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ?
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] - SQL: select user0_.id as id1_, user0_.firstname as firstname1_, user0_.lastname as lastname1_, user0_.username as username1_, user0_.email as email1_, user0_.password as password1_, user0_.expired as expired1_, user0_.locked as locked1_, user0_.enabled as enabled1_, user0_.credentialsExpired as credent10_1_ from user user0_, accesslevel authoritie1_ where user0_.id=authoritie1_.uid and .=?
2007-04-19 10:00:51,890 DEBUG [org.hibernate.hql.ast.ErrorCounter] - throwQueryException() : no errors
2007-04-19 10:00:51,890 ERROR [org.jboss.portal.server.servlet.CommandFilter] - Exception in command invocation
org.apache.jasper.JasperException: Cannot get value for expression '#{accessLevelBean.userList}'
   at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)
   at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:393)
   at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
   at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.jboss.portal.portlet.impl.jsr168.PortletRequestDispatcherImpl.execute(PortletRequestDispatcherImpl.java:85)
   at sun.reflect.GeneratedMethodAccessor366.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.jboss.portal.server.servlet.CommandFilter.doFilter(CommandFilter.java:66)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
   at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
   at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:574)
...
Caused by: org.springframework.orm.hibernate3.HibernateQueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]; nested exception is org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]
   at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:647)
   at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:413)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:371)
   at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:822)
   at main.org.jboss.portlet.milestone.spring.persistence.AccessLevelManager.getUsersInAccessLevel(Unknown Source)
   at main.org.jboss.portlet.milestone.jsf.bean.AccessLevelBean.getUserList(Unknown Source)
   ... 288 more
Caused by: org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]
   at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:319)
   at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:275)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:75)
   at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:831)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
   ... 291 more
2007-04-19 10:00:51,953 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[localhost].[/MileStoneBeta]] - Root cause: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]; nested exception is org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]
org.springframework.orm.hibernate3.HibernateQueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]; nested exception is org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]
Caused by:
org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [main.org.jboss.portlet.milestone.spring.dto.AccessLevel@199457f] [from main.org.jboss.portlet.milestone.spring.security.dto.User user where user.authorities = ? ]
   at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:319)
   at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:275)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:75)
   at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:831)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
   at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:822)
   at main.org.jboss.portlet.milestone.spring.persistence.AccessLevelManager.getUsersInAccessLevel(Unknown Source)
   at main.org.jboss.portlet.milestone.jsf.bean.AccessLevelBean.getUserList(Unknown Source)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)

Any Idea?

thanks in advance,

Omid


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

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.