Java EE - EPSI POE mars 2017 - David Gayerie Licence Creative Commons

JDBC

  1. Préambule : try-with-resources
  2. Le pilote de base de données
  3. Création d'une connexion
  4. L'URL de connexion et la classe des pilotes
  5. Les requêtes SQL (Statement)
  6. Le Statement
  7. Le ResultSet
  8. Le PreparedStatement
  9. Le CallableStatement
  10. La transaction
  11. Exercice : Application Web et JDBC

JDBC (Java DataBase Connectivity) est l'API standard pour interagir avec les bases données relationnelles en Java. JDBC fait partie de l'édition standard et est donc disponible directement dans le JDK.

Préambule : try-with-resources

L'API JDBC donne accès à des objets qui correspondent à des ressources de base de données que le développeur doit impérativement fermer par l'appel à des méthodes close(). Ne pas fermer correctement les objets fournis par JDBC est un bug qui conduit habituellement à un épuisement des ressources système, empêchant l'application de fonctionner correctement.

Java 7 a introduit l'interface AutoCloseable ainsi qu'une nouvelle syntaxe dénommée try-with-resources. L'API JDBC utilise massivement l'interface AutoCloseable et autorise donc le try-with-resources. Ainsi, les deux codes ci-dessous sont équivalents puisque la classe java.sql.Connection implémente AutoCloseable :

Avec try-with-resources

  try (java.sql.Connection connection = dataSource.getConnection()) {
    // ...
  }

Sans try-with-resources

  java.sql.Connection connection = dataSource.getConnection();
  try {
    // ...
  }
  finally {
    if (connection != null) {
      connection.close();
    }
  }

La version utilisant la syntaxe du try-with-resources est plus compacte et prend en charge automatiquement l'appel à la méthode close(). Tout au long de ce chapitre sur JDBC, nous utiliserons alternativement l'une ou l'autre des syntaxes.

Le pilote de base de données

JDBC est une API indépendante de la base de données sous-jacente. D'un côté, les développeurs implémentent les interactions avec une base de données à partir de cette API. D'un autre côté, chaque fournisseur de SGBDR livre sa propre implémentation d'un pilote JDBC (JDBC driver). Pour pouvoir se connecter à une base de données, il faut simplement ajouter le driver (qui se présente sous la forme d'un fichier jar) dans le classpath lors de l'exécution du programme.

Des pilotes JDBC sont disponibles pour les SGBDR les plus utilisés : Oracle DB, MySQL, PostgreSQL, Apache Derby, SQLServer, SQLite, HSQLDB (HyperSQL DataBase), ...

Pour un projet géré par Maven, le pilote JDBC est une dépendance logicielle comme une autre. Pour l'intégrer dans le livrable, il suffit de le déclarer dans le fichier pom.xml dans la section dependencies :

Ajout du driver MySQL dans le fichier pom.xml

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.41</version>
</dependency>

On peut rechercher le pilote souhaité sur le site du Maven Repository.

Pour l'utilisation de JDBC dans un serveur d'application Java EE, il est aussi possible d'installer le pilote JDBC directement dans le serveur plutôt que de l'ajouter comme dépendance de l'application. Pour TomEE, il suffit de télécharger le fichier jar du pilote JDBC et de le copier dans le répertoire lib du répertoire d'installation du serveur.

Création d'une connexion

Une connexion à une base de données est représentée par une instance de la classe Connection. Il existe plusieurs façons d'obtenir une instance de cet objet. Nous simplifierons légèrement le propos en considérant deux cas d'utilisation : la création d'une connexion sans Java EE et la création d'un connexion avec Java EE.

Création d'une connexion sans Java EE

Comme nous l'avons précisé au début de ce chapitre, JDBC fait partie de l'API standard du JDK. Il n'est donc pas nécessaire de disposer d'un conteneur Java EE ou d'un serveur d'application pour l'utiliser. Pour une utilisation hors de Java EE, la classe DriverManager permet d'enregister un pilote JDBC et de créer une connexion :

Création d'une connexion MySQL avec le DriverManager

  DriverManager.registerDriver(new com.mysql.jdbc.Driver());

  // Connexion à la base myschema sur la machine localhost 
  // en utilisant le login "username" et le password "password"
  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/myschema", 
                                                      "username", "password");

