Join Query Generation for Many-to-Many Relationship

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

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:Hibernate 3.3

Mapping documents:Hibernate Annotations

Name and version of the database you are using: Oracle 10g

Dear All,

I am having three tables like
1. VendorTable
vendorId_pkl number (primary key)
vendorName varchar2

2.CategoryTable
categoryId_pkl number, (primary key)
categoryName varcha2,

and the third table I used for many-to-many relationship between two of above
3.Vendor_Category_Table
vendorId_fkl number, (foreign key)
categoryId_fkl number, (foreign primary key)

I am having two entity classes
Like

Code:

@Entity
class VendorTable{
   private long vandorId_pkl;
   private String vendorName;
   //getters-setters
}

@Entity
class CategoryTable{
   private long categoryId_pkl;
   private String categoryName;
   //getters-setters
}

Now I want to execute the a query using either NamedQuery or some join query in JPA which behave like….
SELECT CATEGORYNAME FROM CATEGORYTABLE
WHERE CATEGORYID_PKL IN (SELECT CATEGORYID_FKL FROM VENDOR_CATEGORY_TABLE
WHERE VENDORID_FKL = ?

This is the simple sql query but as I am using the JPA-Hibernate anybody please suggest me that how can I achieve the above functionality with help of this technology.

or is there any way to define @manyToMany relationship in fact i seen this annotation but couldn't get how to use this one.

Thank you very much in advance for you help.

ptaborda · **Posted:** Fri Nov 23, 2007 2:02 pm

Hi ishaan, you can add a property to your first object something like this

/**
* Funcion del Modelo de Equipo
* @uml.property name="usos"
* @uml.stereotype uml_id="AndroMDA Profile Persistence::andromda_persistence_fetch_type" andromda_persistence_fetch_type="EAGER"
*/
private Set<IFuncion> usos;

Add the required getter and setter accordingly , paying special attention
to the joinColumn and inverseJoinColumns

Object A

/**
* Getter of the property <tt>usos</tt>
* @return Returns the usos.
* @uml.property name="usos"
* @uml.stereotype uml_id="AndroMDA Profile Persistence::andromda_persistence_fetch_type" andromda_persistence_fetch_type="LAZY"
*/
@ManyToMany(
targetEntity=Funcion.class,
cascade={CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.LAZY)
@JoinTable(
name="equipo.uso",
joinColumns={@JoinColumn(name="modelo_id")},
inverseJoinColumns={@JoinColumn(name="funcion_id")} )
public Set<IFuncion> getUsos() {
return usos;
}

/**
* Setter of the property <tt>usos</tt>
* @param funcion The usos to set.
* @uml.property name="usos"
* @uml.stereotype uml_id="AndroMDA Profile Persistence::andromda_persistence_fetch_type" andromda_persistence_fetch_type="LAZY"
*/
public void setUsos( Set<IFuncion> usos ) {
this.usos = usos;
}

and in Object B

Add a property like this

/**
* @uml.property name="modelos"
* @uml.associationEnd multiplicity="(0 -1)" inverse="fabricante:com.procedatos.protecciones.modelo.equipo.Modelo"
*/
private Set<IModelo> modelos;

And you have to pay special attention on your getter, where
you use the mappedBy attribute where if you see above you alrready defined in your previous class.

/**
* Getter of the property <tt>modelos</tt>
* @return Returns the modelos.
* @uml.property name="modelos"
*/
@ManyToMany( mappedBy="usos", targetEntity=Modelo.class )
@OrderBy("nombre")
public Set<IModelo> getModelos() {
return modelos;
}

/**
* Setter of the property <tt>modelos</tt>
* @param modelo The modelos to set.
* @uml.property name="modelos"
*/
public void setModelos(Set<IModelo> modelos) {
this.modelos = modelos;
}

That would basically do the trick, sorry for the name of the variables because they are in spanish, but should get the idea.

Best regards,
Pedro Taborda

P.S: You would observe I used a lot the targetEntity=XXXX this is because I program to Interfaces.

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

Dear Pedro,

Thank you very very much !!! :-) for your reply now I implement these in my code and I think these definitely solve my problem.

Thank a lot dearrrrr.

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

Dear Pedro,

I follow your guidelines but it gives me Exception. so again will you please help me to solved out this problem. 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....

again thanks a lot for your valuable reply.

and thank you very much in advance for your reply to this blog.