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 pesel, a 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 i 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.