今天要新增TAG 的資料表儲存了有關於代辦事項的tag,所以TODO對TAG的資料表是多對多的關係,一個代辦事項可以有[生活]、[健康]等多個tag,一個tag也可以對應到很多則代辦事項。多對多對應,可以藉由一個中介表格來完成,使用@ManyToMany
的標註
TODO資料表
+------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+---------------+------+-----+---------+
| id | int | NO | PRI | auto |
| task | varchar | YES | | NULL |
| status | int | NO | | 1 |
| create_time| TIMESTAMP | NO | | current |
| update_time| TIMESTAMP | NO | | current |
| user_id | int | YES | FK | NULL |
+------------+---------------+------+-----+---------+
TAG資料表
+------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+---------------+------+-----+---------+
| id | int | NO | PRI | auto |
| tag | varchar | YES | | NULL |
+------------+---------------+------+-----+---------+
TODOS_TAG 中介資料表
+------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------+---------------+------+-----+---------+
| todo_id | int | NO | | NULL |
| tag_id | int | NO | | NULL |
+------------+---------------+------+-----+---------+
在TODO的Entity 增加@ManyToMany的標註。
@JoinTable(name="todos_tag")連結創造一個中介表格(todos_tag
)。
joinColumns = {@JoinColumn(name="tag_id")},連接關聯外鍵(tag_id)至TAG的id。
inverseJoinColumns= {@JoinColumn(name="todo_id")},連結到外鍵(todo_id)至TODO的id。
@Entity
@Table
@Data
public class Todo {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;
@Column
String task = "";
@Column(insertable = false, columnDefinition = "int default 1")
Integer status = 1;
@CreatedDate
@Column(updatable = false, nullable = false)
Date createTime = new Date();
@LastModifiedDate
@Column(nullable = false)
Date updateTime = new Date();
@JsonBackReference
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name="user_id")
private User user;
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name="todos_tag", joinColumns = {@JoinColumn(name="tag_id")}, inverseJoinColumns = {@JoinColumn(name="todo_id")})
Set<Tag> tags;
}
在TAG的Entity 增加@ManyToMany的標註
mappedBy屬性設定了雙向關聯至tags哪裡
@Entity
@Table
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;
@Column
public String tag;
@ManyToMany(cascade=CascadeType.ALL, mappedBy="tags")
Set<Todo> todos;
}
接著,來試一下此關聯是否成功,
打開資料庫UI介面時,發現有一個TODOS_TAG的中介表格,接著插入TODO的資料與TAG的資料,並插入TODOS_TAG的一筆關聯資料,
INSERT INTO USER (NAME, GENDER) VALUES ('caili', 2);
INSERT INTO TAG (TAG) VALUES ('生活');
INSERT INTO TODO (TASK, STATUS, UPDATE_TIME, CREATE_TIME, USER_ID) values ('寫鐵人賽', 1, '2020-09-09 17: 00', '2020-09-09 17: 00', 1);
INSERT INTO TODOS_TAG (TODO_ID, TAG_ID) values(1, 1) // 關聯TODO與TAG
接著到我們昨天寫的http://localhost:9100/api/users/1/todos
查看user 1 的代辦事項有哪些並且得知代辦事項有哪些tags。