Le mode programme : Qu'est-ce que c'est ?

• Écrire des programmes qui utilisent des ordres SQL pour interagir avec une base de données.
• Ces ordres SQL, similaires à ceux utilisés en mode interactif, sont intégrés dans un programme pour être exécutés dynamiquement.
• Comparable à PL/SQL pour les SGBD, mais conçu pour des langages polyvalents (Java, Python, C#, etc.) avec une gestion complète des connexions et des requêtes.
• En complément, des outils modernes comme les ORM (Object-Relational Mappers) tels que Hibernate sont souvent utilisés pour simplifier l'accès aux bases dans les applications complexes.

Le mode programme : Pourquoi l'utiliser ?

• Étendre les capacités des bases de données aux applications modernes :
  • Calculs complexes, analyses de données.
  • Gestion d'interfaces utilisateur et des services réseau.
• Offrir un accès programmatique aux bases de données pour intégrer des fonctionnalités avancées directement dans des applications.
• Ciblé pour les développeurs cherchant à gérer dynamiquement les données d'une base dans leur code.

Le mode programme : Comment ça fonctionne ?

• Un programme (Java, Python, etc.) :
• Établit une connexion à une base de données à l'aide d'une API (comme JDBC).
• Utilise des mécanismes de gestion des connexions, requêtes et transactions pour interagir avec le SGBD.
• Exécute des ordres SQL tout en assurant une gestion sécurisée et efficace des ressources.

Exemple complet

import java.sql.*;

public class ModernJdbcExample {
    private static final String DB_URL = "jdbc:oracle:thin:@oracle-db:1521/MYPDB";
    private static final String USER = "PH";
    private static final String PASS = "MySuperPassword2024";

    public static void main(String[] args) {
        var sql = "UPDATE personne SET age = age + 1 WHERE nom = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "Jeanne");
            int updatedRows = stmt.executeUpdate();
            System.out.printf("Updated %d rows%n", updatedRows);

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

Problèmes du mode programme : connexion

• Fonction getConnection :
• Récupère driver indiqué par url (déjà chargé)
• L'utilise pour établir connexion avec le serveur
• Renvoie un objet correspondant (classe Connection)
• URL : String
• jdbc:oracle:drivertype:user/password@database
• Drivertype : oci7, oci8, thin (applet : thin)
• @database :
• Optionnel, sinon défaut
• oci : ligne de tnsnames.ora
• thin : host:port:sid, SQL*Net

Exemple complet

// A l'ancienne
Connection c = DriverManager.getConnection(url);

// Version moderne avec DataSource
@Bean
public DataSource dataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:orcl");
    config.setUsername("user");
    config.setPassword("password");
    config.setMaximumPoolSize(10);
    return new HikariDataSource(config);
}

// Utilisation
try (Connection conn = dataSource.getConnection()) {
    // Utilisation de la connexion
}

L'objet de la classe Connection

• "Session" :
1. Connexion à un serveur donné
2. Séquence d'ordres SQL
3. Déconnexion
• Possible : une application JDBC a plusieurs sessions avec un ou plusieurs serveurs
• Chaque session est alors un client
• Un objet Connection :
• Définit une session
• Utilisé via ses méthodes pour :
• Envoi d'ordres BD au serveur
• Gestion des transactions
• Fournit informations sur tables, procédures stockées, etc.
• Créable uniquement par getConnection (Connection : interface)

Introduction aux Transactions

• Une transaction regroupe un ensemble d'opérations SQL.
• Garantit les propriétés ACID :
  • Atomicité : Tout ou rien.
  • Cohérence : L'état de la base reste valide.
  • Isolation : Transactions indépendantes.
  • Durabilité : Changements persistants après commit.
• Exemples typiques :
  • Transferts bancaires (débit/crédit).
  • Mises à jour impliquant plusieurs tables.

Transactions en JDBC

• Les transactions sont activées automatiquement par défaut :
• Chaque commande SQL est validée individuellement.
• Pour gérer manuellement les transactions :
• Désactiver le mode automatique : conn.setAutoCommit(false);
• Valider manuellement : conn.commit();
• Annuler en cas d'échec : conn.rollback();
• Important : Toujours fermer les connexions dans un bloc finally.

Exemple Pratique de Transaction

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    conn.setAutoCommit(false);

    try (PreparedStatement stmt1 = conn.prepareStatement(
            "INSERT INTO commandes (id, client) VALUES (?, ?)");
            PreparedStatement stmt2 = conn.prepareStatement(
            "INSERT INTO details (commande_id, produit, quantite) VALUES (?, ?, ?)")) {

        stmt1.setInt(1, 101);
        stmt1.setString(2, "Client A");
        stmt1.executeUpdate();

        stmt2.setInt(1, 101);
        stmt2.setString(2, "Produit X");
        stmt2.setInt(3, 5);
        stmt2.executeUpdate();

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();  // Annule toutes les opérations
        e.printStackTrace();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Déconnexion

• Méthode de Connection :
• void close() throws SQLException
• Effectue déconnexion normale du serveur qui termine la session
• Utile si on veut déconnexion immédiate au lieu d'attendre l'automatique
• Déconnexion implicite automatique lorsque l'objet Connection est libéré par le garbage collector

Ordres SQL sans paramètre

• Tout ordre SQL copiable-collable sous SQL*Plus
• 2 catégories :
• select
• Autres
• Objet classe Statement :
• Créé par méthode de Connection :
• Statement createStatement() throws SQLException
• Ne pas utiliser si ordre exécuté plusieurs fois avec paramètres différents, car moins efficace

Envoi d'ordres au serveur

• Un seul objet Statement en général (plusieurs possibles hors module)
• Envoyer l'ordre au serveur par la méthode de Statement (si ordre non requête) :
• int executeUpdate(String sql) throws SQLException
• Renvoie :
• Nombre de lignes traitées pour insert, update, delete
• 0 sinon
• String sql : sans le point-virgule de SQL*Plus

Bonnes pratiques avec JDBC

• Utilisez toujours un bloc try-with-resources pour fermer automatiquement les connexions.

  try (Connection conn = DriverManager.getConnection(url);
          PreparedStatement stmt = conn.prepareStatement(...)) {
      // Travail avec la base
  } catch (SQLException e) {
      e.printStackTrace();
  }

• Préférez les PreparedStatement pour éviter les injections SQL.
• Testez vos requêtes SQL dans un environnement contrôlé avant intégration.
• Surveillez et documentez les versions des drivers JDBC utilisés.

Recommandations pratiques

• Utiliser des méthodes de gestion de connexions robustes pour éviter les fuites de ressources
• Privilégier l'utilisation des PreparedStatement pour les ordres SQL (paramétrés ou non)
• Tester les connexions et les ordres SQL dans un environnement contrôlé avant déploiement
• Documenter clairement les dépendances des projets utilisant JDBC

Connectivité aux bases de données

• Chargement automatique : Plus besoin de Class.forName() depuis JDBC 4.0+
• Support multi-bases dans un même programme :
  • Oracle : jdbc:oracle:thin:@localhost:1521/ORCL
  • PostgreSQL : jdbc:postgresql://localhost:5432/mabase
  • MySQL : jdbc:mysql://localhost:3306/mabase
• Pools de connexions recommandés en production : HikariCP, C3P0, DBCP
• Alternatives modernes : JPA/Hibernate pour les applications complexes

Déroulement d'un programme

1. Importer classes JDBC (statique)
2. Lancement et début de l'exécution d'un programme JDBC : inconnu du serveur
3. Charger driver(s) voulu(s) par le programmeur (= choix du SGBD)
4. Connexion (JDBC) : devient client
5. Envoi ordres SQL au serveur (JDBC)
6. Déconnexion (JDBC) : termine en tant que client
7. Inconnu du serveur : continue son exécution, puis termine comme programme (voir sur croquis)

Exemple 1

• Table : (client VARCHAR2(10), dest VARCHAR2(10), jour INTEGER)
• Afficher la première destination de Cassavetes par ordre alphabétique et le jour du voyage
• On suppose qu'il y en a au moins une
• Commençons intuitivement sur un exemple :

Statement s = c.createStatement();
ResultSet rset = 
    s.executeQuery(
        "select dest, jour from train " +
        "where client = 'Cassavetes' order by dest");
rset.next();
System.out.println("Cassavetes va à " + rset.getString(1) +
                    " le jour " + rset.getInt("JOUR"));
rset.close();
s.close();

var sql = """
    SELECT dest, jour 
    FROM train 
    WHERE client = 'Cassavetes' 
    ORDER BY dest""";

try (var conn = dataSource.getConnection();
     var stmt = conn.prepareStatement(sql);
     var rs = stmt.executeQuery()) {
    
    if (rs.next()) {
        log.info("Cassavetes va à {} le jour {}", 
            rs.getString("dest"), 
            rs.getInt("jour"));
    }
} catch (SQLException e) {
    log.error("Query failed", e);
    throw new DatabaseException("Failed to fetch train journey", e);
}

Curseur JDBC

• Sans paramètre ou avec
• Déclaration et nommage :
  • Création « objet ordre »
  • Statement ou PreparedStatement selon si paramètres
• Remplissage : executeQuery renvoie un objet ResultSet
• Avancement du pointeur et indicateur de fin de curseur
• Récupération colonne par colonne
• Libération : close() ou lors de la fermeture de l'ordre

Étapes pour l'utilisation des curseurs JDBC

1. Créer un Statement ou PreparedStatement en fonction du besoin.
2. Exécuter une requête SQL avec executeQuery.
3. Récupérer un ResultSet, représentant les résultats de la requête.
4. Parcourir le curseur ligne par ligne à l'aide de next().
5. Fermer le ResultSet et le Statement pour libérer les ressources.

// A l'ancienne
ResultSet rset = stmt.executeQuery(
    "SELECT nom FROM personne");
while (rset.next()) {
    System.out.println(rset.getString("nom"));
}
rset.close();
stmt.close();

// Modern JDBC Query
String sql = "SELECT nom FROM personne";
try (var conn = dataSource.getConnection();
     var stmt = conn.prepareStatement(sql);
     var rs = stmt.executeQuery()) {

    while (rs.next()) {
        System.out.println(rs.getString("nom"));
    }
}

Statement vs PreparedStatement

PreparedStatement stmt = conn.prepareStatement(
    "UPDATE personne SET age = age + 1 WHERE nom = ?");
stmt.setString(1, "Jeanne");
stmt.executeUpdate();

Exemple moderne

public void incrementAge(String nom) {
    String sql = "UPDATE personne SET age = age + 1 WHERE nom = ?";
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, nom);
            int updatedRows = stmt.executeUpdate();
            if (updatedRows == 0) {
                throw new EntityNotFoundException("Personne non trouvée : " + nom);
            }
            conn.commit();
            logger.debug("Âge incrémenté pour : {}", nom);
        } catch (Exception e) {
            conn.rollback();
            throw new DatabaseException("Erreur lors de la mise à jour de l'âge", e);
        }
    }
}

