Spring Data JPA. Using composite natural key in relationship between entities

Question

I use Spring Data JPA (version is 1.7.2.RELEASE) and MySQL 5.5.

Here is the simplified version of my tables:

CREATE TABLE `station` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

CREATE TABLE `ticket` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `trip_id` bigint(20) unsigned NOT NULL,
  `wagon_number` tinyint(4) unsigned NOT NULL,
  `place_number` tinyint(4) unsigned NOT NULL,
  `departure_station_id` bigint(20) unsigned NOT NULL,
  `arrival_station_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `departure_trip_station_fk_idx` (`trip_id`,`departure_station_id`),
  KEY `arrival_trip_station_fk_idx` (`trip_id`,`arrival_station_id`),
  CONSTRAINT `arrival_trip_station_fk` FOREIGN KEY (`trip_id`, `arrival_station_id`) REFERENCES `trip_station` (`trip_id`, `station_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `departure_trip_station_fk` FOREIGN KEY (`trip_id`, `departure_station_id`) REFERENCES `trip_station` (`trip_id`, `station_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

CREATE TABLE `train` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `enabled` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$

CREATE TABLE `train_station` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `train_id` bigint(20) unsigned NOT NULL,
  `station_id` bigint(20) unsigned NOT NULL,
  `departure_time` mediumint(8) unsigned NOT NULL,
  `arrival_time` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `train_station_uq` (`train_id`,`station_id`),
  KEY `train_id_idx` (`train_id`),
  KEY `id_idx` (`station_id`),
  KEY `station_fk_idx` (`station_id`),
  CONSTRAINT `station_fk` FOREIGN KEY (`station_id`) REFERENCES `station` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `train_fk` FOREIGN KEY (`train_id`) REFERENCES `train` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

CREATE TABLE `trip` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `train_id` bigint(20) unsigned NOT NULL,
  `departure_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `train_id` (`train_id`),
  CONSTRAINT `timetable_fk` FOREIGN KEY (`train_id`) REFERENCES `train` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=latin1$$

CREATE TABLE `trip_station` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `train_id` bigint(20) unsigned NOT NULL,
  `station_id` bigint(20) unsigned NOT NULL,
  `trip_id` bigint(20) unsigned NOT NULL,
  `arrival_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `departure_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `trip_station_uq` (`trip_id`,`station_id`),
  KEY `trip_fk_idx` (`trip_id`),
  KEY `train_station_fk_idx` (`train_id`,`station_id`),
  CONSTRAINT `train_station_fk` FOREIGN KEY (`train_id`, `station_id`) REFERENCES `train_station` (`train_id`, `station_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `trip_fk` FOREIGN KEY (`trip_id`) REFERENCES `trip` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

Here are my entities:

@Entity
@Getter
@Setter
public class Station {
    @Id
    private long id;

    @Basic
    private String name;

    @OneToMany(mappedBy = "station", fetch = FetchType.LAZY)
    private Collection<TrainStation> stationTrains;
}


@Entity
@Getter
@Setter
public class Ticket {
    @Id
    private long id;

    @Basic
    @Column(name = "place_number")
    private byte placeNumber;

    @Basic
    @Column(name = "wagon_number")
    private byte wagonNumber;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name = "trip_id", referencedColumnName = "trip_id", nullable = false),
            @JoinColumn(name = "departure_station_id", referencedColumnName = "station_id", nullable = false)
    })
    private TripStation departureTripStation;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name = "trip_id", referencedColumnName = "trip_id", nullable = false),
            @JoinColumn(name = "arrival_station_id", referencedColumnName = "station_id", nullable = false)
    })
    private TripStation arrivalTripStation;
}


@Entity
@Getter
@Setter
public class Train {
    @Id
    private long id;

    @Basic
    private int number;

    @Basic
    private String name;

    @Basic
    @Column(name = "enabled", columnDefinition = "BIT", length = 1)
    private boolean enabled;

    @OneToMany(mappedBy = "train", fetch = FetchType.LAZY)
    private List<TrainStation> trainStations;

    @OneToMany(mappedBy = "train", fetch = FetchType.LAZY)
    private List<Trip> trips;
}

@Entity
@Table(name = "train_station", schema = "", catalog = "rail_db",
        uniqueConstraints = @UniqueConstraint(columnNames = { "train_id", "station_id" })
)
@Getter
@Setter
public class TrainStation {
    @Id
    private long id;

    @Basic
    @Column(name = "departure_time")
    private int departureTime;

    @Basic
    @Column(name = "arrival_time")
    private int arrivalTime;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "train_id", referencedColumnName = "id", nullable = false)
    private Train train;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "station_id", referencedColumnName = "id", nullable = false)
    private Station station;

    @OneToMany(mappedBy = "trainStation", fetch = FetchType.LAZY)
    private Collection<TripStation> tripStations;
}


@Entity
@Getter
@Setter
public class Trip {
    @Id
    private long id;

    @Basic
    @Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
    @Column(name = "departure_date")
    private DateTime departureDate;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "train_id", referencedColumnName = "id", nullable = false)
    private Train train;

    @OneToMany(mappedBy = "trip", fetch = FetchType.LAZY)
    private Collection<TripStation> tripStations;
}

@Entity
@Table(name = "trip_station", schema = "", catalog = "rail_db",
        uniqueConstraints = @UniqueConstraint(columnNames = { "trip_id", "station_id" })
)
@Getter
@Setter
public class TripStation {
    @Id
    private long id;

    @Basic
    @Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
    @Column(name = "arrival_date")
    private DateTime arrivalDate;

    @Basic
    @Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
    @Column(name = "departure_date")
    private DateTime departureDate;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "trip_id", referencedColumnName = "id", nullable = false)
    private Trip trip;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name = "train_id", referencedColumnName = "train_id", nullable = false),
            @JoinColumn(name = "station_id", referencedColumnName = "station_id", nullable = false)
    })
    private TrainStation trainStation;

    @OneToMany(mappedBy = "departureTripStation", fetch = FetchType.LAZY)
    private Collection<Ticket> departureTickets;

    @OneToMany(mappedBy = "arrivalTripStation", fetch = FetchType.LAZY)
    private Collection<Ticket> arrivalTickets;
}

As you can see, my tables contain the surrogate primary keys and the composite natural keys (e.g. trip_station has UNIQUE KEY trip_station_uq (trip_id,station_id)). For relationships between entities I offen use composite natural keys because they reflect the logic of domain.

But then I get this runtime exception:

ERROR o.s.web.context.ContextLoader - Context initialization failed 
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [com/xxx/rail/config/JpaConfig.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build EntityManagerFactory
    ...
Caused by: org.hibernate.AnnotationException: referencedColumnNames(trip_id, station_id) of com.xxx.rail.domain.entity.Ticket.arrivalTripStation referencing com.xxx.rail.domain.entity.TripStation not mapped to a single property
    ...

I want to understand what is wrong?
Is it possible to use natural composite keys (which are not primary keys) for relationships when surrogate primary keys exist?

Possible duplicate of http://stackoverflow.com/questions/5818373/does-the-jpa-specification-allow-references-to-non-primary-key-columns — holmis83, Mar 16 '15 at 14:32
@holmis83, Yes, maybe, but what should I do in this case? should I do remove surrogate primary keys? There is another way? — neocles, Mar 17 '15 at 07:56

Spring Data JPA. Using composite natural key in relationship between entities

0 Answers0