Table des matières

TME 8 JDBC

TME 8 JDBC

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.

Premiers pas en JDBC

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

Préparation du TME

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

unzip 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  &

Chargement des données

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.

Tester la connexion JDBC

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

Exercice 1 : Requêtes fixes

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:

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

et ensuite exécuter le programme principal:

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.

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

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();
                }
        }
}

Il faut recompiler Tmejdbc.java et afficherDuels.java:

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

et ensuite exécuter le programme principal:

java -cp .:h2-1.4.200.jar Tmejdbc

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.

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

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 prime:" );
                        while (resultat.next()) {
                            System.out.println(resultat.getString(1)+ "\t" +
                                               resultat.getInt(2) + "\t" );
                        }
                        resultat.close();
                        lecture.close();
                }catch(Exception e){
                e.printStackTrace();
                }
        }
}

Il faut recompiler Tmejdbc.java et sponsorPrimes.java:

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

et ensuite exécuter le programme principal:

java -cp .:h2-1.4.200.jar Tmejdbc

Exercice 2 : Requêtes paramétrées

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 les 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 …”.

import java.sql.Connection;
import java.io.Console;
 
public class Tmejdbc {
 
    public static void main(String[] args) {
        Connexion conn = new Connexion();
        conn.connecter();
        /*rajouter les lignes suivantes pour la question 1*/
        Console console = System.console();
        /*prompt de saisie d'une valeur*/
       String annee_saisie =  console.readLine("quelle annee ? ");
       /*conversion vers un entier*/
       int annee = Integer.parseInt(annee_saisie);
       joueurAge ja = new joueurAge();
       ja.executer(conn, annee);
       conn.fermer();
 
    }
 
}

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();
	    	}
	}
}

Il faut recompiler Tmejdbc.java et joueurAge.java:

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

et ensuite exécuter le programme principal:

java -cp .:h2-1.4.200.jar Tmejdbc

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

import java.sql.Connection;
import java.io.Console;
 
public class Tmejdbc {
 
    public static void main(String[] args) {
        Connexion conn = new Connexion();
        conn.connecter();
        /*rajouter les lignes suivantes pour la question 1*/
        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);
       joueursRencontre jr = new joueursRencontre();
       jr.executer(conn, lieu, annee);
       conn.fermer();
    }
 
}

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();
	    	}
	}
}

Il faut recompiler Tmejdbc.java et joueursRencontre.java:

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

et ensuite exécuter le programme principal:

java -cp .:h2-1.4.200.jar Tmejdbc

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

import java.sql.Connection;
import java.io.Console;
 
public class Tmejdbc {
 
    public static void main(String[] args) {
        Connexion conn = new Connexion();
        conn.connecter();
        /*rajouter les lignes suivantes pour la question 1*/
        Console console = System.console();
        /*prompt de saisie d'une valeur*/
       String min_saisie =  console.readLine("montant min ? ");
       String max_saisie =  console.readLine("montant max ? ");
       /*conversion vers un entier*/
       int min = Integer.parseInt(min_saisie);
       int max = Integer.parseInt(max_saisie);
       rechercheSponsor rs = new rechercheSponsor();
       rs.executer(conn, min, max);
       conn.fermer();
    }
 
}

public class rechercheSponsor {
 
    public void executer(Connexion conn, int minsp, int maxsp) {
 
	String requete = "select nomSponsor, max(prime) as maxp from Gain group by nomSponsor 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();
	    	}
	}
}

Il faut recompiler Tmejdbc.java et rechercheSponsor.java:

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

et ensuite exécuter le programme principal:

java -cp .:h2-1.4.200.jar Tmejdbc

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.

Tmejdbc.java

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();
    }
 
}

joueursInfos.java

import java.sql.*;
import java.util.*;
 
public class joueursInfos {
    Statement joueurs;
    PreparedStatement primes;
    ResultSet resultatc
    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();
	    	}
    }
 
}

Il faut recompiler Tmejdbc.java et joueursInfos.java:

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

et ensuite exécuter le programme principal:

java -cp .:h2-1.4.200.jar Tmejdbc

Bases de Données / Databases

Panneau latéral

Table des matières

TME 8 JDBC

Premiers pas en JDBC

Préparation du TME

Chargement des données

Tester la connexion JDBC

Exercice 1 : Requêtes fixes

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.

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

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.

Exercice 2 : Requêtes paramétrées

4- Modifier joueurAge.java pour retourner les joueurs en fournissant leur année de naissance.

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

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

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.

Bases de Données / Databases

Outils pour utilisateurs

Outils du site

Panneau latéral

Table des matières

TME 8 JDBC

Premiers pas en JDBC

Préparation du TME

Chargement des données

Tester la connexion JDBC

Exercice 1 : Requêtes fixes

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.

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

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.

Exercice 2 : Requêtes paramétrées

4- Modifier joueurAge.java pour retourner les joueurs en fournissant leur année de naissance.

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

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

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.

Outils de la page