-->
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 2
  [ 18 posts ]  Go to page 1, 2  Next
  Previous topic | Next topic 
Author Message
Sapsi
 Post subject: Select collections with HQL
PostPosted: Wed Oct 22, 2008 5:53 am 
Newbie

Joined: Wed Oct 22, 2008 4:21 am
Posts: 6
Hi,
I have the following classes:

Code:
class Person
{
   String name;
   Set<Hotel> visitedHotels;
   String someOtherData;

   public Person()
   {
   }
   public Person (   String name,    Set<Hotel> visitedHotels )
   {
      this.name;
      this.visitedHotels = this. visitedHotels;
   }
}

class Hotel
{...}


For security reasons "someOtherData" should sometimes not be loaded.

So I tried the following HQL:
Code:
select new Person( p.name , elements(p.visitedHotels) ) from Person p
OR
Code:
select new Person( p.name , hotels ) from Person p left join p.visitedHotels hotels

But it doesn’t work - error: Unable to locate appropriate constructor on class Person

So I tried
Code:
select new list( p.name , elements(p.visitedHotels) ) from Person p
which delivered a separate row for each hotel of a person.

Is there a possibility to select the collection of hotels together with the person name (maybe also lazy)?
Top
 Profile  
 
jasonfly
 Post subject: where are your getters and setters?
PostPosted: Wed Oct 22, 2008 5:59 am 
Newbie

Joined: Tue Sep 16, 2008 4:36 am
Posts: 11
Hi,
If I am not mistaken, Hibernate needs its getters and setters to access an object data.

It should work if you add getElements() for your Person class.

I hope it helps
Top
 Profile  
 
Sapsi
 Post subject:
PostPosted: Wed Oct 22, 2008 6:07 am 
Newbie

Joined: Wed Oct 22, 2008 4:21 am
Posts: 6
No sorry, I have added the setter / getters. (I only left them out in the example)

Code:
public Set<Hotel> getVisitedHotels()
    {
        return visitedHotels;
    }

    public void setVisitedHotels(Set<Hotel> visitedHotels)
    {
        this.visitedHotels = visitedHotels;
    }

public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }
Top
 Profile  
 
jasonfly
 Post subject: second idea
PostPosted: Wed Oct 22, 2008 8:17 am 
Newbie

Joined: Tue Sep 16, 2008 4:36 am
Posts: 11
I see....

I have another idea, let's see if it works.

have you tried this way:

select new Person( p.name , p.visitedHotels ) from Person p

It could be that elements(...) returns a Collection or a List but not a Set.

Try this and let me know
Top
 Profile  
 
Sapsi
 Post subject:
PostPosted: Wed Oct 22, 2008 8:30 am 
Newbie

Joined: Wed Oct 22, 2008 4:21 am
Posts: 6
Unfortunately this does not work:
Hibernate creates this SQL-Query out of "select new Person( p.name , p.visitedHotels ) from Person p "
Code:
select person0_.name as col_0_0_, . as col_1_0_
from Person person0_
inner join Hotel_2_Person visitedhot1_ on person0_.personId=visitedhot1_.guest_PersonId
inner join Hotel hotel2_ on visitedhot1_.visited_HotelId=hotel2_.hotleId


