我有一个mysql java pojo的问题。我尝试在一列中插入多个值。但是代码抛出空值。这些sql查询不是在jpa上使用,而是在r2dbc spring webflux上使用。如您所知,r2dbc驱动程序不支持表关系,因此我尝试将值直接插入到每个表中。首先我制作pojo代码。
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table
public class Post {
@Id
@Column("post_id")
private Long postId;
@Column
private String title;
@Column
private String body;
@Column("tag_id")
private Collection<Long> tagId; // value of tagId is null
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table
public class Tag {
@Id
@Column("tag_id")
private Long tagId;
@Column
private String body;
@Column("post_id")
private Long postId;
}
执行以下SQL语句
CREATE TABLE IF NOT EXISTS post (
post_id BIGINT NOT NULL AUTO_INCREMENT,
title VARCHAR(30) NOT NULL,
body TEXT,
PRIMARY KEY (post_id)
);
CREATE TABLE IF NOT EXISTS tag (
tag_id BIGINT NOT NULL AUTO_INCREMENT,
body VARCHAR(255),
post_id BIGINT NOT NULL,
PRIMARY KEY (tag_id),
CONSTRAINT tag_ibfk_1 FOREIGN KEY (post_id) REFERENCES post (post_id) ON DELETE CASCADE
);
post和tag表的表关系是一对多,下面的代码是sql insert代码
INSERT INTO post (post_id, title, body) VALUES (1, 2, 'Title 1', 'post #1 body');
INSERT INTO post (post_id, title, body) VALUES (2, 2, 'Title 2', 'post #2 body');
INSERT INTO tag (tag_id, post_id, body) VALUES (1, 1, 'first tag');
INSERT INTO tag (tag_id, post_id, body) VALUES (2, 1, 'second tag');
INSERT INTO tag (tag_id, post_id, body) VALUES (3, 2, 'third tag');
INSERT INTO tag (tag_id, post_id, body) VALUES (4, 2, 'last tag');
上述SQL语句在项目启动时执行。'post' java类的'tagId'成员变量具有java Collection类型,这意味着具有多个标签值。但是当响应式web的响应返回时,标签值为null。
http://localhost:8080/route/post/all
[{"postId":1,"title":"Title 1","body":"post #1 body","tagId":null},{"postId":2,"title":"Title 2","body":"post #2 body","tagId":null}]
如何在post.tagId中插入标签类的多个tag_id值?任何想法?
不能在R2DBC中直接链接tags
表。另一种方法是一次执行两个查询。因为我不知道你的端点看起来像什么,这里是我的实现:
TagRepository:
public interface TagRepository extends ReactiveCrudRepository<Tag, Integer> {
Flux<Tag> getTagsByPostId(@Param("post_id") Integer postId);
}
PostRepository:
public interface PostRepository extends ReactiveCrudRepository<Post, Integer> {
}
然后,当你有post id:
时,你可以一次执行两个查询来获得posts标签:Mono.zip(tagRepository.getTagsByPostId(postID).collectList(),
postRepository.findById(postID));
之后,Mono.zip
函数返回一个Tuple<List<Tag>, Post>
。然后,您可以使用tuple.getT1()
来获取标签列表,使用tuple.getT2()
来获取帖子。