Fetch data from multiple tables using eclipselink

This is in response to my question titled "Join tables using eclipselink" asked yesterday. I will put my scenario here. I have three tables auction, items. one auction can have many items. the mapping between these two tables is through a third table called auctionitems. Below are my tables

CREATE TABLE AUCTION (
  auction_id  integer,
  min_bid_amt decimal(15,2),
  max_bid_amt decimal(15,2),
  auction_start_ts timestamp,
  auction_end_ts timestamp,
  owner_id varchar(30),
  create_ts timestamp,
  last_updt_ts timestamp
);

ALTER TABLE AUCTION ADD CONSTRAINT auction_auction_id_pk PRIMARY KEY(auction_id);

CREATE TABLE ITEM (
  item_id integer,
  item_name varchar(30),
  item_desc varchar(50),
  item_image_id integer,
  create_ts timestamp,
  last_updt_ts timestamp
);


ALTER TABLE ITEM ADD CONSTRAINT item_item_id_pk PRIMARY KEY(item_id);

CREATE TABLE AUCTIONITEMS (
  item_id integer,
  auction_id integer,
  create_ts timestamp,
  last_updt_ts timestamp
);

ALTER TABLE AUCTIONITEMS ADD CONSTRAINT item_item_id_fk FOREIGN KEY(item_id) REFERENCES ITEM(item_id);
ALTER TABLE AUCTIONITEMS ADD CONSTRAINT auction_auction_id_fk FOREIGN KEY(auction_id) REFERENCES AUCTION(auction_id);
ALTER TABLE AUCTIONITEMS ADD CONSTRAINT auctionitems_pk_1 PRIMARY KEY(item_id,auction_id);

Now I want to display item_name, item_desc, auction_start_ts and auction_end_ts in my jsp page for all auctions(lets say for 5 auctions).

I am using eclipse link as JPA provider, Tomcat 6.0.29, Spring 3.0.5 and MYSQL 5.1.

These are my domain classes.

Auction :
package com.persistent.eap.domain;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.sql.*;

@Entity
@Table(name="Auction")

public class Auction implements Serializable
{

 private static final long serialVersionUID = 1L;
 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 private long auction_Id;
 private double min_Bid_Amt;
 private double max_Bid_Amt;
 private Timestamp auction_Start_Ts;
 private Timestamp auction_End_Ts;
 private long owner_Id;
 private Timestamp create_Ts;
 private Timestamp last_Updt_Ts;


 public long getAuction_Id() {
  return auction_Id;
 }
 public void setAuction_Id(long auctionId) {
  auction_Id = auctionId;
 }
 public double getMin_Bid_Amt() {
  return min_Bid_Amt;
 }
 public void setMin_Bid_Amt(double minBidAmt) {
  min_Bid_Amt = minBidAmt;
 }
 public double getMax_Bid_Amt() {
  return max_Bid_Amt;
 }
 public void setMax_Bid_Amt(double maxBidAmt) {
  max_Bid_Amt = maxBidAmt;
 }
 public Timestamp getAuction_Start_Ts() {
  return auction_Start_Ts;
 }
 public void setAuction_Start_Ts(Timestamp auctionStartTs) {
  auction_Start_Ts = auctionStartTs;
 }
 public Timestamp getAuction_End_Ts() {
  return auction_End_Ts;
 }
 public void setAuction_End_Ts(Timestamp auctionEndTs) {
  auction_End_Ts = auctionEndTs;
 }
 public long getOwner_Id() {
  return owner_Id;
 }
 public void setOwner_Id(long ownerId) {
  owner_Id = ownerId;
 }
 public Timestamp getCreate_Ts() {
  return create_Ts;
 }
 public void setCreate_Ts(Timestamp createTs) {
  create_Ts = createTs;
 }
 public Timestamp getLast_Updt_Ts() {
  return last_Updt_Ts;
 }
 public void setLast_Updt_Ts(Timestamp lastUpdtTs) {
  last_Updt_Ts = lastUpdtTs;
 } 

}