Exemple 2

• Afficher la première destination de Cassavetes et le jour du voyage, et la deuxième si elle existe
• On suppose qu'il y en a au moins une

Statement s = c.createStatement();
ResultSet rset = 
    s.executeQuery(
        "select dest, jour from train " +
        "where client = 'Cassavetes' order by dest");
rset.next();
System.out.println("Cassavetes va à " + rset.getString(1) +
                    " le jour " + rset.getInt("JOUR"));
if (rset.next())
    System.out.println("Cassavetes va à " + rset.getString(1) +
                        " le jour " + rset.getInt("JOUR"));
rset.close();
s.close();

var sql = """
    SELECT dest, jour 
    FROM train 
    WHERE client = 'Cassavetes' 
    ORDER BY dest""";

try (var conn = dataSource.getConnection();
     var stmt = conn.prepareStatement(sql);
     var rs = stmt.executeQuery()) {
    
    if (rs.next()) {
        log.info("Cassavetes va à {} le jour {}", 
            rs.getString("dest"), 
            rs.getInt("jour"));
            
        if (rs.next()) {
            log.info("Cassavetes va à {} le jour {}", 
                rs.getString("dest"), 
                rs.getInt("jour"));
        }
    }
} catch (SQLException e) {
    log.error("Query failed", e);
    throw new DatabaseException("Failed to fetch train journeys", e);
}

Exemple 3

• Les afficher toutes
• On ne suppose rien
• Si aucun : on n'entre pas dans la boucle
• Si un ou plusieurs :
  • On se positionne sur la prochaine ligne, qu'on affiche
  • Après la dernière : on teste rset.next et on ne rentre pas dans la boucle

Statement s = c.createStatement();
ResultSet rset = s.executeQuery(...);
while (rset.next()) {
    System.out.println(rset.getString(1));
}
rset.close();
s.close();

var sql = "SELECT dest FROM train WHERE client = 'Cassavetes'";

try (var conn = dataSource.getConnection();
     var stmt = conn.prepareStatement(sql);
     var rs = stmt.executeQuery()) {

    while (rs.next()) {
        log.info("Destination: {}", rs.getString("dest"));
    }
} catch (SQLException e) {
    log.error("Query failed", e);
    throw new DatabaseException("Failed to fetch destinations", e);
}

PreparedStatement

• Création par méthode de Connection :
• PreparedStatement prepareStatement(String sql) throws SQLException
• String sql :
• Contient un symbole « ? » pour chaque paramètre
• Affectation par setXXX
• Exécution par :
• executeUpdate si SQL non requête
• executeQuery si SQL requête

Avantage PreparedStatement sur Statement

String[] p = {"Jeanne", "Jules"};
Statement stmt = conn.createStatement();
for (int i = 0; i < p.length; i++) {
    stmt.executeUpdate(
        """update personne set 
        age = age+1 where nom = '""" 
        + p[i] + "'");
}

