Tuesday, November 6, 2007

Rocky Mountain Software Symposium

I just finished uploading my slides for this weekends Rocky Mountain Software Symposium (aka NFJS Denver). I have two sessions, one on JPA and another on Maven 2. Here are my presentations. I hope to see you there.

4 comments:

ishan said...

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

Chris Maki said...

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

ishan said...

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

Anonymous said...

I found this site using [url=http://google.com]google.com[/url] And i want to thank you for your work. You have done really very good site. Great work, great site! Thank you!

Sorry for offtopic