Subscribe to:
Post Comments (Atom)
About Me
- Chris Maki
- My name is Chris Maki and I'm a Principal Software Engineer at Overstock.com. Before joining Overstock.com, I was an independent consultant specializing in server-side Java development. I have been designing and building server-side systems for 16 years in C++, Objective-C, and Java. Most of my career has been spent working as an enterprise architect specializing in large-scale Java system design and development. In addition to being a Java evangelist, I'm an avid proponent of Agile Software Development. In addition to my day job, I run the Utah Java User Group and I'm a member of the WebBeans (JSR-299) and JPA 2.0 (JSR-317) expert groups. In addition to computers I love to spend time hanging out with my wonderful wife of 13 years, Nicole, and my three boys, Trenton, Avery, and Brayden.
JPA 101
JPA 101 was published in January 2007 and is intended for those new to the Java Persistence API. If you want to know more about JPA or are interested in starting to use JPA, this is the book for you.
What I'm Reading
|
|
|
|
3 comments:
Dear Chris,
Congratulations on your new step in Overstock.com
Actually I would like to thank you a lot for your wonderful presentation on JPA from which I learned a lot. Thank a lot for sharing that presentation with us.
now if you permit me than i need some help from you as I am very new to JPA. actually I want to write a query somethin like this
SELECT CATEGORYNAME FROM CATEGORYTABLE
WHERE CATEGORYID_PKL IN (SELECT CATEGORYID_FKL FROM VENDOR_CATEGORY_TABLE
WHERE VENDORID_FKL = ?
as I am having two entities VendorClass and CategoryClass which having the coresponding id fields. in db I am having three tables one for each entity class and another one for many-to-many relationship now will you please please guide me that using JPA how can I create a query like this.
Thanks in advance,
with best regards,
Ishaan
ishaaan@gmail.com
Hi Ishaan
Thanks, I'm really enjoying myself at Overstock.com. I'm also glad to hear that you enjoyed the presentations. I had a good time at RMSS as well.
So on to your question...
As you know JPQL works with Objects and because of that you may need to adjust the way you think of your query. I'm assuming there is a one-to-many relationship between CategoryClass (the one side) and VendorClass (the many side). If that is the case, you will want to use JPQL's "MEMBER [OF]" operator to refine your query of Category instances. I'm assuming CategoryClass has a categoryName field and that it also has a field, vendors, representing the one-to-many relationship with VendorClass. If all this is correct, here is how you could rewrite your projection query with JPQL:
SELECT c.categoryName
FROM Category c
WHERE :vendor MEMBER OF c.vendors
I'll put up a more detailed post on this in a couple of days.
I hoep this helps.
Chris
Dear Chris,
Thank you very very much for your valuable reply. Sorry for delay in reply as I was out of station. Actually my problem is with Many To Many relationship.
======== Category Class===========
@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 ===========
@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 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.
With Best Regards,
Ishaan
ishaaan@gmail.com
Post a Comment