Lorsque la connexion n'est plus nécessaire, il faut libérer les ressources allouées en la fermant avec la méthode close(). La classe Connection implémente AutoCloseable, ce qui l'autorise à être utilisée dans un try-with-resources.


  connection.close();

Création d'une connexion avec Java EE

Dans un serveur d'application, l'utilisation du DriverManager est remplacée par celle de la DataSource. L'interface DataSource n'offre que deux méthodes :


// Attempts to establish a connection with the data source
Connection getConnection()

// Attempts to establish a connection with the data source
Connection getConnection(String username, String password)

Il n'est pas possible de spécifier l'URL de connection à la base de données avec une DataSource. Par contre une DataSource peut être injectée dans n'importe quel composant Java EE (Servlet, Bean CDI, EJB) grâce à l'annotation @Resource :

Injection d'une DataSource dans une Servlet

import java.io.IOException;
import java.sql.Connection;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet("/MyServlet")
public class MyServlet extends HttpServlet {

  @Resource(name = "nomDeLaDataSource")
  private DataSource dataSource;

  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
                          throws ServletException, IOException {

    try (Connection connection = dataSource.getConnection()) {
      // ...
    }

  }

}

L'annotation @Resource permet de spécifier le nom de la DataSource grâce à l'attribut name. Mais comment le serveur d'application fait-il pour lier une DataSource avec une connexion vers une base de données ? Malheureusement, il n'existe pas de standard et chaque serveur d'application dispose de sa procédure. Pour TomEE, une DataSource se configure dans le fichier tomee.xml. Ce fichier se trouve dans le répertoire conf du répertoire d'installation du serveur. On peut ainsi déclarer une source de données directement dans le serveur. Il est également possible d'ajouter un fichier resources.xml dans le répertoire WEB-INF de son application. Ce fichier a le même format que le fichier tomee.xml mais il fournit une définition des sources de données uniquement pour cette application.

Exemple de déclaration d'une DataSource MySQL dans le fichier resources.xml (ou tomee.xml)

<?xml version="1.0" encoding="UTF-8"?>
<tomee>
<Resource id="nomDeLaDataSource" type="javax.sql.DataSource">
  JdbcDriver com.mysql.jdbc.Driver
  JdbcUrl jdbc:mysql://localhost:3306/myDataBase
  UserName root
  Password root
  JtaManaged false
</Resource>
</tomee>

Le nom de la source de données est indiqué par l'attribut id de la balise Resource. La documentation officielle de TomEE contient des informations intéressantes à consulter :

Ce système de configuration est certes plus compliqué que l'utilisation du DriverManager mais il permet à l'application d'ignorer les détails de configuration. L'utilisation des DataSources dans un serveur d'application apporte généralement des fonctionnalités supplémentaires telles que la mise en cache et la réutilisation de connexions (pour améliorer les performances), les tests permettant de vérifier que les connexions sont correctements établies, la supervision des connexions, ...

L'URL de connexion et la classe des pilotes

Comme nous l'avons vu à la section précédente, pour établir une connexion, nous avons besoin de connaître la classe du pilote et l'URL de connexion à la base de données. Il n'existe pas vraiment de règle en la matière puisque chaque fournisseur de pilote décide du nom de la classe et du format de l'URL. Le tableau suivant donne les informations nécessaires suivant le SGBDR :

SGBDR Nom de classe du pilote Format de l'URL de connexion
Oracle DB oracle.jdbc.OracleDriver jdbc:oracle:thin:@[host]:[port]:[schema]
Ex : jdbc:oracle:thin:@localhost:1521:maBase
MySQL com.mysql.jdbc.Driver jdbc:mysql://[host]:[port]/[schema]
Ex : jdbc:mysql://localhost:3306/maBase
PosgreSQL org.postgresql.Driver jdbc:postgresql://[host]:[port]/[schema]
Ex : jdbc:postgresql://localhost:5432/maBase
HSQLDB (mode fichier) org.hsqldb.jdbcDriver jdbc:hsqldb:file:[chemin du fichier]
Ex : jdbc:hsqldb:file:maBase
HSQLDB (mode mémoire) org.hsqldb.jdbcDriver jdbc:hsqldb:mem:[schema]
Ex : jdbc:hsqldb:mem:maBase

