Table of Contents

Obsługa baz danych

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

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.

Projekt

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>

Obsługa baz danych poprzez JDBC

Napiszemy 3 klasy:

Config

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)

BookLoader

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

Baza danych będzie zawierała 3 tabele:

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

Queries

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

Zadania

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

Obsługa baz danych poprzez Hibernate/JPA

Zdefiniujemy dwie klasy encji:

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

Book

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

Author

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

Plik konfiguracyjny Hibernate

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>

Pomocnicza klasa do odczytu konfiguracji i tworzenia sesji

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

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

Klasa HibernateQueries

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

Zadanie

Zrefaktoryzuj kod (lub konfigurację) tak, aby