The problem on this query is the "." (before "as col_1_0_
from Person person0_") that was createt for the "p.visitedHotels" in the HQL expression. So no valid SQL was created by hibernate :-(
Top
 Profile  
 
Sapsi
 Post subject:
PostPosted: Wed Oct 22, 2008 8:32 am 
Newbie

Joined: Wed Oct 22, 2008 4:21 am
Posts: 6
Unfortunately this does not work:
Hibernate creates this SQL-Query out of "select new Person( p.name , p.visitedHotels ) from Person p "
Code:
select person0_.name as col_0_0_, . as col_1_0_
from Person person0_
inner join Hotel_2_Person visitedhot1_ on person0_.personId=visitedhot1_.guest_PersonId
inner join Hotel hotel2_ on visitedhot1_.visited_HotelId=hotel2_.hotleId


The problem on this query is the "." (before "as col_1_0_
from Person person0_") that was createt for the "p.visitedHotels" in the HQL expression. So no valid SQL was created by hibernate :-(
Top
 Profile  
 
jasonfly
 Post subject: last idea
PostPosted: Wed Oct 22, 2008 8:36 am 
Newbie

Joined: Tue Sep 16, 2008 4:36 am
Posts: 11
I see the problem.
For what I understand, if you create the query like this:

Code:
select new Person( p.name , elements(p.visitedHotels) ) from Person p


it works but Hibernate doesn't find the Constructor

and if you do it as I told you, the SQL is invalid.

Maybe another idea:

select new Person( p.name , new HashSet(elements(p.visitedHotels)) ) from Person p

This way you are sure you are getting the good parameters for your constructor.
Top
 Profile  
 
Sapsi
 Post subject: Re: last idea
PostPosted: Wed Oct 22, 2008 8:48 am 
Newbie

Joined: Wed Oct 22, 2008 4:21 am
Posts: 6
jasonfly wrote:
Code:
select new Person( p.name , new HashSet(elements(p.visitedHotels)) ) from Person p

No this creates an Systax-Exception in Hibernate: "unexpected token: , near line 1, column 25"

To descripe my problem better:

This query
Code:
select new Person( p.name , elements(p.visitedHotels) ) from Person p

is valid if I add the following constructor to class Person
Code:
public Person(   String name, Hotel visitedHotel )
{
this.name=name;
this.visitedHotels =  new HashSet<Hotel>();
this.visitedHotels.add(visitedHotel);
}

But so I get the same person for each hotel that the person visited. But I want all hotels as collection.
Top
 Profile  
 
jasonfly
 Post subject: I think now I know what you want to do
PostPosted: Wed Oct 22, 2008 9:11 am 
Newbie

Joined: Tue Sep 16, 2008 4:36 am
Posts: 11
Ok,

So I see the problem.

It is very strange that
Code:
select new Person( p.name , hotels ) from Person p left join p.visitedHotels hotels

doesn't work because according to the documentation:

Code:
select new Family(mother, mate, offspr)
from DomesticCat as mother
    join mother.mate as mate
    left join mother.kittens as offspr


[url]http://www.hibernate.org/hib_docs/v3/reference/en-US/html/queryhql-
select.html[/url]

works.

For what I see here, Hibernate doesn't find the appropriate Constructor (which is very strange). The only advice I can give you is to try to create a Constructor like this:

Code:
Person(String name, Object test){
/* put here something and add a breakpoint to see the type of the second parameter */
}


I wish I could help you more but I really don't see what the problem can be
Top
 Profile  
 
Sapsi
 Post subject:
PostPosted: Wed Oct 22, 2008 9:27 am 
Newbie

Joined: Wed Oct 22, 2008 4:21 am
Posts: 6
I have found the example
Code:
select new Family(mother, mate, offspr)
from DomesticCat as mother
    join mother.mate as mate
    left join mother.kittens as offspr
in the documentation and tried it and it only work if family has the following constructor:
Code:
public Family( Cat mother, Cat mate, Cat offspr )

No lists or sets :-(
Top
 Profile  
 
P.J.
 Post subject:
PostPosted: Sat Oct 25, 2008 10:00 am 
Newbie

Joined: Sat Oct 25, 2008 9:19 am
Posts: 1
I think I have the same problem:

A query like this:

    Code:
    SELECT new list(person, emails, COUNT(emails)) FROM Person LEFT OUTER JOIN Person.emails GROUP BY Person
with following data:
    Code:
    Person 1: Mark Twain, (mark@twain.com, happymark@server.org)
    Person 2: Bill Gates, (bill@microsoft.com)
    Person 3: Linus Torvalds, (linus@home.cc)
    Person 4: Wolfgang A. Mozart, ()
returns this "incorrect" result:

    Code:
    Row 1: Mark Twain, mark@twain.com, 2
    Row 2: Bill Gates, bill@microsoft.com, 1
    Row 3: Linus Torvalds, linus@home.cc, 1
    Row 4: Wolfgang A. Mozart, null, 0


In my opinion column #2 should be a list not a single value.
Person in row #1 has 2 mail addresses, how to get the second one?
Top
 Profile  
 
radrad
 Post subject:
PostPosted: Wed Nov 19, 2008 1:11 pm 
Newbie

Joined: Fri Feb 08, 2008 10:10 am
Posts: 1
I've just hit exactly the same problem, did anyone here find a way around it? Is this a problem with hibernate, the documentation or me??
Top
 Profile  
 
ap_kelly
 Post subject: Re: Select collections with HQL
PostPosted: Wed May 20, 2009 4:02 am 
Newbie

Joined: Wed May 20, 2009 3:52 am
Posts: 2
I've just hit exactly the same problem, did anyone here find a way around it?

In my case I have a list of topics for a book, for books that have more than one topic I get multiple rows back instead of just 1 row. My constructor is currently set to accept a single Topic as a parameter as that is the only way I get get it to work, I've tried using Object[] and Set<Topic> as well without success.

<query name="findBookListByTopic">
<![CDATA[
select new BookListRow (
book.bookId,
book.title,
book.isbn,
book.author,
publisher.publisherName,
book.yearPublished,
book.annotations,
book.series.seriesId,
book.positionInSeries,
elements(book.topics))
from Book as book, BookTopic topic
left outer join book.publisher as publisher
left outer join book.listType as listType
where book.challengeYear <= :challengeYear
and listType.publicFacing = 'Y'
and topic.topicId = :topic
and topic in elements(book.topics)
and book.rejectionReason IS NULL
order by lower(book.title), lower(book.author)
]]>
</query>


Cheers,

Andy
Top
 Profile  
 
ap_kelly
 Post subject: Re: Select collections with HQL
PostPosted: Tue Jul 14, 2009 9:35 pm 
Newbie

Joined: Wed May 20, 2009 3:52 am
Posts: 2
Can anyone from the Hibernate team provide a working example?

I'm using Hibernate v3.2.5

Thanks,

Andy.
Top
 Profile  
 
prateek.singh
 Post subject: Re: Select collections with HQL
PostPosted: Wed Jul 15, 2009 10:15 am 
Beginner
Beginner

Joined: Wed Jun 17, 2009 9:03 pm
Posts: 31
Location: mumbai
Code:
select new Person( p.name , elements(p.visitedHotels) ) from Person p
or
select new Person( p.name , hotels ) from Person p left join p.visitedHotels hotels
will result in appropriate constructor on class Person error as the Object getting passed in
Code:
public Person (   String name,    Set<Hotel> visitedHotels )
   {
      this.name;
      this.visitedHotels = this. visitedHotels;
   }


Constructor second parameter will be of type Hotel and not set of hotels . Need to create a set of hotel type from the result in your code only. Although not very elegant solution this will work
and load the person and hotel in same query.

Code:
<sql-query name="loadPerson">
       <return alias="p" class="com.forum.Person"/>
       <return-join alias="h" property="p.visitedHotels"/>
       select
           {p.*}, {h.*}
       from
           person p
       left outer join hotel h
           on p.name = h.personid
       where
           p.name = ?
   </sql-query>


l_qur     =   l_sess.getNamedQuery("loadPerson");
      l_qur.setString(0, "xyz");
      l_persons      =   l_qur.list();
      l_itr         =   l_persons.iterator();
      
      while (l_itr.hasNext()) {
         Object[] row = (Object[]) l_itr.next();
         Person per = (Person)row[0]);
         Hotel hotel  =  (Hotel)row[1]);  // adding this  in set of hotels
         }
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 2
  [ 18 posts ]  Go to page 1, 2  Next

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.