-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: ManyToMany. Collection filtration.
PostPosted: Fri Dec 12, 2008 1:29 pm 
Newbie

Joined: Tue Jun 27, 2006 9:34 am
Posts: 9
Location: St. Petersburg
Hello.

I have standart many to many assosiation.

First object is OFFER
Second is PRODUCT

Offer can contain several products.
Product can be in several offers.

And we have list of valid product.

I need such a selection that filter offers that contain ONLY valid products.

For example:

We have products:
"pro1"
"pro2"
"pro3"

Valid products are "pro1" and "pro2"

And there is list of offers:
"off1"
"off2"
"off3"


"off1" contains "pro1" and "pro2" (only valid)
"off2" contains "pro3" (only NOT valid)
"off3" contains "pro1" and "pro2" and "pro3" (both valid and not valid)


As a result only "off1" must be selected.

How can query be constructed in this case?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 12, 2008 1:34 pm 
Newbie

Joined: Tue Jun 27, 2006 9:34 am
Posts: 9
Location: St. Petersburg
Objects:

Offer:
Code:
package ccos;

import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Cascade;
import org.hibernate.annotations.CollectionId;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name="OFFERS")
public class OfferVO {
   
    private Integer internalOfferId = 0;
   
    private String offerName;
   
    private List<ProductVO> offerProducts;
   
    @Column(name = "OFFER_NAME")
    public String getOfferName() {
        return offerName;
    }

    public void setOfferName(String offerName) {
        this.offerName = offerName;
    }

    @Id
    @Column(name = "OFFER_ID", nullable = false)
    @GenericGenerator(name="OFF_ID_GENERATOR", strategy="increment")
    @GeneratedValue(generator="OFF_ID_GENERATOR")
    public Integer getInternalOfferId() {
        return internalOfferId;
    }

    public void setInternalOfferId(Integer internalOfferId) {
        this.internalOfferId = internalOfferId;
    }
   
    @ManyToMany(fetch=FetchType.EAGER, targetEntity=ProductVO.class)
    @JoinTable(name="OFF_REL_PRO", joinColumns = {
        @JoinColumn(name="OFFER_ID", insertable=true, nullable=true, updatable=true) }, inverseJoinColumns = {
        @JoinColumn(name="PRODUCT_ID", insertable=true, nullable=true, updatable=true) })       
    @GenericGenerator(name="OFF_REL_PRO_ID_GENERATOR", strategy="increment")
    @CollectionId(
                columns=@Column(name="OFFER_PRODUCT_ID"),
                type = @org.hibernate.annotations.Type(type = "long"),
                generator="OFF_REL_PRO_ID_GENERATOR")
    @Cascade({org.hibernate.annotations.CascadeType.SAVE_UPDATE, org.hibernate.annotations.CascadeType.DELETE_ORPHAN, org.hibernate.annotations.CascadeType.ALL})
    @Fetch(FetchMode.JOIN)
    public List<ProductVO> getOfferProducts() {
        return offerProducts;
    }

    public void setOfferProducts(List<ProductVO> offerProducts) {
        this.offerProducts = offerProducts;
    }


}


Product:
Code:
package ccos;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name="PRODUCTS")
public class ProductVO {
   
    private Integer internalProductId = 0;
   
    private String productName;
   
    @Column(name = "PRODUCT_NAME")
    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    @Id
    @Column(name = "PRODUCT_ID", nullable = false)
    @GenericGenerator(name="PRO_ID_GENERATOR", strategy="increment")
    @GeneratedValue(generator="PRO_ID_GENERATOR")
    public Integer getInternalProductId() {
        return internalProductId;
    }

    public void setInternalProductId(Integer internalProductId) {
        this.internalProductId = internalProductId;
    }

}


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 12, 2008 1:39 pm 
Newbie

Joined: Tue Jun 27, 2006 9:34 am
Posts: 9
Location: St. Petersburg
One of solution in pure sql is using subselect.

At first select from relations table OFF_REL_PRO such offerID's that contain not only valid products.

Then select offers that are not in list of previous select.

But here we need to ask relations table OFF_REL_PRO. And HQL and Criterias do not know about it.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

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.