如何使用复合键映射多对多

我有以下表格

Trainingplan
    TrainingplanID int(11) AI PK
    Trainer int(11)
    Client int(11)
    validFrom date
    validTo date
    type int(11)

TrainingplanExercises
    trainingplan int(11) PK
    exercise int(11) PK
    parameter int(11) PK
    value varchar(45)

不，我在将它们与 Hibernate 连接时遇到问题。我做了以下事情： 包装豆；

@Entity
@Table(name = "Trainingplan")
public class Training {

    private IntegerProperty id;
    private ObjectProperty<Person> client;
    private ObjectProperty<Person> trainer;
    private ObjectProperty<Date> validFrom;
    private ObjectProperty<Date> validTo;
    private ObjectProperty<TrainingplanType> type;
    private List<TrainingplanExercise> exercises;

    public Training(int id, Person client, Person trainer, Date validFrom, Date validTo, TrainingplanType type) {
        this.id = new SimpleIntegerProperty(id);
        this.client = new SimpleObjectProperty<>(client);
        this.trainer = new SimpleObjectProperty<>(trainer);
        this.validFrom = new SimpleObjectProperty<>(validFrom);
        this.validTo = new SimpleObjectProperty<>(validTo);
        this.type = new SimpleObjectProperty<>(type);
        exercises = FXCollections.observableArrayList();
    }

    public Training(Person client, Person trainer, Date validFrom, Date validTo, TrainingplanType type){
        this(0, client, trainer, validFrom, validTo, type);
    }

    public Training(){
        this(0, null,null,null,null, null);
    }

    @OneToOne
    @JoinColumn(name = "client")
    public Person getClient() {
        return client.get();
    }

    public ObjectProperty<Person> clientProperty() {
        return client;
    }

    public void setClient(Person client) {
        this.client.set(client);
    }

    @OneToOne
    @JoinColumn(name = "trainer")
    public Person getTrainer() {
        return trainer.get();
    }

    public ObjectProperty<Person> trainerProperty() {
        return trainer;
    }

    public void setTrainer(Person trainer) {
        this.trainer.set(trainer);
    }

    @Column
    public Date getValidFrom() {
        return validFrom.get();
    }

    public ObjectProperty<Date> validFromProperty() {
        return validFrom;
    }

    public void setValidFrom(Date validFrom) {
        this.validFrom.set(validFrom);
    }

    @Column
    public Date getValidTo() {
        return validTo.get();
    }

    public ObjectProperty<Date> validTillProperty() {
        return validTo;
    }

    public void setValidTo(Date validTill) {
        this.validTo.set(validTill);
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "TrainingplanID")
    public int getId() {
        return id.get();
    }

    public IntegerProperty idProperty() {
        return id;
    }

    public void setId(int id) {
        this.id.set(id);
    }

    @OneToOne
    @JoinColumn(name = "type")
    public TrainingplanType getType() {
        return type.get();
    }

    public ObjectProperty<TrainingplanType> typeProperty() {
        return type;
    }

    public void setType(TrainingplanType type) {
        this.type.set(type);
    }

    @ManyToMany()
    @JoinTable(name="TrainingplanExercises",
            joinColumns={@JoinColumn(name="trainingplan")},
            inverseJoinColumns={@JoinColumn(name="trainingplan"), @JoinColumn(name="exercise"), @JoinColumn(name="parameter")})
    public List<TrainingplanExercise> getExercises() {
        return exercises;
    }

    public void setExercises(List<TrainingplanExercise> exercises) {
        this.exercises = exercises;
    }

    @Override
    public String toString() {
        return "Training{" +
                "id=" + getId() +
                ", client=" + getClient() +
                ", trainer=" + getTrainer() +
                ", validFrom=" + getValidFrom() +
                ", validTill=" + getValidTo() +
                ", type=" + getType() +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Training training = (Training) o;

        return id != null ? id.equals(training.id) : training.id == null;

    }

    @Override
    public int hashCode() {
        return id != null ? id.hashCode() : 0;
    }
}

训练计划Exercise.java

