TME 8 JDBC (H2)

Consulter la brève introduction à JDBC vue en cours pdf

L'objectif de ce TME est d'utiliser l'interface JDBC pour interroger la Base de Données tennis à partir d'un programme java. Il vous sera demandé de compléter des squelettes de classes java dans le but de vous connecter au SGBD H2 et d'envoyer des requêtes. Pour ce TME, on utilisera la base Tennis dont le schéma est rappelé ci-dessous.

• JOUEUR(NUJOUEUR, NOM, PRENOM, ANNAISS, NATIONALITE)
• GAIN(NUJOUEUR, LIEUTOURNOI, ANNEE, PRIME, NOMSPONSOR)
• RENCONTRE(NUGAGNANT, NUPERDANT, LIEUTOURNOI, ANNEE)

Les attributs NuGagnant, NuPerdant et NuJoueur sont définis sur le même domaine. Les clés des relations sont soulignées.

Le package JDBC permet d'accéder au SGBD depuis une application écrite en langage java. Consulter la documentation officielle. Notamment le lien suivant qui explique comment utiliser une ResultSet https://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#JJDBC28066

La réalisation de ce TME nécessite des connaissances de base en compilation de programmes Java.

Pour compiler un fichier source Fichier.java en utilisant une librairie externe lib.jar taper

Linux et Mac:

 javac -cp .:lib.jar Fichier.java 

Windows:

 javac -cp .;lib.jar Fichier.java 

Cette commande va créer un binaire appelé Fichier.class (et MaClasse.java). Pour exécuter le programme taper

Linux et Mac:

 java -cp .:lib.jar Fichier 

Windows:

 java -cp .;lib.jar Fichier 

Remarques

• Afin d'éviter les erreurs de compilation dues à l'utilisation de caractères accentués dans les fichiers sources, compiler ces derniers en tapant

javac -encoding UTF-8 Fichier.java

• Les étudiants souhaitant utiliser l'IDE Eclipse peuvent se réferer à la rubrique Configuration d'Eclipse à la fin de la page. A défaut, utiliser un éditeur texte qui reconnait la syntaxe Java (tel que emacs).

Télécharger l'archive TMEJDBC-H2 contenant les sources puis décompresser et extraire son contenu avec la commande zip ou tar.

AVec tar:

tar xzf TMEJDBC-H2.zip 
cd TMEJDBC-H2

L'archive contient :

• h2-1.4.200.jar qui permet de lancer le serveur et qui est utile pour le compilation des sources java
• Les squelettes java à modifier pour répondre aux questions (les fichiers *.java)
• Les scripts de création et de remplissage de la base tennis (les fichiers *.sql)

Lancer le serveur en tapant

java -cp h2-1.4.200.jar org.h2.tools.Server -ifNotExists -tcp -tcpPort 9093  &

Il existe déjà une base par défaut H2/jdbc (user: jdbc, password: jdbc). Pour charger les données, taper les commandes suivantes :

Linux et Mac:

java -cp .:h2-1.4.200.jar org.h2.tools.RunScript -url jdbc:h2:./H2/jdbc -user jdbc -password jdbc -script creations.sql
java -cp .:h2-1.4.200.jar org.h2.tools.RunScript -url jdbc:h2:./H2/jdbc -user jdbc -password jdbc -script insertions.sql

Windows:

java -cp .;h2-1.4.200.jar org.h2.tools.RunScript -url jdbc:h2:./H2/jdbc -user jdbc -password jdbc -script creations.sql
java -cp .;h2-1.4.200.jar org.h2.tools.RunScript -url jdbc:h2:./H2/jdbc -user jdbc -password jdbc -script insertions.sql

Vous pouvez créer une autre base en utilisant le menu du serveur H2 accessible à partir de votre barre de menu en bas de l'ecran. Dans ce cas, il faudra aussi changer le fichier Connexion.java.

Tout le long du TME, vous utiliserez le fichier Tmejdbc.java pour lancer vos programmes.

Compiler puis exécuter Tmejdbc.java en tapant

Linux et Mac (compilation):

javac -cp .:h2-1.4.200.jar Tmejdbc.java 

Windows (compilation):

javac -cp .;h2-1.4.200.jar Tmejdbc.java 

Mac et Linux (exécution):

java -cp .:h2-1.4.200.jar Tmejdbc

Windows (exécution):