Les requêtes SQL (Statement)

L'interface Connection permet, entre autres, de créer des Statements. Un Statement est une interface qui permet d'effectuer des requêtes SQL. On distingue 3 types de Statement :

Le Statement

Un Statement est créé à partir d'une des méthodes createStatement de l'interface Connection. À partir d'un Statement, il est possible d'exécuter des requêtes SQL :

Utilisation d'un Statement

  java.sql.Statement stmt = connection.createStatement();

  // méthode la plus générique d'un statement. Retourne true si la requête SQL 
  // exécutée est un select (c'est-à-dire si la requête produit un résultat) 
  stmt.execute("insert into myTable (col1, col2) values ('value1', 'value1')");

  // méthode spécialisée pour l'exécution d'un select. Cette méthode retourne
  // un ResultSet (voir plus loin)
  stmt.executeQuery("select col1, col2 from myTable");

  // méthode spécialisée pour toutes les requêtes qui ne sont pas de type select.
  // Contrairement à ce que son nom indique, on peut l'utiliser pour des requêtes
  // DDL (create table, drop table, ...) et pour toutes requêtes DML (insert, update, delete).
  stmt.executeUpdate("insert into myTable (col1, col2) values ('value1', 'value1')");

Attention, un Statement est une ressource JDBC et il doit être fermé dès qu'il n'est plus nécessaire :


  stmt.close();

La classe Statement implémente AutoCloseable, ce qui l'autorise à être utilisée dans un try-with-resources.

Pour des raisons de performance, il est également possible d'utiliser un Statement en mode batch. Cela signifie, que l'on accumule l'ensemble des requêtes SQL côté client, puis on les envoie en bloc au serveur plutôt que de les exécuter séquentiellement.

Utilisation d'un Statement en mode batch

  java.sql.Statement stmt = connection.createStatement();
  try {
    stmt.addBatch("update myTable set col3 = 'sameValue' where col1 = col2");
    stmt.addBatch("update myTable set col3 = 'anotherValue' where col1 <> col2");
    stmt.addBatch("update myTable set col3 = 'nullValue' where col1 = null and col2 = null");
    // les requêtes SQL sont soumises au serveur au moment de l'appel à executeBatch
    stmt.executeBatch();
  }
  finally {
    stmt.close();
  }

Le ResultSet

Lorsqu'on exécute une requête SQL de type select, JDBC nous donne accès à une instance de ResultSet. Avec un ResultSet, il est possible de parcourir ligne à ligne les résultats de la requête (comme avec un itérateur) grâce à la méthode ResultSet.next(). Pour chaque résultat, il est possible d'extraire les données dans un type supporté par Java.

Le ResultSet offre une liste de méthodes de la forme :


  ResultSet.getXXX(String columnName)
  ResultSet.getXXX(int columnIndex)

XXX représent le type Java que la méthode retourne. Si on passe un numéro en paramètre, il s'agit du numéro de la colonne dans l'ordre du select. Attention, le numéro de la première colonne est 1.

Exemple d'utilisation d'un ResultSet

  String request = "select titre, date_sortie, duree from films";

  try (java.sql.Statement stmt = connection.createStatement();
       java.sql.ResultSet resultSet = stmt.executeQuery(request);) {
    
    // on parcourt l'ensemble des résultats retourné par la requête
    while (resultSet.next()) {
      String titre = resultSet.getString("titre");
      java.sql.Date dateSortie = resultSet.getDate("date_sortie");
      long duree = resultSet.getLong("duree");
      
      // ...
    }
  }

Attention, un ResultSet est une ressource JDBC et il doit être fermé dès qu'il n'est plus nécessaire :


  resultSet.close();

La classe ResultSet implémente AutoCloseable, ce qui l'autorise à être utilisée dans un try-with-resources.

Le PreparedStatement

Un PreparedStatement est créé à partir d'une des méthodes prepareStatement de l'interface Connection. Lors de l'appel à prepareStatement, il faut passer la requête SQL à exécuter. Cependant, cette requête peut contenir des ? indiquant l'emplacement des paramètres.

