12-couche-dal.Rmd

# Couche DAL {#dal}

La couche _**Data Access Layer**_ gère les échanges avec le SGBD (sytème de gestion de bases de données) et avec les bases de données.

> Ce module est grandement inspiré du livre "Java, les fondamentaux du langage" publié par l'ENI, plus précisément le chapitre 6 (p.361 à 412).

Pour manipuler une base de données à partir d'un langage de programmation, il est possible de :

  `r knitr::asis_output("\U2E3A")` communiquer directement avec la base de données  
  `r knitr::asis_output("\U2E3A")` ou utiliser une couche logicielle qui assure elle-même le dialogue avec la base de données

L'**API JDBC** (*java database connectivity*) a été développée spécifiquement pour mettre en place la deuxième solution.   
Elle est composée de deux packages :

  - **java.sql** : un ensemble de classes, essentiellement des interfaces, pour accéder et traiter des données stockées dans une base
  - javax.sql : un ensemble de classes, essentiellement des interfaces, pour l'accès et le traitement des sources de données côté serveur
  
> Plus de détails sur l'API dans la javadoc [ici](https: --docs.oracle.com/javase/8/docs/technotes/guides/jdbc/).

Pour utiliser l'API JDBC avec un SGBD particulier, il est nécessaire d'utiliser un **pilote** (*driver*) pour assurer la médiation entre la technologie JDBC et la base de données.  
Il existe différents types de pilotes (*hors programme*).  
--> Nous allons utiliser un type de pilote entièrement écrit en Java : chaque SGBD propose son pilote spécifique, qu'il faut télécharger et lier à son projet Java.  


L'API JDBC est une bibliothèque de classes JDBC, qui se charge de trois étapes indispensables à la connexion à une base de données : la création d'une connexion à la base, l'envoi d'instructions SQL et l'exploitation des résultats provenant de la base.

> *Site à checker plus tard* [ici](https: --web.maths.unsw.edu.au/~lafaye/CCM/java/javajdbc.htm)

Les principales classes de l'API JDBC que nous allons utiliser permettent de retracer le cheminement logique depuis le pilote jusqu'aux données :

  - 1/ la classe `DriveManager` : point de départ qui assure la liaison avec le pilote, c'est par son intermédiaire que l'on obtient une connexion vers la base de données
  
  - 2/ la classe `Connection` : représente une connexion vers la base de données  
  
  - 3/ La connexion ainsi créée permet de transmettre des instructions (*requêtes*) vers la base :
    - la classe `Statement` : utilisée pour exécuter les requêtes simples (statiques) et retourner le résultat \@ref(statement)  
    - la classe `PreparedStatement` : utilisée pour les requêtes paramétrées \@ref(prepared-statement) (représente les requêtes précompilées)  
    - la classe `CallableStatement` : utilisée pour exécuter les procédures stockées \@ref(callable-statement)
  
  - 4/ la classe `ResultSet` : les éventuels enregistrements sélectionnés par l'instruction SQL sont accessibles avec un élément `ResultSet`

> Plus de détails sur les classes du package java.sql dans la javadoc [ici](https: --docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html).

> Hors-programme :  
Une API (*application programming interface*) est un ensemble normalisé de classes, de méthodes, de fonctions et de constantes qui sert de façade par laquelle un logiciel offre des services à d'autres logiciels.


 -- TODO à supprimer

méthodes de `Connection` :
Statement     createStatement()
Creates a Statement object for sending SQL statements to the database.
PreparedStatement     prepareStatement(String sql)
Creates a PreparedStatement object for sending parameterized SQL statements to the database.
CallableStatement     prepareCall(String sql)
Creates a CallableStatement object for calling database stored procedures.


tuto : https: --www.jmdoudoux.fr/java/dej/chap-jdbc.htm




## Les bases de SQL

Cette partie comporte principalement des rappels de l'initiation à SQL. Les notions sont donc peu détaillées.

> Une cheatsheet pratique [ici](https: --www.codecademy.com/learn/learn-sql/modules/learn-sql-manipulation/cheatsheet).

### Le langage SQL

Le langage SQL (*structured query langage*) est un langage de **requêtes** normalisé, standardisé, universel et descriptif.

> Ce n'est pas un langage procédural : pas de boucles, de variables ou de gestion d'erreurs.

