Obsługa baz danych

W pliku good_reads.csv zgromadzone są informacje o książkach, ich autorach i ocenach czytelników.

• bookID - identyfikator liczbowy
• title - tytuł
• authors - lista autorów, autorzy są oddzieleni znakiem ‘-’
• average_rating - średnia ocena
• isbn - numer ISBN
• isbn13 - inny wariant numeru ISBN
• language_code - język książki
• num_pages - liczba stron
• ratings_count - liczba ocen
• text_reviews_count - liczba recenzji

Wczytamy te dane za pomocą klasy CSVReader i załadujemy do lokalnej bazy H2 (w pliku).

O silniku H2 możesz przeczytać tu: https://www.h2database.com/html/main.html. Jest on najczęściej używany do testów jednostkowych wymagających operacji bazodanowych.

Utwórz nowy projekt wybierając Maven jako narzędzie do budowy systemu. Dodaj następujące zależności od bibliotek zewnętrznych do pliku pom.xml.

Uwaga najnowsza wersja biblioteki lombok to 1.18.42 – jest wymagana dla JDK25. W przypadku dziwnych błędów zmień wersję JDK w sekcji <properties> pliku pom.xml.

    <dependencies>
        <!-- H2 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.3.232</version>
        </dependency>
 
        <!-- SLF4J do logów -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>2.0.9</version>
        </dependency>
 
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.30</version> <!-- najnowsza wersja to 1.18.42 może być wymagana dla JDK25 -->
            <scope>provided</scope>
        </dependency>
 
        <!-- Hibernate core -->
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.3.0.Final</version>
        </dependency>
 
 
    </dependencies>

Napiszemy 3 klasy:

• Config - konfiguracja dostępu do BD
• BookLoader - klasa która ładuje dane do bazy danych
• Queries - klasa implementująca kwerendy

Zdefiniuj klasę z informacjami niezbędnymi do otwarcia połączenia: adresem bazy danymi użytkownika

public class Config {
//    public static final String JDBC_URL = "jdbc:h2:mem:booksdb;DB_CLOSE_DELAY=-1";
    public static final String JDBC_URL = "jdbc:h2:./booksdb"; // wersja plikowa
 
    public static final String USER = "sa";
    public static final String PASSWORD = "";
}

Wartości atrybutów będą wykorzystywane do utworzenia połaczenia z bazą danych:

Connection conn = DriverManager.getConnection(Config.JDBC_URL,Config.USER,Config.PASSWORD)

W funkcji main klasy BookLoader zaimplementowana zostanie funkcjonalność tworzenia tabel i wypełniania ich danymi.

Baza danych będzie zawierała 3 tabele:

• BOOKS - informacje o książkach
• AUTHORS - nazwiska autorów
• BOOK_AUTHOR - relacja wiele do wielu pomiędzy książkami i autorami

Zaczniemy od utworzenia tabel.

    private static void createTables(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
 
        stmt.execute("DROP TABLE IF EXISTS BOOK_AUTHOR");
        stmt.execute("DROP TABLE IF EXISTS AUTHORS");
        stmt.execute("DROP TABLE IF EXISTS BOOKS");
 
        stmt.execute("""
            CREATE TABLE BOOKS (
                bookID BIGINT PRIMARY KEY,
                title VARCHAR(500),
                average_rating DOUBLE,
                isbn VARCHAR(20),
                isbn13 VARCHAR(20),
                language_code VARCHAR(10),
                num_pages INT,
                ratings_count INT,
                text_reviews_count INT
            )
        """);
 
        stmt.execute("""
            CREATE TABLE AUTHORS (
                author_id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(200) UNIQUE
            )
        """);
 
        stmt.execute("""
            CREATE TABLE BOOK_AUTHOR (
                bookID BIGINT,
                author_id BIGINT,
                PRIMARY KEY (bookID, author_id),
                FOREIGN KEY (bookID) REFERENCES BOOKS(bookID),
                FOREIGN KEY (author_id) REFERENCES AUTHORS(author_id)
            )
        """);
    }

W funkcji używany jest obiekt klasy Statement za pośrednictwem którego mozna wykonać kwerendy podane jako tekst.