@Entity
@Table(name = "TrainingplanExercises")
@IdClass(TrainingplanExerciseId.class)
public class TrainingplanExercise {

    private ObjectProperty<Exercise> exercise;
    private ObjectProperty<Training> training;
    private ObjectProperty<String> value;
    private ObjectProperty<Parameter> parameter;

    public TrainingplanExercise(Exercise exercise, Training training, String value, Parameter parameter){
        this.exercise = new SimpleObjectProperty<>(exercise);
        this.training = new SimpleObjectProperty<>(training);
        this.value = new SimpleObjectProperty<>(value);
        this.parameter = new SimpleObjectProperty<>(parameter);
    }

    public TrainingplanExercise(){
        this(null,null,null,null);
    }

    @Id
    @OneToOne
    @JoinColumn(name = "parameter")
    public Parameter getParameter() {
        return parameter.get();
    }

    public ObjectProperty<Parameter> parameterProperty() {
        return parameter;
    }

    public void setParameter(Parameter parameter) {
        this.parameter.set(parameter);
    }

    @Id
    @OneToOne
    @JoinColumn(name = "exercise")
    public Exercise getExercise() {
        return exercise.get();
    }

    public ObjectProperty<Exercise> exerciseProperty() {
        return exercise;
    }

    public void setExercise(Exercise exercise) {
        this.exercise.set(exercise);
    }

    @Id
    @OneToOne
    @JoinColumn(name = "trainingplan")
    public Training getTraining() {
        return training.get();
    }

    public ObjectProperty<Training> trainingProperty() {
        return training;
    }

    public void setTraining(Training training) {
        this.training.set(training);
    }

    @Column(name = "value")
    public String getValue(){
        return value.get();
    }

    public ObjectProperty<String> valueProperty() {
        return value;
    }

    public void setValue(String value) {
        this.value.set(value);
    }

    @Override
    public String toString() {
        return "TrainingplanExercise{" + "exercise=" + exercise + ", training=" + training + ", value=" + value + '}';
    }

}

 class TrainingplanExerciseId implements Serializable{
     protected ObjectProperty<Exercise> exercise;
     protected ObjectProperty<Training> training;
     protected ObjectProperty<Parameter> parameter;

     public TrainingplanExerciseId() {
         if(exercise == null)
             exercise = new SimpleObjectProperty<>(null);

         if(training == null)
             training = new SimpleObjectProperty<>(null);

         if(parameter == null)
             parameter = new SimpleObjectProperty<>(null);
     }

     public TrainingplanExerciseId(ObjectProperty<Exercise> exercise, ObjectProperty<Training> training, ObjectProperty<Parameter> parameter) {
         this.exercise = exercise;
         this.training = training;
         this.parameter = parameter;
     }

     @Override
     public boolean equals(Object o) {
         if (this == o) return true;
         if (o == null || getClass() != o.getClass()) return false;

         TrainingplanExerciseId that = (TrainingplanExerciseId) o;

         if (exercise != null ? !exercise.equals(that.exercise) : that.exercise != null) return false;
         if (training != null ? !training.equals(that.training) : that.training != null) return false;
         return parameter != null ? parameter.equals(that.parameter) : that.parameter == null;

     }

     @Override
     public int hashCode() {
         int result = exercise != null ? exercise.hashCode() : 0;
         result = 31 * result + (training != null ? training.hashCode() : 0);
         result = 31 * result + (parameter != null ? parameter.hashCode() : 0);
         return result;
     }

     public Exercise getExercise() {
         return exercise.get();
     }

     public ObjectProperty<Exercise> exerciseProperty() {
         return exercise;
     }

     public void setExercise(Exercise exercise) {
         this.exercise.set(exercise);
     }

     public Training getTraining() {
         return training.get();
     }

     public ObjectProperty<Training> trainingProperty() {
         return training;
     }

     public void setTraining(Training training) {
         this.training.set(training);
     }

     public Parameter getParameter() {
         return parameter.get();
     }

     public ObjectProperty<Parameter> parameterProperty() {
         return parameter;
     }

     public void setParameter(Parameter parameter) {
         this.parameter.set(parameter);
     }
 }

