Materiál k cvičení DBM1 týden #4, sezona 2023/2024

Strukturování kódu Java aplikace

V této sekci představím několik způsobů jak lépe strukturovat Java kód pro případy nahrávání dat do databáze, resp. datového skladu. Další cvičení s komplexnějším řešeným případem bude na toto navazovat.

V předchozích dílech jsme:

Vytvoření třídy pro hráče

První krok k lepšímu strukturování kódu je vytvoření třídy pro hráče. Třída bude obsahovat atributy pro jednotlivé sloupce tabulky players.


  

public class Player {

    public String id;
    public String name;

    @Override
    public String toString() {
        return "Player{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                '}';
    }
}

V kódu výše je vytvořena nosná třída s atributy id a name. Dále je přetížena metoda toString pro čitelnější výpis obsahu objektu, která se projeví při volání např. System.out.println(player).

※ Formálně by bylo lepší atributy deklarovat jako private a používat gettery a settery pro přístup k atributům. Pro zjednodušení kódu a intuitivnost volím jiný přístup.

Třída pro práci s databází

Dalším krokem je vytvoření třídy pro práci s databází. Třída bude udržovat spojení s databází, vytvářet očekávané schéma tabulek, umožní vkládání dat a vyhodnocení dotazů.


  

import java.sql.*;

public class DuckDBOperations {

    private Connection conn;

