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.