Hibernate Community • View topic - Emulating Expression.In with Collection and HQL (urgent!)

View unanswered posts | View active topics

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

Emulating Expression.In with Collection and HQL (urgent!)

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

bLaDe00

Post subject: Emulating Expression.In with Collection and HQL (urgent!)

Posted: Sat Aug 09, 2008 1:45 pm

Newbie

Joined: Thu Jul 24, 2008 10:13 am
Posts: 17

Hello, I'm trying to emulate the Expression.In with a collection of objects ( java.util.List in this case ) but I can't do it correctly. One more problem to add to this situation is that I need to do it in a inheritance context.

To explain the problem, I'll show the code and the context:

Suppose that we have an entity named Researcher which represents an author of a publication. So, we have different kind of publications: Book, Book Chapters ... To represent this (and simplify the situation), we create an inheritance like:

Publication (superclass)
|
Book (subclass)

Publication Entity

Code:

@Entity
@Table(name="publication")
@Inheritance(strategy=InheritanceType.JOINED)
@SuppressWarnings("serial")
public abstract class Publication implements Serializable
{

   @Id
   @Column(name="pub_key") private String pubKey;
   @Column(name="pub_isbn") private String ISBN;
   @Column(name="pub_title") private String title;
   @Column(name="pub_year") private int year;
   @Column(name="pub_loc") private String location;
   @Column(name="pub_edit") private String editorial;
   
   @OneToOne
   @JoinColumn(name="pub_subj") private PublicationSubject subject;
   
   @Column(name="pub_file") private String file;
   @Column(name="pub_prsnt") private String presentation;
   @Column(name="pub_book") private String book;
   @Column(name="pub_link") private String link;
   
   @Transient private String prettyName;
   
   @OrderBy("writerOrder") 
   @OneToMany(mappedBy="publicationWriterPK.publication", targetEntity=PublicationWriter.class)
   private List<Researcher> researcherList = new ArrayList<Researcher>();

...

As we can see, a publication can be written by various researches, so to emulate the N:N relation, I use an Primary Key object PublicationWriter ( the intermediate entity ):

PK Object

Code:

@Embeddable
@SuppressWarnings("serial")
public class PublicationWriterPK implements Serializable
{

   @ManyToOne 
   @JoinColumn(name="rese_nif")
   private Researcher researcher;
   
   
   @ManyToOne 
   @JoinColumn(name="pub_key")
   private Publication publication;
...
}

Entity to represent the relation

Code:

@Entity
@Table(name="publication_writer")
@SuppressWarnings("serial")
public class PublicationWriter implements Serializable
{

   @Id
   private PublicationWriterPK publicationWriterPK = new PublicationWriterPK();
   
   @Column(name="pwrit_ord") private Integer writerOrder;
   
   
   public PublicationWriter(){}

   
   public Researcher getResearcher()
   {
      return publicationWriterPK.getResearcher();
   }
   
   
   public void setResearcher(Researcher researcher)
   {
      publicationWriterPK.setResearcher(researcher);
   }
   
   
   public Publication getPublication()
   {
      return publicationWriterPK.getPublication();
   }
   
   
   public void setPublication(Publication publication)
   {
      publicationWriterPK.setPublication(publication);
   }


   
   public Integer getWriterOrder()
   {
      return writerOrder;
   }


   public void setWriterOrder(Integer writerOrder)
   {
      this.writerOrder = writerOrder;
   }

}

And finally, a subclass of Publication :

Book Entity

Code:

@Entity
@Table(name="book")
@PrimaryKeyJoinColumn(name="book_key")
@SuppressWarnings("serial")
public class Book extends Publication
{

   @Column(name="book_sub_tit") private String subtitle;
   @Column(name="book_vol") private String volume;
   @Column(name="book_pages") private Integer pages;
   @Column(name="book_edit") private String edition;
...
}

So, I want to get from BookDAO the list of books of an author. Thinking in the HQL way to get this, the query would be (?) like this:

Code:

   @SuppressWarnings("unchecked")
   public List<Book> getPublicationsByResearcher(Researcher researcher)
   {
      return (List<Book>) getHibernateTemplate().findByNamedParam("FROM Book AS b " +
            "WHERE :rese IN b.researcherList ORDER BY b.year DESC","rese",new Researcher[]{researcher});
   }

but doesn't work because "researcherList" is "{non-qualified-property-ref}".

My question is: ¿what is the way to do this? I algo tried with Criterion Expression.In but it doesn't work.

Please, is very urgent to solve this to continue working.

A lot of thanks.

P.D. One more question... to represent the relation between Publication and Researcher, the list would be List<PublicationWriter> or directly List<Researcher> as shown?

Top

hardy.ferentschik

Post subject:

Posted: Sat Aug 09, 2008 3:51 pm

Hibernate Team

Joined: Thu Apr 05, 2007 5:52 am
Posts: 1689
Location: Sweden

Hi,

I am not sure about your query, but I am a little confused about your mapping as well. Does it do what you expect. It seems to be the use case where you want to add columns (writerOrder in your case) to the join table. The configuration for this type of use case is for example described here: http://forum.hibernate.org/viewtopic.php?t=986506.
Now, I am not sure whether this is causing the problem. Also, have you tried to switch inheritance strategy to for example SINGLE_TABLE?

--Hardy

Top

bLaDe00

Post subject:

Posted: Mon Aug 11, 2008 10:21 am

Newbie

Joined: Thu Jul 24, 2008 10:13 am
Posts: 17

I'm trying to solve the problem, but now i'm trying to understand the way to do thinks like the post you refer. Although, i'll try to explain the problem more deeply:

A Book (a kind of Publication) has one or more writers (Researchers), and in a concrete use case i need to know the list of books of a Researcher. Remember that the Book has a list with its authors...

In a classic way, the "idea" would be like this : "give me the books with the specified author in their author list" ( IN operator in SQL ).

Reading the documentation about HQL in Hibernate's Web (http://www.hibernate.org/hib_docs/v3/re ... ryhql.html) I found this:

Quote:

Elements of indexed collections (arrays, lists, maps) may be referred to by index (in a where clause only):

from Order order where order.items[0].id = 1234

The problem I have when I try to launch the query :

Code:

   @SuppressWarnings("unchecked")
   public List<Book> getPublicationsByResearcher(Researcher researcher)
   {
      DetachedCriteria criteria = DetachedCriteria.forClass(Book.class);
      List<Researcher> researcherList = new ArrayList<Researcher>();
      researcherList.add(researcher);
      
      criteria.add(Restrictions.in("researcherList", researcherList));
      criteria.addOrder(Order.desc("year"));
      
      return (List<Book>) getHibernateTemplate().findByCriteria(criteria);
   }

, the resulted SQL error is that the first parameter doesn't have value:

Code:

select
        this_.book_key as pub1_27_1_,
        this_1_.pub_isbn as pub2_27_1_,
        this_1_.pub_book as pub3_27_1_,
        this_1_.pub_edit as pub4_27_1_,
        this_1_.pub_file as pub5_27_1_,
        this_1_.pub_link as pub6_27_1_,
        this_1_.pub_loc as pub7_27_1_,
        this_1_.pub_prsnt as pub8_27_1_,
        this_1_.pub_subj as pub11_27_1_,
        this_1_.pub_title as pub9_27_1_,
        this_1_.pub_year as pub10_27_1_,
        this_.book_edit as book2_29_1_,
        this_.book_pages as book3_29_1_,
        this_.book_sub_tit as book4_29_1_,
        this_.book_vol as book5_29_1_,
        publicatio2_.pub_sub_id as pub1_28_0_,
        publicatio2_.pub_sub_name_EN as pub2_28_0_,
        publicatio2_.pub_sub_name_ES as pub3_28_0_,
        publicatio2_.pub_sub_name_GL as pub4_28_0_ 
    from
        book this_ 
    inner join
        publication this_1_ 
            on this_.book_key=this_1_.pub_key 
    left outer join
        publication_subject publicatio2_ 
            on this_1_.pub_subj=publicatio2_.pub_sub_id 
    where
        this_.book_key in (
            ?
        ) 
    order by
        this_1_.pub_year desc

The problem is the clause where this_.book_key in ( ? ) . If I'm trying to query for the researchers, why Hibernate ask for the book's key and not for the researcher (as writed in the Criteria ). Perhaps caused by using a ordered collection... can anyone confirm this?

Other question to Hardy, due to your answer... ¿ are incorrect my mappings to represent the situation above? (one publication has one or more Researchers, and one researcher can write zero or more publications - N:N relation, refering from one entity the other side to direct access it).

I'll continue searching for the error, but your help will be very useful to perform my using of Hibernate.

P.D. Refering to the "writerOrder" its an attribute of the relation that represents the relevance (the order) of each researcher in the publication (so, the name of the researchers will appear ordered based on the value of this atttribute).

Top

bLaDe00

Post subject:

Posted: Tue Aug 12, 2008 5:41 am

Newbie

Joined: Thu Jul 24, 2008 10:13 am
Posts: 17

Thinking in doing this in a different way, I try to create a HQL query through @NamedQuery.

The "target" list to search in, is in the Publication entity (related to last post, now I'm trying with java.util.Set (non-ordered collection)):

Code:

   @OrderBy("writerOrder") 
   @OneToMany(mappedBy="publicationWriterPK.publication", targetEntity=PublicationWriter.class)
   protected Set<Researcher> researcherList = new HashSet<Researcher>();

In Book entity, the declared query seems like this :

Code:

@NamedQuery(name="BooksFromResearcher", query="FROM Book " +
      "                              WHERE :researcherList IN researcherList " +
      "                              ORDER BY year")

And in the corresponding DAO:

Code:

   @SuppressWarnings("unchecked")
   public List<Book> getPublicationsByResearcher(Researcher researcher)
   {
      Set<Researcher> researcherList = new HashSet<Researcher>();
      researcherList.add(researcher);
      
      return (List<Book>) getHibernateTemplate().findByNamedQueryAndNamedParam("BooksFromResearcher", "researcherList", researcherList);
   }

Launching this query, the result is :

Code:

Hibernate: 
    select
        book0_.book_key as pub1_27_,
        book0_1_.pub_isbn as pub2_27_,
        book0_1_.pub_book as pub3_27_,
        book0_1_.pub_edit as pub4_27_,
        book0_1_.pub_file as pub5_27_,
        book0_1_.pub_link as pub6_27_,
        book0_1_.pub_loc as pub7_27_,
        book0_1_.pub_prsnt as pub8_27_,
        book0_1_.pub_subj as pub11_27_,
        book0_1_.pub_title as pub9_27_,
        book0_1_.pub_year as pub10_27_,
        book0_.book_edit as book2_29_,
        book0_.book_pages as book3_29_,
        book0_.book_sub_tit as book4_29_,
        book0_.book_vol as book5_29_ 
    from
        book book0_ 
    inner join
        publication book0_1_ 
            on book0_.book_key=book0_1_.pub_key,
        publication_writer researcher1_ 
    where
        book0_.book_key=researcher1_.pub_key 
        and (
            ? in (
                {non-qualified-property-ref}
            )
        ) 
    order by
        book0_1_.pub_year

If I try to change the query to:

Code:

@NamedQuery(name="BooksFromResearcher", query="FROM Book AS b " +
      "                              WHERE :researcherList IN b.researcherList " +
      "                              ORDER BY b.year")

the resulted query is :

Code:

Hibernate: 
    select
        book0_.book_key as pub1_27_,
        book0_1_.pub_isbn as pub2_27_,
        book0_1_.pub_book as pub3_27_,
        book0_1_.pub_edit as pub4_27_,
        book0_1_.pub_file as pub5_27_,
        book0_1_.pub_link as pub6_27_,
        book0_1_.pub_loc as pub7_27_,
        book0_1_.pub_prsnt as pub8_27_,
        book0_1_.pub_subj as pub11_27_,
        book0_1_.pub_title as pub9_27_,
        book0_1_.pub_year as pub10_27_,
        book0_.book_edit as book2_29_,
        book0_.book_pages as book3_29_,
        book0_.book_sub_tit as book4_29_,
        book0_.book_vol as book5_29_ 
    from
        book book0_ 
    inner join
        publication book0_1_ 
            on book0_.book_key=book0_1_.pub_key,
        publication_writer researcher1_ 
    where
        book0_.book_key=researcher1_.pub_key 
        and (
            ? in (
                .
            )
        ) 
    order by
        book0_1_.pub_year

I don't know if this is the correct way to solve my problem, but I'm still waiting for the response to the last post (and now for the reply to this, jeje).

A lot of thanks

Top

hardy.ferentschik

Post subject:

Posted: Wed Aug 13, 2008 5:41 am

Hibernate Team

Joined: Thu Apr 05, 2007 5:52 am
Posts: 1689
Location: Sweden

bLaDe00 wrote:

A Book (a kind of Publication) has one or more writers (Researchers), and in a concrete use case i need to know the list of books of a Researcher. Remember that the Book has a list with its authors...

In a classic way, the "idea" would be like this : "give me the books with the specified author in their author list" ( IN operator in SQL ).

I don't think that you need Restrictons.in, if you only want to know the books of a single researcher. Either way you should use joins to express your query. There are probably several ways of doing this, but I would try something like this:

Code:

   @SuppressWarnings("unchecked")
   public List<Book> getPublicationsByResearcher(Researcher researcher)
   {
      DetachedCriteria criteria = 
                    DetachedCriteria.forClass(Book.class).
                    createCriteria("researcherList").
                    add( Restrictions.ideq(researcher.getId()).
                    addOrder(Order.desc("year"));
      
      return (List<Book>) getHibernateTemplate().findByCriteria(criteria);
   }

Quote:

Without a join all restrictions will apply to the root entity in your case Book.

Quote:

Other question to Hardy, due to your answer... ¿ are incorrect my mappings to represent the situation above? (one publication has one or more Researchers, and one researcher can write zero or more publications - N:N relation, refering from one entity the other side to direct access it).

There are always many ways to map/implement something. If I understand your usecase correctly I would have chosen to map the relationship between Book and Researcher a little different. The only reason you cannot use a simple @ManyToMany is that you have this additional property writerOrder. Somehow you want to add this to the mapping table between Book and Researcher. This is a classic usecase described in the threads I mentioned in the previous post or in "Java Persistence with Hibernate".

--Hardy

Top

bLaDe00

Post subject:

Posted: Tue Oct 14, 2008 4:53 am

Newbie

Joined: Thu Jul 24, 2008 10:13 am
Posts: 17

Hi! I was working in other parts of my application and leave this problem aside, but now it's time to continue with it (sorry for "taking back to live" this thread, but it is important).

Quote:

Without a join all restrictions will apply to the root entity in your case Book.

As I understand this, I need to specify to Hibernate that a full join is needed to this query, but the question is... ¿it will join automatically all known subclasses of Publication to do it or I need to manually build a HQL query to do it? (remember that Publication has 4 subclasses: Book, BookChapter, Journal and Conference).

I was searching about this, and the only way to specify Hibernate to do the full join is with an alias ... but I don't see the way to add this fact to the given queries in upper posts... ¿Can anyone help me?

Top

Page 1 of 1

[ 6 posts ]

Board index » Projects » Search, Validator, Shards

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