java -cp .;h2-1.4.200.jar Tmejdbc

Vous devriez voir s'afficher dans la console le message suivant :

Driver JDBC H2 reconnu!
Les tables du compte  sont :
-GAIN
-JOUEUR
-RENCONTRE

Le but de cet exercice est d'exécuter, via JDBC, des requêtes codées en dur, i.e où les valeurs de prédicats sont définies dans le code source. A chaque question correspond un fichier source qu'il vous faudra éditer en conséquence puis instancier et appeler sa méthode executer(Connexion conn) depuis Tmejdbc.java. Pour consulter le résultat attendu de chaque question résultat).

1- Modifier afficherJoueurs.java pour permettre d'afficher le nom, prénom et la date de naissance de tous les joueurs par ordre alphabétique.

Attention, ne pas ajouter de point virgule à la fin de la chaîne de caractères de la requête

Remarque : Utiliser Tmejdbc.java tout le long du TME pour répondre aux questions. Par exemple, pour cette question, rajouter dans Tmejdbc.java les deux lignes suivantes qui permettent d'instancier un objet de la classe afficherJoueurs et d'exécuter sa méthode executer().

import java.sql.*;
 
 public class Tmejdbc {
 
    public static void main(String[] args) {
	Connexion conn = new Connexion();
	conn.connecter();
        /*rajouter les lignes suivantes pour la question 1*/
        afficherJoueurs aj = new afficherJoueurs();
        aj.executer(conn);
        /**/
      }
}

public class afficherJoueurs {
        public void executer(Connexion conn) {
                String requete = "select nom, prenom, annaiss from Joueur order by nom, prenom";
                try {
                        Statement lecture =  conn.connexion.createStatement();
                        ResultSet resultat = lecture.executeQuery(requete);
                        System.out.println("1- Les joueurs avec leur annee de naissance sont :" );
                        while (resultat.next()) {
                            System.out.println(resultat.getString(1)+ "\t" + resultat.getString(2) + "\t" + resultat.getInt(3));
                            }
                        resultat.close();
                        lecture.close();
 
                }catch(Exception e){
 
                e.printStackTrace();
                }
	}
}

Il faut recompiler Tmejdbc.java et afficherJoueurs.java:

Linux et Mac:

javac -cp .:h2-1.4.200.jar Tmejdbc.java afficherJoueurs.java

Windows:

javac -cp .;h2-1.4.200.jar Tmejdbc.java afficherJoueurs.java

et ensuite exécuter le programme principal: Mac et Linux:

java -cp .:h2-1.4.200.jar Tmejdbc

java -cp .;h2-1.4.200.jar Tmejdbc

2- Modifier afficherDuels.java pour afficher les joueurs qui se sont affrontés à Roland Garros en 1994.

public class afficherDuels {
        public void executer(Connexion conn) {
            String requete = "select j1.nom, j2.nom from Joueur j1, Joueur j2, Rencontre r where j1.nujoueur=r.nugagnant and j2.nujoueur=r.nuperdant and r.lieutournoi='Roland Garros' and r.annee=1994 order by j1.nom, j2.nom";
                try {
                        Statement lecture =  conn.connexion.createStatement();
                        ResultSet resultat = lecture.executeQuery(requete);
                        System.out.println("2- Les duels de Roland Garros 1994 sont :");
                        while (resultat.next()) {
                            System.out.println(resultat.getString(1)+ "\t vs \t" + resultat.getString(2) + "\t" );
                }
                        resultat.close();
                        lecture.close();
                }catch(Exception e){
                e.printStackTrace();
                }
        }
}

3- Modifier sponsorPrimes.java pour afficher les sponsors avec leur plus grande prime. Le résultat devra être trié par ordre décroissant de cette prime.

public class sponsorPrimes {
        public void executer(Connexion conn) {
            String requete = "select nomsponsor, max(Prime) as max_prime from Gain group by nomsponsor order by max_prime desc ";
                try {
                        Statement lecture =  conn.connexion.createStatement();
                        ResultSet resultat = lecture.executeQuery(requete);
                        System.out.println("3- Les sponsors avec leur plus grande:" );
                        while (resultat.next()) {
                            System.out.println(resultat.getString(1)+ "\t" +
                                               resultat.getInt(2) + "\t" );
                        }
                        resultat.close();
                        lecture.close();
                }catch(Exception e){
                e.printStackTrace();
                }
        }
}