现在，当我想保存新的训练时，我收到此错误：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TrainingplanID' in 'field list'

因为这个 SQL：

Hibernate: insert into TrainingplanExercises (TrainingplanID, trainingplan, exercise, parameter) values (?, ?, ?, ?)

我该如何解决？ 如果我将 joinColumn 更改为“trainingplan”，我会收到错误消息，有两个相同的列。如果我从反向列中删除“trainingplan”，则会收到一条错误消息，其中缺少一个，因为外部约束需要 3 列

编辑： 尝试一下评论中的一些内容。我确实尝试过 OneToMany/ManyToOne：

@Id
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "trainingplan", nullable = false)
public Training getTraining() {
    return training.get();
}

@OneToMany(fetch = FetchType.EAGER, mappedBy = "training")
public List<TrainingplanExercise> getExercises() {
    return exercises;
}

如果我现在尝试将训练保存到数据库，它就会起作用。 假设我想从数据库中获取 Trainingplan，并添加新的 TrainingplanExercises。我会使用这段代码：

Exercise ex = (Exercise) db.getAll(Exercise.class).get(1);


Training t = (Training) db.getAll(Training.class).get(0);


TrainingplanExercise te = new TrainingplanExercise(ex, t, "asdf", ex.getParameters().get(0));
TrainingplanExercise te1 = new TrainingplanExercise(ex, t, "asdf", ex.getParameters().get(1));
TrainingplanExercise te2 = new TrainingplanExercise(ex, t, "asdf", ex.getParameters().get(2));
TrainingplanExercise te3 = new TrainingplanExercise(ex, t, "asdf", ex.getParameters().get(3));



t.getExercises().clear();
t.getExercises().add(te);
t.getExercises().add(te1);
t.getExercises().add(te2);
t.getExercises().add(te3);

db.updateObj(t);

我得到这个异常：

Exception in thread "main" org.hibernate.exception.LockTimeoutException: could not execute statement
    at org.hibernate.dialect.MySQLDialect$1.convert(MySQLDialect.java:447)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3124)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3581)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:465)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177)
    at db.Database.updateObj(Database.java:100)
    at db.Database.main(Database.java:171)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    ... 19 more

好吧，看一下。你遇到的是一个设计问题，而不是一个普遍的问题。首先，据我了解，你想要制作一套独特的TrainingplanExercise's。为此，你有这个Entity:

@Entity
public class TrainingplanExercise implements Serializable {
    @EmbeddedId private TrainingplanExerciseId trainingplanExerciseId;

    public TrainingplanExercise() {}        
    public TrainingplanExercise(TrainingplanExerciseId trainingplanExerciseId) {
        this.trainingplanExerciseId = trainingplanExerciseId;
    }
    ... other fields ...   
}

以上的区别Entity和你原来的Entity是我已经做了ID an EmbeddableId。为了确保只将独特的练习放入TrainingplanExercise's， 你有一个compositeKey被定义为一个单独的类：

@Embeddable
public class TrainingplanExerciseId implements Serializable {
    private String exercise;
    private String parameter;

    public TrainingplanExerciseId() {}
    public TrainingplanExerciseId(String exercise, String parameter) {
        this.exercise = exercise;
        this.parameter = parameter;
    }

    ... getters, setters, hashCode, and equals
}

在这里，我已经上课了Embeddable以便它可以用作ID。你试图声明的方式compositeKey没有任何意义；您试图声明中的每个单独字段TrainingplanExercise Entity as an ID，但你只能拥有一个ID.

这有什么不同model那是TrainingplanExerciseId compositeKey不包括对a的引用TrainingPlan。如果您想获取以下列表TrainingPlan's使用任何特定的TrainingplanExercise，那么你需要一个双向而不是单向关系 https://howtoprogramwithjava.com/hibernate-manytomany-unidirectional-bidirectional/，但那是另一个问题了。否则，我不知道你为什么要参考TrainingPlan from a TrainingplanExercise。此外，您还引用了TrainingPlan进入TrainingplanExerciseId compositeKey，这需要TrainingPlan被序列化，这实际上不能作为唯一的 ID。