    public DuckDBOperations(String dbPath) {
        try {
            // Initialize the connection
            this.conn = DriverManager.getConnection("jdbc:duckdb:" + dbPath);
            // Create the players table if it does not exist
            createPlayersTable();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void createPlayersTable() throws SQLException {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS players (\n"
                + "  id VARCHAR PRIMARY KEY,\n"
                + "  name VARCHAR\n"
                + ");";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSQL);
        }
    }
    
    public void insertPlayer(Player player) {
        String insertSQL = "INSERT OR REPLACE INTO players (id, name) VALUES (?, ?);";

        try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
            pstmt.setString(1, player.id);
            pstmt.setString(2, player.name);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
// ... Selecty
    
}

※ Catch řešení výjimek by zasloužilo lepší implementaci. Zároveň by bylo vhodné lépe ošetřit situaci po zavření spojení, aby se zablokovaly ostatní akce.

V kódu výše je vytvořena třída DuckDBOperations s konstruktorem, který vytvoří spojení s databází a vytvoří tabulku players pokud neexistuje. Dále je implementována metoda insertPlayer pro vkládání hráčů (jako instance třídy Player) do tabulky.

Všimněte si použití pomocných SQL konstrukcí CREATE TABLE IF NOT EXISTS a INSERT OR REPLACE INTO. První z nich vytvoří tabulku jen v případě, že již neexistuje tabulka se stejným jménem (s libovolnou strukturou a obsahem). Pokud by bylo potřeba starou tabulku nahradit novou, je potřeba zavolat DROP TABLE před vytvořením nové tabulky.

Druhý konstrukt INSERT OR REPLACE INTO vloží nový záznam, nebo nahradí existující záznam s příslušným klíčem. K správné funkcionalitě je nutné mít v tabulce definovaný primární klíč nebo alespoň unikátní omezení. Tento konstrukt je ve skutečnosti zkratkou pro ON CONFLICT DO UPDATE ... viz DuckDB dokumentace.

Alternativním řešením než přepsání původního záznamu při konfliktu by bylo původní záznam nechat a nový zahodit. Toho by bylo dosaženo pomocí INSERT OR IGNORE INTO konstruktu.

Použití nových tříd

Nově vytořené třídy lze použít v hlavním programu následovně:


  

public static void main(String[] args) throws SQLException {
    DuckDBOperations dbOps = new DuckDBOperations("a.db"); // database path

    try{
        Reader reader = Files.newBufferedReader(Paths.get("data/matches.csv"));
        CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT.withFirstRecordAsHeader());
        for (CSVRecord record : csvParser) {
            Player pl1 = new Player();
            pl1.id = record.get("player1_id");
            pl1.name = record.get("player1_name");

            Player pl2 = new Player();
            pl2.id = record.get("player2_id");
            pl2.name = record.get("player2_name");

            dbOps.insertPlayer(pl1);
            dbOps.insertPlayer(pl2);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    dbOps.close(); // Close the connection when done
}

Třída pro extrakci dat z CSV

Dalším krokem je separace kódu pro extrakci dat do zvláštní třídy. Třída bude později kromě extrakce dat o hráčích nabízet i další extrakce, např. o zápasech.


  

import org.apache.commons.csv.*;
import java.io.Reader;
import java.nio.file.*;
import java.util.ArrayList;
import java.util.List;

public class CsvExtractor {

    private Path filePath = null;

    public CsvExtractor(String filePath) {
        this.filePath = Paths.get(filePath);
    }

    private CSVParser readFile() {
        try {
            Reader reader = Files.newBufferedReader(filePath);
            return new CSVParser(reader, CSVFormat.DEFAULT.withFirstRecordAsHeader());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    public List<Player> parsePlayers(){
        List<Player> players = new ArrayList<>();

        for (CSVRecord record : readFile()) {
            Player pl1 = new Player();
            pl1.id = record.get("player1_id");
            pl1.name = record.get("player1_name");

            Player pl2 = new Player();
            pl2.id = record.get("player2_id");
            pl2.name = record.get("player2_name");

            players.add(pl1);
            players.add(pl2);
        }

        return players;
    }
    
    // ... další metody pro extrakci dat
}

Třída momentálně obsahuje obecnou metodu readFile pro načtení CSV souboru a metodu parsePlayers, která provede následnou extrakci podobně jako v předchozím řešeném případu.

Metoda parsePlayer vrací seznam hráčů, který je následně potřeba ještě vložit do databáze.

Použití třídy pro extrakci

Metody extrakční třídy lze použít následovně:


  

public static void main(String[] args) throws SQLException {
    DuckDBOperations dbOps = new DuckDBOperations("a.db");
    CsvExtractor csvExtractor = new CsvExtractor("data/matches.csv");
    csvExtractor.parsePlayers().forEach(dbOps::insertPlayer);

    dbOps.close(); // Close the connection when done
}

Použití forEach a metody insertPlayer je zkrácený zápis pro iteraci přes seznam a volání metody pro každý prvek.

Rozšíření o zápasy

Pro zpracování dat o zápasech je potřeba přidat jednak třídu nesoucí data k zápasu, jednak rozšířit třídu pro extrakci dat.


  

public class Match {
    public int duration;
    public String server;
    public String leaderboard;
    public String match_id;
    public String created_at;
    
    // Player 1 fields
    public String player1_id;
    public String player1_name;
    public String player1_result;
    public String player1_race;
    public double player1_mmr;
    public int player1_ping;
    // Player 2 fields
    
    public String player2_id;
    public String player2_name;
    public String player2_result;
    public String player2_race;
    public double player2_mmr;
    public int player2_ping;

    @Override
    public String toString() {
        return "Match{" +
                "duration=" + duration +
                ", server='" + server + '\'' +
                ", leaderboard='" + leaderboard + '\'' +
                ", match_id='" + match_id + '\'' +
                ", created_at='" + created_at + '\'' +
                ", player1_id='" + player1_id + '\'' +
                ", player1_result='" + player1_result + '\'' +
                ", player1_race='" + player1_race + '\'' +
                ", player1_mmr=" + player1_mmr +
                ", player1_ping=" + player1_ping +
                ", player2_id='" + player2_id + '\'' +
                ", player2_result='" + player2_result + '\'' +
                ", player2_race='" + player2_race + '\'' +
                ", player2_mmr=" + player2_mmr +
                ", player2_ping=" + player2_ping +
                '}';
    }
}

※ U komplexních tříd doporučuji zkusit nějakého pomocníka pro generování kódu. V tomto ohledu např. ChatGPT při poskytnutí několika řádek CSV souboru a obecného pokynu funguje poměrně dobře.

Do třídy CsvExtractor je nutné přidat implementaci jedné metody parseMatches.


  

public List<Match> parseMatches(){
    List<Match> matches = new ArrayList<>();

    for (CSVRecord record : readFile()) {
        Match match = new Match();
        match.duration = Integer.parseInt(record.get(0)); // hotfix BOM char messing up the "duration" text
        match.server = record.get("server");
        match.leaderboard = record.get("leaderboard");
        match.match_id = record.get("match_id");
        match.created_at = record.get("created_at");
        match.player1_id = record.get("player1_id");
        match.player1_result = record.get("player1_result");
        match.player1_race = record.get("player1_race");
        match.player1_mmr = Double.parseDouble(record.get("player1_mmr"));
        match.player1_ping = Integer.parseInt(record.get("player1_ping"));
        match.player2_id = record.get("player2_id");
        match.player2_result = record.get("player2_result");
        match.player2_race = record.get("player2_race");
        match.player2_mmr = Double.parseDouble(record.get("player2_mmr"));
        match.player2_ping = Integer.parseInt(record.get("player2_ping"));

        matches.add(match);
    }

    return matches;
}

Za zmínku stojí řešení u atributu duration, kde se vyskytl problém při zpracování souboru s 📚 BOM (Byte Order Mark) znakem. Knihovna pro načtení CSV souboru tento znak špatně interpretovala a požadavek .get("duration") končil chybou. Proto byl použit ekvivalentní zápis s použitím indexu sloupce, ze kterého má být načtena hodnota.

V případě použití jiných datových typů než String je nutné vhodně data přetypovat viz. Integer.parseInt(), Double.parseDouble().

Na závěr je přidána do třídy DuckDBOperations metoda pro vkládání zápasů do databáze a vytvoření tabulky pro zápasy.


  

private void createMatchesTable() throws SQLException {
    String createTableSQL = "CREATE TABLE IF NOT EXISTS matches (\n"
    + "  duration INTEGER, \n"
    + "  server VARCHAR, \n"
    + "  leaderboard VARCHAR, \n"
    + "  match_id VARCHAR PRIMARY KEY, \n"
    + "  created_at VARCHAR, \n"
    + "  player1_id VARCHAR, \n"
    + "  player1_result VARCHAR, \n"
    + "  player1_race VARCHAR, \n"
    + "  player1_mmr DOUBLE, \n"
    + "  player1_ping INTEGER, \n"
    + "  player2_id VARCHAR, \n"
    + "  player2_result VARCHAR, \n"
    + "  player2_race VARCHAR, \n"
    + "  player2_mmr DOUBLE, \n"
    + "  player2_ping INTEGER\n"
    + ");\n";
    try (Statement stmt = conn.createStatement()) {
    stmt.execute(createTableSQL);
    }
}

    // ...
    
public void insertMatch(Match match) {
    String insertSQL = "INSERT OR REPLACE INTO matches (" +
            "duration, server, leaderboard, match_id, created_at, " +
            "player1_id, player1_result, player1_race, player1_mmr, player1_ping, " +
            "player2_id, player2_result, player2_race, player2_mmr, player2_ping" +
            ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
        // Set parameters from Match object
        pstmt.setInt(1, match.duration);
        pstmt.setString(2, match.server);
        pstmt.setString(3, match.leaderboard);
        pstmt.setString(4, match.match_id);
        pstmt.setString(5, match.created_at);
        pstmt.setString(6, match.player1_id);
        pstmt.setString(7, match.player1_result);
        pstmt.setString(8, match.player1_race);
        pstmt.setDouble(9, match.player1_mmr);
        pstmt.setInt(10, match.player1_ping);
        pstmt.setString(11, match.player2_id);
        pstmt.setString(12, match.player2_result);
        pstmt.setString(13, match.player2_race);
        pstmt.setDouble(14, match.player2_mmr);
        pstmt.setInt(15, match.player2_ping);

        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Celý program by pak mohl být následující:


  

public static void main(String[] args) throws SQLException {
    DuckDBOperations dbOps = new DuckDBOperations("a.db"); // Specify your database path
    CsvExtractor csvExtractor = new CsvExtractor("data/matches.csv");
    csvExtractor.parsePlayers().forEach(dbOps::insertPlayer);
    csvExtractor.parseMatches().forEach(dbOps::insertMatch);

    dbOps.close(); // Close the connection when done
}

Základní souhrny

Třída pro komunikaci s databází bude rozšířena o výběrové dotazy řešící vybrané analytické otázky.

Q1: Sestavte SQL dotaz pro spočtení, kolik her se odehrálo na jednotlivých serverech a seřaďte výsledky od nejčastějšího.

Výsledek London - 40
Washington_DC - 31
Frankfurt - 19
...

Q2: Sestavte SQL dotaz pro spočtení, kolik her odehráli jednotliví hráči a seřaďte výsledky od nejaktivnějšího. Zdůvodněte, zda výsledek dává smysl.