Java, SGBDR Hypersonic SQL

Lycée Léonard de Vinci - Melun (Olivier Capuozzo)

06-Mars-2003

Résumé

Ce document a été réalisé sous Linux avec gvim, au format docbook , mis en page avec le processeur XSLT saxon développé par Michael Kay, les feuilles de styles de Norman Walsh.


Table des matières

Hypersonic SQL
Introduction
Installation
Démarrage d'Hypersonic SQL
Base de données TonerExpress
Aménagement du cas
Outil Database Manager
Conclusion
Communication Java -> SGBDR
Introduction à JDBC
I - Chargement du pilote dans la JVM (Java Virtual Machine)
II - Etablissement de la connexion
III - Exécution d'une requête SQL
IV - Analyse des résultats
Exemple de programme
TP programmation

Hypersonic SQL

Introduction

Hypersonic SQL est un petit système de gestion de bases de données écrit en Java, intialement développé par Thomas Mueller et Hypersonic SQL Group.

HSQL est principalement utilisé pour des démonstrations, du maquettage et de petites applications ayant besoin de s'interfacer avec un système de gestion de bases de données relationnel (SGBDR). Sa taille (<300 ko), son prix (gratuit), sa portabilité (Java) et sa rapidité de mise en oeuvre sont ses principaux atouts.

Ajoutons à ces qualités la relation priviliégiée de ce système avec le langage Java, et nous avons une solution bien adaptée à un apprentissage de la programmation, portable sous Windows comme sur Linux.

Installation

Téléchargez Hypersonic SQL à l'adresse suivante http://hsqldb.sourceforge.net.

Décompressez le fichier dans un répertoire prévu à cet effet (ex: hsqldb).

Démarrage d'Hypersonic SQL

Pour utiliser Hypersonic SQL , vous pouvez :

  • Administrer une base via l'outil Database Manager livré avec Hypersonic SQL.

  • Utiliser la base via une application cliente écrite en Java.

Base de données TonerExpress

La base de données que nous allons créer est issue du cas TONER EXPRESS de la session 1999 du BTS Informatique de gestion, option développeur d'applications, disponible ici reseaucerta.

Extrait du dossier 2

Pour gérer les informations concernant les types de cartouches neuves vendues par l'entreprise, Monsieur Clermont, responsable technique, utilise une base de données dont le schéma est le suivant :

