how to insert multi values into one column?-CodePudding

I have a question of mysql java pojo. I try to insert multi values into one column. But the code throws null values. These sql queries are used not on jpa, but r2dbc spring webflux. As you know table realtionship is not supported in r2dbc driver, so I try to insert the values directly to each tables. First I make pojo codes.

@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;
}

And I execute the bleow SQL statments

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
);

The table relationship of post and tag table is one to many, Below codes are the sql insert codes.

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');

The above SQL statements are executed when the project starts. The 'tagId' member variable of 'post' java class has java Collection type which means having multi tag values. But when the response of reactive web returns, the tag values are 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}]

How can I insert multiple tag_id values of tag class into post.tagId? Any idea?

CodePudding user response：

You can't link the tags table directly in R2DBC. Another way of doing it is executing two queries at once. Since I have no idea what your endpoint looks like here is my implementation of it:

TagRepository:

public interface TagRepository extends ReactiveCrudRepository<Tag, Integer> {
    Flux<Tag> getTagsByPostId(@Param("post_id") Integer postId);
}

PostRepository:

public interface PostRepository extends ReactiveCrudRepository<Post, Integer> {
}

Then to get a posts tags you can just execute two queries at once when you have the post id:

Mono.zip(tagRepository.getTagsByPostId(postID).collectList(),
postRepository.findById(postID));

After doing that the Mono.zip function returns a Tuple<List<Tag>, Post>. You can then use tuple.getT1() to get the list of tags and tuple.getT2() to get the post.