PreparedStatement stmt = conn.prepareStatement(
    """update personne set age = age+1 
        where nom = ?""");
for (int i = 0; i < p.length; i++) {
    stmt.setString(1, p[i]);
    stmt.executeUpdate();
}

Exemple moderne

public void celebrerAnniversaire(String nom) {
    String sql = "{call anniversaire(?, ?)}";
    try (Connection conn = dataSource.getConnection();
         CallableStatement stmt = conn.prepareCall(sql)) {
        
        stmt.setString(1, nom);
        stmt.registerOutParameter(2, Types.INTEGER); // Nouveau âge
        
        stmt.execute();
        
        int nouvelAge = stmt.getInt(2);
        logger.info("Anniversaire célébré pour {} - Nouvel âge : {}", nom, nouvelAge);
        
    } catch (SQLException e) {
        throw new DatabaseException("Erreur lors de la célébration d'anniversaire", e);
    }
}

Cas concret : Utilisation des procédures stockées

• Exemple : Calculer le total des ventes pour un produit donné.
• Procédure stockée dans la base :

  CREATE PROCEDURE totalVentes(produit_id IN NUMBER, total OUT NUMBER) AS
  BEGIN
      SELECT SUM(quantite) INTO total FROM ventes WHERE produit = produit_id;
  END;

• Appel depuis JDBC :

  CallableStatement cstmt = conn.prepareCall("{ call totalVentes(?, ?) }");
  cstmt.setInt(1, 101); // produit_id
  cstmt.registerOutParameter(2, Types.INTEGER); // total
  cstmt.execute();
  System.out.println("Total des ventes : " + cstmt.getInt(2));

Lecture de paramètres OUT

• Lecture d'un paramètre OUT d'une procédure stockée
• Valeur renvoyée par une fonction stockée

Après exécution :

• registerOutParameters : déclare le type du paramètre
• getXXX

Exemple d'appel de la fonction âge :

CallableStatement stmt = conn.prepareCall("{? = call age(?)}");
stmt.setString(2, "Jeanne");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.execute();
System.out.println(stmt.getInt(1));

JDBC et l'architecture en couches

• Même principe qu'en objet :
• Données encapsulées
• Accès par procédures stockées (sauf rares cas)
• En Java :
• Interaction limitée avec la base :
• Appelle les procédures stockées
• Gère commit et rollback
• Si nécessaire : curseur pour affichage
• Dédié à la partie non BD de l'application

DAO (Data Access Object) Pattern

• Sépare la logique d'accès aux données du reste de l'application
• Structure type :
• Interface DAO définissant les opérations CRUD
• Classe d'implémentation concrète
• Classe modèle (POJO)

// Interface DAO
public interface PersonneDAO {
    void insert(Personne personne);
    Personne findById(int id);
    List<personne> findAll();
    void update(Personne personne);
    void delete(int id);
}
</personne>

// Implémentation
public class PersonneDAOImpl implements PersonneDAO {
    private Connection connection;

    public PersonneDAOImpl(Connection connection) {
        this.connection = connection;
    }

    @Override
    public void insert(Personne personne) {
    try (PreparedStatement stmt = connection.prepareStatement(
        "INSERT INTO personne (nom, age) VALUES (?, ?)")) {
            stmt.setString(1, personne.getNom());
            stmt.setInt(2, personne.getAge());
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    // Autres méthodes CRUD...
}

Batch Processing moderne

public void insertPersonnes(List<personne> personnes) {
    String sql = "INSERT INTO personne (nom, age, email) VALUES (?, ?, ?)";
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (Personne p : personnes) {
                stmt.setString(1, p.getNom());
                stmt.setInt(2, p.getAge());
                stmt.setString(3, p.getEmail());
                stmt.addBatch();
            }
            stmt.executeBatch();
            conn.commit();
            logger.info("{} personnes insérées avec succès", personnes.size());
        } catch (Exception e) {
            conn.rollback();
            throw new DatabaseException("Erreur lors de l'insertion batch", e);
        }
    }
}</personne>

