?
Solved

Hibernate hql query using list collection mapping problem

Posted on 2009-02-10
14
Medium Priority
?
12,995 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

0
Comment
Question by:TheFACTORY_Max
  • 7
  • 6
14 Comments
 
LVL 92

Expert Comment

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

0
 
LVL 10

Expert Comment

by:mahome
ID: 23598481
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

0
 
LVL 10

Expert Comment

by:mahome
ID: 23598503
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:TheFACTORY_Max
ID: 23598549
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)
0
 
LVL 10

Expert Comment

by:mahome
ID: 23598584
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

0
 

Author Comment

by:TheFACTORY_Max
ID: 23599258
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]
0
 
LVL 10

Expert Comment

by:mahome
ID: 23599317
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

0
 

Author Comment

by:TheFACTORY_Max
ID: 23599363
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}
0
 
LVL 10

Expert Comment

by:mahome
ID: 23599405
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

0
 

Author Comment

by:TheFACTORY_Max
ID: 23599613
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
0
 
LVL 10

Assisted Solution

by:mahome
mahome earned 1200 total points
ID: 23600537
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

0
 

Author Comment

by:TheFACTORY_Max
ID: 23608421
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]
0
 

Accepted Solution

by:
TheFACTORY_Max earned 0 total points
ID: 23619647
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

0
 
LVL 10

Expert Comment

by:mahome
ID: 23620174
TheFACTORY_Max, thanks for posting the solution. I'm glad you finally found a solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question