Kurs Java od podstaw Tydzień 7

Podstawy relacyjnych baz danych

Podstawy relacyjnych baz danych

Rozpoczynając temat baz danych obowiązkowo trzeba wspomnieć o relacjach w bazach danych, czyli zagadnieniu, które bardzo ułatwia odwzorowywanie nam danych relacji w aplikacji.

Czym są relacje?

Relacje to nic innego jak zależności między konkretnymi obiektami – encjami. Relacyjne bazy danych opierają się na teorii mnogości, jednak nie będziemy się w to aż tak wciągać. Nie obchodzi nas to jak to działa „pod spodem”, a to jak możemy to w fajny sposób wykorzystać w naszych aplikacjach.

Jaka może być przykładowa relacja?

Przypuśćmy, że mamy encję ludzi, każdy z nich ma jakieś podstawowe informacje – takie jak imię, nazwisko, wiek i pesel oraz tworzy z drugą encją – numery telefonu – relację. Relacja polega na tym, że w jednej tabeli mamy informację o jednym człowieku, a w drugiej tabeli jest rekord odpowiadający (jest relacja) konkretnemu człowiekowi – w naszym przypadku jest to numer telefonu.

Możemy tworzyć wiele różnych relacji, następnym przykładem może być tabela wraz z rolami na stronie np. moderator, administrator, user. Każda rola ma różne funkcje w aplikacji, dlatego każdy użytkownik powinien mieć jakąś przypisaną do siebie.

Oczywiście role możemy bezpośrednio zapisywać przy użytkowników – jednak lepszym rozwiązaniem jest właśnie tabela na role i osobna tabela na użytkowników, gdzie występuje relacja między nimi.

Mówię o relacjach i mówię, ale jak to dokładnie jest widoczne w bazie danych? Jeśli mamy relację między tabelami to musimy ją jakoś identyfikować, a jeśli przychodzi nam słowo identyfikacja to przychodzi nam kolejne słowo – id. Właśnie tak, relacja między rekordami jest nadawana przez konkretne ID – są to primary key oraz foreign key.

Keys…

O ile primary key już znasz – dla przypomnienia jest to główny (primary) klucz w naszej tabeli, jest on unikalny dzięki niemu możemy zidentyfikować konkretny rekord w tabeli.

Do naszej tabeli możemy również dodać foreign key czyli klucz obcy, który właśnie odpowiada za relację między tabelami. To on właśnie określa relację, czyli dokładnie w tej kolumnie jest trzymany primary key rekordu z drugiej tabeli, który jest właśnie w relacji z tabelą.

Trochę zagmatwane, ale wszystko się wyjaśni, ale jeszcze chwilę…

Relacje

Żeby było trochę trudniej musimy jeszcze powiedzieć o rodzajach relacji, a mamy ich dokładnie cztery. Po krótce opiszę Ci każdą z nich.

OneToOne

Relacja jeden do jednego polega na tym, że rekord z jednej tabeli ma dokładnie jedną relację z rekordem z drugiej tabeli.

Co może być przykładem?

Człowiek i jego numer pesel – relacja jest dokładnie jeden do jednego.

Człowiek może mieć tylko jeden numer pesela numer pesel może być przypisany tylko do jednego człowieka.

Ot co, cała filozofia tej relacji.

OneToMany

Jeden do wielu to relacja, gdy jeden rekord ma relację z wieloma rekordami z innej tabeli.

Przykładem takiej relacji mogą być właśnie wcześniej omawiane role w obrębie aplikacji – jedna rola może być przypisana do wielu użytkowników jednocześnie.

Właśnie tą relację będziemy implementować w przykładzie.

ManyToMany

Na koniec zostaje nam relacja wiele do wielu, czyli możliwość przypisania wielu rekordów do wielu rekordów. 😉

Przykładem może być wielu hurtowników, którzy kupują wiele produktów. Każdy produkt może być przypisany do wielu hurtowników, każdy hurtownik może być przypisany do wielu produktów.

I to tyle relacji, z grubsza powinieneś to widzieć. 😉

Przykład – OneToMany

