프로젝트를 진행하다 복합키를 설정해야 하는 경우가 생겼서 Id Class를 이용해 복합키를 생성하였는데, Insert 후 똑같은 구문을 Insert 할 경우 duplicate error 가 발생하여야 하는데, update 구문이 나갔다. 이를 해결?은 아니고 그냥 다른 방법으로 대체하였는데, 이를 알아보자.
현재 상태
Entity
코드는 이렇다.
@Entity
@Getter
@NoArgsConstructor
@IdClass(PlayMatchId.class)
public class PlayMatch {
@Id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "player_id")
private Player player_id;
@Id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "match_id")
private MatchDay matchDay;
@Column(name = "goals")
@ColumnDefault("0")
private int goals;
@Column(name = "assists")
@ColumnDefault("0")
private int assists;
@Builder
public PlayMatch(Player player, MatchDay matchDay) {
this.player_id = player;
this.matchDay = matchDay;
this.goals = 0;
this.assists = 0;
}
public void updateGoals(int goals) {
this.goals = goals;
}
public void updateAssists(int assists) {
this.assists = assists;
}
public void setPlayer_id(Player player) {
this.player_id = player;
}
public void setMatchDay(MatchDay matchDay) {
this.matchDay = matchDay;
}
}쿼리문
Hibernate: create table match_day (match_id bigint not null auto_increment, away_goals integer default 0 not null, away_name varchar(255), goals integer default 0 not null, match_date datetime(6), state varchar(255), primary key (match_id)) engine=InnoDB
Hibernate: create table player (player_id bigint not null auto_increment, appearances integer default 0 not null, assists integer default 0 not null, back_num integer, birth_date date, description longtext, goals integer default 0 not null, height integer not null, image_url longtext, player_name varchar(255) not null, position varchar(255), weight integer not null, primary key (player_id)) engine=InnoDB
Hibernate: create table play_match (match_id bigint not null, player_id bigint not null, assists integer default 0, goals integer default 0, primary key (match_id, player_id)) engine=InnoDB
Hibernate: alter table player add constraint UK_nvmkd35itx0ibm2btbu3naluk unique (back_num)
Hibernate: alter table play_match add constraint FK5dljrm6d9y05ispdkm5r03rfx foreign key (match_id) references match_day (match_id)
Hibernate: alter table play_match add constraint FK8cl22xt318la2xc209c04q5en foreign key (player_id) references player (player_id)Hibernate에서 table을 생성할 때, play_match table에서 PK와 FK로 match_id, player_id를 사용하는 것을 쿼리문에서 확인할 수 있다.
문제 발생
현재 디비 상태
select * from player;
+-----------+-------------+---------+----------+------------+-------------+-------+--------+-----------+-------------+----------+--------+
| player_id | appearances | assists | back_num | birth_date | description | goals | height | image_url | player_name | position | weight |
+-----------+-------------+---------+----------+------------+-------------+-------+--------+-----------+-------------+----------+--------+
| 1 | 0 | 0 | 9 | NULL | NULL | 0 | 170 | NULL | 양원영 | FW | 69 |
| 2 | 0 | 0 | 6 | NULL | NULL | 0 | 178 | NULL | 정형우 | DF | 74 |
| 3 | 0 | 0 | 30 | NULL | NULL | 0 | 170 | NULL | 양원영 | DF | 69 |
+-----------+-------------+---------+----------+------------+-------------+-------+--------+-----------+-------------+----------+--------+
select * from match_day;
+----------+------------+--------------+-------+----------------------------+--------+
| match_id | away_goals | away_name | goals | match_date | state |
+----------+------------+--------------+-------+----------------------------+--------+
| 1 | 0 | 무지막지 | 0 | 2022-09-29 18:00:00.000000 | BEFORE |
+----------+------------+--------------+-------+----------------------------+--------+
MariaDB [spring_test]> select * from play_match;
+----------+-----------+---------+-------+
| match_id | player_id | assists | goals |
+----------+-----------+---------+-------+
| 1 | 1 | 0 | 1 |
+----------+-----------+---------+-------+현재 DB가 이런 상황에서, match_id=1, player_id=1인 Data는 play_match에 삽입이 될 수 없다.
그러나 api로 해당 부분을 날려보면
성공적으로 추가 되었다고 되며,
Hibernate: select player0_.player_id as player_i1_1_0_, player0_.appearances as appearan2_1_0_, player0_.assists as assists3_1_0_, player0_.back_num as back_num4_1_0_, player0_.birth_date as birth_da5_1_0_, player0_.description as descript6_1_0_, player0_.goals as goals7_1_0_, player0_.height as height8_1_0_, player0_.image_url as image_ur9_1_0_, player0_.player_name as player_10_1_0_, player0_.position as positio11_1_0_, player0_.weight as weight12_1_0_ from player player0_ where player0_.player_id=?
Hibernate: select matchday0_.match_id as match_id1_0_0_, matchday0_.away_goals as away_goa2_0_0_, matchday0_.away_name as away_nam3_0_0_, matchday0_.goals as goals4_0_0_, matchday0_.match_date as match_da5_0_0_, matchday0_.state as state6_0_0_ from match_day matchday0_ where matchday0_.match_id=?
Hibernate: select playmatch0_.match_id as match_id1_2_0_, playmatch0_.player_id as player_i2_2_0_, playmatch0_.assists as assists3_2_0_, playmatch0_.goals as goals4_2_0_ from play_match playmatch0_ where playmatch0_.match_id=? and playmatch0_.player_id=?
Hibernate: update play_match set assists=?, goals=? where match_id=? and player_id=?쿼리문도 정상적으로 나가고(심지어 update로 나간다.)
select * from play_match;
+----------+-----------+---------+-------+
| match_id | player_id | assists | goals |
+----------+-----------+---------+-------+
| 1 | 1 | 0 | 0 |
+----------+-----------+---------+-------+확인해보면 update가 된 것을 확인할 수 있다. 왜 이런 현상이 발생하는 것일까?(원래는 pk가 이미 존재하므로 아예 삽입이 되면 안된다. JpaRepository의 save를 이용하기 때문)
StackOverflow등 여러 사이트를 뒤져 본 결과, 삽입하기 전에 미리 확인하라고 하는 것 같다.(아니면 내가 연관관계를 잘못 짠 것일지도..)
Unique Key를 사용하여 해결
그래서 그렇게 하는 것보단 그냥 unique key 처리를 하는 게 낫다 싶어서, https://jwkim96.tistory.com/166 링크를 보니 pk는 따로 두고 unique key를 설정하는 것이 유지 보수가 낫다고 한다.
그래서 UNIQUE KEY로 바꿔주고
@Entity
@Table(
uniqueConstraints = {
@UniqueConstraint(
name = "constraintName",
columnNames = {"player_id", "match_id"}
)
}
)
@Getter
@NoArgsConstructor
public class PlayMatch {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "player_id")
private Player player;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "match_id")
private MatchDay matchDay;
@Column(name = "goals")
@ColumnDefault("0")
private int goals;
@Column(name = "assists")
@ColumnDefault("0")
private int assists;
@Builder
public PlayMatch(Player player, MatchDay matchDay) {
this.player = player;
this.matchDay = matchDay;
this.goals = 0;
this.assists = 0;
}
public void updateGoals(int goals) {
this.goals = goals;
}
public void updateAssists(int assists) {
this.assists = assists;
}
public void setPlayer_id(Player player) {
this.player = player;
}
public void setMatchDay(MatchDay matchDay) {
this.matchDay = matchDay;
}
}삽입을 해보았더니
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1-1' for key 'constraintName'에러가 나오면서 삽입이 되지 않고 정상작동한다.
원인을 좀 더 생각해보고싶은데, 자료를 못찾겠다.
혹시 지적사항이나 다른 방안이 있으면 댓글로 알려주시면 감사하겠습니다.
Hongik CE