Accès aux bases de données relationnelles en Java (JDBC)

%%shell
docker volume create shared-data
docker run --rm  --volume shared-data:/shared-data \
    alpine ash -c "mkdir -p /shared-data/gtfs && cd /shared-data/gtfs/ && wget -O gtfs-complet.zip --quiet https://s3.eu-west-1.amazonaws.com/files.orchestra.ratpdev.com/networks/rd-toulon/exports/gtfs-complet.zip && unzip -o *.zip && rm *.zip"

%%shell
docker run --rm --quiet --volume shared-data:/shared-data \
    --entrypoint sh \
    ghcr.io/public-transport/gtfs-via-postgres:4 -eu -o pipefail -c '/app/cli.js $@ --require-dependencies --trips-without-shape-id --lower-case-lang-codes -- /shared-data/gtfs/*.txt> /shared-data/gtfs/data.sql'

%%shell
docker network ls|grep notebooknet > /dev/null || docker network create notebooknet
docker container ls|grep db > /dev/null && docker stop db

echo "--> Launch DBMS"
docker run --quiet --rm --name db -d \
      --env POSTGRES_USER=${DB_USERNAME:-dba} \
      --env POSTGRES_PASSWORD=${DB_PASSWORD:-secretsecret} \
      --env POSTGRES_DB=${DB_NAME:-notebook-db} \
      --volume dbhost-data:/var/lib/postgresql \
      --volume shared-data:/shared-data \
      --network=notebooknet \
      -p 5432:5432 \
      postgis/postgis:17-master
          
docker container exec \
    --env PGPASSWORD=secretsecret \
    db \
    sh -c 'until pg_isready -U dba -d notebook-db; do echo "$(date) - waiting for database to start"; sleep 1; done'
    

%%shell  
echo "--> Create DB"
docker container exec \
    --env PGPASSWORD=secretsecret \
    db \
    psql -h db -U dba notebook-db -c 'CREATE DATABASE gtfs OWNER dba'

echo "--> Populate DB"
docker container exec \
    --env PGPASSWORD=secretsecret \
    db \
    psql --quiet -h db -U dba gtfs -f /shared-data/gtfs/data.sql;

%%shell
docker run --quiet --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c \\dt;

%%shell
# Définition d'une requête SQL pour rechercher les arrêts "Campus"
read -r -d '' QUERY << EOM
SELECT 
    stop_id,      -- Identifiant unique de l'arrêt
    stop_name,    -- Nom de l'arrêt
    stop_loc      -- Localisation de l'arrêt,  Géométrie PostGIS (format natif) 
FROM 

    stops
WHERE stop_name ILIKE '%Campus%'
LIMIT 3;
EOM

docker run --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c $QUERY;

%%shell
# Requête SQL pour trouver les arrêts "Université" avec leurs coordonnées GPS
read -r -d '' QUERY << EOM
SELECT 
    stop_id,      -- Identifiant unique de l'arrêt
    stop_name,    -- Nom de l'arrêt
    ST_X(ST_Transform(stop_loc::geometry, 4326)) AS stop_latitude, -- Conversion des coordonnées en latitude (EPSG:4326 = standard GPS) 
    ST_Y(ST_Transform(stop_loc::geometry, 4326)) AS stop_longitude -- Conversion des coordonnées en longitude (EPSG:4326 = standard GPS)
FROM 
    stops
WHERE stop_id ILIKE 'GACAM%'
LIMIT 3;
EOM

docker run --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c $QUERY;

%%shell
# Requête SQL pour trouver les arrêts "Université" avec leurs coordonnées GPS
read -r -d '' QUERY << EOM
SELECT 
    route_id,           -- Identifiant unique de la ligne
    route_short_name,   -- Nom court de la ligne
    route_long_name     -- Nom long de la ligne
FROM 
    routes
WHERE route_long_name ILIKE '%Tln%'
LIMIT 3;
EOM

docker run --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c $QUERY;

