JPA Primary Key from two foreign key

[peterbo]

Hello,

I am new with java, eclipse, jpa(eclipselink), postgresql, and trying to make a web application. I have two tables:

bids: id, quantity, price
trades: bidid, askid, quantity, price

bidid and askid columns are foreign keys from bids table (id), and they are the primary key for the trades table.

I created the Entities from the Tables (Bid and Trade class) with eclipse and it generated a TradePK class for the primary key.

Trade.java:
@Entity
@Table(name="trades")
public class Trade implements Serializable {

@EmbeddedId
private TradePK id;

@ManyToOne
@JoinColumn(name = "askid")
private Bid ask;

@JoinColumn(name = "bidid")
@ManyToOne
private Bid bid;
... constructor, getter, setter, etc

TradePK.java:
@Embeddable
public class TradePK implements Serializable {

private Integer askid;
private Integer bidid;
... constructor, getter, setter, equals, hashCode

I understand that this is necessary because the primary key is from two column, but as soon I want to persist a Trade back to the database Eclipselink call the column names twice:

INSERT INTO trades (price, quantity, datetime, BIDID, ASKID, bidid, askid) VALUES (?, ?, ?, ?, ?, ?, ?)

so postgres give me back an exception: ERROR: column "bidid" specified more than once

And I don't know why. Could somebody help please?
According to the JPA docs I didn't found any mistake:
Introduction to EclipseLink JPA (ELUG) - Eclipsepedia
Or I just can't see it... or is there a better doc which explains it better?

thanks,
peter bo

[Sno]

You can not have two primary keys on a table. It just won't work, you either need to separate each id into its own table and create a relationship table.

Example

Trip Table
---------
Trip ID Primary
Trip Details

Passenger Table
---------
Passenger ID Primary
Passeneger Name

rel_trp_psngr_table
--------
rel_trp_psngr_ID - Primary
Trip ID Foreign
Passenger_ID Foreign


The below statement is completely wrong to your table
trades: bidid, askid, quantity, price
INSERT INTO trades (price, quantity, datetime, BIDID, ASKID, bidid, askid) VALUES (?, ?, ?, ?, ?, ?, ?)

should be

INSERT INTO trades (bidid, askdid, quantity. price) VALUES (?,?,?,?)

Must be in the order that your table identifies them.

[peterbo]

Hello Sno,
There is only one primary key in the trades table in postgreSQL:

CONSTRAINT trades_pkey PRIMARY KEY (bidid, askid)

it means that the two foreign keys can be only once in the table, and this pair the primary key for the table

the INSERT statement made by JPA, I only wrote:

EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpa");
EntityManager em = emf.createEntityManager();
em.persist(trade);

JPA mixes the order as I saw in other examples, and was not a problem so far, I think it knows the binding. What new for me is that it uses the column names twice, and that throws the exception.

Do you think, I should make a new primary key, which is not using the foreign keys, like in your example? Is this the only one possibility?

thanks for the help,
peter bo

[Sno]

I would make a new primary key only because It will keep you more organized in the long run without having data duplication which would case an error, especially since in a table no two records can have the same value as a primary key
Example

bidid = 4
askid = 2

bidid = 5
askid = 3

bidid = 5
askid = 7

this would cause an error because in bidid is a primary key and has two values that are the same, the database would not be able to determine which record to pull.

[peterbo]

hello Sno,

I think what I want is looking good from the database side: less columns, but from the other side where I have the java code of the entities will be complicated.
So with your suggestion the java code looks far simpler and the main thing it is working. :) However in the database there is a new column for the id. And a new constrain checks that the two foreign keys are unique.

Thanks for your help,
Best regards,
peter bo