使用 eclipselink 从多个表中获取数据

Question

这是对我昨天提出的标题为“使用 eclipselink 连接表”的问题的回应。 我将把我的场景放在这里。我有三张桌子拍卖物品。一场拍卖可以有很多物品。这两个表之间的映射是通过第三个表（称为“auctionitems”）进行的。下面是我的表格

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);

现在我想在我的jsp页面中显示所有拍卖的item_name、item_desc、auction_start_ts和auction_end_ts（假设有5次拍卖）。

我使用 eclipse link 作为 JPA 提供程序、Tomcat 6.0.29、Spring 3.0.5 和 MYSQL 5.1。

这些是我的域类。

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;
 }

}

在 AuctionItems 中没有 @Id，因为它是复合主键。我不知道如何放置复合主键。 JPA 抱怨此类不是已知的实体类型。

最后我的 persistence.xml 文件

<?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.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>

我有几个问题？

1. 如果我使用 JPA 注释，我将如何实现这一目标？
2. 如果我使用 JPQL，获取上述 4 个字段的查询会是什么样子？

Answer 1

通常您会使用 @ManyToMany 映射来映射连接表，您的拍卖将有一个 List 的 items 字段。

看，
http://en.wikibooks.org/wiki/Java_Persistence/ManyToMany

您加入表似乎稍微复杂一点，有附加字段，因此您可能还想将其映射为实体，请参阅

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