Item :

package com.persistent.eap.domain;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.sql.*;

@Entity
@Table(name="Item")

public class Item implements Serializable
{

 private static final long serialVersionUID = 1L;
 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 private long item_Id;
 private String item_Name;
 private String item_Desc;
 private int item_Image_Id;
 private Timestamp create_ts;
 private Timestamp last_updt_ts;

 public long getItem_Id() {
  return item_Id;
 }
 public void setItem_Id(long itemId) {
  item_Id = itemId;
 }
 public String getItem_Name() {
  return item_Name;
 }
 public void setItem_Name(String itemName) {
  item_Name = itemName;
 }
 public String getItem_Desc() {
  return item_Desc;
 }
 public void setItem_Desc(String itemDesc) {
  item_Desc = itemDesc;
 }
 public int getItem_Image_Id() {
  return item_Image_Id;
 }
 public void setItem_Image_Id(int itemImageId) {
  item_Image_Id = itemImageId;
 }
 public Timestamp getCretae_ts() {
  return create_ts;
 }
 public void setCretae_ts(Timestamp cretaeTs) {
  create_ts = cretaeTs;
 }
 public Timestamp getLast_updt_ts() {
  return last_updt_ts;
 }
 public void setLast_updt_ts(Timestamp lastUpdtTs) {
  last_updt_ts = lastUpdtTs;
 }

}


AuctionItems:

package com.persistent.eap.domain;

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.Entity;

@Entity
public class AuctionItems implements Serializable{

 private static final long serialVersionUID = 1L;
 private int itemId;
 private int auctionId;
 private Timestamp createTs;
 private Timestamp lastUpdtTs;

 public AuctionItems(){

 }
 public int getItemId() {
  return itemId;
 }

 public void setItemId(int itemId) {
  this.itemId = itemId;
 }

 public int getAuctionId() {
  return auctionId;
 }

 public void setAuctionId(int auctionId) {
  this.auctionId = auctionId;
 }

 public Timestamp getCreateTs() {
  return createTs;
 }

 public void setCreateTs(Timestamp createTs) {
  this.createTs = createTs;
 }

 public Timestamp getLastUpdtTs() {
  return lastUpdtTs;
 }

 public void setLastUpdtTs(Timestamp lastUpdtTs) {
  this.lastUpdtTs = lastUpdtTs;
 }

}

In AuctionItems there is no @Id because it is a composite primary key. I do not know how to put a composite primary key. And JPA complains that this class is not a known Entity type.

And finally my persistence.xml file

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <persistence-unit name="portalintegrator" transaction-type="RESOURCE_LOCAL">
  <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
  <class>com.persistent.eap.domain.UserDetails</class>
  <class>com.persistent.eap.domain.Item</class>
  <class>com.persistent.eap.domain开发者_C百科.Auction</class>
  <properties>
   <property name="eclipselink.jdbc.driver"     value="com.mysql.jdbc.Driver" />
   <property name="eclipselink.jdbc.url"        value="jdbc:mysql://localhost:3306/portaldemo" />
   <property name="eclipselink.jdbc.user"       value="root" />
   <property name="eclipselink.jdbc.password"   value="root" />
   <property name="eclipselink.ddl-generation"  value="None" />
   <property name="eclipselink.logging.level"   value="INFO" />
   <property name="eclipselink.target-database" value="MYSQL" />
  </properties>
 </persistence-unit>
</persistence>

I have a couple of questions?

1. If I use JPA annotations how would I achieve this?
2. If I use JPQL what is the query going to look like to fetch the above 4 fields?

Normally you would use a @ManyToMany mapping to map a join table, your Auction would have an items field of List.

See, http://en.wikibooks.org/wiki/Java_Persistence/ManyToMany

You join table seems a little more complex, with additional fields, so you may also want to map it an an Entity, see,

http://en.wikibooks.org/wiki/Java_Persistence/ManyToMany#Mapping_a_Join_Table_with_Additional_Columns