%%shell
# Requête pour trouver les 3 arrêts les plus proches de l'Université de Toulon
read -r -d '' QUERY << EOM
-- Recherche des arrêts par proximité géographique
SELECT 
    stop_id,    -- Identifiant de l'arrêt
    stop_name,  -- Nom de l'arrêt
    -- Calcul de la distance entre chaque arrêt et l'Université
    ROUND(ST_Distance(
        stop_loc::geography,    -- Position de l'arrêt
        ST_SetSRID(           -- Définit le système de coordonnées
            ST_MakePoint(      -- Crée un point géographique
                6.016499,      -- Longitude Université
                43.135440      -- Latitude Université
            ), 
            4326              -- En WGS84
        )
    )) AS distance,

    -- Conversion des coordonnées en WGS84 (format GPS standard)
    ST_X(                      -- Extrait la latitude
        ST_Transform(          -- Convertit les coordonnées
            stop_loc::geometry, -- Position de l'arrêt
            4326               -- Code EPSG pour WGS84 (standard GPS)
        )
    ) AS LATITUDE,
    
    ST_Y(ST_Transform(stop_loc::geometry, 4326)) AS LONGITUDE -- Idem pour longitude

FROM stops
ORDER BY distance
     ASC                     -- Tri du plus proche au plus éloigné
LIMIT 5                      -- Retourne les 5 plus proches
EOM

docker run --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c $QUERY;

%%shell
# Store a query in the QUERY shell variable
read -r -d '' QUERY << EOM
SELECT 
    s.stop_id AS arret,              -- Identifiant de l'arrêt
    s.stop_name AS nom_arret,        -- Nom de l'arrêt
    r.route_short_name AS ligne,     -- Numéro de ligne
    r.route_long_name AS nom_ligne,  -- Nom de la ligne
    t.trip_headsign AS destination,  -- Destination du voyage
    st.departure_time AS depart      -- Heure de départ 
FROM 
    stop_times st
    JOIN stops s ON st.stop_id = s.stop_id           -- Liaison avec les arrêts
    JOIN trips t ON st.trip_id = t.trip_id           -- Liaison avec les voyages
    JOIN routes r ON t.route_id = r.route_id         -- Liaison avec les lignes
WHERE 
    st.stop_id = 'VACAMN'
    AND r.route_short_name = 'U'
    AND st.departure_time > (NOW()::time) -- Départ après l'heure actuelle

ORDER BY 
    st.departure_time
LIMIT 3;
EOM

docker run --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c $QUERY;

%%shell
# Store a query in the QUERY shell variable
read -r -d '' QUERY << EOM
SELECT 
    r.route_short_name AS ligne,     -- Numéro de ligne
    st.arrival_time AS arrivee,      -- Heure d'arrivée
    st.departure_time AS depart      -- Heure de départ 
FROM stop_times st
JOIN stops s ON st.stop_id = s.stop_id           -- Liaison avec les arrêts
JOIN trips t ON st.trip_id = t.trip_id           -- Liaison avec les voyages
JOIN routes r ON t.route_id = r.route_id         -- Liaison avec les lignes
WHERE st.stop_id = 'GAUNSS'

LIMIT 3
EOM

docker run --rm \
    --env PGPASSWORD=secretsecret \
    --network=notebooknet \
    postgres \
        psql -h db -U dba gtfs -c $QUERY;

/**
 * Représente un arrêt de transport public
 */
public record Stop(
    String id,            // Identifiant unique
    String name,          // Nom de l'arrêt
    Double latitude,      // Latitude
    Double longitude,     // Longitude
    String locationType   // arrêt, station
) {
    public Stop {
        if (id == null || name == null || latitude == null || longitude == null) {
            throw new IllegalArgumentException("Required fields cannot be null");
        }
    }
}

/**
 * Représente une ligne de transport
 */
public record Route(
    String id,         // Identifiant unique
    String shortName,  // Numéro/Code court
    String longName    // Nom complet
) {
    public Route {
        if (id == null || shortName == null) {
            throw new IllegalArgumentException("Required fields cannot be null");
        }
    }
}

/**
 * Représente un voyage sur une ligne
 */
public record Trip(
    String id,          // Identifiant unique
    Route route,         // ID de la ligne
    String tripHeadsign,    // Destination affichée
    String tripShortName   // Nom court du voyage
) {
    public Trip {
        if (id == null || route == null || tripHeadsign == null) {
            throw new IllegalArgumentException("Required fields cannot be null");
        }
    }
}

