Today our task is to complete the second project on the Hibernate topic. Its essence is to understand the structure of the database, map the entity to existing tables, and add the minimum functionality to check that the mapping is done correctly.

Now in more detail:

  1. Download the dump file and deploy it to your local machine. As a database, we will use a test database, which is distributed as an example along with the MySQL installation package. The dump is needed in order to fix the state of the database, since we cannot guarantee that it will not change in a day, month, year.
  2. We will not have a project template, so create the project yourself. It should be a maven project with all required dependencies ( hibernate-core-jakarta , mysql-connector-java , p6spy ).
  3. Connect our locally deployed database as a datasource in IntelliJ Idea. After that, in the Database tab, put the cursor on the movie schema and press the key combination Alt+Ctrl+Shift+U(only works in the Ultimate version). This will show the structure of the entire movie schema (with column names, keys, etc.). Looks like this:

    I agree, it's not very comfortable to look at. Turn off the display of the title for all columns and comments:

    As a result, you will get a database schema that can already be analyzed:

  4. The schema looks complicated, but it's not that bad. To analyze the structure of the database, you need to find where to start. There is no single correct answer, but I would recommend starting with the film table. Let's go through a few relationships as an example:
    • The relationship between tables film and film_text is an explicit OneToOne relationship, because the table film_text has a film_id field that does NOT refer to an ID from the film table (no foreign key). But by name and logic, we should have this connection. In addition, in the film_text table, the film_id field acts as a primary key, which guarantees that one "film" corresponds to no more than one "film text".
    • Now let's look at tables film and category. Logically, a film can have several categories. And different films can have the same category. In addition, there is an intermediate link table between these two tables: film_category. Based on all of the above, this is an explicit ManyToMany relationship.
    • Let's have a look at tables film and language. From the logical point of view, the film may have a translation into different languages ​​and different films may be in the same language. This sounds like a ManyToMany relationship. But if we look at the contents of the film table, we can see that each row of the table is a unique film. And there is only one language_id field in the line (there is also original_language_id, but in all records it is null, so we can ignore it). That is, one film can have only one language. And different films can have only one language. The connection is ManyToOne (the connection is directed from film to language).
  5. Now the main task is to create all the necessary entity classes and map them onto the movie schema tables.
  6. Add a method that can create a new customer (customer table) with all dependent fields. Do not forget to make the method transactional (so as not to get into the situation that the address of the buyer is recorded in the database, but the buyer himself is not).
  7. Add a transactional method that describes the "customer went and returned a previously rented movie" event. Choose any buyer and rental event of your choice. The rating of the film does not need to be recalculated.
  8. Add a transactional method that describes the event “the buyer went to the store (store) and rented (rental) inventory (inventory) there. At the same time, he made a payment (payment) to the seller (staff). Choose a film (through inventory) at your discretion. The only restriction is that the film must be available for rent. That is, either there should be no inventory records in rental at all, or the return_date column of the rental table for the last rental of this inventory should be filled.
  9. Add a transactional method that describes the event "a new movie was shot, and it became available for rent." You can choose the film, language, actors, categories, etc., at your discretion.
  10. The table structure cannot be changed. But you need to make suggestions for improvement. We identified one problematic place in point 4 (absence of foreign key in the film_text table, on the film_id field of the film table). See if there are still such “blunders” in the database structure. If so, add a readme file to the root of the project and describe these blunders.
Java university

Project analysis: