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