Implements tri angluar table structure in JDBC

I have a problem with a triangular table structure. See ER diagram.

I have implemented this in Java with the help of JDBC.

The station class:

@Table("station")
public class Station {

    @Id
    private Long id;

    @Setter
    @Getter
    private String name;

    @Setter
    @Getter
    private Double[] gps;

    @Setter
    @Getter
    @MappedCollection(idColumn = "station_id")
    private Set<Sensor> sensors = new HashSet<>();

    @Setter
    @Getter
    @MappedCollection(idColumn = "station_id")
    private Set<Measurement> measurementList = new HashSet<>();

}

The sensor class:

@Getter
@Table("sensor")
public class Sensor {
    @Id
    private Long id;

    @Setter
    private String type;

    @Setter
    private String name;

    @Setter
    private String sensor_unique;

    @Setter
    @Getter
    @MappedCollection(idColumn = "sensor_id")
    private Set<Measurement> measurementList = new HashSet<>();

}

The measurement class:

@Getter
@Table("measurement")
public class Measurement {

    @Id
    private long id;

    @Setter
    private LocalDateTime measurement_datetime;

    @Setter
    private double value;

    @Setter
    private String unit;

}

I am always missing one of the foreign keys for the Measurement table. How can I implement it so that both foreign keys are saved.

enter image description here

I try to copy the list from station to sensor, that not allow. I have no further idea or do I have a design error in my table.

Solution

Looking at the diagram and the code you have passed, if you want the foreign keys to be saved correctly for your case, simply use a one-to-many relationship.

In your Station class change your sensors and measurementList fields with the following code:

@OneToMany(mappedBy = “station”)
private Set<Sensor> sensors = new HashSet<>();

@OneToMany(mappedBy = “station”)
private Set<Measurement> measurementList = new HashSet<>();

In your Sensor class modify your measurementList field to the following:

@OneToMany(mappedBy = “sensor”)
private Set<Measurement> measurementList = new HashSet<>();

Also add the following field:

@ManyToOne()
@JoinColumn(name = “station_id”)
private Station station;

Finally in the Measurement class add the following to your code:

@ManyToOne()
@JoinColumn(name = “station_id”)
private Station station;

@ManyToOne()
@JoinColumn(name = “sensor_id”)
private Sensor sensor;

I leave you a link with the different possible relationships in case you need it.

Note: By default collections marked with @OneToMany use the lazy load strategy, if you get a lazy initialization exception, nothing should happen if your service is @transactional. It should happen when you exit the service, if you try to get the lazy collection. If the method of your service is supposed to return the collections initialized because you have to manipulate them, then it's the method's responsibility to fetch it. You can do this for example using Hibernate.initialize(station.getSensors()). There are also other ways, in the following link I leave you the other possible ways to solve the exception.