How to build a criteria from many-to-many association?

cartas · **Joined:** Fri Aug 03, 2007 9:35 am **Posts:** 8

Hi, I'm not able to solve such a "basic" problem:

I have an ordinary many-to-many association. There are two classes: User and Group. These classes are mapped well and they worked correctly. They have the properties User.Groups and Group.Users.

The property User.Group returns all groups which user is member in.

Now I need to build a criteria for User class and one part of the criteria is, that i need all users, which are members of certain group (this is exactly what the Group.Users property returns). This condition will be then added to a more difficult condition.

Lets say I need all users who has name="ABC" OR belongs to Group "XYZ"

Please help me to build such a criteria.

Thanks!

cartas · **Joined:** Fri Aug 03, 2007 9:35 am **Posts:** 8

no ideas?

ishaan · **Joined:** Tue Oct 30, 2007 5:08 am **Posts:** 10

Dear Cartas,

I am facing the same problem you faced, if you came out with the solution please do informe me.

my main problem is how to fetch the data from many-to-many relationship table.details are as follwos.

======== Category Class===========

Code:

@Entity(name="CATEGORYTABLE") 
public class CategoryVO { 
@Id 
@GeneratedValue(strategy = GenerationType.AUTO) 
private long categoryId; 
private String category; 

@ManyToMany(cascade=CascadeType.ALL,fetch=FetchType.LAZY) 
@JoinTable(name="CATEGORYVENDORRELATION", 
joinColumns = @JoinColumn( 
name="CATEGORYID", referencedColumnName="CATEGORYID"), 
inverseJoinColumns = @JoinColumn( 
name="VENDID", referencedColumnName="VENDID")) 
List>VendorVO< vendors= new ArrayList>VendorVO< 

//getters & setters 
} 

and following is the

====== Vendor Class ===========

Code:

@NamedQueries({@NamedQuery  
    (name= "VendorVO.SELECT_VENDORS_BY_CATEGORID", query="SELECT V FROM VENDORTABLE AS V WHERE ?1 MEMBER OF V.categories") 
     }) 
@Entity(name="VENDORTABLE ") 
public class VendorVO 
@Id 
@GeneratedValue(strategy = GenerationType.AUTO) 
private long vendId; 
private String vendName; 

@ManyToMany 
private List<CategoryVO> categories; 

//getters & setters 
} 

---------------------------

now the data of these two's many to many realtion is being stored in CATEGORYVENDORRELATION table now i want to fetch
*Name of vendors from vendor table where category id is 2" so i need a simple sql query like....
SELECT CATEGORY FROM CATEGORYTABLE WHERE CATEGORYID IN (SELECT CATEGORYID FROM CATEGORYVENDORRELATION WHERE VENDID = ?); Am i right ?
now what should the same query in JPQL that is my question I tried
SELECT V FROM VENDORTABLE AS V WHERE :?1 MEMBER OF V.categories
and then I am passing an object of CategoryVO like query.setParameter(1,new CategoryVO(2,"Supply")) here I am setting the value of the object and then set to this query in my DAO class the snippet of DAO is....

Code:

@Transactional 
public List<VendorVO> getVendorsByCategoryVO() { 
     List vendors= null; 
     CategoryVO category = new CategoryVO(); 
     category.setCategoryId(2); 
     category.setCategory("Supply"); 
       
     try{ 
            if(category != null){ 
                Query query = entityManager.createNamedQuery("VendorVO.SELECT_VENDORS_BY_CATEGORID");       
   query.setParameter(1, category); 
   [b][i]vendors= query.getResultList();//at this point i get the Exception[/i][/b] 
             }       
         } catch (NoResultException nre) { 
      System.out.println("No data found "+nre): 
         } 
        return ((List<VendorVO>)vendors); 
} 

but I am getting the following exception......

SEVERE: ORA-00972: identifier is too long

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
----------------

so will you please guide me in these regards....

Thank you in advance

marcal · **Posted:** Mon Nov 26, 2007 5:05 pm

cartas wrote:

Lets say I need all users who has name="ABC" OR belongs to Group "XYZ"

Here's one way:

Code:

DetachedCriteria gps = DetachedCriteria.For( typeof( Group ) ).CreateAlias( typeof( User ), "u" ).Add( Expression.Eq( "Name", "XYZ" ) ).SetProjection( Projections.Property.( "ID" ) );
ICriteria c = session.CreateCriteria( typeof( User ), "u" );
ICriterion name = Expression.Eq( "Name", "ABC" );
ICriterion group = Subqueries.PropertyIn( "ID", gps );
c.Add( Expression.Or( name, group ) );
IList users = c.List();