数据库中字符串列表的最佳存储

Question

我有一个实体：

public class BookFilter {
@Id
private Integer id;
private List <String> bookId;
private String filterId;
public BookFilter (List <String> bookId, String filterId) {
this.bookId = bookId;
this.filterId = filterId;
}
}

和

public class BookAvailability {
@Id
private Integer id;
private String bookId;
private String type;
private String term;
private Instant dateFrom;
private Instant dateTo;
}

如何以最佳方式将其存储在数据库中？

多本书可以在多个过滤器中，我必须查询filterId才能获得bookId列表。有什么告诉我JSON字段不会是最优的？

CREATE TABLE `asset_global_filter`
(
`id`        INTEGER,
`asset_uid` JSON,
`filter_id` VARCHAR(38) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

在Criteria API中，我有一个条件，即bookId对于两个实体都必须相等，并且filterId=指定的值。也许可以像下面这样建模？但是，我如何在实体中反映我有许多行并将它们转换为列表？

CREATE TABLE `asset_global_filter`
(
`id`        INTEGER,
`asset_uid` VARCHAR(255) NOT NULL,
`filter_id` VARCHAR(38) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

Answer 1

根据我对您问题的描述，您需要的是BookFilter对象和Book对象之间的ManyToMany关系。

假设以下示例：

@Data
@Entity(name = "BookFilter")
@Table(name = "book_filter")
public class BookFilter {
@Id
@GeneratedValue(generator = "book_filter_sequence", strategy = GenerationType.SEQUENCE)
@SequenceGenerator(name = "book_filter_sequence", sequenceName = "book_filter_sequence", allocationSize = 1)
private Long id;
private String filterId;
@ManyToMany(
cascade = {
CascadeType.PERSIST,
CascadeType.MERGE
}
)
@JoinTable(
name = "filter_books",
joinColumns = @JoinColumn(name = "bookfilter_id"),
inverseJoinColumns = @JoinColumn(name = "book_id")
)
private Set<Book> books = new HashSet<>();
}

@Data
@Entity
@Table(name = "book")
public class Book {
@Id
@GeneratedValue(generator = "book_sequence", strategy = GenerationType.SEQUENCE)
@SequenceGenerator(name = "book_sequence", sequenceName = "book_sequence", allocationSize = 1)
private Long id;
@NaturalId
private String name;
private String author;
private String publisher;
private String plot;
@ManyToMany(mappedBy = "books")
@ToString.Exclude
@EqualsAndHashCode.Exclude
private Set<BookFilter> filters = new HashSet<>();
}

在这两种情况下，您可以继续使用BookFilter实体进行查询，并让JPA提供程序(在本例中为hibernate(返回属于所讨论的BookFilter的相关Book集。