Mapping many-to-one foreign key Java EE

Question

This is homework and I don't understand so help is appreciated. The system is an Olympic medal tally simulator.

We have an Events table and a Countries table:

statement.execute("create table EVENTS (EVENTNUMBER integer primary key not null, EVENTNAME varchar(32), " +
               " GENDER varchar(5), GOLD integer, SILVER integer, BRONZE integer)");

statement.execute("create table COUNTRIES (COUNTRYID integer primary key not null, NAME varchar(32)," +
                       "TOTALGOLD integer, TOTALSILVER integer, TOTALBRONZE integer)");

These relate to entities:

@Entity
@Table(name = "COUNTRIES")
public class Country implements Serializable
{

   @Id
   private int countryID; // This is the primary key
   private String name;
   private int totalGold;
   private int totalSilver;
   private int totalBronze;
//getters, setters omitted

@Entity
@Table(name = "EVENTS")
public class Event implements Serializable
{

    @Id
    private int eventNumber;     // This is the primary key
    private String eventName;
    private String gender;       // Men, Women or Mixed
    @ManyToOne(optional=false)
    @JoinColumn(name="COUNTRYID", nullable=false, updatable=false)
    private Country gold, silver, bronze;

//getter, setter methods omitted

The question says to add three Country instance variables to Event to represent the gold, silver and bronze winners and include the relationship type. To me it makes sense to be a many-to-one. But, when I try run this I get an error about join being wrong because it should allow only one write and the rest as read only.

So, I changed the JoinColumn to insertable=false and get an error: Column 'COUNTRYID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list.

Which is hit at this method in my QueriesBean:

   public List<Event> getEvents()
   {
      List<Event> eventList = null;

      Query query = em.createQuery("SELECT e FROM Event e");
      eventList = (List<Event>) query.getResultList();

      return eventList;
   }

Perhaps someone can give me some guidance?

Answer 1

@JoinColumn(name="COUNTRYID", nullable=false, updatable=false)
private Country gold;

The above line means: There is an association between this entity (Event) and the Country entity, and this association is materialized by the column "COUNTRID" in the table EVENTS.

There is already a problem: you don't have any COUNTRYID column in the EVENTS table.

@JoinColumn(name="COUNTRYID", nullable=false, updatable=false)
private Country gold, silver, bronze;

The above line means: there are three different associations between this entity (Event) and the Country entity, and all these associations are materialized by the same column COUNTRYID. This is of course impossible. If you have three associations, you need three different columns.

A good rule, whether you have annotations or not, is to always have one declaration per line. Declare your three associations in three separate lines, and annotate each of them with the right annotations. Each of them is a ManyToOne. And each of them has its own column to materialize the association. And of course this column must exist in the table.