Optimal storage of the list of Strings in the database

user_8557673 0
Viewed 173

I have an Entity:

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

and

public class BookAvailability {

    @Id
    private Integer id;
    private String bookId;
    private String type;
    private String term;
    private Instant dateFrom;
    private Instant dateTo;
}

how to optimally store it in the database?

Multiple books can be in multiple filters, and I have to query for filterId to get the bookId list. Something tells me that the JSON field will not be optimal?

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

In the Criteria API, I have a condition that bookId must be equal for both entities and filterId = the specified value. Maybe model it like below? But how do I reflect in an Entity that I have many rows and convert them to a list?

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;
hibernate sql java
1 Answers

From what I understand from the description of your question, what you need to have in place is a ManyToMany relationship, between a BookFilter object and a Book object.

Suppose the following example:

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

}

With both in place, you can go ahead and query using the BookFilter entity and have the JPA provider (hibernate in this case) return back the associated set of Book belonging to the BookFilter in question.

user_29259620
Related