现在您可以将单独的练习放入表中：

public TrainingplanExercise createExercise(String exercise, String parameter) {
    TrainingplanExercise trainingplanExercise = new TrainingplanExercise(new TrainingplanExerciseId(exercise, parameter));
    em.persist( trainingplanExercise );
    return trainingplanExercise;
}

之后，您想要拥有任意数量的TrainingPlan's就是利用可能的TrainingplanExercise's，你用这个做的Entity:

@Entity
public class TrainingPlan implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    @ManyToMany(fetch=FetchType.EAGER)
    private List<TrainingplanExercise> trainingplanExercises = new ArrayList<TrainingplanExercise>();

    ... getters, setters, 
}

你有一个ManyToMany关系因为一个TrainingPlan指的是许多TrainingplanExercise's and a TrainingplanExercise被很多人使用TrainingPlan's。除此之外你不需要任何特殊的注释ManyToMany, the JPA提供商将创建一个link table，将每个密钥Entity排成一行，像这样：

create table TrainingPlan_TrainingplanExercise (
    TrainingPlan_id bigint not null,
    trainingplanExercises_exercise varchar(255) not null,
    trainingplanExercises_parameter varchar(255) not null
);

如果您将其声明为OneToMany关系，那么JPA提供商将额外添加constraint on the link table确保一个TrainingplanExercise不能链接到多个TrainingPlan，所以你不想要这样。仅作为示例，这就是约束的样子。

alter table TrainingPlan_TrainingplanExercise 
    add constraint UK_t0ku26ydvjkrme5ycrnlechgi  unique (trainingplanExercises_exercise, trainingplanExercises_parameter);

创建和更新TrainingPlans很简单：

public TrainingPlan createTrainingPlan() {
    TrainingPlan trainingPlan = new TrainingPlan();
    em.persist(trainingPlan);
    return trainingPlan;
}
public TrainingPlan updateTrainingPlan(TrainingPlan trainingPlan) {
    return em.merge(trainingPlan);
}

现在，您可以创建TrainingplanExercises and TrainingPlans，并将练习添加到训练计划中并更新。

TrainingplanExercise squats20 = trainingService.createExercise("Squats", "20");
TrainingplanExercise lifts10 = trainingService.createExercise("Lifts", "10");
TrainingplanExercise crunches50 = trainingService.createExercise("Crunches", "50");

TrainingPlan trainingPlan = trainingService.createTrainingPlan();
trainingPlan.getTrainingplanExercises().add( squats20 );
trainingPlan.getTrainingplanExercises().add( lifts10 );
trainingService.updateTrainingPlan(trainingPlan);

trainingPlan = trainingService.createTrainingPlan();
trainingPlan.getTrainingplanExercises().add( lifts10 );
trainingPlan.getTrainingplanExercises().add( crunches50 );
trainingService.updateTrainingPlan(trainingPlan);

另请注意，您的应用程序面临的挑战是确保只有唯一的TrainingplanExercises由用户创建。如果一个TrainingplanExercise有重复的exercise and parameter试图创建你会得到一个Unique index or primary key violation异常，事务将被回滚。

编辑：用于阅读TrainingPlans，可以使用这样的东西：

public List<TrainingPlan> listTrainingPlans() {
    CriteriaQuery<TrainingPlan> criteria = em.getCriteriaBuilder().createQuery(TrainingPlan.class);
    criteria.select(criteria.from(TrainingPlan.class));
    List<TrainingPlan> trainingPlans = em.createQuery(criteria).getResultList();
    return trainingPlans;
}

请注意，自从List<TrainingplanExercise> trainingplanExercises被设定为FetchType.EAGER这个特定的查询将拉入整个数据库。FetchType.EAGER阅读单个内容可能不是问题TrainingPlan，但如果你只想要一个列表TrainingPlan's如果没有获得所有详细信息，那么您需要弄清楚如何FetchType.LAZY应予以实施。