Tak jak wspomniałem my wykorzystamy relację OneToMany w naszej aplikacji, czyli będziemy mieli jedną tabelę, której rekordy będą przypisane do wielu rekordów z innej, jednak druga tabela będzie posiadała relację tylko do jednego rekordu z pierwszej tabeli.

Pierwsza tabela roles będzie przedstawiała role użytkownika na stronie, będzie miała dokładnie takie kolumny:

  • ID
  • ROLE

I tyle nam wystarczy w tej tabeli, zaś druga users będzie przedstawiała użytkownika, który będzie w relacji do jednej roli z tabeli roles.

  • ID
  • NAME
  • LASTNAME
  • AGE
  • USER_ROLE_ID

W tabeli users pojawia nam się kolumna user_role_id będzie to tak naprawdę foreign key czyli klucz obcy do rekordów z tabeli roles – to własnie po nim będziemy mogli zidentyfikować jaką role ma użytkownik. 😉

Tworzenie tabel

Skoro znamy już schemat tabel to czas je stworzyć – włączamy naszą konsolę MySQL, wybieramy bazę danych – u mnie jest to people.

use <nazwa-bazy-danych>

No i tworzymy tabelę roles:

create table roles( 
  id int primary key not null auto_increment,
  role varchar(25) not null
);

Oraz tabela users:

create table users 
(
  id int primary key not null auto_increment,
  name varchar(50) not null,
  lastname varchar(50) not null,
  age int not null,
  user_role_id int,
  foreign key(user_role_id) references roles(id)
);

Pojawiły się nowa słowa kluczowe foreign key oraz references – już tłumaczę o co chodzi.

Foreign key jako argument przyjmuje nazwy kolumny z tabeli users, która ma być kluczem obcym – zaś references definiuje, do której tabeli ma być nawiązana relacja – w naszym przypadku jest to tabela roles i jej kolumna id.

Po utworzeniu tych tabel możemy oczywiście dodać jakieś role – niech to będą user i admin – dodaj je wielkimi literami, aby nie było problemu podczas późniejszej konwersji do enum.

insert into roles(role) values("USER");
insert into roles(role) values("ADMIN");

I po użyciu select widzimy, że się poprawnie dodało:

mysql> select * from roles;
+----+-------+
| id | role  |
+----+-------+
|  1 | user  |
|  2 | admin |
+----+-------+
2 rows in set (0.00 sec)

Napiszmy kod!

Na początek sklonuj repozytorium, na którym bedziemy pracować – jest to repozytorium bazujące na kodzie z poprzednich lekcji czyli UserDao.

git clone https://github.com/1024kb-pl/KJOP_UserDao.git

Mając już projekt stwórzmy nowego brancha – user-roles.

git checkout -b "user-roles"

Mając nowy branch możemy przejść do implementancji i lekkiej refaktoryzacji kodu.

Role

Na początek stwórzmy sobie enum odpowiedzialny za role – będzie to ładniejsze rozwiązanie. 😉

public enum Role {
    USER, ADMIN;
}

UserRole

Następnie tworzymy sobie encję – czyli model odpowiedzialny za reprezentację rekordu z tabeli roles.

public class UserRole {
    private Integer id;
    private Role role;

    public UserRole(Integer id, Role role) {
        this.id = id;
        this.role = role;
    }

    public UserRole(Role role) {
        this.role = role;
    }

    public Integer getId() {
        return id;
    }

    public Role getRole() {
        return role;
    }

    @Override
    public String toString() {
        return "UserRole{" +
                "id=" + id +
                ", role=" + role +
                '}';
    }
}

Encja musi zawierać takie pola jak tabela w bazie, aby móc je łatwo przenosić w aplikacji. 😉

User

Musimy dokonać małych zmian w Userze, dodamy mu pole typu UserRole, które będzie definiowało rolę Usera – dodatkowo getter, zmiana w konstruktorze i metoda toString() – aby wszystko było spójne z dotychczasowym Userem.

public class UserRole {
    private Integer id;
    private Role role;

    public UserRole(Integer id, Role role) {
        this.id = id;
        this.role = role;
    }

    public UserRole(Role role) {
        this.role = role;
    }

    public Integer getId() {
        return id;
    }

    public Role getRole() {
        return role;
    }