W przypadku wielokrotnego użycia tych samych kwerend, lepszym rozwiązaniem jest uzycie klasy PreparedStatement z oznaczeniami miejsc, w które należy wstawic parametry. W zależności od typu, ustawione argumenty wywołania są odpowiednio przekształcane (np. przez dodanie cudzysłowów i znaków escape dla tekstów), co zwiększa bezpieczeństwo operacji.

Szkielet funkcji main():

    public static void main(String[] args) {
 
        try (Connection conn = DriverManager.getConnection(Config.JDBC_URL,Config.USER,Config.PASSWORD)) {
 
            conn.setAutoCommit(false); // transakcja
 
            createTables(conn);
 
            // Przygotuj obiekty prepared statement dla kwerend
 
            // Utwórz CSVreader
 
            while (reader.next()) {
                //odczytaj rekord z CSV
 
                //dodaj zawartość do tablic BOOKS
 
                //dodaj zawartość do tablic AUTHORS
 
                //dodaj połączenia BOOK_AUTHOR
                conn.commit();
            }

Definiowanie obiektów PreparedStatement

PreparedStatement insertBook = conn.prepareStatement(
        "INSERT INTO BOOKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
);
 
PreparedStatement insertAuthorIfNotExists = conn.prepareStatement(
        """
            INSERT INTO AUTHORS(name)
            SELECT ?
            WHERE NOT EXISTS (
                SELECT 1 FROM AUTHORS WHERE name = ?
            )
        """);
 
 
PreparedStatement selectAuthorId = conn.prepareStatement(
        "SELECT author_id FROM AUTHORS WHERE name = ?"
);
 
 
 
PreparedStatement insertAuthor = conn.prepareStatement(
        "INSERT INTO AUTHORS(name) VALUES (?)",
        Statement.RETURN_GENERATED_KEYS
);
 
PreparedStatement insertBookAuthor = conn.prepareStatement(
        "INSERT INTO BOOK_AUTHOR (bookID, author_id) VALUES (?, ?)"
);

Dodawanie rekordu do tabeli BOOKS

long bookId = reader.getLong("bookID");
insertBook.setLong(1, bookId);
insertBook.setString(2, reader.get("title"));
insertBook.setDouble(3, reader.getDouble("average_rating"));
insertBook.setString(4, //  "isbn"
insertBook.setString(5, //  "isbn13"
insertBook.setString(6, //  "language_code"
insertBook.setInt(7, //  "num_pages"
insertBook.setInt(8, //  "ratings_count"
insertBook.setInt(9, //  "text_reviews_count"
insertBook.executeUpdate();

Dodawanie autorów do tablic AUTHORS

Wczytaj informacje o autorach i umieść w zbiorze authors ( typu HashSet lub TreeSet). Nazwiska są oddzielone znakiem '-'. Na wszelki wypadek wywołaj też funkcję trim().

W petli po elementach zbioru authors

for (String authorName : authors) {
   // ustaw parametry i wykonaj insertAuthorIfNotExists;
 
    // Odczytaj authorId
    selectAuthorId.setString(1, authorName);
    ResultSet rs = selectAuthorId.executeQuery();
    rs.next();
    long authorId = rs.getLong("author_id");
    rs.close();
 
   // ustaw parametry i wykonaj insertBookAuthor
 
 }

Wykonaj funkcję main

Rekordy powinny zostać dodane do bazy danych

Dodaj klasę Queries:

import java.sql.*;
 
public class Queries {
 
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                Config.JDBC_URL, Config.USER, Config.PASSWORD)) {
 
            System.out.println("Połączenie z bazą OK");
 
            top20BooksByRatings(conn);
 
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * b) 20 książek o average_rating > 4,
     * posortowane malejąco wg ratings_count
     */
    static void top20BooksByRatings(Connection conn) throws SQLException {
        System.out.println("\n=== b) Top 20 książek (average_rating > 4) ===");
 
        String sql = """
                SELECT bookID, title, average_rating, ratings_count
                FROM BOOKS
                WHERE average_rating > 4
                ORDER BY ratings_count DESC
                LIMIT 20
                """;
 
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
 
            while (rs.next()) {
                System.out.printf("%d | %s | %.2f | %d%n",
                        rs.getLong("bookID"),
                        rs.getString("title"),
                        rs.getDouble("average_rating"),
                        rs.getInt("ratings_count"));
            }
        }
    }
}

Napisz w podobny sposób następujące kwerendy:

• Książki, których autorem (lub współautorem) jest “J.R.R. Tolkien” i język = “eng”
• 10 książek mających co najmniej dwóch autorów, posortowane malejąco wg. text_reviews_count
• 10 książek w języku “rus”, posortowane malejąco wg: text_reviews_count i ratings_count
• Ilu autorów (unikalnych) jest w bazie danych?

Zdefiniujemy dwie klasy encji:

• Book
• Author

O encjach możesz przeczytać tu: https://www.baeldung.com/jpa-entities.

package books;  // Nazwa pakietu musi sie zgadzać z wpisem w hibernate.cfg.xml
 
import jakarta.persistence.*;
import lombok.*;
 
import java.util.HashSet;
import java.util.Set;
 
@Entity
@Table(name = "BOOKS")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString(exclude = "authors")
@EqualsAndHashCode(of = "id")
public class Book {
 
    @Id
    @Column(name = "bookID")
    private Long id;
 
    private String title;
 
    private double average_rating;
 
    private String language_code;
 
    private int num_pages;
 
    private int ratings_count;
 
    private int text_reviews_count;
 
    @ManyToMany
    @JoinTable(
            name = "BOOK_AUTHOR",
            joinColumns = @JoinColumn(name = "bookID"),
            inverseJoinColumns = @JoinColumn(name = "author_id")
    )
    private Set<Author> authors = new HashSet<>();
}

Poniższe adnotacje sterują funkcjami biblioteki lombok.

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString(exclude = "authors")
@EqualsAndHashCode(of = "id")

Odpowiadają za automatyczną generację getterów/setterów/konstruktorów i metody toString(). Opis adnotacji lombok: https://projectlombok.org/features/.

Z kolei

• @Entity i @Table(name = “BOOKS”) - łączą encję z tabelą
• @Id i @Column(name = “bookID”) definiują klucz główny
• @ManyToMany oraz @JoinTable definiują sposób odwzorowania relacji wiele do wielu w zbiór.

Podobnie jest zdefiniowana encja Author

package books;  // Nazwa pakietu musi sie zgadzać z wpisem w hibernate.cfg.xml
 
import jakarta.persistence.*;
import lombok.*;
 
import java.util.HashSet;
import java.util.Set;
 
@Entity
@Table(name = "AUTHORS")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString(exclude = "books")
@EqualsAndHashCode(of = "id")
public class Author {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "author_id")
    private Long id;
 
    @Column(nullable = false, unique = true)
    private String name;
 
    @ManyToMany(mappedBy = "authors")
    private Set<Book> books = new HashSet<>();
}

W katalogu src/main/resources umieścimy plik konfiguracyjny hibernate.cfg.xml. Definiuje on dane dostępu oraz wskazuje klasy encji podlegające mapowaniu ORM.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "https://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
 
<hibernate-configuration>
    <session-factory>
 
        <!-- JDBC -->
        <property name="hibernate.connection.driver_class">org.h2.Driver</property>
        <property name="hibernate.connection.url">
            jdbc:h2:file:./booksdb
        </property>
        <property name="hibernate.connection.username">sa</property>
        <property name="hibernate.connection.password"></property>
 
        <!-- Dialect -->
        <!-- property name="hibernate.dialect">org.hibernate.dialect.H2Dialect</property -->
 
        <!-- Debug -->
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
 
        <!-- Schema -->
        <property name="hibernate.hbm2ddl.auto">validate</property>
 
        <!-- Encodings -->
        <property name="hibernate.connection.characterEncoding">UTF-8</property>
 
        <!-- Mapowanie encji -->
        <mapping class="books.Book"/>
        <mapping class="books.Author"/>
 
    </session-factory>
</hibernate-configuration>

SessionFactory to podstatwowy obiekt Hibernate odpowiedzialny przechowywanie metadanych ORM (odwzorowanie tabel w encje) oraz za tworzenie sesji. Przy inicjalizacji:

• ładuje konfigurację z ustalonej lokalizacji
• parsuje adnotacje i buduje wewnętrzną reprezentację metadanych

Na żądanie tworzy sesję (otwiera połączenie z bazą danych).

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
 
public class HibernateUtil {
 
    private static final SessionFactory sessionFactory =
            new Configuration().configure().buildSessionFactory();
 
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
}

Napisz klasę HibernateQueries z własna funkcją funkcją main(). Wywołaj funkcję test do sprawdzenia, czy konfiguracja jest ładowana i tworzone jest połaczenie.

public class HibernateQueries {
    static void test(){
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            System.out.println("Hibernate działa: sesja otwarta " + session.isConnected());
        }
    }
    public static void main(String[] args) {
        test();
    }    
}

Przykłady kwerend w języku HQL (Hibernate Query Language):

Książki Tolkiena (bez specyfikacji języka)

    static void booksByTolkien(){
        String hql = """
            select distinct b
            from Book b
            join b.authors a
            where a.name = :name
        """;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
 
            List<Book> books = session.createQuery(hql, Book.class)
                    .setParameter("name", "J.R.R. Tolkien")
                    .getResultList();
 
            System.out.println("Książki Tolkiena:");
            for (Book b : books) {
                System.out.println(b.getId() + " | " + b.getTitle());
            }
        }

Autorzy według liczby książek

    static void topTenAuthors(){
        String hql = """
            select a, count(b)
            from Author a
            join a.books b
            group by a
            order by count(b) desc
            """;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
 
            List<Object[]> result = session.createQuery(hql, Object[].class)
                    .setMaxResults(10)
                    .getResultList();
 
            System.out.println("Top 10 autorów (liczba książek):");
 
            for (Object[] row : result) {
                Author a = (Author) row[0];
                Long count = (Long) row[1];
 
                System.out.printf("%s | %d%n", a.getName(), count);
            }
        }
    }

Policz ile książek jest zdefiniowanych dla każdego języka?

    static void booksCountByLanguage() {
        String hql = """
        select b.language_code, count(b)
        from Book b
        group by b.language_code
        order by count(b) desc
    """;
 
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
 
            List<Object[]> result = session.createQuery(hql, Object[].class)
                    .getResultList();
 
            System.out.println("Liczba książek wg języka:");
 
            for (Object[] row : result) {
                String lang = (String) row[0];
                Long count = (Long) row[1];
                System.out.printf("%s | %d%n", lang, count);
            }
        }
    }

10 książek w języku “spa”, posortowanych malejąco wg. liczby autorów text_reviews_count

    static void topSpanishBooksByReviews() {
        String hql = """
        select distinct b
        from Book b
        where b.language_code = :lang
        order by b.text_reviews_count desc
    """;
 
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
 
            List<Book> books = session.createQuery(hql, Book.class)
                    .setParameter("lang", "spa")
                    .setMaxResults(10)
                    .getResultList();
 
            System.out.println("Top 10 książek (spa, wg text_reviews_count):");
 
            for (Book b : books) {
                System.out.printf(
                        "%s | recenzje: %d | autorów: %d%n",
                        b.getTitle(),
                        b.getText_reviews_count(),
                        b.getAuthors().size()
                );
            }
        }
    }

10 książek posortowanych malejąco po sumarycznej ocenie obliczanej jako iloczyn average_rating i 'ratings_count' oraz rosnąco po num_pages

    static void topBooksByWeightedRating() {
        String hql = """
        select b
        from Book b
        order by (b.average_rating * b.ratings_count) desc,
                 b.num_pages asc
    """;
 
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
 
            List<Book> books = session.createQuery(hql, Book.class)
                    .setMaxResults(10)
                    .getResultList();
 
            System.out.println("Top 10 książek wg ważonej oceny:");
 
            for (Book b : books) {
                double score = b.getAverage_rating() * b.getRatings_count();
                System.out.printf(
                        "%s | score=%.2f | pages=%d%n",
                        b.getTitle(),
                        score,
                        b.getNum_pages()
                );
            }
        }
    }

Zrefaktoryzuj kod (lub konfigurację) tak, aby

• nazwy kolumn w tabelach zachowywały standard snake_case
• nazwy atrybutach w encjach (i wygenerowane przez lombok settery/gettery) zachowywały typowy dla języka Java standard camelCase
• zademonstruj, że przykładowe kwerendy uruchamiają się po refaktoryzacji (wywołaj je w main).