How to get the correct entity in the DAO layer using JDBC?

Question

Java

There is a program with entities Author, Book, Genre which are connected with each other as many to many.
There are tables in the database to store them and provide links between them.

In java code:

spoiler

public class Author {
  ...
    private List<Book> books;
    private List<Genre> genres;
}

public class Book {
  ...
    private List<Author> authors;
    private List<Genre> genres;
}

public class Genre {
  ...
    private List<Author> authors;
    private List<Book> books;
}

In the database:

spoiler

CREATE TABLE author (
  `author_id` INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
  ...
);

CREATE TABLE book (
  `book_id` INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
  ...
);

CREATE TABLE genre (
  `genre_id` INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
  ...
);

CREATE TABLE author_book (
  `author_id` INT NOT NULL REFERENCES author(author_id),
  `book_id` INT NOT NULL REFERENCES book(book_id),
    UNIQUE KEY `author_book_unique_id` (`author_id`,`book_id`)
);

CREATE TABLE author_genre (
  `author_id` INT NOT NULL REFERENCES author(author_id),
  `genre_id`  INT NOT NULL REFERENCES genre(genre_id),
    UNIQUE KEY `author_genre_id` (`author_id`,`genre_id`)
);


CREATE TABLE book_genre (
  `book_id` INT NOT NULL REFERENCES book(book_id),
  `genre_id`  INT NOT NULL REFERENCES genre(genre_id),
    UNIQUE KEY `book_genre_id` (`book_id`,`genre_id`)
);

For example, the service layer calls the getAuthor(id) method on the DAO layer.
The returned author must have the books and genres fields initialized. There should be
books that the author wrote and the genres in which he worked. In turn, each book should have
information in what genre it is written and what authors it has. A genre should store a list of books related to it
and authors working in this genre.

Thus, returning the author to the service layer, we must load a bunch of information from the database, and this will not be one request, but a lot.
The question is whether this architecture is correct (looking through the code of hibernate examples, I see that it is the entities that are stored, and not their ids).
But how to implement this option in JDBC is not clear in practice. If it is architecturally correct, tell me how to implement it!

Or vice versa, it is necessary to store in the entity not a reference to another entity, but information about the id of another entity.

public class Author {
...
private List booksID;
private List genresID;
}

In the future, the code will be translated to hibernate, but at the moment the answer is needed only using JDBC and Java , without using hibernate and other frameworks.

UPD for those interested in the same topic, it may be useful to look at:
https://ru.stackoverflow.com/questions/1127662/%D0...

Answer 1

Good afternoon.
In Hibernate, when using the ManyToMany annotation, 3 tables are created. 2 tables for the entities themselves, where information is stored and 1 table with mappings, where the id of the entities are stored. In this case, your tables are fine.

The returned author must have the books and genres fields initialized. There should be
books that the author wrote and the genres in which he worked. In turn, each book should have
information in what genre it is written and what authors it has. A genre should store a list of books related to it
and authors working in this genre.

When working in this way (when serializing data or when calling the toString method), you will encounter recursion. For example, an author has a list of books, books have authors, and each time methods will be called that will request either the authors who wrote this book or the books that were written by this author. These issues when working with Jackson are solved using the JsonManagedReference & JsonBackReference or JsonIgnore annotations. For lombok, ToString.Ignore can be used to exclude from toString.

Thus, returning the author to the service layer, we must load a bunch of information from the database, and this will not be one request, but a lot.

If we are talking about hibernate (jpa), then there are options like FetchType (EAGER & LAZY). So, if LAZY, then the information from the List will be loaded only when it is accessed. In the case of EAGER, information is loaded the first time an entity is accessed.
https://thorben-janssen.com/entity-mappings-introd...
Maybe this link will be useful for you:
https://stackoverflow.com/questions/21956042/mappi...