CARTOUCHE (RefCartouche, DesCartouche, PrixCartoucheNeuve)
IMPRIMANTE (RefImprimante, DesImprimante, CodeType#)
TYPE(CodeType, LibelleType)
ACCEPTER(RefImprimante#, RefCartouche#)
  • Un attribut (ou un ensemble d'attributs) en gras est une clé primaire. Un attribut dont le nom est suivi du symbole # (croisillon) est une clé étrangère.

  • La table CARTOUCHE renseigne sur tous les modèles de cartouches vendues par TONEREXPRESS.

  • La table IMPRIMANTE renseigne sur tous les modèles d'imprimantes existants.

  • La table TYPE contient trois lignes (imprimantes matricielles, laser et jet d'encre).

  • La table ACCEPTER permet de connaître toutes les imprimantes acceptant un modèle de cartouche et, réciproquement, toutes les cartouches compatibles avec un modèle d'imprimante.

Aménagement du cas

Pour construire notre base de données nous disposons de deux scripts pour Oracle (SGBDR du nom de son éditeur).

Les données initiales ont un gros défaut : les identifiants sont porteurs d'une logique métier. Exemple :

 refImprimante    codeType    desImprimante
 ...
 'HPLJ3P'         'L'         'Hewlett Packard Laserjet 3P'
 ...

Or, c'est une pratique dangereuse d'associer "une valeur de gestion" à celle d'un identifiant technique : si les spécifications qui ont servi de base à la valeur des identifiants changent (probabilité non négligeable), ces derniers devront être mis à jour. Des répercussions en cascade devront alors être réalisées qui auront un impact sur les clés primaires, les clés étrangères (et les programmes bien-entendu).

Exemples d'attributs porteurs d'une logique métier : Numéro SS, numéro de téléphone, numéro d'immatriculation d'un véhicule. En aucun cas ces attributs ne doivent être utilisés comme clé primaire, au risque de rencontrer des problèmes sérieux lors de changement de spécifications (passage d'un numéro de téléphone de 8 à 10 chiffres, nouvelle norme Européenne d'immatriculation des véhicules, attribution de valeurs pour des cas particuliers en sont des exemples.)

Pour se prémunir de problèmes liés à ce type de mise à jour, il est fortement conseillé de ne pas mélanger les rôles, celui d'une clé primaire étant l'identification d'un tuple, c'est tout.

A titre d'exemple et pour ne pas perdre la valeur des identifiants fournis (rappelez-vous qu'ils portent une logique métier certainement exploitée par des applications), nous avons traduit la clé initiale en un simple attribut, que nous déclarons UNIQUE. Puis nous avons ajouté un identifiant purement technique, typiquement un attribut auto-incrémenable.

Exemple

  • Avant

    create table Imprimante
    (  refImprimante       VARCHAR(10)           primary key,
       codeType            CHAR(1)               not null,
       desImprimante       VARCHAR(40)           not null,
       foreign key(codeType) references TypeImpr (codeType) on delete cascade
    )
    

    Exemple d'insertion

    insert into imprimante
    values ('HPLJ3P','L','Hewlett Packard Laserjet 3P');
    
  • Après

    create table Imprimante
    (  id                  int                   identity,
       refImprimante       VARCHAR(10)           not null,
       codeType            CHAR(1)               not null,
       desImprimante       VARCHAR(40)           not null,
       constraint UNIQUE_REFIMPRIMANTE UNIQUE (refImprimante),
       foreign key(codeType) references TypeImpr (codeType) on delete cascade
    )
    

    Exemple d'insertion, avec auto-incrémentation de la clé primaire :

    insert into imprimante
    values (null,'HPLJ3P','L','Hewlett Packard Laserjet 3P');
    
Type auto-incrémenté

La clause identity n'est pas propre à HypersonicSql, elle fera certainement partie de la norme SQL 2003. En attendant, la caractéristique «auto-incrémentée» ne fait pas partie des types de base de SQL, les SGBDR proposent généralement leur propre version.

Par exemple : 'serial' sous PostgreSQL, 'auto_increment' sous MySql, 'AutoNumber' sous MSAccess etc.

Pour aller plus loin : http://sqlpro.developpez.com/ClefsAuto/

Outil Database Manager

Nous allons donc créer notre base de données. Pour cela nous utilisons Database Manager, un des outils livrés avec Hypersonic SQL (package org.hsqldb.util).

  • Lancer l'application

    Le répertoire demo contient des fichiers permettant de lancer certains outils. Par exemple le fichier de commandes runManager.bat permet de lancer le Database Manager afin d'interagir avec les bases de données. Ces fichiers de commandes existent aussi pour les environnements Unix, par exemple runManager.sh.

    Nous vous invitons à comparer ces deux fichiers, runManager.bat et runManager.sh, leurs différences sont mineures.

    Lancer le gestionnaire.

    ./runManager.sh
    

    Une première fenêtre apparaît :

    Elle vous demande :

    • Le mode dans lequel vous voulez créer ou ouvrir la base de données (Type).

      Choisir Standalone, en effet par défaut les données ne sont gérées qu'en mémoire (In-Memory), ce qui signifie que les informations sont perdues à la fermeture de l'application.

      En choisissant Standalone, vous utilisez HSQL en mode non partagé. En mode Client/Serveur, nous choisirons Server ou WebServer selon le cas.

      Dans le cas d'une utilisation client/serveur, il faut lancer préalablement le serveur HSQL. Pour cela le plus simple est de configurer le fichier bin/hsqldbserver :

      Exemple

      # Step 1: Specify your dbhome here
      dbhome="/home/kpu/java/hsqldb/"
      # Step 2: change this to point to your JDK directory.
      jdkhome="/usr/java/j2sdk1.4.1/"
      # Step 3: change to the appropriate port, user and password
      dbport="9001"
      dbuser="sa"
      dbpassword=""
      #step 4(Optional): Set the MAX startup time and shutdown time.
      STARTUP_MAXSEC_DEFAULT=420
      SHUTDOWN_MAXSEC_DEFAULT=420
      # Step 5(Optional): Set user params
      userparams="-database /cirdan/home/kpu/tp18/imprimantes"
      [...]
      

      Puis de lancer le serveur :

      [kpu@kpu hsqldb]$ bin/hsqldbserver start
      

      Autres options : stop, status, restart et kill

      Pour la création de la base nous nous contenterons d'une utilisation Standalone (In-Process).

    • La classe du driver d'Hypersonic SQL (Driver)

    • L'URL de la base de données que vous allez créer ou utiliser

      Dans l'url, le nom de la base fait suite à jdbc:hsqldb:. Dans notre exemple, la base se nomme imprimantes, dans le répertoire /cirdan/home/kpu/tp18.

      (Dans le cas d'une utilisation client/serveur nous indiquerions l'adresse IP du serveur, comme dans cet exemple: jdbc:hsqldb:hsql://127.0.0.1.)

    • Le nom de l'utilisateur (User)

    • Le mot de passe de l'utilisateur (Password)

Vous accédez alors à la fenêtre suivante, permettant de réaliser des opérations SQL sur la base.

Vous avez à votre disposition deux fichiers (creertoner.sql et instoner.sql) permettant de créer les tables imprimante, typeimpr, cartouche, accepter et de les valoriser avec quelques données.

Une fois le fichier rapatrié, ouvrez-le via la commande File->Open Script... de l'application. Le contenu est alors placé dans la zone d'édition de Database Manager. Il ne vous reste plus qu'à déclencher l'interprétation des ordres SQL en cliquant sur le bouton Execute.

Le fichier creertoner.sql contient des ordres de création de tables, ainsi que des commentaires. Exécutez-le

Puis supprimez (clear) le contenu du panel d'édition, et chargez le script instoner.sql.

Exécutez-le et vérifiez le bon déroulement de l'opération.

Dans le doute, fermez Database Manager et relancez le comme indiqué précédemment.

Conclusion

Nous avons vu comment créer une base de données pour Hypersonic SQL.

Avec Database Manager nous disposons d'un bon outil visuel pour gérer les données d'une base.

Bon à savoir

Vous trouverez dans le répertoire doc le fichier hSqlSyntax.html. Il contient la syntaxe SQL compatible avec Hypersonic SQL.

Le répertoire demo contient le fichier TestSelf.txt qui fourmille d'exemples d'ordres SQL compatibles avec Hypersonic SQL.

Hypersonic SQL tient sur une disquette, n'hésitez pas à le transporter chez vous ! (que vous soyez sous Windows ou Linux).

L'étape suivante consiste à gérer ces données par un programme écrit en Java.

Communication Java -> SGBDR

Introduction à JDBC

L'API JDBC se trouve dans java.sql et les pilotes doivent implémenter l'interface java.sql.Driver.

Il existe quatre types de pilotes jdbc :

  • Type 1 : Pont jdbc-odbc (livré en standard, idéal comme premier driver sous Windows)

  • Type 2 : API native + un peu de java.

  • Type 3 : Comme type 2 mais avec un protocole réseau tout en java.

  • Type 4 : Protocole natif 100% java.

Les éditeurs de SGBDR proposent leurs propores pilotes JDBC.

L'interaction à un système de gestion de base de données réquiert en général au moins quatre étapes :

  1. Chargement du pilote

  2. Etablissement de la connexion

  3. Exécution d'une requête

  4. Analyse des résultats

Les étapes 1 et 2 le sont pour un ensemble d'opérations (étapes 3 et 4). Nous présentons ci-dessous chacune de ces étapes.

I - Chargement du pilote dans la JVM (Java Virtual Machine)

On charge généralement le pilote par son nom. Ci-dessous, un exemple de programme chargeant un des deux pilotes définis sous forme de chaînes de caractères.

  final String driverPostgreSql = "jdbc.postgresql.Driver"; // driver PostgreSql
  final String driverHsql       = "org.hsqldb.jdbcDriver";  // driver Hypersonic SQL

  String driver = driverHsql;

  Class.forName(driver).newInstance();                     // Autochargement du driver

II - Etablissement de la connexion

Une fois le driver chargé en mémoire, nous pouvons obtenir une connexion via la méthode de classe getConnection() de la classe DriverManager

   Connection con = DriverManager.getConnection(URL, "user", "passwd");
   //  URL : url de connexion de la forme jdbc:sous-protocole:sous-nom
   //     sous-protocole:identification du pilote
   //     sous-nom      :informations nécessaire au pilote pour la connexion (chemin, port, nom) 
   // "passwd" : Mot de passe 
   // "user"   : Nom de l'utilisateur référencé par la base 

Exemple

   final String driver = "org.hsqldb.jdbcDriver";
   final String url = "jdbc:hsqldb:/home/kpu/java/hsql/refuge/refuge";
   final String user="sa";
   final String password="";
        
   Connection con = null;
   try {
       Class.forName(driver).newInstance();
       con = DriverManager.getConnection(url, user, password);
   ...

III - Exécution d'une requête SQL

L'exécution d'une requête SQL s'effectue via un objet de la classe java.sql.Statement. C'est l'objet Connection qui nous fournira une référence d'objet Statement (à ne pas instancier directement). Exemple :

   Statement stat = con.createStatement();

On distingue deux types de requêtes : requête d'interrogation et de mise à jour.

  • Requête d'interrogation avec l'ordre SELECT

    Nous utiliserons la méthode de Statement executeQuery( ) qui retourne un objet java.sql.ResultSet.

       ResultSet rs = stat.executeQuery("SELECT * FROM ANIMAL");
    
  • Requête de mise à jour avec les ordres UPDATE, INSERT, DELETE

    On utilisera la méthode executeUpdate( ) de Statement.

    Cet exemple supprime de la table ENTREPRISES toutes les entreprises de Seine et Marne.

       stat.executeUpdate( "DELETE FROM ENTREPRISES WHERE CODEPOST LIKE '77%'");
    

IV - Analyse des résultats

Requête d'interrogation avec l'ordre SELECT

Le retour d'un ordre executeQuery(...) est un objet de type ResultSet, une collection de lignes constituées de 1 à n attributs (colonnes).

Pour accéder à la première ligne du résultat, il est nécessaire d'appeler la méthode next(), pour passer à la ligne suivante, il suffit d'appeler de nouveau cette méthode, etc.

   ResultSet rs = stat.executeQuery("SELECT * FROM ANIMAL");
   
   // Pour accéder à chacun des tuples du résultat de la requête : 
   while (rs.next()) { 
      String nom              = rs.getString("nom"); 
      java.sql.Date date_nais = rs.getDate("date_nais");
      int id                  = rs.getInt(1);
      ... 
   }

Remarque 1 : L'appel à la méthode next() de l'objet Statement est obligatoire avant tout appel aux méthodes permettant d'accéder à une valeur d'un attribut de la ligne courante.

Remarque 2 : Il y a deux façons d'accéder à une valeur d'un attribut (colonne) : 1/ soit par le nom de la colonne, comme par exemple les deux premiers appels de l'exemple. 2/ soit par position, qui commence à la position 1 (et non 0 comme avec les collections), comme le montre le troisième appel.

Requête de mise à jour (UPDATE, INSERT, DELETE)

La méthode executeUpdate( ) de Statement, ne retourne pas un objet java.sql.ResultSet mais retourne le nombre de lignes impactées par l'instruction.

Cet exemple supprime de la table ENTREPRISES toutes les entreprises de Seine et Marne.

   int count = stat.executeUpdate( "DELETE FROM ENTREPRISES WHERE CODEPOST LIKE '77%'");
   System.out.println("Il y a eu " + count + " lignes supprimées.");

Exemple de programme

Le programme ci-dessous utilise une base nommée Refugedb.

Cette base de données contient une table nommée ANIMAL; voici un script de création :

  CREATE TABLE ANIMAL (
    id INTEGER PRIMARY KEY, 
    categorie VARCHAR NOT NULL, 
    nom VARCHAR, race VARCHAR, 
    sexe CHAR, 
    date_nais DATE, 
    id_proprio INTEGER, 
    present BIT
  )
  INSERT INTO ANIMAL VALUES (1,'CRM', 'kiki','berger','M','2000-2-21','21',false)
  INSERT INTO ANIMAL VALUES (2,'CRM','rex','caniche','M','1996-12-2','11',true)
  INSERT INTO ANIMAL VALUES (3,'CGR','mana','berger allemand','F','1995-2-2',3,true)
  INSERT INTO ANIMAL VALUES (4,'CRM','medor','fox terrier','M','2001-4-15',31,true)
  INSERT INTO ANIMAL VALUES (5,'CGR','sultan','dog','M','1994-8-18',21,true)
  INSERT INTO ANIMAL VALUES (6,'CRM','rox','caniche','M','1993-11-27',11,true)
  INSERT INTO ANIMAL VALUES (7,'CHT','roméo','chat de goutiere','M','1992-1-7',62,true)
  INSERT INTO ANIMAL VALUES (8,'CRM','violette','dalmatien','F','1993-4-23',21,true)
  INSERT INTO ANIMAL VALUES (9,'CGR','pensée','danois','F','1991-2-11',13,true)
  INSERT INTO ANIMAL VALUES (10,'CHT','perlette','persan','F','1990-12-2',51,true)
  INSERT INTO ANIMAL VALUES (11,'CGR','perdita','berger allemand','F','1989-8-1',8,false)
  INSERT INTO ANIMAL VALUES (12,'CPR','neige','bichon','F','1997-2-9',2,true)
  INSERT INTO ANIMAL VALUES (13,'CGR','filou','labrador','M','1990-2-19',11,true)
  INSERT INTO ANIMAL VALUES (14,'CPR','lou','caniche nain','M','1993-5-9',9,true)
  

On remarquera que la valeur de la clé primaire n'est pas auto-incrémentable. Le programme ci-dessous recherche la plus grande valeur de cet attribut dans la table, pour déterminer la valeur de l'id du nouveau tuple (ligne) à insérer. Attention, cette technique n'est pas valable lors d'accès concurrents (lorsque plusieurs clients réalisent une insertion dans une même table dans un même temps - l'un n'a pas encore réalisé l'insertion alors que l'autre demande le max(id).). C'est pour cela que l'on préfère les clés de type auto-incrémenté.

Lorsque l'on accède à une base de données, une gestion des exceptions s'avère nécessaire car de multiples problèmes peuvent survenir : le pilote ne peut être chargé (introuvable ?), connexion refusée, requête SQL mal formée... Voici l'exemple complet.


 1	import java.sql.*;
 2	
 3	public class TestAnimal {
 4	  public void test() {
 5	    final String driver   = "org.hsqldb.jdbcDriver";
 6	    final String url      = "jdbc:hsqldb:/home/kpu/Refuge/Refugedb";
 7	    final String user     = "sa";
 8	    final String password ="";
 9	        
10	    Statement  st  = null;
11	    Connection con = null;
12	    ResultSet  rs  = null;
13	    String    sql  = "";
14	    try {
15	       Class.forName(driver).newInstance();
16	       con = DriverManager.getConnection(url, user, password);
17	       int maxID = 0;
18	       sql = "SELECT max(id) FROM ANIMAL";
19	       st  = con.createStatement();
20	       rs  = st.executeQuery(sql);
21	       rs.next();
22	       maxID=rs.getInt(1);
23	       if (maxID==14) {
24	          System.out.println("Ajout d'un animal.");
27	          maxID++;
28	          sql = "INSERT INTO ANIMAL VALUES ("
29	                + maxID
30	                + ",'CPR','loulou','yorkshire','M','1994-11-9',2,true)";
31	          st.executeUpdate(sql);
32	          System.out.println("Ajout d'un animal terminé.");
33	        }
36	        sql = "SELECT * FROM ANIMAL";
37	        rs  = st.executeQuery(sql);
38	        System.out.println("ID\tTYPE\tNOM\t\tRACE\t");
39	        while (rs.next()) {
40             System.out.print(rs.getInt(1)+"\t");
               // ATTENTION, les indices commencent à 1.
41	           System.out.print(rs.getString(2)+"\t");  
42	           System.out.print(rs.getString("nom")+"\t\t");  
43	           System.out.println(rs.getString("race")+"\t");  
44	        }  
45	     }
46	     catch (ClassNotFoundException e) {
47	        System.err.println("Classe non trouvée : (" + driver + ")  => "
                               + e.getMessage());
48	     }
49	     catch (SQLException e) {
50	        System.err.println("SQL erreur : "+ sql + "  " + e.getMessage());
51	     }
52	     catch (Exception e) {
53	        System.err.println("Erreur : "+ e.getMessage());
54	     }
55	 
56	     finally {
57	        try { if (con != null) { con.close(); } }
58	        catch (Exception e) { System.err.println(e); }
59	     }
60	  }
61	  static public void main(String[] arg) {
62	     TestAnimal app = new TestAnimal();
63	     app.test();
64	  }
65	}

Quelques commentaires :

  • Ligne 15 : Chargement du pilote Hypersonic SQL.

  • Ligne 16 : Etablissement d'une connexion à la base.

  • Lignes 18 - 20 : Instructions pour la recherche de la plus grande valeur d'identifiant.

  • Linges 21 - 23 : Exploitation du résultat.

  • Lignes 23 - 33 : Si la base est dans son état initial (id max = 14) alors insertion d'un nouvel animal dans la base.

  • Lignes 36 - 44 : Selection de toutes les lignes de la table ANIMAL.

    Affichage de quelques attributs (ID, TYPE, NOM et RACE). La condition de poursuite dans le while permet d'avancer à la prochaine ligne et de tester si la fin n'est pas atteinte (rend false alors).

  • Lignes 46 - 54 : Une gestion des exceptions.

  • Lignes 56 - 59 : A ne pas oublier, fermeture de la connexion.

TP programmation

Voici un programme qui liste les tuples (lignes) de la table imprimante :

  
import java.sql.*;

public class TestDB {
  public void test() {
    final String driver   = "org.hsqldb.jdbcDriver";
    final String url      = "jdbc:hsqldb:/home/kpu/tp18/imprimantes";
    final String user     = "sa";
    final String password ="";
        
    Statement  st  = null;
    Connection con = null;
    ResultSet  rs  = null;
    String    sql  = "";
    try {
       Class.forName(driver).newInstance();
       con = DriverManager.getConnection(url, user, password);
       st  = con.createStatement();
       sql = "SELECT * FROM imprimante";
       rs  = st.executeQuery(sql);
       System.out.println("ID   \tREF     \tCODETYPE\tDESIGNATION\t");
       while (rs.next()) {
           System.out.print(rs.getInt(1)+"   \t");
             //ATTENTION, les indices commencent à 1.
           System.out.print(rs.getString(2)+"   \t");  
           System.out.print(rs.getString("codeType")+" \t\t");
             //accès par le nom de la colonne  
           System.out.println(rs.getString(4)+" \t\t");  
        }  
     }
     catch (ClassNotFoundException e) {
        System.out.println(
          "Classe non trouvée : (" + driver + ")  => " + e.getMessage());
     }
     catch (SQLException e) {
        System.out.println("SQL erreur : "+ sql + "  " + e.getMessage());
     }
     catch (Exception e) {
        System.out.println("Erreur : "+ e.getMessage());
     }
 
     finally {
        try { if (con != null) { st.close(); con.close(); } }
        catch (Exception e) { }
     }
  }
  static public void main(String[] arg) {
     TestDB app = new TestDB();
     app.test();
  }
}
  

Exemple de compilation et d'exécution :

On remarquera que le classpath est valorisé au lancement de notre test. On indique le paquetage (package) détenant le pilote chargé dynamiquement (lors de l'exécution de l'instruction Class.forName(driver).newInstance();). Ici le paquetage hsqldb.jar est supposé dans le répertoire courant.

L'objectif de ce TP est de vous familiariser avec un type particulier de programmation : gérer l'interaction avec un SGBDR via SQL. Comme le compilateur java ne vérifie par la syntaxe des requêtes SQL que votre programme envoie au SGBDR, ce dernier peut déclencher des exceptions, donc des erreurs qui ne seront repérées qu'à l'exécution.

Sur la base de TestDB, vous réaliserez un programme (en mode console) qui offre à l'utilisateur la possibilité de :

  • Lister l'une des 4 tables contenues dans la base imprimantes (prévoir un menu simple).

  • Ajouter une imprimante - c'est l'utilisateur qui fournit les informations nécessaires à l'insertion d'une nouvelle imprimante. Vous trouverez dans instoner.sql la façon de gérer, lors d'une insertion, la valeur d'un identifiant auto-incrémenté.