Factory Pattern pour la gestion des connexions

• Centralise la création des connexions
• Facilite la configuration et la maintenance
• Permet d'implémenter facilement un pool de connexions

public class DatabaseFactory {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USER = "user";
    private static final String PASSWORD = "password";

    private static DatabaseFactory instance;

    private DatabaseFactory() {}

    public static DatabaseFactory getInstance() {
        if (instance == null) {
            instance = new DatabaseFactory();
        }
        return instance;
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}

Exemple simplifié de Connection Pool

public class SimpleConnectionPool {
    private List<connection> connectionPool;
    private List<connection> usedConnections = new ArrayList<>();
    private static final int INITIAL_POOL_SIZE = 10;

    public SimpleConnectionPool(String url, String user, String password) {
        connectionPool = new ArrayList<>(INITIAL_POOL_SIZE);
        for (int i = 0; i < INITIAL_POOL_SIZE; i++) {
            connectionPool.add(createConnection(url, user, password));
        }
    }

    public Connection getConnection() {
        if (connectionPool.isEmpty()) {
            throw new RuntimeException("Pas de connexions disponibles");
        }
        Connection connection = connectionPool.remove(connectionPool.size() - 1);
        usedConnections.add(connection);
        return connection;
    }

    public void releaseConnection(Connection connection) {
        usedConnections.remove(connection);
        connectionPool.add(connection);
    }
}</connection></connection>

Gestion des ressources

• Toujours utiliser try-with-resources
• Fermer les ressources dans l'ordre inverse de leur création
• Gérer les connexions via un pool en production
• Éviter les fuites de ressources

// Bon exemple
try (Connection conn = dataSource.getConnection();
    PreparedStatement stmt = conn.prepareStatement(SQL);
    ResultSet rs = stmt.executeQuery()) {
    while (rs.next()) {
        // Traitement
    }
} catch (SQLException e) {
    logger.error("Erreur DB", e);
    throw new DatabaseException("Erreur lors de la requête", e);
}

Sécurité

• Protection contre les injections SQL
• Toujours utiliser PreparedStatement pour les requêtes paramétrées
• Ne jamais concaténer des chaînes SQL
• Valider les entrées utilisateur
• Gestion sécurisée des credentials
• Utiliser des fichiers de propriétés externes
• Chiffrer les mots de passe
• Utiliser des variables d'environnement

// Mauvais exemple - Vulnérable aux injections SQL
String sql = "SELECT * FROM users WHERE username = '" + username + "'";

// Bon exemple
String sql = "SELECT * FROM users WHERE username = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, username);

Performance et optimisation

• Utilisation de batch updates pour les opérations multiples
• Limitation de la taille des ResultSets
• Choix approprié des types de données
• Gestion efficace des transactions

// Exemple de batch update
PreparedStatement stmt = conn.prepareStatement(
    "INSERT INTO users (name, email) VALUES (?, ?)");
for (User user : users) {
    stmt.setString(1, user.getName());
    stmt.setString(2, user.getEmail());
    stmt.addBatch();
}
stmt.executeBatch();

Exemple moderne

String sql = "SELECT dest, jour FROM train WHERE client = ?";
try (Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setString(1, "Cassavetes");
    try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
            String destination = rs.getString("dest");
            int jour = rs.getInt("jour");
            logger.info("{} va à {} le jour {}", "Cassavetes", destination, jour);
        }
    }
} catch (SQLException e) {
    throw new DatabaseException("Erreur lors de la recherche du trajet", e);
}

Gestion des erreurs

• Création d'exceptions métier personnalisées
• Logging approprié des erreurs
• Rollback automatique en cas d'erreur
• Vérification des états de connexion

public class DatabaseException extends RuntimeException {
    public DatabaseException(String message, Throwable cause) {
        super(message, cause);
    }
}

try {
    // Code d'accès à la base
} catch (SQLException e) {
    logger.error("Erreur d'accès à la base", e);
    throw new DatabaseException("Opération échouée", e);
}