Il permet d'effectuer des opérations sur les bases de données et leur contenu.

Il s'utilise au sein de SBGDR (systèmes de gestion de données relationnelle) : PostgreSQL (logiciel libre), SQLite, Microsoft SQL Server, MySQL, Oracle Database...

Des IDE ont été développé pour simplifier le développement et la gestion des bases de données : pgAdmin pour PostgreSQL, Microsoft SQL Server Management Studio pour Microsoft SQL Server, SQL Plus et SQL Developer pour Oracle Database...

> Il existe des langages procéduraux spécifiques à des SGBDR, ils permettent d'accéder à des fonctionnalités supplémentaires sous la forme de "procédures ou fonctions stockées".  
**(!)** Il est possible de faire 98% des choses avec simplement le SQL. Les langages procéduraux qui étendent le SQL sont propriétaires et présentent de nombreux désavantages.  
Ex : Transact-SQL pour Microsoft SQL Server, PL-SQL pour Oracle Database

Les instructions peuvent être regroupées en quatre catégories *(subjectives et divergentes selon les sources)* :

  - DCL (*Data Control Language*) : `GRANT`, `REVOKE`
  - DDL (*Data Description Language*) : `CREATE`, `ALTER`, `DROP`
  - DML (*Data Manipulation Language*) : `SELECT`, `INSERT`, `UPDATE`, `DELETE`
  - TCL (*Transaction Control Language*) : `BEGIN TRAN`, `COMMIT TRAN`, `SAVE TRAN`, `ROLLBACK TRAN`

> Par convention, les instructions SQL sont en majuscules et les noms de tables, colonnes, etc, sont en minuscules (et en snake_case).  
Néanmoins, le langage n'est pas sensible à la casse et ce n'est donc pas obligatoire.

### Normalisation des bases de données