L'interface PreparedStatement fournit des méthodes de la forme :


  PreparedStatement.setXXX(int parameterIndex, XXX x)

XXX représente le type du paramètre, parameterIndex sa position dans la requête SQL (attention, le premier paramètre a l'indice 1) et x sa valeur.

Exemple d'utilisation d'un PreparedStatement

  String request = "insert into films (titre, date_sortie, duree) values (?, ?, ?)";

  try (java.sql.PreparedStatement pstmt = connection.prepareStatement(request)) {
    
    pstmt.setString(1, "live JDBC");
    pstmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
    pstmt.setInt(3, 120);
    
    pstmt.executeUpdate();
  }

Attention, un PreparedStatement est une ressource JDBC et il doit être fermé dès qu'il n'est plus nécessaire :


  pstmt.close();

La classe PreparedStatement implémente AutoCloseable, ce qui l'autorise à être utilisée dans un try-with-resources

Le PreparedStatement reprend une API similaire à celle du Statement :

Le PreparedStatement offre trois avantages :

Le CallableStatement

Un CallableStatement permet d'appeler des procédures ou des fonctions stockées. Il est créé à partir d'une des méthodes prepareCall de l'interface Connection. Comme pour le PreparedStatement, il est nécessaire de passer la requête lors de l'appel à prepareCall et l'utilisation de ? permet de spécifier les paramètres.

Cependant, il n'existe pas de syntaxe standard en SQL pour appeler des procédures ou des fonctions stockées. JDBC définit tout de même une syntaxe compatible avec tous les pilotes JDBC :

Requête JDBC pour l'appel d'une procédure stockée

  {call nom_de_la_procedure(?, ?, ?, ...)}

Requête JDBC pour l'appel d'une fonction stockée

  {? = call nom_de_la_fonction(?, ?, ?, ...)}

Un CallableStatement permet de passer des paramètres en entrée avec des méthodes de type setXXX comme pour le PreparedStatement. Il permet également de récupérer les paramètres en sortie avec des méthodes de type getXXX comme on peut trouver dans l'interface ResultSet. Comme pour le PreparedStatement, on retrouve les méthodes execute, executeUpdate et executeQuery pour réaliser l'appel à la base de données.

Exemple de procédure stockée MySQL

  create procedure sayHello (in nom varchar(50), out message varchar(60))
  begin
    select concat('hello ', nom, ' !') into message;
  end

Exemple d'appel de la procédure stockée avec JDBC

  String request = "{call sayHello(?, ?)}";

  try (java.sql.CallableStatement stmt = connection.prepareCall(request)) {
    // on positionne le paramètre d'entrée
    stmt.setString(1, "EPSI");
    // on appelle la procédure
    stmt.executeUpdate();
    // on récupère le paramètre de sortie
    String message = stmt.getString(2);

    // ...
  }

Attention, un CallableStatement est une ressource JDBC et il doit être fermé dès qu'il n'est plus nécessaire :


  stmt.close();

La classe CallableStatement implémente AutoCloseable, ce qui l'autorise à être utilisée dans un try-with-resources

La transaction

La plupart des SGBDR intègrent un moteur de transaction. Une transaction est définie par le respect de quatre propriétés désignées par l'acronyme ACID :

Une transaction est définie par un début et une fin qui peut être soit une validation des modifications (commit), soit une annulation des modifications effectuées (rollback). On parle de démarcation transactionnelle pour désigner la portion de code qui doit s'exécuter dans le cadre d'une transaction.

Avec JDBC, il faut d'abord s'assurer que le pilote ne commite pas sytématiquement à chaque requête SQL (l'auto commit). Une opération de commit à chaque requête SQL équivaut en fait à ne pas avoir de démarcation transactionnelle. Sur l'interface Connection, il existe les méthodes setAutoCommit(boolean) et getAutoCommit() pour nous aider à gérer ce comportement. Attention, dans la plupart des implémentations des pilotes JDBC, l'auto commit est activé par défaut (mais ce n'est pas une règle).

Dans les serveurs d'application Java EE, l'activation ou non de l'auto commit par défaut et souvent configurable au niveau de la DataSource. C'est le cas pour TomEE, puisque l'attribut defaultAutoCommit peut être positionné sur une balise Resource. Cet attribut vaut true par défaut.

Configuration de l'auto commit dans le fichier resources.xml (ou tomee.xml)

<?xml version="1.0" encoding="UTF-8"?>
<tomee>
<Resource id="nomDeLaDataSource" type="javax.sql.DataSource">
  JdbcDriver com.mysql.jdbc.Driver
  JdbcUrl jdbc:mysql://localhost:3306/myDataBase
  defaultAutoCommit false
  UserName root
  Password root
  JtaManaged false
</Resource>
</tomee>

À partir du moment où l'auto commit n'est plus actif sur une connexion, il est de la responsabilité du développeur d'appeler sur l'instance de Connection la méthode commit() (ou rollback()).

Le contrôle de la démarcation transactionnelle par programmation est surtout utile lorsque l'on souhaite garantir l'atomicité d'un ensemble de requêtes SQL.

Exemple de démarcation transactionnelle avec JDBC

  // si nécessaire on force la désactivation de l'auto commit
  connection.setAutoCommit(false);
  boolean transactionOk = false;
  
  try {
  
    // on ajoute un produit avec une quantité donnée dans la facture
    String requeteAjoutProduit = 
              "insert into ligne_facture (facture_id, produit_id, quantite) values (?, ?, ?)";

    try (PreparedStatement pstmt = connection.prepareStatement(requeteAjoutProduit)) {
      pstmt.setString(1, factureId);
      pstmt.setString(2, produitId);
      pstmt.setLong(3, quantite);
    
      pstmt.executeUpdate();
    }
  
    // on déstocke la quantité de produit qui a été ajoutée dans la facture
    String requeteDestockeProduit = 
              "update stock_produit set quantite = (quantite - ?) where produit_id = ?";

    try (PreparedStatement pstmt = connection.prepareStatement(requeteDestockeProduit)) {
      pstmt.setLong(1, quantite);
      pstmt.setString(2, produitId);
    
      pstmt.executeUpdate();
    }
  
    transactionOk = true;
  }
  finally {
    // L'utilisation d'une transaction dans cet exemple permet d'éviter d'aboutir à
    // des états incohérents si un problème survient pendant l'exécution du code.
    // Par exemple, si le code ne parvient pas à exécuter la seconde requête SQL
    // (bug logiciel, perte de la connexion avec la base de données, ...) alors
    // une quantité d'un produit aura été ajoutée dans une facture sans avoir été
    // déstockée. Ceci est clairement un état incohérent du système. Dans ce cas,
    // on effectue un rollback de la transaction pour annuler l'insertion dans
    // la table ligne_facture.
    if (transactionOk) {
      connection.commit();
    }
    else {
      connection.rollback();
    }
  }

Exercice : Application Web et JDBC

Objectif

Développer une application MVC permettant d'ajouter des individus. Un individu est défini par son nom, son prénom et son âge. L'application doit permettre :

  1. D'ajouter un individu
  2. D'afficher la liste des individus
  3. De supprimer un individu

Commencez par valider la configuration de votre connection à la base de données. Essayez d'exécuter une requête simple dans votre application Web comme :


select 1

Et écrivez le résultat sur la sortie standard de votre serveur (disponible dans le panneau "Console" d'Eclipse.

Script SQL

Pour la base de données MySQL, le script de création de la table Person sera :


CREATE TABLE IF NOT EXISTS Person (
  id int(11) NOT NULL AUTO_INCREMENT,
  firstname varchar(200) NOT NULL,
  lastname varchar(200) NOT NULL,
  age int(3),
  dateCreation date NOT NULL,
  PRIMARY KEY (id)
) engine=innodb;
        			
Modèle Maven du projet à télécharger
webapp-template.zip
Mise en place du projet
Éditer le fichier pom.xml du template et modifier la balise artifactId pour spécifier le nom de votre projet. Pensez à déclarer la dépendance vers le pilote JDBC dans le fichier pom.xml
Intégration du projet dans Eclipse
L'intégration du projet dans Eclipse suit la même procédure que celle vue lors de l'introduction à Maven