hibernate: retrieving only part of child collection

[Carl Wauters]

Hi everyone,

I'm quite new to Hibernate, and in case this question had been posted many times before, I'm sorry for that. I have the following situation:

Table A (Product)
-----------------
- Id (PK)
- related fields

Table B (Product description)
----------------------------
- Id (FK)
- Language
- Description

* There's a composite PK in table B (combination of Id and Language column)

Now, I would like to write a query that returns products - including the description - but only in the language that was passed as a parameter. I tried many things, but I always get a list with all languages, so the parameter has no influence on the result. I can understand why this is the case, but however, I would like to get only the descriptions I need. Is there a way to do this via Hibernate, and if this is possible, what do I have to do then?

I appreciate your help!

regards,

Carl

[Mark Spritzler]

What have you tried. You can write an HQL query that joins the two objects, then filters on the second object's language attribute.

Something like

"Select a from A a join fetch B b where b.language = :language"

then create the Query object, and before calling list(), set the "language" parameter using the setParameter() method of the Query object.

Mark

[Mark Spritzler]

I haven't tested it, but it might also fill the Collection with all Languages.

Mark

[Carl Wauters]

Hi Mark,

Thanks for your answer. The solution you proposed is one of the things I tried before, and this still returns a list with all descriptions (having the same foreign key value, of course), instead of a filtered list.

regards,

Carl

[Cristian Vrabie]

The `where b.language = "language"` condition should work in Iibernate. It's one of the basic things in any query language. Maybe there's something in the mappings or code. Can you paste the ".hbm.xml" files and the part of the code where you do the query?

[Carl Wauters]

Hi Cristian,

Ik work with annotations coming from the persistence 1.0 API instead of the hibernate .hbm.xml file. So my problem could be caused to a wrong implementation of those annotations. The queries are written via Hibernate API.

I'm gonna show you the mapping code I used:

Entity Product
--------------

 
@Table(name = "PROD")
public class Product implements Serializable {
 
    @Id
    @Column(name = "PRODUCT_ID")
    private Long productId;
 
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "PRODUCT_ID", referencedColumnName = "PRODUCT_ID")
    @MapKey(name = "language")
    private Map<Long, ProductDescription> descriptions;
 
    // other fields and their getters and setters
}

Entity Description
------------------

 
@Table(name = "PROD_DESCR")
@IdClass(ProductDescriptionPK.class)
public class ProductDescription implements Serializable {
    @Id
    private Long productId;
 
    @Id
    private String language;
 
    @Column(name = "DESCRIPTION")
    private String description;
 
    // getters and setters
}

Query:
------

   
public List<Product> findProductByName(final String name, final String language) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    Query query = session.createQuery("SELECT distinct prod 
    FROM product prod INNER JOIN prod.descriptions descr 
    WHERE prod.productId =  descr.productId 
    AND descr.language = :language
    AND UPPER(descr.description) LIKE UPPER(:name)");

    query.setParameter("name", "%" + name + "%");
    query.setParameter("language", language);
    List<Product> list = query.list();
    return list;
}

I changed the query a bit because it's a little bit more complicated.

when I loop through my returned product list, the descriptions map still contains all descriptions per productId. I would like to have only the descriptions in the specified language.

Regards,

Carl
[ July 09, 2008: Message edited by: Carl Wauters ]

[Mark Spritzler]

What if you switch your query where the language is the first object in the from clause. I wonder if that would work.

like

Select a from B b fetch join A a Where ....

I am also wondering if this might be a case where a "filter" would be used, and you set the filter at runtime to the value you want. I haven't used filters.

I am just guessing here.

Mark

[Carl Wauters]

Hi Mark,

Do you mean this way:

 SELECT distinct prod FROM prod.descriptions descr INNER JOIN  product prod  

I'm afraid this causes mapping exceptions...
[ July 09, 2008: Message edited by: Carl Wauters ]