import java.time.LocalTime;

/**
 * Représente un horaire de passage à un arrêt
 */
public record StopTime(
    Trip trip,               // ID du voyage
    Stop stop,               // ID de l'arrêt
    LocalTime arrivalTime,   // Heure d'arrivée
    LocalTime departureTime, // Heure de départ
    String stopHeadsign      // Destination affichée à l'arrêt
) {
    public StopTime {
        // Validation des champs obligatoires
        if (trip == null || stop == null || 
            arrivalTime == null || departureTime == null) {
            throw new IllegalArgumentException(
                "trip, stop, times are required"
            );
        }
        
        // Validation de la cohérence des heures
        if (departureTime.isBefore(arrivalTime)) {
            throw new IllegalArgumentException(
                "Departure time cannot be before arrival time"
            );
        }
    }
}

Stop stop=new Stop("TOCMAS", "Champ de Mars", 5.940372, 43.121363,"stop");
Route route=new Route("U", "U", "Université");
Trip trip=new Trip("U_1", route, "Université", "U1");
StopTime stopTime=new StopTime(trip, stop, LocalTime.of(8, 0), LocalTime.of(8, 5), "Université");

stopTime;

%maven org.postgresql:postgresql:42.7.3

import java.sql.Connection;
import java.sql.DriverManager;



System.out.println("%s connecting on %s.".formatted(DB_USERNAME,DB_URL));

final String query = """
   SELECT stop_id, 
          stop_name, 
          ST_X(ST_Transform(stop_loc::geometry, 4326)) AS LATITUDE, 
          ST_Y(ST_Transform(stop_loc::geometry, 4326)) AS LONGITUDE, 
          location_type 
   FROM stops 
   ORDER BY ST_Distance(stop_loc::geometry, ST_SetSRID(ST_MakePoint(5.940207, 43.121145), 4326)) ASC
   LIMIT 3""";

try(Connection connection = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
         Statement stmt = connection.createStatement();
      ) {             
         ResultSet rs = stmt.executeQuery(query);
         long id=0;
         while (rs.next())             
             System.out.println(new Stop(rs.getString("stop_id"),
                                         rs.getString("stop_name"),
                                         rs.getDouble("latitude"),
                                         rs.getDouble("longitude"),
                                         rs.getString("location_type")
                                        ));

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

        // Create a list to store Stop objects
        List<Stop> stops = new ArrayList<>();

        final String query = """
            SELECT stop_id, 
                   stop_name, 
                   ST_X(ST_Transform(stop_loc::geometry, 4326)) AS LATITUDE, 
                   ST_Y(ST_Transform(stop_loc::geometry, 4326)) AS LONGITUDE, 
                   location_type 
            FROM stops 
            ORDER BY ST_Distance(stop_loc::geometry, ST_SetSRID(ST_MakePoint(5.940207, 43.121145), 4326)) ASC
            LIMIT 3""";
         
         try(Connection connection = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
                  Statement stmt = connection.createStatement();
               ) {            
                  ResultSet rs = stmt.executeQuery(query);
                  long id=0;
                  while (rs.next())             
                      stops.add(new Stop(rs.getString("stop_id"),
                                                  rs.getString("stop_name"),
                                                  rs.getDouble("latitude"),
                                                  rs.getDouble("longitude"),
                                                  rs.getString("location_type")
                                                 ));
         
               } catch (SQLException e) {
                  e.printStackTrace();
               }
               
stops.forEach(System.out::println);               

try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
             Statement statement = connection.createStatement()) {
            // SQL query to insert data into the 'agency' table
            String sql = """
                    INSERT INTO agency(agency_id, agency_name, agency_url, agency_timezone,
                                      agency_lang, agency_phone, agency_fare_url, agency_email)
                    VALUES(3, 'Neverland', 'http://nowhere.fr', 'Europe/Paris',
                           'fr', '555-555-555', 'http://nowhere.fr/fare', 'neverland@nowhere.org')""";

            // Execute the query and get the number of changes
            int numberOfChanges = statement.executeUpdate(sql);

            // Print the result
            System.out.println("Nombre de changements: " + numberOfChanges);
        } catch (SQLException e) {
            e.printStackTrace();
        }

