当尝试保存具有复合主键的实体时出现了 SQLSyntaxErrorException。

huangapple go评论83阅读模式
英文:

SQLSyntaxErrorException when trying to save Entity with Composite Key

问题

以下是翻译好的内容:

这些是我的类:

UserActivity

@Entity
@Data
@IdClass(UserActivityId.class)
public class UserActivity {

    @ManyToOne
    private User user;

    @Id
    @Column(name = "user_id", insertable = false, updatable = false)
    private Long userId;

    @ManyToOne
    private Stream stream;

    @Id
    @Column(name = "stream_id", insertable = false, updatable = false)
    private Long streamId;

    @Id
    private String userIp;

    //...还有8个字段

}

UserActivityId

@Data
public class UserActivityId implements Serializable {
    private Long userId;
    private Long streamId;
    private String userIp;

    //构造函数
}

Stream

@Entity
@Data
public class Stream {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToMany(mappedBy = "stream")
    private List<UserActivity> UserActivities = new ArrayList<>();

}

User

public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    protected long id;
}

UserActivityRepository

public interface UserActivityRepository extends JpaRepository<UserActivity, UserActivityId> {
}

但是当我尝试像这样保存一个实体:

var stream = streamRepository.findById(activity.getStreamId);
var user = userRespository.findById(activity.getUserId);
activity.setStream(stream);
activity.setUser(user);
userActivityRepository.save(activity);

我会得到一个异常,类似于这样的:

java.sql.SQLSyntaxErrorException: (conn=1058) Could not set parameter at position 12 (values was 1)
Query - conn:1058(M)  - "insert into user_activity (a, b, c, d, e, f, g, h, user_id, stream_id, user_ip) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

这很奇怪,因为我只有11个字段,但 Hibernate 却试图设置第12个参数。我尝试重新创建数据库,问题仍然存在,但是当我移除复合键时,它可以正常工作。

英文:

these are my classes

UserActivity

@Entity
@Data
@IdClass(UserActivityId.class)
public class UserActivity {

    @ManyToOne
    private User user;

    @Id
    @Column(name = &quot;user_id&quot;, insertable = false, updatable = false)
    private Long userId;

    @ManyToOne
    private Stream stream;

    @Id
    @Column(name = &quot;stream_id&quot;, insertable = false, updatable = false)
    private Long streamId;

    @Id
    private String userIp;

    //...8 more fields

}

UserActivityId

@Data
public class UserActivityId implements Serializable {
    private Long userId;
    private Long streamId;
    private String userIp;

    //constructors
}

Stream

@Entity
@Data
public class Stream {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToMany(mappedBy = &quot;stream&quot;)
    private List&lt;UserActivity&gt; UserActivities = new ArrayList&lt;&gt;();

}

User

public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    protected long id;
}

UserActivityRepository

public interface UserActivityRepository extends JpaRepository&lt;UserActivity, UserActivityId&gt;  
}

but when i try to save an entity like this :

var stream = streamRepository.findById(activity.getStreamId);
var user = userRespository.findById(activity.getUserId);
activity.setStream(stream);
activity.setUser(user);
userActivityRepository.save(activity);

i get and exception like this:

java.sql.SQLSyntaxErrorException: (conn=1058) Could not set parameter at position 12 (values was 1)
Query - conn:1058(M)  - &quot;insert into user_activity (a, b, c, d, e, f, g, h, user_id, stream_id, user_ip) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)&quot;

which is strange because i only have 11 fields and hibernate is trying to set the 12th parameter
i tried recreating the database and the problem persisted, but when i remove the composite key it works

答案1

得分: 0

insertable=false, updatable=false 的定义在需要通过 OneToOneManyToOne 映射在实体中多次映射字段时非常有用。

我认为要解决这个问题,你需要从 Column 注解中移除 insertable=false, updatable=false

参考链接:
http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Primary_Keys_through_OneToOne_Relationships

英文:

Defining insertable=false, updatable=false is useful when you need to map a field more than once in an entity through a OneToOne or ManyToOne mapping.

I think to fix this issue you need to remove insertable=false, updatable=false from the Column annotation.

See,
http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Primary_Keys_through_OneToOne_Relationships

答案2

得分: 0

因此,我最终找到了问题所在,我需要将@id放在关系引用(Stream、User)之上,而不是它们的外键上,另外我还需要为它们添加@MapsId

 @ManyToOne
    @Id
    @MapsId
    private User user;

    
    @Column(name = "user_id", insertable = false, updatable = false)
    private Long userId;

    @ManyToOne
    @Id
    @MapsId
    private Stream stream;

    
    @Column(name = "stream_id", insertable = false, updatable = false)
    private Long streamId;

    @Id
    private String userIp;
英文:

So i finally out found what the problem was,i had to place @id above the relationship references (Stream, User) instead of their foreign keys, also i needed to add @MapsId to them

 @ManyToOne
    @Id
    @MapsId
    private User user;

    
    @Column(name = &quot;user_id&quot;, insertable = false, updatable = false)
    private Long userId;

    @ManyToOne
    @Id
    @MapsId
    private Stream stream;

    
    @Column(name = &quot;stream_id&quot;, insertable = false, updatable = false)
    private Long streamId;

    @Id
    private String userIp;

huangapple
  • 本文由 发表于 2020年8月24日 22:33:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/63563132.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定