On veut paramétrer les trois requêtes précédentes en donnant la possibilité à l'utilisateur de spécifier des critères de sélection. Ces critères devraient être fournis dans la console et utilisés pour interroger la base. Pour ce faire, il faudra utiliser la classe preparedStatement qui permet de fournir les valeurs de prédicats pendant l'exécution (cf https://java.developpez.com/faq/jdbc/?page=Les-instructions-parametrees-moins-PreparedStatement) Pour lire les données depuis la console, utiliser le package java.io.Console dont voici un cas d'usage :

import java.sql.*;
import java.io.Console;
 
public class Tmejdbc {
 
    public static void main(String[] args) {
	Connexion conn = new Connexion();
	conn.connecter();
        /*Instanciation d'une console*/
	Console console = System.console();
       /*prompt de saisie d'une valeur*/
       String annee_saisie =  console.readLine("quelle annee ? ");
       String lieu = console.readLine("quel lieu ? ");
       /*conversion vers un entier*/
       int annee = Integer.parseInt(annee_saisie);
       /*suite des traitements ...*/
 
   }
}

Ce code produit la sortie suivante

quelle annee ? 
quel lieu ? 

4- Modifier joueurAge.java pour retourner le joueurs en fournissant leur année de naissance. Personnaliser le message de réponse de sorte à indiquer le cas où l'année de naissance saisie ne figure pas dans la base. Par exemple, afficher le message “Il n'existe pas de joueur né en …”.

public class joueurAge {
 
    public void executer(Connexion conn, int annee) {
		String requete = "select nom, prenom from Joueur where annaiss = ? order by nom, prenom";
		try {
			PreparedStatement lecture =  conn.connexion.prepareStatement(requete);
			lecture.setInt(1, annee);
			ResultSet resultat = lecture.executeQuery();
 
			Boolean print = true; 
			while (resultat.next()) {
			    if(print){
				print = false;
				System.out.println(" Les joueurs nés en " + annee + "  sont :" );
			    }
			    System.out.println(resultat.getString(1)+ "\t" + resultat.getString(2) );
			  }
			if(print)
			     System.out.println(" Aucun joueur né en " + annee);
			resultat.close();
			lecture.close();
 
		}catch(Exception e){ 
 
	    	e.printStackTrace();
	    	}
	}
}

5- Modifier joueursRencontre.java pour retourner les joueurs qui se sont affrontés lors du tournoi (lieutournoi, annee) fourni en argument.

public class joueursRencontre {
 
    public void executer(Connexion conn, String lieu, int annee) {
 
	String requete = "select j1.nom, j2.nom from Joueur j1,Joueur j2, Rencontre r where j1.nujoueur=r.nugagnant and j2.nujoueur=r.nuperdant and r.lieutournoi=? and r.annee=?";
 
		try {
			PreparedStatement lecture =  conn.connexion.prepareStatement(requete);
			lecture.setString(1, lieu);
			lecture.setInt(2, annee);
			ResultSet resultat = lecture.executeQuery();
 
			Boolean print = true; 
			while (resultat.next()) {
			    if(print){
				print = false;
				System.out.println(" Les duels de " + lieu + " en " + annee + "  sont :" );
			    }
			    System.out.println(resultat.getString(1)+ "\t" + resultat.getString(2) );
			  }
			if(print)
			     System.out.println(" Aucun duel pour " + lieu + " en " + annee);
			resultat.close();
			lecture.close();
 
		}catch(Exception e){ 
 
	    	e.printStackTrace();
	    	}
	}
}

6- Modifier rechercheSponsor.java pour retourner les sponsors dont la prime maximale varie entre montantMin et montantMax fournis en argument.

public class rechercheSponsor {
 
    public void executer(Connexion conn, int minsp, int maxsp) {
 
	String requete = "select Sponsor, max(prime) as maxp from Gain group by Sponsor having max(prime) between ? and ? ";
 
		try {
			PreparedStatement lecture =  conn.connexion.prepareStatement(requete);
			lecture.setInt(1, minsp);
			lecture.setInt(2, maxsp);
			ResultSet resultat = lecture.executeQuery();
 
			Boolean print = true; 
			while (resultat.next()) {
			    if(print){
				print = false;
				System.out.println(" Les sponsors dont la prime maximale varie entre " + minsp + " et " + maxsp + "  sont :" );
			    }
			    System.out.println("Sponsor : " + resultat.getString(1)+ "\t Prime : " + resultat.getInt(2) );
			  }
			if(print)
			     System.out.println(" Aucun sponsor ayant une prime entre " + minsp + " et " + maxsp);
			resultat.close();
			lecture.close();
 
		}catch(Exception e){ 
 
	    	e.printStackTrace();
	    	}
	}
}

7- Modifier joueursInfos.java qui, dans un premier temps, liste tous les numéros et noms des joueurs. Dans un deuxième temps l'utilisateur pourra demander les primes des joueurs dont le numéro est fourni dans la console. La sortie du programme est comme suit :

Les joueurs de la base sont :
Numéro 1	 Nom : MARTINEZ
Numéro 2	 Nom : NAVRATILOVA
Numéro 3	 Nom : GRAF
Numéro 4	 Nom : HALARD
Numéro 5	 Nom : PIERCE
Numéro 6	 Nom : EDBERG
Numéro 7	 Nom : LARSSON
Numéro 8	 Nom : LECONTE
Numéro 9	 Nom : FORGET
Numéro 10	 Nom : FLEURIAN
Numéro 11	 Nom : WILANDER
Numéro 12	 Nom : CONNORS
Numéro 13	 Nom : McENROE
Numéro 14	 Nom : SAMPRAS
Entrer numéro joueur ou taper fin : 14
 Les primes du joueur numéro 14 sont :	200000	1800000	700000	1400000
Entrer numéro joueur ou taper fin : fin
**Merci et au revoir!**

Bonus : Rajouter un contrôle qui vérifie le numéro de joueur saisi par l'utilisateur sans ré-exécuter de requête.

import java.sql.*;
import java.io.Console;
 
public class Tmejdbc {
 
    public static void main(String[] args) {
	Connexion conn = new Connexion();
	conn.connecter();
        Console console = System.console();
	joueursInfos ji = new joueursInfos(conn);
	ji.listerJoueurs();
	String snumj = console.readLine("Entrer numéro joueur : ");
	int numj;
 
	while(!snumj.equals("fin")){
	    numj = Integer.parseInt(snumj);
	    if(ji.existeJoueur(numj))
		ji.listerPrimesJoueur(numj);
	    else
		System.out.println("Aucun joueur avec le numéro "+ numj);
	     snumj = console.readLine("Entrer numéro joueur ou taper fin : ");
	}
	System.out.println("**Merci et au revoir!**");
	conn.fermer();
    }
 
}

import java.sql.*;
import java.util.*;
 
 
public class joueursInfos {
    Statement joueurs;
    PreparedStatement primes;
    ResultSet resultat;
    Connexion conn;
    List listeJoueurs;
 
    String reqJoueurs = "select nujoueur, nom from Joueur";
    String reqPrimes = "select prime from Gain where nujoueur = ?";
 
 
    /*constructeurs*/
    public joueursInfos(Connexion conn){
	this.conn = conn;
	listeJoueurs = new LinkedList();
	try {
	    joueurs = conn.connexion.createStatement();
	    primes  = conn.connexion.prepareStatement(reqPrimes);
	}
	catch(Exception e){ 
 	    	e.printStackTrace();
	    	}
    }
 
    /**/
    public boolean existeJoueur(int numj){
	return listeJoueurs.contains(numj);
    }
    /**/
    public void listerJoueurs() {
	try{
	    resultat = joueurs.executeQuery(reqJoueurs);
	    System.out.println("Les joueurs de la base sont :");
 
	    int numJ;
	    while(resultat.next()){
		numJ = resultat.getInt(1);
		System.out.println("Numéro "+ numJ + "\t Nom : "+ resultat.getString(2));
		listeJoueurs.add(numJ);
	    }
	    resultat.close();
	    joueurs.close();
       	}catch(Exception e){ 
 
	    	e.printStackTrace();
	    	}
    }
 
    /**/
    public void listerPrimesJoueur(int numjoueur){
	try {
	    primes.setInt(1, numjoueur);
	    resultat = primes.executeQuery();
	    System.out.print(" Les primes du joueur numéro "+ numjoueur + " sont :");
 
	    while (resultat.next())
		System.out.print("\t" + resultat.getInt(1));
	    System.out.println("");
 
	    resultat.close();
      	}catch(Exception e){ 
 
	    	e.printStackTrace();
	    	}
    }
 
}