Java, SGBDR Hypersonic SQL

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.

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).

Pour utiliser Hypersonic SQL , vous pouvez :

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.

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

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/

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).

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.

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.

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 :

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 :

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.

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

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);
   ...

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

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.");

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	}