    @Override
    public String toString() {
        return "UserRole{" +
                "id=" + id +
                ", role=" + role +
                '}';
    }
}

UserRoleDao

Na podstawie UserDao stwórzmy sobię klasę odpowiedzialną za połączenie z tabelą roles.

Wszystko wygląda na początku podobnie – zmieniamy tylko nazwę tabeli:

public class UserRoleDao {

    private Connection connection;
    private final String databaseName = "people";
    private final String tableName = "roles";
    private final String user = "root";
    private final String password = "admin";

    public UserRoleDao() {
        init();
    }

    private void init() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/"+databaseName+"?useSSL=false", user, password);
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Na podstawie poprzedniej klasy UserDao zaimplementujmy sobie następujące metody, które nam pomogą w aplikacji:

getAllUserRoles()

Do wyciągania wszystkich ról.

public List<UserRole> getAllUserRoles() {
    List<UserRole> userRoles = new LinkedList<UserRole>();
    Statement statement = null;
    try {
        statement = connection.createStatement();
        String query = "select * from " + tableName;
        ResultSet resultSet = statement.executeQuery(query);

        while (resultSet.next()) {
            Integer id = resultSet.getInt("id");
            String name = resultSet.getString("role");


            userRoles.add(new UserRole(id, Role.valueOf(name)));
        }
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return userRoles;
}

getRoleById()

Do wyciągania roli po jego id.

public UserRole getRoleById(Integer id) {
    Statement statement = null;
    try {
        String query = "select * from " + tableName + " where id = '" + id + "'";
        statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery(query);

        while (resultSet.next()) {
            String role = resultSet.getString("role");
            Role userRole = Role.valueOf(role);
            return new UserRole(id, userRole);
        }
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return null;
}

GetRoleIdByName

Do wyciągania id roli po jej nazwie

public Integer getRoleIdByName(String roleName) {
    Statement statement = null;
    try {
        statement = connection.createStatement();
        String query = "select * from " + tableName + " where role = '" + roleName + "'";

        ResultSet resultSet = statement.executeQuery(query);

        while (resultSet.next()) {
            Integer id = resultSet.getInt("id");
            return id;
        }
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return 0;
}

Co prawda metody getRoleIdByName getRoleById moglibyśmy oprzeć na bazie metody getAllUsers – jednak nie jest to opłacalne, wszystkie możliwe operacje lepiej przerzucać na bazę danych, która zrobi to z pewnością szybciej niż nasze pętle w aplikacji. 😉

UserRoleDao – refaktoryzacja

Mamy już zbudowaną UserRoleDao teraz czas na refaktoryzację userRoleDao.
Na początek dodajemy UserRoleDao jako pole w klasie.

private UserRoleDao userRoleDao = new UserRoleDao();

Oraz zmieniamy nazwę tabeli na users:

private final String tableName = "users";

Podczas wyciągania Userów musimy również wyciągnąć jego rolę z innej tabeli na podstawie user_role_id, które zapisujemy razem z naszym userem. 😉

while (resultSet.next()) {
    Integer id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    String lastname = resultSet.getString("lastname");
    Integer age = resultSet.getInt("age");
    Integer userRoleId = resultSet.getInt("user_role_id");
    UserRole userRole = userRoleDao.getRoleById(userRoleId);


    User user = new User(id, name, lastname, age, userRole);
    users.add(user);
}

Aby uniknąć problemu n + 1 selectów warto byłoby tu zastosować join podczas select – jednak ty jeszcze nie wiesz czym jest join, dlatego póki co wybierzemy to rozwiązanie. 😉

Podczas tworzenia usera musimy również zapisać id roli, która została mu przypisana – a znamy tylko jej nazwę. Dlatego musimy najpierw wyciągnąć jego id po nazwie, a dopiero zapisac usera.

public void createUser(User user) {
    PreparedStatement statement;
    try {
        Integer roleId = userRoleDao.getRoleIdByName(user.getUserRole().getRole().name());
        String query = "insert into " + tableName + " (name, lastname, age, user_role_id) values(?, ?, ?, ?)";
        statement = connection.prepareStatement(query);

        statement.setString(1, user.getName());
        statement.setString(2, user.getLastname());
        statement.setInt(3, user.getAge());
        statement.setInt(4, roleId);
        System.out.println(roleId);

        statement.execute();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Wyciąganie id roli po nazwie jest wykonywane tutaj:

Integer roleId = userRoleDao.getRoleIdByName(user.getUserRole().getRole().name());

Podczas update usera również musimy poznać roleId – w końcu jego id jest nieaktualne, a znamy tylko jego nazwę. 😉

public void updateUser(User user) {
    PreparedStatement statement;
    try {
        Integer roleId = userRoleDao.getRoleIdByName(user.getUserRole().getRole().name());
        String query = "update " + tableName + " set name = ?, lastname = ?, age = ?, user_role_id = ? where id=?";
        statement = connection.prepareStatement(query);

        statement.setString(1, user.getName());
        statement.setString(2, user.getLastname());
        statement.setInt(3, user.getAge());
        statement.setInt(4, roleId);
        statement.setInt(5, user.getId());

        statement.execute();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Kilka zmian w main…

Musimy również edytować naszą klasę Main:

Dodanie UserRoleDao:

static UserRoleDao userRoleDao = new UserRoleDao();

Dodanie wczytywania roli w createUser:

public static void createUser() {
    String name, lastname, role;
    Integer age;

    System.out.println("Type a name: ");
    name = scanner.next();

    System.out.println("Type a lastname: ");
    lastname = scanner.next();

    System.out.println("Type your age: ");
    age = scanner.nextInt();

    System.out.println("Type user role: {USER, ADMIN) ");
    role = scanner.next();

    User user = new User(name, lastname, age, new UserRole(Role.valueOf(role)));
    userDao.createUser(user);
    System.out.println("Utworzono Usera: " + user.toString());
}

Możesz zauważyc tutaj taki zapis:

Role.valueOf(role))

Odpowiada on za zwrócenie odpowiedniego typu wyliczeniowego na podstawie nazwy – czyli nazwy typu.

I jeszcze małe zmiany w updateUser:

public static void updateUser() {
    String lastname, name, role;
    Integer id, age;

    System.out.println("Type user id to update: ");
    id = scanner.nextInt();

    System.out.println("Type a new name: ");
    name = scanner.next();

    System.out.println("Type a new lastname: ");
    lastname = scanner.next();


    System.out.println("Type a new age: ");
    age = scanner.nextInt();

    System.out.println("Type user role: {USER, ADMIN) ");
    role = scanner.next();

    User user = new User(id, name, lastname, age, new UserRole(Role.valueOf(role)));
    userDao.updateUser(user);
    System.out.println("Update usera: " + user);
}

I jeszcze główne wywołanie lekko zmodyfikujmy:

public static void main(String[] args) {
    System.out.println("User roles: " + userRoleDao.getAllUserRoles());
    createUser();
    deleteUser();
    calculate();

    UserDao userDao = new UserDao();
    System.out.println(userDao.getAllUsers());
    updateUser();
    System.out.println(userDao.getAllUsers());
}

I możemy uruchamiać – jeśli wszystko zrobiłeś to będzie działać poprawnie.

Cały kod aplikacji możesz znaleźc tutaj na branchu – user roles.

Podsumowanie

Relacyjne bazy danych pozwalają nam w prostszy sposób odwzorować prawdziwe relację i czasami kilkukrotnie wykorzystywać tych samych rekordów – w kilku relacjach.

Aby utrwalić swoją wiedzę z relacyjnych baz danych dodaj kolejną relację do tabeli users – adres zamieszkania.

Niech tabela zawiera:

  • ID
  • CITY_NAME
  • ZIP_CODE
  • STREET_NAME
  • HOUSE_NUMBER

Oczywiście do tabeli users musisz dodać kolejny foreign key odpowiedzialny za adres zamieszkania – pamiętaj, że w przypadku foreign key podczas tworzenia nowego usera, adres musi już istnieć, aby móc się do niego odwołać po id.

Dodaj klasę UserAdressDao, która będzie wyświetlała adressa i umożliwiała tworzenie nowych adresów w klasie Main.

Subscribe
Powiadom o
guest
0 komentarzy
Inline Feedbacks
View all comments