We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Hibernate hql query using list collection mapping problem

TheFACTORY_Max
on
Medium Priority
14,237 Views
Last Modified: 2012-05-06
Hello everyone,

What I am trying to achieve is to use a HQL query to select objects, the problems start when I want to set a where condition which uses a List collection map.

the used hibernate hbm and java beans are in the code section.


The current (not working) query goes:

golfertype = new GolferType();
golfertype.setId((short)3);
getHibernateTemplate().find("select startTimeHours, startTimeMinutes from BookException where bookExceptionGolferTypes = ?", golfertype)


This below does work (so it is caused by the collection mapping used in the where query):
getHibernateTemplate().find("select startTimeHours, startTimeMinutes from BookException")

The given error is:
SEVERE: Servlet.service() for servlet action threw exception
org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [com.GolfOnline.Beans.GolferType@7b1d52] [select startTimeHours, startTimeMinutes from BookException where bookExceptionGolferTypes = ?]

I have no idea how to do this..

Thanks, Max
The following relevant hibernate hbm's are being used:
 
----------------------------------------- BookException.hbm
<hibernate-mapping>
    <class name="com.GolfOnline.Beans.BookException" table="tbl_BookException" lazy="false">
        
        <id name="id" column="be_ID" type="short">
            <generator class="native"/>
        </id>
        
        <list name="bookExceptionGolferTypes" table="tbl_BookExceptionGolferType" lazy="false" inverse="true">
            <key column="eg_BookException_be_ID"/>
            <list-index base="0" column="eg_ID"/>
            <composite-element class="com.GolfOnline.Beans.BookExceptionGolferType"> 
                <many-to-one class="com.GolfOnline.Beans.GolferType" column="eg_GolferType_gt_ID" name="golferType"/>
                <property name="fee" column="eg_Fee" type="integer" />
                <property name="id" column="eg_Id" type="integer" />
            </composite-element>
        </list>
        <property name="startTimeHours" column="be_StartTimeHours"/>
        <property name="startTimeMinutes" column="be_StartTimeMinutes"/>
    </class>
</hibernate-mapping> 
 
----------------------------------------- BookExceptionGolferType.hbm
<hibernate-mapping>
    <class name="com.GolfOnline.Beans.BookExceptionGolferType" table="tbl_BookExceptionGolferType" lazy="false">
        
        <id name="id" column="eg_ID">
            <generator class="native"/>
        </id>
        
        <many-to-one name="bookException" column="eg_BookException_be_ID" class="com.GolfOnline.Beans.BookException"/>
        <many-to-one name="golferType" column="eg_GolferType_gt_ID" class="com.GolfOnline.Beans.GolferType"/>
        <property name="fee" column="eg_Fee"/>
        
    </class>
</hibernate-mapping> 
 
----------------------------------------- GolferType.hbm
<hibernate-mapping>
    <class name="com.GolfOnline.Beans.GolferType" table="tbl_GolferType" lazy="false">
        
        <id name="id" column="gt_ID" type="short">
            <generator class="native"/>
        </id>
        <property name="title" column="gt_Title"/>
 
    </class>
</hibernate-mapping> 
 
The following relevant java beans is being used:
 
----------------------------------------- BookException .java
 
public class BookException implements Serializable {
    
    private List bookexceptiongolfertypes = null;
    private short id;
    private String starttimehours;
    private String starttimeminutes;
    
    public BookException() {
        super();
    }
    
    public List getBookExceptionGolferTypes() {   return bookexceptiongolfertypes; }
    public short getId() {   return id;    }
    public String getStartTimeHours() {   return starttimehours;    }
    public String getStartTimeMinutes() {   return starttimeminutes;    }
    
    public void setBookExceptionGolferTypes(List bookexceptiongolfertypes) {    this.bookexceptiongolfertypes = bookexceptiongolfertypes;  }
    public void setId (short id) { this.id = id;   }
    public void setStartTimeHours (String starttimehours) { this.starttimehours = starttimehours;   }
    public void setStartTimeMinutes (String starttimeminutes) { this.starttimeminutes = starttimeminutes;   }
    
}

Open in new window

Comment
Watch Question

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
you're comparing a list to an instance by the looks,don't you mean .... contains ?

Commented:
You have to set an entity instead of a parameter:



Query query = getSession().createQuery(("select startTimeHours, startTimeMinutes from BookException where bookExceptionGolferTypes = :golferType");
query.setEntity("golferType", golferType);

Open in new window

Commented:
objects is right, try the following

Query query = getSession().createQuery(
             "select startTimeHours, startTimeMinutes from BookException " + 
             "join bookExceptionGolferTypes " + 
             "where bookExceptionGolferTypes.id = 3");

Open in new window

Author

Commented:
Objects:
Yes it should only return those which contain the given argument in the collection.

mahome:
Your last suggestion gives the following error:
javax.servlet.ServletException: org.hibernate.QueryException: illegal attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}bookExceptionGolferTypes] with element property reference [id] [select startTimeHours, startTimeMinutes from com.GolfOnline.Beans.BookException join bookExceptionGolferTypes where bookExceptionGolferTypes.id = 3]
      org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:535)

Commented:
Sorry, I forgot the alias


Query query = getSession().createQuery(
             "select startTimeHours, startTimeMinutes from BookException " +
             "join bookExceptionGolferTypes gt " +
             "where gt.id = 3");

Open in new window

Author

Commented:
Thanks, but it gives the following error now:

javax.servlet.ServletException: org.hibernate.QueryException: Unable to resolve path [gt.id], unexpected token [gt] [select startTimeHours, startTimeMinutes from com.GolfOnline.Beans.BookException join bookExceptionGolferTypes gt where gt.id = 3]

Commented:
Sorry again, try the following: (Old problem of me, I always need iterations to the correct code)

Have a look here for hql: http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#queryhql

Query query = getSession().createQuery(
             "select startTimeHours, startTimeMinutes from BookException as be " +
             "join be.bookExceptionGolferTypes as gt " +
             "where gt.id = 3");

Open in new window

Author

Commented:
Strange error this time:

javax.servlet.ServletException: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.IdentNode
 +-[IDENT] IdentNode: 'startTimeHours' {originalText=startTimeHours}

Commented:
Damn! As I've introduced an alias it must be used:
be.startTimeHours

Query query = getSession().createQuery(
             "select be.startTimeHours, be.startTimeMinutes from BookException as be " +
             "join be.bookExceptionGolferTypes as gt " +
             "where gt.id = 3");

Open in new window

Author

Commented:
We'll get there, this time it this error (from the stacktrace):

Hibernate: select bookexcept0_.be_StartTimeHours as col_0_0_, bookexcept0_.be_StartTimeMinutes as col_1_0_ from tbl_BookException bookexcept0_ inner join tbl_BookExceptionGolferType bookexcept1_ on bookexcept0_.be_ID=bookexcept1_.eg_BookException_be_ID where bookexcept1_.be_ID=3
10-Feb-2009 15:15:02 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1054, SQLState: 42S22
10-Feb-2009 15:15:02 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Unknown column 'bookexcept1_.be_ID' in 'where clause'
10-Feb-2009 15:15:02 org.apache.struts.action.RequestProcessor processException
WARNING: Unhandled Exception thrown: class org.hibernate.exception.SQLGrammarException
Commented:
Sorry, no idea left. I don't understand why it translates to bookexcept1_.be_ID instead of bookexcept1_.eg_ID

And I just saw a logical error. I checked the id of of the bookExceptionGolferType instead of GolferType, but that's just for interest. The error will possibly the same.

Query query = getSession().createQuery(
             "select be.startTimeHours, be.startTimeMinutes from BookException as be " +
             "join be.bookExceptionGolferTypes as begt " +
             "where begt.golferType.id = 3");

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
It now says something different:

org.hibernate.QueryException: could not resolve property: golferType.id of: com.GolfOnline.Beans.BookException [select be.startTimeHours, be.startTimeMinutes from com.GolfOnline.Beans.BookException as be join be.bookExceptionGolferTypes as begt where begt.golferType.id = 3]
I've found a solution for what is wrong now, using a golfertype object instead of the id of the object does work. See code snippet for the working code.

Mahome, thanks for you efforts and getting me really close to the solution.
        golfertype = new GolferType();
        golfertype.setId((short)3);
 
        getHibernateTemplate().find(
                     "select be.startTimeHours, be.startTimeMinutes from BookException as be " +
                     "join be.bookExceptionGolferTypes as begt " +
                     "where begt.golferType = ?", golfertype);

Open in new window

Commented:
TheFACTORY_Max, thanks for posting the solution. I'm glad you finally found a solution.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.