Formes normales --> traduction :

  - une colonne = une et une seule donnée  
  (*une colonne pour le nom, une pour le prénom : on ne mélange pas*)
  - une seule colonne pour une même donnée  
  (*une colonne "année" et surtout pas des colonnes "2014", "2015", etc*)
  - une table = un et un seul thème  
  (*une table "auteurs" avec la liste des auteurs et les informations qui les concernent, une autre table "ouvrages" avec la liste des ouvrages et les informations qui les concernent : oui, une colonne "auteur" dans la table "Ouvrages" mais on décrit l'auteur dans une table à part*)
  - une ligne = une et une seule information donc une ligne est unique  
  (*dans la table "auteur", toutes les informations sur un auteur spécifique sont regroupées sur une seule ligne*)

> Plus de détails sur la normalisation des données  [ici](https: --www.ionos.fr/digitalguide/hebergement/aspects-techniques/normalisation-base-de-donnees/).

### Mise en place

Se connecter :

La connexion à une base de données est spécifique à chaque SGBD, avec toujours un login et un mot de passe. 

Il existe différentes façon de se connecter, selon ses privilèges :

  - privilèges serveur : réservés à l'administrateur, notamment droits de propriété sur toutes les bases de données mais aussi sur les utilisateurs et leurs droits
  - privilèges base de donnée : pour les utilisateurs, notamment droits de propriété sur une base de donnée ou une table, droits d'écriture ou de lecture uniquement
  
> Plus de détails sur les privilèges [ici](https://sqlpro.developpez.com/article/authentification/#LVI) (article spécifique à SQL Server, mais la théorie est sensiblement la même pour les autres SGBD).

Créer une base de données :

```{sql, eval=FALSE}
CREATE DATABASE ma_bdd;

DROP DATABASE ma_bdd;
```

### DCL

Les instructions telles que `GRANT` et `REVOKE` sont réservées à l'administrateur de la base de données. Elles permettent principalement de contrôler les accès utilisateur aux objets de base de données et à leur contenu.

> Plus de détails sur le DCL [ici](https://www.ibm.com/docs/fr/psfa/7.1.0?topic=categories-data-control-language).

### DDL

Créer une table :

```{sql, eval=FALSE}
CREATE TABLE ma_table(
    ma_colonne_1 TYPE,
    ma_colonne_2 TYPE,
    ma_colonne_3 TYPE,
    ...
    );
```

> Les types possibles sont décrits [ici](https: --developpement-informatique.com/article/282/types-de-donnees-sql).  
Brièvement : `bit | bigint |int | smallint | tinyint | numeric | decimal | float | real | binary| varbinary | image char| varchar | text | money | smallmoney | time | timestamp | date | datetime | smalldatetime | nchar | nvarchar | ntext | timestamp | xml | sql variant`.

Intégrité des données :  

  - contrainte de non nullité : `NOT NULL`
  - contrainte d'unicité : `UNIQUE` (aussi appelée clé secondaire)
  - contrainte de clé primaire : `PRIMARY KEY` (regroupe implicitement les deux contraintes précédentes)
  - contrainte d'intégrité référentielle (clé étrangère) : `FOREIGN KEY` 
  - contraite de validation : `CHECK` (conditions imposées)
  - (+) possibilité de donner une valeur par défaut : `DEFAULT`
  - (+) possibilité d'ajouter un compteur (incrément automatique, un seul par table) : `IDENTITY`

> Une contrainte peut être indiquée sur la ligne de définition de la colonne ou à la fin de la déclaration de toutes les colonnes.

> Il est possible d'avoir un effet sur la colonne FK lors de la modification de la colonne PK liée grâce à `ON DELETE` (et `ON UPDATE`, plus rare) avec comme options `CASCADE`, `SET NULL` et `SET DEFAULT`.  
Par défaut, c'est `ON DELETE NO ACTION` qui est utilisé.

```{sql, eval=FALSE}
CREATE TABLE ma_table(
    ma_colonne_1 TYPE CONSTRAINT nn_matable_macolonne1 NOT NULL,
    ma_colonne_2 TYPE CONSTRAINT un_matable_macolonne2 UNIQUE,
    ma_colonne_3 TYPE CONSTRAINT pk matable_macolonne3 PRIMARY KEY,
    ma_colonne_4 TYPE,
    ma_colonne_5 TYPE,
    ma_colonne_6 TYPE,
    ma_colonne_7 TYPE IDENTITY(1,1),
    ...

 -- CONSTRAINT fk_matable_macolonne4 FOREIGN KEY ma_colonne_4 REFERENCES une_autre_table(colonne_a_lier),
 -- il n'est pas conseille de gerer les cles etrangeres pendant la creation de la table mais plus tard, une fois toutes les tables creees

    CONSTRAINT ck_matable_macolonne5 CHECK (ma_colonne_5 > 0),
    CONSTRAINT df_matable_macolonne6 DEFAULT 0
    );
```

> Il est conseillé de donner un nom explicatif à chaque contrainte, pour faciliter leur (ré)utilisation.  
Par convention, on commence par rappeler le type de contrainte puis le nom de la table puis le nom de la ou les colonnes concernées : `pk_table_col1col2`

Modifier une table :  

  - Colonnes : ajouter, modifier, renommer, supprimer

```{sql, eval=FALSE}
ALTER TABLE ma_table ADD ma_nouvelle_colonne TYPE;

ALTER TABLE ma_table ALTER COLUMN ma_colonne_a_modifier NOUVEAU_TYPE;

ALTER TABLE ma_table RENAME COLUMN ancien_nom TO nouveau_nom;

ALTER TABLE ma_table DROP COLUMN ma_colonne_a_supprimer;
```

  - Contraintes : ajouter, supprimer, activer/désactiver

```{sql, eval=FALSE}
ALTER TABLE ma_table [WITH CHECK | WITH NO CHECK] ADD CONSTRAINT (description de la contrainte);

 -- ajout d'une cle etrangere
ALTER TABLE ma_table ADD CONSTRAINT fk_matable_macolonne4 FOREIGN KEY ma_colonne_4 REFERENCES une_autre_table(colonne_a_lier) ON DELETE CASCADE;

ALTER TABLE ma_table DROP CONSTRAINT contrainte_a_suprrimer;

ALTER TABLE ma_table {CHECK | NO CHECK} CONSTRAINT {ALL | contrainte_a_activer_desactiver};
```

> Il est possible de passer outre les contraintes déjà en place avec `WITH NO CHECK` : attention, ce n'est pas très recommandé.

Supprimer une table :  
D'abord supprimer les contraintes, puis la table

```{sql, eval=FALSE}
ALTER TABLE ma_table {CHECK | NO CHECK} CONSTRAINT ALL;

DROP TABLE ma_table;
```

Index : créer, supprimer, réorganiser, reconstruire

> Les index sont automatiquement créés pour les clés primaires (`CLUSTERED`) et les clés secondaires.

> On en définit généralement sur les clés étrangères et les colonnes de tri et de recherche.

```{sql, eval=FALSE}
CREATE [UNIQUE] UNCLUSTERED INDEX mon_index ON ma_table(une_colonne_a_indexer [ASC | DSC]);

DROP INDEX mon_index ON ma_table;

ALTER INDEX {ALL | mon_index} ON ma_table REORGANIZE;

ALTER INDEX {ALL | mon_index} ON ma_table REBUILD;
```

### DML

Insérer des données :

```{sql, eval=FALSE}
INSERT [INTO] ma_table VALUES (val1,val2,val3...);

INSERT [INTO] ma_table(col1,col2) VALUES (val1,val2);
```

Modifier des données :

```{sql, eval=FALSE}
 -- pour toutes les lignes de la table
UPDATE ma_table SET ma_colonne = nouvelle_valeur;

 -- pour les lignes concernees par la condition
UPDATE ma_table SET ma_colonne = nouvelle_valeur
    WHERE {condition};
```

Supprimer des données :

```{sql, eval=FALSE}
 -- pour toutes les lignes de la table
DELETE [FROM] ma_table;

 -- pour les lignes concernees par la condition
DELETE [FROM] ma_table
    WHERE {condition};
```

Extraire (rechercher) des informations :  

  - Projection : sélection des colonnes

> Il est notamment possible d'utiliser `ALL | DISTINCT` et de faire des calculs élémentaires comme `UPPER(colonne_a_mettre_en_majuscule) | CONVERT(type, expression, code du format de date) | ISNULL(boolean) | [texte a afficher] | TRIM()`.

```{sql, eval=FALSE}
 -- pour la table en entier
SELECT * FROM ma_table;

 -- pour toutes les lignes mais certaines colonnes uniquement
SELECT col1, col2 FROM ma_table;

 -- pour les lignes concernees par la condition et certaines colonnes
SELECT col4, col8 FROM ma_table
    WHERE {condition};
```

  - Restriction : sélection des lignes avec `WHERE`

> Condition boléenne qui peut être composée de : colonnes, constantes, fonctions, opérateurs de comparaison et logiques tels que `OR | AND`, `> | >= | < | <= | = | != ou <>`, `IN | BETWEEN | LIKE | IS NULL | IS NOT NULL`.

  - Calcul d'agrégat, *çàd* regroupement de lignes, avec `GROUP BY` et des fonctions telles que `COUNT() | SUM() | AVG() | MIN() | MAX()`.  
Il est possible d'ajouter une restriction sur les lignes calculées avec `HAVING`.

> Il est nécessaire d'avoir une liste de regroupements qui comprend au minimum la liste de la projection, à laquelle on enlève les colonnes utilisées pour le calcul d'agrégat.

```{sql, eval=FALSE}
SELECT col5 FROM ma_table
    WHERE {condition}
    GROUP BY {expression}
    HAVING {condition};
```

  - Tri : dernière clause du `SELECT` avec `ORDER BY` (qui peut être `ASC | DESC`).

> A utiliser avec parcimonie car très coûteux.

  - Jointures : fusion de tables de différentes manières avec `cross join`, `inner join`, `left | right | full outer join`

```{sql, eval=FALSE}
SELECT a.col2, a.col6, a.col9 FROM ma_table AS a
    INNER JOIN autre_table AS b
    ON a.col1 = b.col1;
```

Les notions suivantes ne sont pas détaillées ici :

  `r knitr::asis_output("\U22B9")` tables temporaires : 

```{sql, eval=FALSE}
-- table temporaire locale
SELECT AVG(col2) AS moyenne INTO #table_temporaire FROM ma_table;

-- table temporaire globale
SELECT AVG(col2) AS moyenne INTO ##table_temporaire FROM ma_table;
```

  `r knitr::asis_output("\U22B9")` sous-requêtes (imbriquées, corrélées) :

```{sql, eval=FALSE}
-- requete interne imbriquee
DELETE FROM ma_table_a 
    WHERE col4 NOT IN(
        SELECT DISTINCT col4 FROM ma_table_b 
    );
-- requete interne correlee ou subordonnee
DELETE FROM ma_table_a AS a 
    WHERE NOT EXISTS (
        SELECT DISTINCT col4 FROM ma_table_b AS b
            WHERE a.col4 = b.col4
    );

```

  `r knitr::asis_output("\U22B9")` table CTE (Common Table Expression) :  
éléments de la norme ANSI SQL99, pour simplifier l'écriture et donc la compréhension des requêtes complexes --> plus performantes que les tables temporaires

```{sql, eval=FALSE}
With Table_name_cte[(col1, col2)] AS (
  SELECT ...
);
```

  `r knitr::asis_output("\U22B9")` UNION, EXCEPT, INTERSECT

  `r knitr::asis_output("\U22B9")` vues :  
Une vue stocke une requête SELECT mais jamais de données.

```{sql, eval=FALSE}
-- creer une vue
CREATE VIEW ma_vue[(colonne1, colonne2, ...)] AS 
  SELECT ...[WITH CHECK OPTION];

-- supprimer une vue
DROP VIEW ma_vue;
```

### TCL

Les transactions permettent de créer des "bacs à sable", *çàd* des environnements où l'on peut tester, se tromper, remonter dans le temps et, quand on est prêt, valider les modifications.

Une transaction est un ensemble indivisible d'instructions, qui suit les principes ACID (\@ref(acid)).

Gérer une transaction :

```{sql, eval=FALSE}
BEGIN TRANSACTION ma_transaction;

-- si reussie, on la valide
COMMIT TRANSACTION ma_transaction;

-- si echouee, on l'annule (on remonte dans le temps)
ROLLBACK TRANSACTION ma_transaction;

-- on peut faire des points d'arrêt au sein d'une transaction
SAVE TRANSACTION mon_point_arret;
```

> Il est possible d'abréger `TRANSACTION` par `TRAN`.

Un verrou permet de restreindre voire interdire l'accès à une partie des données. On l'utilise lorsque l'on ouvre une transaction, pour pas que les utilisateurs puissent interférer ou avoir accès à des données non validées.

Imposer un verrou pour n'afficher que les données validées :

```{sql, eval=FALSE}
SET TRANSACTION ISOLATION LEVEL READ COMMITED;
```

> Il existe d'autres options : `READ UNCOMMITED | REPEATABLE READ | SERIALIZABLE`.

## Charger le pilote JDBC

Sur le site correspond au SGBD choisi, télécharger le pilote JDBC : un fichier archive en .jar.

Créer un projet Java.

Créer un dossier "lib" à la racine du projet,*çàd* au même niveau que le dossier "src".

Ajouter le fichier du pilote dans le dossier "lib".

Configurer le *Build Path* du projet en y ajoutant le fichier du pilote : il apparaît maintenant dans les *Referenced Libraries*.

> Dans Eclipse, un exemple :  
Project > Properties > Java Build Path > Libraries > Add JARs... > *Sélectionner le pilote* > OK


> Dans IntelliJ, un exemple :  
Clique-droit sur le projet > *Open Module Settings* > *Libraries* > "+" > Java > *Sélectionner le pilote* > OK

Le pilote est chargé automatiquement à l'exécution du projet.

## Etablir une connexion

La connexion à la base de données s'établit grâce aux classes `DriverManager` et `Connection` : on déclare une instance de la classe `Connection` et on l'initialise avec la méthode `getConnection` de la classe `DriverManager`.

Cette méthode attend trois paramètres sous forme de chaîne de caractères : 

  - l'url de la base de données, qui est sous la forme : `jdbc:typebasededonnees:host:port/nombasededonnees`
  - le nom de l'utilisateur
  - le mot de passe associé

### Exemple de connexion simple

Un exemple de connexion à une base de donnnées PostgreSQL, directement dans le `main` d'une classe de test :

```{java, eval=FALSE}
package fr.lsarribouette.mumblinginlayeredarchitecture.ihm;

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

public class TestDBConnexion {
    public static void main(String[] args) {

    /* Il faut charger le module java.sql car il n'est pas charge par defaut :
          - IntelliJ : alt+enter sur l'import java.sql > "add to module compiler"
          - Eclipse : sans module-info.java, un import suffit
                      avec module-info.java, ajouter "requires java.sql"
     */
     
        String url = "jdbc:postgresql://localhost:5433/gestion_cours";
        String user = "postgres";
        String pwd = "postgres";
        Connection cnx = null;
        try {
            cnx = DriverManager.getConnection(url,user,pwd);
            System.out.println("Etat de la connexion : ");
            System.out.println(cnx.isClosed()?"fermée":"ouverte");
        } catch (SQLException e) {
            System.out.println("Une erreur est survenue lors la connexion à la base de données.");
        } finally {
            try {
                cnx.close();
                System.out.println("Etat de la connexion après déconnexion : ");
                System.out.println(cnx.isClosed() ? "fermée" : "ouverte");
            } catch (SQLException e) {
                System.out.println("Une erreur est survenue lors la déconnexion à la base de données.");
            }
        }
```

### Externaliser la chaîne de connexion

Pour mieux organiser son code, il est possible d'externaliser la chaîne de connexion, *çàd* que l'on va séparer chaque étape en différents fichiers.

On crée un fichier `connexion.properties` qui va servir à stocker les informations de connexion : les paramètres url, utilisateur, mot de passe associé.

> `Properties` est une classe java qui propose une méthode pour lire ce type de fichiers.

On ajoute une classe `Settings` qui va permettre de récupérer les informations de connexion stockées dans le fichier `connexion.properties`.  
Elle est composée de :

  - un attribut de classe `proprietes`
  - un bloc anonyme qui récupère les informations de connexion : instancie `proprietes`, charge le fichier et traite une IOException
  - méthode de classe `getPropriete()` qui permet de retourner une information de connexion

> Les exceptions IO sont traitées dans la classe directement, car c'est bien au développeur de gérer ces erreurs.

> Un bloc anonyme est exécuté en premier lors de l'appel de la classe, il est défini simplement avec `static {}`.

On ajoute une classe DBConnexion qui va décrire des méthodes pour se connecter et se déconnecter de la base de données :

  - méthode de classe `seConnecter()` qui crée la connexion avec la base de données : récupère les trois informations de connexion, instancie la connexion et la retourne, propage une SQLException
  - méthode de classe `seDeconnecter()` qui arrête la connexion avec la base de données : vérifie si la connexion est null, la ferme si besoin et propage une SQLException
  - *je teste dans ihm*

> Les exceptions SQL sont progagées (`throws` dans la signature de la méthode) car c'est à l'administrateur de la base de données de gérer ces erreurs.

Pour tester tout ça, on peut ajouter une classe de `testDBConnexion` dans la couche IHM où l'on va se connecter puis se déconnecter en traitant leurs exceptions SQL respectives.

### Exemple de connexion avec externalisation

Un exemple de connexion à une base de donnnées PostgreSQL, avec externalisation de la chaîne de connexion :

> Les paramètres de connexion sont bien sûr à adapter à chaque base de données.

Fichier `connexion.properties` :

```{java, eval=FALSE}
#parametres de connexion sur PostgreSQL
url=jdbc:postgresql://localhost:5433/gestion_cours
user=postgres
pwd=postgres
```

Classe `Settings` :

```{java, eval=FALSE}
package fr.lsarribouette.mumblinginlayeredarchitecture.dal;

import java.io.IOException;
import java.util.Properties;

public class Settings {
    private static Properties proprietes;

    // methode anonyme : lors de l'appel de la classe, ce bloc est execute en premier
    static {
        try {
            proprietes = new Properties();
            proprietes.load(Settings.class.getResourceAsStream("connexion.properties"));
        } catch (IOException e) {
            System.out.println("Une erreur est survenue lors de chargement du ficher connexion.properties.");
        }
    }

    // methode qui retourne une information de connexion
    public static String getPropriete(String cle) {
        return proprietes.getProperty(cle);
    }
}
```

Classe DBConnexion :

```{java, eval=FALSE}
package fr.lsarribouette.mumblinginlayeredarchitecture.ihm;

import fr.lsarribouette.mumblinginlayeredarchitecture.dal.DBConnexion;

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

public class TestDBConnexion {
    public static void main(String[] args) {

    /* Il faut charger le module java.sql car il n'est pas charge par defaut :
        alt+enter > "add to module compiler"
     */

        Connection cnx = null;
        try {
            cnx = DBConnexion.seConnecter();
            System.out.println("Etat de la connexion : ");
            System.out.println(cnx.isClosed()?"fermée":"ouverte");
        } catch (SQLException e) {
            System.out.println("Une erreur est survenue lors la connexion à la base de données.");
        } finally {
            try {
                DBConnexion.seDeconnecter(cnx);
                System.out.println("Etat de la connexion après déconnexion : ");
                System.out.println(cnx.isClosed()?"fermée":"ouverte");
            } catch (SQLException e) {
                System.out.println("Une erreur est survenue lors la déconnexion à la base de données.");
            }
        }
    }
}
```

## Exécuter une instruction SQL

La connexion à une base de données permet l'exécution d'instructions SQL.

Trois types d'instructions SQL peuvent être exécutés :  

  - les requêtes simples
  - les requêtes paramétrées (ou précompilées)
  - les procédures stockées
  
A chacun de ces types correspond une classe de l'API JDBC et une instance de ces classes peut être obtenue par une méthode spécifique de la classse `Connection`.

```{r cache=TRUE, echo=FALSE, fig.cap='(ref:tab-wrapper)', message=FALSE, warning=FALSE}
library(dplyr)
library(kableExtra)

tab <- dplyr::bind_cols(
  c("requête simple",
    "requête paramétrée",
    "procédure stockée"), 
  c("Statement",
    "PreparedStatement",
    "CallableStatement"),
  c("createStatement",
    "prepareStatement",
    "prepareCall")
  ) 
tab %>% setNames(c("Types d'instruction SQL", "classe correspondante", "méthode associée")) %>% 
  kableExtra::kbl("html", caption = "Récapitulatif des instructions SQL avec leur classe correspondante dans l'API JDBC et les méthodes associées de la classe `Connection`") %>% 
  kable_styling(full_width = FALSE)
```

<br>
Ces trois classes proposent des méthodes similaires pour exécuter les instructions SQL.

Pour l'extraction (recherche) d'informations avec une instruction `SELECT`, on utilise la méthode `executeQuery()` qui retourne un `ResultSet` non exploitable qu'il faut parcourir
  
> Un objet de type `ResultSet` est **jeu d'enregistrements**. Il permet de lire les résultats de la requête obtenus auprès du serveur.  
La notion est détaillée plus loin (\@ref(resultset)).
  
Pour la mise à jour (insertion, modification, suppression) d'informations avec des instructions `INSERT | UPDATE | DELETE`, on utilise la méthode `executeUpdate()` qui retourne un entier indiquant le nombre de lignes concernées

D'autres méthodes sont disponibles, comme `execute()` qui retourne un booléan indiquant si un jeu d'enregistrements a été généré et `executeBatch()` qui permet d'exécuter un ensemble d'instructions SQL par lot.  
Elles ne seront pas détaillées ici.

<br>
Les trois méthodes associées peuvent être utilisées sans paramètre.

Dans le cas d'une instruction `SELECT`, le jeu d'enregistrements généré sera par défaut en lecture seule et avec un défilement en avant uniquement.

Il est possible de modifier ces caractéristiques en donnant deux paramètres aux méthodes, dont les constantes sont définies la classe `ResultSet`  :

  - le type de jeu d'enregistrements: `ResultSet.TYPE_FORWARD_ONLY | TYPE_SCROLL_INSENSITIVE | TYPE_SCROLL`
  - la possibilité de modification des informations : `ResultSet.CONCUR_READ_ONLY | CONCUR_UPDATABLE` 


### Statement

Un objet de type `Statement` peut prendre en charge l'exécution de n'importe quelle instruction SQL (DDL ou DML, par exemple). C'est l'objet le plus élémentaire permettant l'exécution de requêtes SQL simples.

Il est obtenu par la méthode de `createStatement` de la connexion :  
`Statement stm = cnx.createStatement();`  

Sans paramètres, la méthode 


### PreparedStatement

### CallableStatement

### ResultSet

C'est un objet prédéfini qui se fait l'équivalent du cursor SQL. On peut en appeler certaines méthodes pour intéragir avec sa database directement depuis notre programme java.

## Gérer les transactions

## Design Pattern DAO

Définir le Data Access Object (DAO)

Utiliser la DAL depuis la BLL

Utiliser une Factory

Utiliser la DAL depuis la BLL

Le modèle statique du Design Pattern DAO

Le modèle dynamique du Design Pattern DAO

Le Design Pattern DAO : démo





## Exemple simple

```{java, eval=FALSE}
 -- à compléter
```