String wrongQuery = " SELECT * FROM Employee" ;
try (Connection connection = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(wrongQuery)) {
        } catch (SQLException e) {
            //Erreur lors de la requête
            log.severe(e.getMessage());        
        }

// Déclaration de la requête SQL
String sql = """
SELECT stop_id, 
       stop_name, 
       ST_X(ST_Transform(stop_loc::geometry, 4326)) AS LATITUDE, 
       ST_Y(ST_Transform(stop_loc::geometry, 4326)) AS LONGITUDE, 
       location_type 
FROM stops 
WHERE stop_id = ?             
ORDER BY ST_Distance(stop_loc::geometry, ST_SetSRID(ST_MakePoint(5.940207, 43.121145), 4326)) ASC
LIMIT 3""";

try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
     PreparedStatement pstmt = connection.prepareStatement(sql)) {
    // Tableau contenant les identifiants à rechercher
    String[] ids = {"TOCMAS", "TOCMAN", "TOPIXO"};
    for (String id : ids) {
        // Remplacer le paramètre de la requête par l'identifiant actuel
        pstmt.setString(1, id);
        // Exécution de la requête
        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                // Création et affichage d'un objet Stop avec les données récupérées
                System.out.println(new Stop(rs.getString("stop_id"),
                        rs.getString("stop_name"),
                        rs.getDouble("latitude"),
                        rs.getDouble("longitude"),
                        rs.getString("location_type")));
            }
        }
    }
} catch (SQLException e) {
    // Gestion des exceptions liées à la connexion ou à la requête
    e.printStackTrace();
}

try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD)) {

    // Création de la table account
    String createAccountTableSql = "CREATE TABLE account (" +
        "id SERIAL PRIMARY KEY," +
        "name VARCHAR(100) NOT NULL," +
        "balance DEC(15,2) NOT NULL)";
    try (Statement statement = connection.createStatement()) {
        statement.executeUpdate(createAccountTableSql);
    }

    // Création des comptes pour Alice et Bob avec une requête préparée
    String createAccountSql = "INSERT INTO account(name, balance) VALUES(?, ?);";
    try (PreparedStatement pstmt = connection.prepareStatement(createAccountSql)) {
        pstmt.setString(1, "Bob");
        pstmt.setInt(2, 1000);
        pstmt.executeUpdate();

        pstmt.setString(1, "Alice");
        pstmt.setInt(2, 1000);
        pstmt.executeUpdate();
    }

    // Préparation de la requête pour mettre à jour le solde des comptes
    String updateAccountSql = "UPDATE account SET balance = balance + ? WHERE id = ?";
    try (PreparedStatement pstmtIncreaseAccount = connection.prepareStatement(updateAccountSql)) {

        // Sauvegarde de l'état auto-commit
        boolean autoCommit = connection.getAutoCommit();
        try {
            connection.setAutoCommit(false); // Désactivation de l'auto-commit pour la transaction

            // Retirer 500€ du compte de Bob
            pstmtIncreaseAccount.setInt(1, -500);
            pstmtIncreaseAccount.setInt(2, 1); // ID de Bob
            pstmtIncreaseAccount.executeUpdate();

            // Ajouter 500€ au compte d'Alice
            pstmtIncreaseAccount.setInt(1, 500);
            pstmtIncreaseAccount.setInt(2, 2); // ID d'Alice
            pstmtIncreaseAccount.executeUpdate();

            connection.commit(); // Valider la transaction
        } catch (SQLException exc) {
            connection.rollback(); // Annuler la transaction en cas de problème
            throw exc; // Relancer l'exception pour gestion ultérieure
        } finally {
            connection.setAutoCommit(autoCommit); // Rétablir l'état auto-commit
        }
    }

    // Suppression de la table account
    try (Statement statement = connection.createStatement()) {
        statement.executeUpdate("DROP TABLE account");
    }

} catch (SQLException e) {
    e.printStackTrace(); // Gestion des exceptions SQL
}