Results 1 to 7 of 7
  1. #1
    peterbo is offline Member
    Join Date
    Oct 2010
    Posts
    3
    Rep Power
    0

    Default JPA Primary Key from two foreign key

    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

  2. #2
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    5

    Default

    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.
    Last edited by Sno; 10-22-2010 at 06:17 PM.
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  3. #3
    peterbo is offline Member
    Join Date
    Oct 2010
    Posts
    3
    Rep Power
    0

    Default

    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

  4. #4
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    5

    Default

    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.
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  5. #5
    peterbo is offline Member
    Join Date
    Oct 2010
    Posts
    3
    Rep Power
    0

    Default [Solved] JPA Primary Key from two foreign key

    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

  6. #6
    Join Date
    Jun 2014
    Posts
    1
    Rep Power
    0

    Default Re: [Solved] JPA Primary Key from two foreign key

    Solucion: en el insert salen campos duplicados por la relación, para evitar que salguen los campos adiciona el insertable y updatable a FALSE.

    @ManyToOne
    @JoinColumns({
    @JoinColumn(name="campo1_fk",insertable=false,upda table=false),
    @JoinColumn(name="campo2_fk",insertable=false,upda table=false)
    })

  7. #7
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,656
    Blog Entries
    7
    Rep Power
    21

    Default Re: JPA Primary Key from two foreign key

    This thread is almost four years old. I'm closing it.

    Jos
    cenosillicaphobia: the fear for an empty beer glass

Similar Threads

  1. JPA Composite Primary Key
    By The_S in forum Advanced Java
    Replies: 2
    Last Post: 02-06-2009, 06:30 AM
  2. How to generate primary key in EJB
    By naresh_m in forum Enterprise JavaBeans (EJB)
    Replies: 1
    Last Post: 07-17-2008, 09:41 AM
  3. Replies: 0
    Last Post: 05-14-2008, 09:46 AM
  4. Replies: 0
    Last Post: 05-14-2008, 07:18 AM
  5. how to determine the primary key
    By osval in forum JDBC
    Replies: 1
    Last Post: 08-07-2007, 02:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •