Chapitre 1

JAVA ET SQL
JDBC
Java - JDBC
SOMMAIRE
Chapitre 1
1.1
1.2
- JDBC ............................................................................ 4
Présentation ............................................................................................. 5
Se Connecter à la Base et Parcourir un curseur ........................................... 6
1.2.1
1.2.2
1.2.3
1.2.4
1.2.5
1.2.6
1.2.7
1.2.8
Objectif..................................................................................................................... 6
Ecran ........................................................................................................................ 6
Principes et démarche ............................................................................................... 6
Principales méthodes utilisées .................................................................................... 7
Script ........................................................................................................................ 8
Commentaires ........................................................................................................... 9
Gestion avancée des curseurs ...................................................................................11
Schéma résumé .......................................................................................................12
1.3.1
1.3.2
1.3.3
1.3.4
1.3.5
1.3.6
Objectif....................................................................................................................13
Principes et démarche ..............................................................................................13
Principales méthodes utilisées ...................................................................................13
Insertion d'un enregistrement ...................................................................................13
Commentaires ..........................................................................................................14
Suppression d'un enregistrement...............................................................................14
1.4.1
1.4.2
1.4.3
1.4.4
1.4.5
1.4.6
Objectif....................................................................................................................15
Principes et démarche ..............................................................................................15
Méthodes utilisées ....................................................................................................15
Script .......................................................................................................................15
Commentaires ..........................................................................................................17
Alternative pour la récupération des valeurs ...............................................................18
1.5.1
1.5.2
1.5.3
1.5.4
1.5.5
Objectif....................................................................................................................19
Principes et démarche ..............................................................................................19
Méthodes utilisées ....................................................................................................19
Script .......................................................................................................................19
Commentaires ..........................................................................................................20
1.6.1
1.6.2
Principe et démarche ................................................................................................21
Exemples .................................................................................................................21
1.7.1
1.7.2
1.7.3
1.7.4
1.7.5
1.7.6
1.7.7
1.7.8
Objectif....................................................................................................................22
Principes et démarche ..............................................................................................22
Méthodes utilisées ....................................................................................................22
La procédure stockée Oracle .....................................................................................23
La procédure stockée MySQL ....................................................................................23
Script pour MySQL ....................................................................................................24
Script pour Oracle ....................................................................................................24
Commentaires ..........................................................................................................25
1.9.1
1.9.2
Objectif....................................................................................................................28
Principes et démarche ..............................................................................................28
1.3
1.4
1.5
1.6
1.7
1.8
1.9
Mises à jour dans la BD ........................................................................... 13
Récupération dynamique du résultat d'un Select ....................................... 15
La méthode Execute() ............................................................................. 19
SQL dynamique (Préparé) ........................................................................ 21
Exécuter une procédure stockée avec des paramètres IN .......................... 22
Exécuter une procédure stockée qui renvoie un SELECT ............................ 26
Exécuter une procédure stockée avec des paramètres OUT ....................... 28
16 avril 2017 - Page 2
Java - JDBC
1.9.3
1.9.4
1.9.5
1.9.6
1.10
1.10.1
1.10.2
1.10.3
1.10.4
1.10.5
1.10.6
1.10.7
1.11
1.11.1
1.11.2
1.11.3
1.12
1.13
Méthodes utilisées ....................................................................................................28
La procédure stockée Oracle .....................................................................................29
Script .......................................................................................................................30
Commentaires ..........................................................................................................31
Exécuter une fonction stockée ............................................................... 32
Objectif....................................................................................................................32
Principes et démarche ..............................................................................................32
Méthodes utilisées ....................................................................................................32
La fonction stockée Oracle ........................................................................................33
La fonction stockée MySQL .......................................................................................33
Script .......................................................................................................................34
Commentaires ..........................................................................................................35
Accès à la MetaBase ............................................................................. 36
Objectif et démarche ................................................................................................36
Quelques méthodes ..................................................................................................36
Script .......................................................................................................................37
Un curseur "scrollable".......................................................................... 38
Un curseur "updatable" ......................................................................... 39
Chapitre 2
2.1
- ANNEXES .................................................................... 40
Une seule classe pour la gestion BD ......................................................... 41
2.1.1
2.1.2
2.1.3
Objectif....................................................................................................................41
Exemple ..................................................................................................................42
Code de la classe .....................................................................................................42
2.2.1
2.2.2
2.2.3
2.2.4
2.2.5
2.2.6
Class.forName ..........................................................................................................43
DriverManager.getConnection ...................................................................................43
Connection.close ......................................................................................................44
createStatement .......................................................................................................44
executeQuery ...........................................................................................................45
Tableaux des Classes et Interfaces ............................................................................46
2.3.1
2.3.2
Bd2Fichier ................................................................................................................49
Fichier2Bd ................................................................................................................51
2.4.1
2.4.2
2.4.3
ODBC (Open DataBase Connectivity) .........................................................................52
JDBC-MySQL ............................................................................................................54
JDBC-Oracle (Java DataBase Connectivity) .................................................................55
2.5.1
2.5.2
2.5.3
Le modèle ................................................................................................................56
Le JavaBean.............................................................................................................57
Le DAO ....................................................................................................................59
2.2
2.3
2.4
2.5
2.6
Quelques méthodes pour la manipulation des BD ...................................... 43
Transferts BD-Fichier ............................................................................... 49
Les pilotes .............................................................................................. 52
Le pattern DAO et les Beans .................................................................... 56
Introspection et SQL ............................................................................... 64
16 avril 2017 - Page 3
Java - JDBC
CHAPITRE 1 - JDBC
16 avril 2017 - Page 4
Java - JDBC
1.1
PRESENTATION
Ce chapitre a pour objectif de vous introduire à la technologie JAVA-BD de type C/S
(Client/Serveur) de première et deuxième génération.
Cela comprend :
L'accès à une BD (Base de Données)
La manipulation des données avec les ordres SQL de base.
pour la première génération
et
la manipulation des données avec :
des procédures stockées
des fonctions stockées
pour la deuxième génération.
Nous retrouverons la gestion de données relationnelles dans d'autres chapitres :
SQL et AWT
SQL et SWING
JAVA et la technologie n-tiers avec les Applets, les Servlets, les JSP, …
Les exemples et exercices sont principalement basés sur une BD MySQL. Quelques-uns avec
Oracle. De toutes façons ils sont facilement adaptables à toutes BD (Oracle, SQL Server, MsAccess, ODBC, …).
16 avril 2017 - Page 5
Java - JDBC
1.2
1.2.1
SE CONNECTER A LA BASE ET PARCOURIR UN CURSEUR
Objectif
Se connecter à une BD et afficher les enregistrements d'une table.
1.2.2 Ecran
1.2.3 Principes et démarche
Se connecter à la base de données,
Créer un objet de type Statement (Nécessaire pour exécuter un ordre SQL statique),
Ouvrir un curseur,
Afficher les enregistrements,
Fermer le curseur,
Se déconnecter.
Créez un nouveau projet nommé projetSQL.
Dans ce projet créez un nouveau package nommé packageSQL.
Ajoutez le fichier .jar du pilote.
Note : pour les différents types de connexions (Oracle, MySQL, ODBC, …) cf dans
les annexes du support le paragraphe Les pilotes.
Ensuite créez une première classe nommée VillesSelect.
16 avril 2017 - Page 6
Java - JDBC
1.2.4 Principales méthodes utilisées
Méthodes
Class.forName("pilote")
Cn = DriverManager.getConnection("url",
"user","pwd")
Cmd = Connection.createStatement()
Rs = Statement.executeQuery("SELECT")
ResultSet.next()
ResultSet.getString(indexColonne)
close()
Fonctionnalités
Monter le pilote d'accès à la BD
Permet de se connecter et renvoie une connexion
Crée un objet de type Statement (instruction)
Exécute une requête et renvoie un curseur
Avance dans le curseur; renvoie False à la fin du curseur
et True autrement.
Récupère une valeur string dans une colonne de
curseur. Les méthodes getXXX() permettent la
récupération de tous les types.
Ferme un curseur, un statement, une connexion.
Si le pilote le permet ainsi que le gestionnaire de curseur vous pouvez scroller un curseur et
faire des MAJ à partir de celui-ci. Ceci via le paramétrage du createStatement().
Connection.createStatement(ResultSet.TYPE_SCROLL , ResultSet.CONCURRENCY); cf en
annexe les valeurs possibles et les supports AWT et SWING pour la mise en place.
Note sur la méthode next() du ResultSet :
La méthode next() avance dans le curseur et renvoie un booléen. Voici un exemple de recherche d'un enregistrement. Le test
évite ainsi une levée d'exception.
ResultSet lrs = lstSql.executeQuery(lsSelect);
if(lrs.next()) lsResultat = lrs.getString(1);
else lsResultat = "Introuvable";
16 avril 2017 - Page 7
Java - JDBC
1.2.5 Script
package packageSQL;
import java.sql.*;
public class VillesSelect
{
public static void main(String[] args)
{
// --- Déclarations
Connection lcConnexion = null;
Statement lstSql
= null;
ResultSet lrs
= null;
// --- Pour une connexion ORACLE avec un pilote natif oracle
//String lsPilote
= "oracle.jdbc.driver.OracleDriver";
//String lsConnexion = "jdbc:oracle:thin:@localhost:1521:xe";
// --- Pour une connexion ORACLE via OBDC
//String lsPilote
= "sun.jdbc.odbc.JdbcOdbcDriver";
//String lsConnexion = "jdbc:odbc:dsn_bd_oracle";
// --- Pour une connexion MySQL native
String lsPilote
= "org.gjt.mm.mysql.Driver";
String lsConnexion = "jdbc:mysql://localhost/cours";
String lsUt = "root";
String lsMdp = "";
String lsSelect = "SELECT * FROM villes";
try
{
Class.forName(lsPilote);
lcConnexion = DriverManager.getConnection(lsConnexion,lsUt,lsMdp);
lstSql = lcConnexion.createStatement();
lrs
= lstSql.executeQuery(lsSelect);
while(lrs.next())
{
System.out.println(lrs.getString(1) + "-" + lrs.getString(2));
}
lrs.close();
lstSql.close();
lcConnexion.close();
lrs
= null;
lstSql
= null;
lcConnexion = null;
}
catch(ClassNotFoundException err) { System.err.println(err.getMessage()); }
catch(SQLException err)
{ System.err.println(err.getMessage()); }
}
}
16 avril 2017 - Page 8
Java - JDBC
1.2.6 Commentaires
1.2.6.1 Les interfaces utilisées
Interfaces
Connection
Statement
RecordSet
Fonctions
Interface permettant de se connecter à la base
Interface permettant de "créer" des commandes
Interface curseur
1.2.6.2 La connexion
Les classes et les interfaces utilisées sont dans le package java.sql
import java.sql.*;
La connexion se fait en deux temps :
On définit d'abord la classe de pilotes que l'on utilise avec la méthode forName de la classe
Class (package java.lang) à laquelle on passe comme paramètre le nom complet du package
et de la classe.
Class.forName("oracle.jdbc.driver.OracleDriver"); // Pour Oracle Natif
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Pour Oracle via ODBC
Ensuite on tente une connexion en utilisant la méthode getConnection de la classe
DriverManager (du package java.sql).
// --- Pour Oracle natif
lcConnexion = DriverManager.getConnection("jdbc:oracle:thin:@140.0.0.68:1521:orcl"
, lsUt, lsMdp);
// --- Pour Oracle Via ODBC
lcConnexion = DriverManager.getConnection("jdbc:odbc:dsn_bd_nt_orac",lsUt,lsMdp);
L'adresse de la base est formée comme une URL avec le format suivant :
protocole:sous_protocole:nom_de_la_base.
1.2.6.3 L'ouverture du curseur
Avant d'ouvrir un curseur il faut créer un objet Statement – qui peut être traduit par
instruction ou commande - avec la méthode createStatement d'un objet de type Connection.
lstSql = lcConnexion.createStatement();
Ensuite la méthode executeQuery de l'objet Statement est exécutée.
lrs = lstSql.executeQuery(lsSelect);
16 avril 2017 - Page 9
Java - JDBC
1.2.6.4 Le parcours du curseur
On utilise la méthode next() - qui renvoie un booléen – de la classe RecordSet.
On utilise la méthode getString(nom_de_colonne | numéro_de_colonne) de l'objet RecordSet
pour récupérer les valeurs de type String; l'indice commence à 1.
while(lrs.next())
{
lsCp = lrs.getString(1);
}
1.2.6.5 La fermeture du curseur
On utilise la méthode Close() de la classe RecordSet.
lrs.close(); // Fermeture du curseur
lstSql.close(); // Fermeture du Statement
1.2.6.6 La déconnexion
On utilise la méthode close() de la classe Connection.
lcConnexion.close(); // Fermeture de la connexion
1.2.6.7 Les exceptions
Les instructions du script peuvent générer deux types d'erreur :
Classe introuvable : ceci concerne le pilote
Erreur SQL
Ces erreurs sont gérées par les exceptions suivantes :
catch(ClassNotFoundException err)
{
System.err.println("\nPilote BD non trouvé");
}
catch(SQLException err)
{
System.err.println(err.getMessage());
}
16 avril 2017 - Page 10
Java - JDBC
1.2.7
Gestion avancée des curseurs
Présentation
Le curseur utilisé précédemment est un curseur en lecture seule et en avant seulement.
Il est possible de l'ouvrir en d'autres modes.
Read Only
Read Write
En avant
Par défault
Impossible
Dans tous les sens
Scroll + Lecture
Scroll + écriture
Syntaxe
Cn.createStatement(sens, lecture)
Valeurs des paramètres :
Constante
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE
Description
En avant
Tous les sens
Tous les sens
ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE
Lecture seule
Lecture/Ecriture
16 avril 2017 - Page 11
Java - JDBC
1.2.8 Schéma résumé
C
Exercice
Créez un nouveau projet nommé projetBD.
Dans ce projet créez un nouveau package nommé packageBD.
Créez une classe BD avec les méthodes seConnecter("bd","ut","mdp"), seDeconnecter() et
getCurseurCSV("select").
La connexion est un attribut de la classe.
La méthode seConnecter() renvoie une StringBuilder qui contiendra soit OK soit une
message d'erreur.
La méthode seDeconnecter() renvoie un booléen.
La méthode getCurseurCSV("select") renvoie une StringBuilder.
Lorsque nous voudrons réutiliser cette classe dans le projet projetSQL, il faudra au préalable
ajouter le fichier projetBD.jar au projet projetSQL.
Dans NetBeans :
Soit Projet/Clic droit/Propriétés/Bibliothèque/Ajouter un fichier jar/ …
Soit Bibliothèques/ Ajouter un fichier jar/ …
16 avril 2017 - Page 12
Java - JDBC
1.3
MISES A JOUR DANS LA BD
1.3.1 Objectif
Réaliser des mises à jour (Insertion, Suppression, Modification) dans une BD.
1.3.2 Principes et démarche
Se connecter à la base de données,
Créer un objet de type Statement,
Exécuter une instruction de mise à jour,
Valider,
Se déconnecter.
1.3.3 Principales méthodes utilisées
Méthodes
Fonctionnalités
Connection.setAutoCommit(boolean) Permet de basculer le mode automatique de commitment.
Statement.executeUpdate(sql)
Connection.rollback()
Connection.commit()
Exécute une instruction SQL de mise à jour.
Cette méthode renvoie un int qui correspond au nombre de
lignes mises à jour.
Invalide la transaction.
Valide la transaction.
1.3.4 Insertion d'un enregistrement
package packageSQL;
import java.sql.*;
public class VillesInsert
{
public static void main(String[] args)
{
// --- Déclarations
Connection lcConnexion = null;
Statement lstSql;
String
lsInsert;
int
liNum;
// --- Instructions de connexion
try
{
Class.forName("org.gjt.mm.mysql.Driver");
lcConnexion =
DriverManager.getConnection("jdbc:mysql://localhost/cours","root","");
lcConnexion.setAutoCommit(false);
lstSql
= lcConnexion.createStatement();
lsInsert = "INSERT INTO villes(cp, nom_ville, id_pays)
VALUES('14000','Caen','033')";
liNum = lstSql.executeUpdate(lsInsert);
16 avril 2017 - Page 13
Java - JDBC
System.out.println("Nombre d'insert : " + liNum);
lcConnexion.commit();
lstSql.close();
lcConnexion.close();
}
catch(ClassNotFoundException err)
{
System.err.println("\nPilote BD non trouvé");
}
catch(SQLException err)
{
System.err.println(err);
}
}
}
1.3.5 Commentaires
1.3.5.1 Insertion
On utilise la méthode executeUpdate() de l'interface Statement pour exécuter une mise à
jour dans la BD.
liNum = lstSql.executeUpdate(lsInsert);
1.3.5.2 Validation
Vous validez avec la méthode commit() appliquée à l'interface de type Connection.
lcConnexion.commit();
1.3.6 Suppression d'un enregistrement
Script
lsDelete = "DELETE FROM villes WHERE cp = '14000'";
liNum = lstSql.executeUpdate(lsDelete);
System.out.println("Nombre de suppression : " + liNum);
lcConnexion.commit();
Commentaires
La méthode utilisée est la même; la différence est que vous envoyez un ordre SQL DELETE à
la place d'un ordre INSERT.
Il en serait de même pour une mise à jour au sens strict avec l'instruction SQL UPDATE.
Exercice
Ajoutez les méthodes supprimer() et inserer() à la classe BD.
16 avril 2017 - Page 14
Java - JDBC
1.4 RECUPERATION DYNAMIQUE DU RESULTAT D'UN SELECT
1.4.1 Objectif
Pouvoir récupérer les valeurs extraites à partir d'un SELECT quel que soit le nombre de
colonnes et le type des colonnes.
1.4.2 Principes et démarche
Connexion à la base et ouverture d'un curseur en exécutant un ordre SQL Select,
Récupération de la structure du curseur dans un objet ResultSetMetaData,
Récupération du nombre de colonnes,
Pour chaque colonne récupération du nom de la colonne,
Pour chaque colonne (pour chaque ligne) récupération de la valeur de la colonne.
1.4.3 Méthodes utilisées
Méthodes
Rs.getMetaData()
Rsmd.getColumnCount()
Rsmd.getColumnName(index)
Rsmd.getColumnType(index)
Rs.getObject(rang)
Rs.getString(rang)
Renvoie
Renvoie
Renvoie
Renvoie
Renvoie
Renvoie
Fonctionnalités
la structure du SELECT
le nombre de colonnes de la structure
le nom de la colonne
le type de la colonne
un objet d'une structure
une string
1.4.4 Script
package packageSQL;
import java.sql.*;
public class RsDynamique
{
public static void main(String[] args)
{
// --- Déclarations
Connection lcConnexion = null;
Statement lstSql
= null;
ResultSet lrs
= null;
ResultSetMetaData lrsmd = null;
String lsSelect
= "";
String lsLigne
= "";
int liCols, i;
Object loValeur;
try
{
Class.forName("org.gjt.mm.mysql.Driver");
lcConnexion =
DriverManager.getConnection("jdbc:mysql://localhost/cours","root","");
lstSql = lcConnexion.createStatement();
16 avril 2017 - Page 15
Java - JDBC
//lsSelect
lsSelect
lrs
lrsmd
liCols
=
=
=
=
=
"SELECT * FROM villes";
"SELECT * FROM clients";
lstSql.executeQuery(lsSelect);
lrs.getMetaData();
lrsmd.getColumnCount();
// --- Affichage des en-têtes des colonnes
for(i=1; i<=liCols; ++i)
{
lsLigne += lrsmd.getColumnName(i) + "-";
}
System.out.println(lsLigne);
// --- Affichage du contenu des colonnes
while(lrs.next())
{
lsLigne = "";
for(i=1; i<=liCols; i++)
{
loValeur = lrs.getObject(i);
if(loValeur == null) lsLigne += "NUL -";
else lsLigne += loValeur.toString() + "-";
}
System.out.println(lsLigne);
}
lrs.close();
lstSql.close();
lcConnexion.close();
lrs
= null;
lstSql
= null;
lcConnexion = null;
}
catch(ClassNotFoundException err)
{
System.err.println("Pilote non monté : " + err);
}
catch(SQLException err)
{
System.err.println("Erreur SQL : " + err);
}
}
}
16 avril 2017 - Page 16
Java - JDBC
1.4.5 Commentaires
1.4.5.1 Ouverture du curseur
Le curseur est créé et ouvert avec la méthode executeQuery() de l'objet Statement.
La chaîne passée en argument est l'ordre Select.
lrs = lstSql.executeQuery(lsSelect);
1.4.5.2 Récupération de la structure
La structure du curseur est récupérée avec la méthode getMetaData() de l'objet RecordSet.
Cette méthode renvoie la structure dans un objet de type ResultSetMetaData.
lrsmd = lrs.getMetaData();
1.4.5.3 Récupération des éléments de la structure
On récupère le nombre de colonnes avec la méthode getColumnCount() de l'objet
ResultSetMetaData.
liCols = lrsmd.getColumnCount();
On récupère le nom de chaque colonne avec la méthode getColumnName(position) de l'objet
ResultSetMetaData.
lsNomCol = lrsmd.getColumnName(i);
1.4.5.4 Récupération des valeurs
On récupère la valeur en transformant en String l'extraction de l'objet correspondant pour la
ligne en cours à la colonne en cours.
On utilise getObject() parce que l'on ne connaît pas le type de la colonne de la table.
loValeur = lrs.getObject(i);
lsLigne = lsLigne + loValeur.toString() + "-";
16 avril 2017 - Page 17
Java - JDBC
1.4.6 Alternative pour la récupération des valeurs
Pour affiner le script précédent le type de chaque colonne du curseur est testé. En fonction
du type de la colonne la fonction getXXX() appropriée est sollicitée.
1.4.6.1 Script
while(lrs.next())
{
lsLigne = "";
for(i=1; i<=liCols; i++)
{
switch(lrsmd.getColumnType(i))
{
case Types.VARCHAR:
lsLigne += lrs.getString(i) + "-";
break;
case Types.CHAR:
lsLigne += lrs.getString(i) + "-";
break;
case Types.INTEGER:
lsLigne += lrs.getInt(i) + "-";
break;
case Types.DOUBLE:
lsLigne += lrs.getLong(i) + "-";
break;
default:
lsLigne += "Inconnu-";
}
}
System.out.println(lsLigne);
}
1.4.6.2 Commentaires
Dans ce cas on utilise la méthode getColumnType(position) pour ensuite récupérer la valeur
avec une méthode de type getString() ou getInt(),…
16 avril 2017 - Page 18
Java - JDBC
1.5 LA METHODE EXECUTE()
1.5.1 Objectif
Utiliser la méthode execute() qui permet d'exécuter n'importe quel type d'ordre SQL; aussi
bien un Select qu'un Insert, qu'un Delete, qu'un Update.
1.5.2 Principes et démarche
On utilise la méthode Execute lorsque l'on ne connaît pas la nature de l'ordre SQL qui sera
passé en argument.
La méthode renvoie un booléen qui a la valeur :
True si l'ordre exécuté est un Select
False si l'ordre exécuté est un ordre de mise à jour.
Selon le résultat on ouvrira un curseur ou on fera un traitement de validation de mise à jour.
1.5.3 Méthodes utilisées
Méthodes
boolean = Statement.execute()
ResultSet = Statement.getResultSet()
Fonctionnalités
Exécute un ordre SQL et renvoie un booléen
Renvoie un curseur
1.5.4 Script
lstSql = lcConnexion.createStatement();
lbStatut = lstSql.execute(lsSql);
if(lbStatut)
{
System.out.println("Un Select a été exécuté");
gRs = lstSql.getResultSet();
while(gRs.next())
{
lsLigne = gRs.getString(1) + "-" + gRs.getString(2);
System.out.println(lsLigne);
}
}
else
{
System.out.println("Un ordre de mise à jour a été exécuté");
lcConnexion.commit();
}
16 avril 2017 - Page 19
Java - JDBC
1.5.5 Commentaires
La méthode Execute est une méthode de l'interface Statement. Elle a pour paramètre un
ordre SQL. Elle renvoie un booléen.
lbStatut = lstSql.execute(lsSql);
Si la méthode renvoie true on récupère le curseur grâce à la méthode getResultSet().
gRs = lstSql.getResultSet();
et on parcourt le curseur comme n'importe quel curseur.
Si la méthode renvoie false on valide définitivement.
16 avril 2017 - Page 20
Java - JDBC
1.6
1.6.1
SQL DYNAMIQUE (PREPARE)
Principe et démarche
A côté de l'interface Statement il existe l'interface PreparedStatement qui permet
d'exécuter un ordre SQL paramétré (aussi bien un SELECT qu'un ordre de MAJ). Ceci est
indiqué dans deux cas : la requête est plusieurs fois exécutée, la requête envoie des
caractères à échapper (', …).
1.6.2
Exemples
String lsSql = "SELECT * FROM villes WHERE cp = ?";
PreparedStatement pst = cn.prepareStatement(lsSql);
// --- Valorisation du ou des paramètre(s)
pst.setString(1, "75011"); // Les valeurs pourraient être saisies au clavier
// --- Exécution de la requête
ResultSet rs = pst.executeQuery();
rs.next();
System.out.println(rs.getString("cp") + "-" + rs.getString("nom_ville"));
String lsSql = "SELECT * FROM villes WHERE cp LIKE ?";
PreparedStatement pst = cn.prepareStatement(lsSql);
pst.setString(1, "75%");
ResultSet rs = pst.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("cp") + "-" + rs.getString("nom_ville"));
}
String lsSql = "INSERT INTO villes(cp, nom_ville) VALUES(?,?)";
PreparedStatement pst = cn.prepareStatement(lsSql);
pst.setString(1, "75999");
pst.setString(2, "Paris 999");
pst.executeUpdate();
16 avril 2017 - Page 21
Java - JDBC
1.7
EXECUTER UNE PROCEDURE STOCKEE AVEC DES PARAMETRES IN
1.7.1 Objectif
Exécuter une procédure stockée.
Utiliser l'interface PreparedStatement.
Remarques
PreparedStatement pour les procédures stockées IN avec Connection.prepareStatement.
CallableStatement pour les procédures stockées IN/OUT avec Connection.prepareCall.
1.7.2 Principes et démarche
Créer une procédure stockée si besoin est.
Créer un PreparedStatement et préparer l'appel.
Appeler la procédure stockée.
1.7.3 Méthodes utilisées
Méthodes
PreparedStatement =
Connection.prepareStatement(String lsCall)
Fonctionnalités
Crée un PreparedStatement
PreparedStatement.executeQuery()
PreparedStatement.executeUpdate()
PreparedStatement.execute()
Exécute l'ordre d'appel (SQL SELECT)
Exécute l'ordre d'appel (SQL Maj)
Exécute l'ordre d'appel (SQL SELECT ou Maj)
16 avril 2017 - Page 22
Java - JDBC
1.7.4 La procédure stockée Oracle
Cette procédure stockée permet d'insérer un enregistrement dans la table Villes.
CREATE OR REPLACE PROCEDURE VILLESINSERT(asCp IN villes.cp%type , asNomVille IN
villes.nom_ville%type)
IS
BEGIN
INSERT INTO villes(cp, nom) VALUES(asCp, asNomVille);
COMMIT;
END;
/
-- TEST sous SQL+
EXEC villesinsert('14000','Caen');
1.7.5 La procédure stockée MySQL
Cette procédure stockée permet d'insérer un enregistrement dans la table Villes.
DELIMITER $$
DROP PROCEDURE IF EXISTS villesInsert $$
CREATE DEFINER=root@localhost PROCEDURE villesInsert(IN as_cp CHAR(5), IN as_ville
CHAR(50), IN as_id_pays CHAR(3))
BEGIN
INSERT INTO VILLES(cp, nom_ville, id_pays) VALUES(as_cp, as_ville, as_id_pays);
COMMIT;
END $$
DELIMITER ;
-- Test sous MySQL Query Browser
CALL villesInsert('14000','Caen','033');
16 avril 2017 - Page 23
Java - JDBC
1.7.6 Script pour MySQL
package packageSQL;
import java.sql.*;
public class VillesAjoutViaPs
{
public static void main(String[] args)
{
// --- Déclarations
Connection lcConnexion
= null;
PreparedStatement lpstSql = null;
String lsPilote
= "";
String lsConnexion
= "";
String lsUt
= "root";
String lsMdp
= "";
String lsCp
= "14000";
String lsNomVille
= "Caen";
String lsIdPays
= "033";
String lsSQL
= "{CALL villesInsert(?,?,?)}";;
// --- Pour une connexion MySQL native
lsPilote
= "org.gjt.mm.mysql.Driver";
lsConnexion = "jdbc:mysql://localhost/cours";
try
{
Class.forName(lsPilote);
lcConnexion = DriverManager.getConnection(lsConnexion,lsUt,lsMdp);
lpstSql = lcConnexion.prepareStatement(lsSQL);
lpstSql.setString(1,lsCp);
lpstSql.setString(2,lsNomVille);
lpstSql.setString(3,lsIdPays);
int liR = lpstSql.executeUpdate();
System.out.println("Un Insert a été exécuté : " + liR);
lpstSql.close();
lcConnexion.close();
lpstSql
= null;
lcConnexion = null;
}
catch(ClassNotFoundException err) { System.err.println(err.getMessage()); }
catch(SQLException err)
{ System.err.println(err.getMessage()); }
}
}
Notes
St.execute() pour un ordre SQL quelconque (Renvoie un booléen).
St.executeQuery() pour un ordre SELECT (Renvoie un Resultset).
St.executeUpdate() pour un ordre de mise à jour (Renvoie un int).
1.7.7 Script pour Oracle
// --- IDEM sauf la connexion
16 avril 2017 - Page 24
Java - JDBC
1.7.8 Commentaires
1.7.8.1 La déclaration de l'objet PreparedStatement
En premier lieu il faut déclarer une variable de type interface PreparedStatement.
PreparedStatement lpstSql;
1.7.8.2 La préparation de l'ordre d'appel
L'appel de la procédure stockée se fera avec un CALL nomDeLaProcédure;
La syntaxe est la suivante :
"{CALL nomDeLaProcédure(paramètre 1[, paramètre 2]) }"
lsSql = "{CALL villesinsert(?,?)}";
Il faut créer un objet de type PreparedStatement avec la méthode prepareStatement() de
l'objet Connection en lui passant comme paramètre la chaîne de caractères précédente.
lpstSql = lcConnexion.prepareStatement(lsSql);
1.7.8.3 L'appel de la procédure stockée
Il faut ensuite demander l'exécution de l'ordre qui appelle la procédure stockée avec la
méthode executeUpdate() de l'objet CallableStatement.
lpstSql.executeUpdate();
16 avril 2017 - Page 25
Java - JDBC
1.8
EXECUTER UNE PROCEDURE STOCKEE QUI RENVOIE UN SELECT
La procédure MySQL
DELIMITER $$
DROP PROCEDURE IF EXISTS villesSelect $$
CREATE PROCEDURE villesSelect()
BEGIN
SELECT * FROM villes;
END $$
DELIMITER;
-- Test sous MySQL Query Browser
CALL villesSelect();
La procédure Oracle
16 avril 2017 - Page 26
Java - JDBC
Le code java
package packageSQL;
import java.sql.*;
import packageBD.BD;
// ----------------------public class PsSelectMySQL
// ----------------------{
public static void main(String[] args)
{
BD bd = new BD();
bd.seConnecter("cours", "root", "", "mysql");
try
{
Connection cn = bd.getConnexion();
PreparedStatement lpst = cn.prepareStatement("{CALL villesSelect()}");
ResultSet lrs = lpst.executeQuery();
while(lrs.next())
{
System.out.println(lrs.getString(1));
}
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
bd.seDeconnecter();
}
}
16 avril 2017 - Page 27
Java - JDBC
1.9 EXECUTER UNE PROCEDURE STOCKEE AVEC DES PARAMETRES OUT
1.9.1 Objectif
Exécuter une procédure stockée avec des paramètres OUT.
Voir les particularités de l'utilisation des paramètres OUT.
Utiliser l'interface CallableStatement.
1.9.2
Principes et démarche
Créer la procédure stockée Oracle ou autre.
Déclarer un CallableStatement et préparer l'appel avec un PrepareCall().
Déclarer les paramètres et affecter des valeurs aux paramètres IN.
Appeler la procédure stockée avec un execute().
Récupérer les valeurs des paramètres OUT.
1.9.3
Méthodes utilisées
Méthodes
Connection.prepareCall()
CallableStatement.registerOutParameter(position, type)
CallableStatement.execute()
CallableStatement.setString(rang, valeur)
CallableStatement.getString(rang)
16 avril 2017 - Page 28
Fonctionnalités
Crée un CallableStatement
Précise la position et le type du retour
des paramètres
Exécute l'ordre d'appel
Affecte une valeur à un paramètre
Récupère une valeur d'un paramètre
Java - JDBC
1.9.4 La procédure stockée Oracle
Cette procédure stockée Oracle permet d'insérer une ville dans la table [villes] et de
récupérer le code d'erreur et le message d'erreur du SGBD.
CREATE OR REPLACE PROCEDURE VILLESINSERTOUT(
asCp
in villes.cp%type,
asNomVille
in villes.nom_ville%type,
aiCodeErreur out NUMBER,
asTexteErreur out VARCHAR2)
IS
vide EXCEPTION;
BEGIN
IF asCp IS NULL THEN RAISE Vide; END IF;
IF asNomVille IS NULL THEN RAISE Vide; END IF;
INSERT INTO villes(cp,nom_ville) VALUES(asCp,asNomVille);
COMMIT;
aiCodeErreur := 0;
asTexteErreur := 'OK';
EXCEPTION
WHEN Vide THEN
aiCodeErreur := -1;
asTexteErreur := 'vide';
WHEN OTHERS THEN
aiCodeErreur := SQLCODE;
asTexteErreur := SUBSTR(SQLERRM,1,100);
END;
/
-- Test sous SQL*PLUS
SET SERVER OUTPUT ON
DECLARE
liCode NUMBER(10);
lsMsg VARCHAR2(100);
BEGIN
villesinsertout('14000','Caen', liCode, lsMsg);
dbms_output.put_line('Err : ' || liCode || ' msg : ' || lsMsg);
END;
/
16 avril 2017 - Page 29
Java - JDBC
1.9.5 Script
package packageSQL;
import java.sql.*;
// ---------------------public class ProcStockOut
// ---------------------{
public static void main(String[] args)
{
// --- Déclarations
Connection lcConnexion
= null;
CallableStatement lcstSql = null;
String lsConnexion;
String lsSql = null;
String lsUt = "p"; String lsMdp = "b";
String lsMsgErreur = null;
double ldCodeErreur;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
lsConnexion = "jdbc:oracle:thin:@140.0.0.68:1521:orac";
lcConnexion = DriverManager.getConnection(lsConnexion,lsUt,lsMdp);
// --- Les paramètres out doivent être en premier autrement tous en ?
pour pouvoir de toutes façons dynamiser l'appel
lsSql = "{CALL villesinsertout(?,?,?,?)}";
lcstSql = lcConnexion.prepareCall(lsSql);
lcstSql.setString(1,"14000");
lcstSql.setString(2,"Caen");
lcstSql.registerOutParameter(3,Types.NUMERIC);
lcstSql.registerOutParameter(4,Types.VARCHAR);
lcstSql.execute();
ldCodeErreur = lcstSql.getLong(3);
lsMsgErreur = lcstSql.getString(4);
System.out.println("Message d'erreur BD : " + lsMsgErreur);
System.out.println("Code d'erreur BD : " + ldCodeErreur);
lcstSql.close();
lcConnexion.close();
lcstSql
= null;
lcConnexion = null;
}
catch(ClassNotFoundException err)
{
System.err.println(err.getMessage());
}
catch(SQLException err)
{
System.err.println(err.getMessage());
}
}
}
16 avril 2017 - Page 30
Java - JDBC
1.9.6
Commentaires
1.9.6.1 La préparation de l'appel
Pour dynamiser l'appel vous faites un appel paramétré avec autant de ? qu'il y a de
paramètres.
lsSql = "{CALL villesinsert2(?,?,?,?)}";
Vous préparez l'appel :
lcstSql = lcConnexion.prepareCall(lsSql);
Vous affectez des valeurs aux paramètres IN avec la méthode setString(rang, valeur) ou
setXXX(rang, valeur) applicable à l'objet CallableStatement.
Vous déclarez les paramètres OUT au moyen de la méthode registerOutParameter(rang,
type).
lcstSql.setString(1,"14000");
lcstSql.registerOutParameter(3,Types.NUMERIC);
1.9.6.2 L'appel via la méthode execute()
lcstSql.execute();
1.9.6.3 La récupération des résultats
Vous récupérez les valeurs des paramètres OUT avec une méthode getXXX() appliquée à
l'objet CallableStatement.
ldCodeErreur = lcstSql.getLong(3);
lsMsgErreur = lcstSql.getString(4);
16 avril 2017 - Page 31
Java - JDBC
1.10 EXECUTER UNE FONCTION STOCKEE
1.10.1
Objectif
Utiliser une fonction stockée à partir d'une application JAVA.
1.10.2
Principes et démarche
Le principe est le même que pour les procédures stockées mis à part que le résultat (Out)
est le premier "paramètre".
1.10.3
Méthodes utilisées
Méthodes
Connection.prepareCall()
CallableStatement.registerOutParameter(position, type)
CallableStatement.execute()
CallableStatement.setString(rang, valeur)
CallableStatement.getString(rang)
16 avril 2017 - Page 32
Fonctionnalités
Crée un CallableStatement
Précise la position et le type du retour
Exécute l'ordre d'appel
Affecte une valeur à un paramètre
Récupère une valeur d'un paramètre
Java - JDBC
1.10.4
La fonction stockée Oracle
Cette fonction stockée Oracle insère un enregistrement dans la table Villes. Elle possède
deux paramètres en entrée et le retour (de type Varchar2) qui renvoie Ok ou le message
d'erreur.
CREATE OR REPLACE FUNCTION VILLESINSERTFCT(asCp IN villes.cp%type , asNomVille IN
villes.nom_ville%type) RETURN VARCHAR2
IS
lsMsg VARCHAR2(100);
BEGIN
INSERT INTO villes(cp, nom_ville) VALUES(asCp, asNomVille);
COMMIT;
RETURN 'OK';
EXCEPTION
WHEN dup_val_on_index THEN
lsMsg := SUBSTR(sqlerrm(sqlcode),1,100);
RETURN lsMsg;
WHEN OTHERS THEN
lsMsg := SUBSTR(sqlerrm(sqlcode),1,100);
RETURN lsMsg;
END;
/
-- TEST sous SQL
SET SERVEROUTPUT ON
DECLARE
lsMsg VARCHAR2(100);
BEGIN
lsMsg := VILLESINSERTFCT('75021','Paris 21');
dbms_output.put_line('Résultat : ' || lsMsg);
END;
/
1.10.5
La fonction stockée MySQL
DELIMITER $$
DROP FUNCTION IF EXISTS villesInsertFct $$
CREATE FUNCTION villesInsertFct(asCp VARCHAR(5), asNomVille VARCHAR(50), asIdPays
VARCHAR(3)) RETURNS VARCHAR(255)
BEGIN
INSERT INTO villes(cp, nom_ville, id_pays) VALUES(asCp, asNomVille, asIdPays);
RETURN 'OK';
END $$
DELIMITER ;
-- Test sous MySQL Query Browser
SELECT villesInsertFct('71000','Tournus','033');
16 avril 2017 - Page 33
Java - JDBC
1.10.6
Script
String lsSql = "{? = CALL villesinsertfct(?,?)}";
CallableStatement lcstSql = null;
lcstSql = lcConnexion.prepareCall(lsSql);
lcstSql.registerOutParameter(1,Types.VARCHAR);
lcstSql.execute();
System.out.println(lcstSql.getString(1));
16 avril 2017 - Page 34
Java - JDBC
1.10.7
Commentaires
1.10.7.1
Création de la chaîne d'appel
La chaîne d'appel a le format suivant :
"{? = call nomDeLaFonction( paramètres) }"
lsSql = "{? = CALL villesinsertfct('14000','Caen')}";
1.10.7.2
Création du callableStatement
Utilisation de la méthode prepareCall() de l'objet Connection en passant comme paramètre la
chaîne d'appel.
lcstSql = lcConnexion.prepareCall(lsSql);
1.10.7.3
Précision du type de retour
Il faut préciser de quel type est le retour de la fonction (String, int,…) avec la méthode
registerOutParameter() de l'objet CallableStatement qui possède deux paramètres : la
position et le type.
La position pour le retour d'une fonction est toujours 1.
Le type correspond aux types SQL définis dans l'API java.sql.
lcstSql.registerOutParameter(1,Types.VARCHAR);
1.10.7.4
Appel de la fonction
Comme pour une procédure stockée on invoque la méthode execute() de l'objet
CallableStatement.
lcstSql.execute();
1.10.7.5
Récupération du résultat
Selon le type du résultat on utilise la méthode getXXX() adéquate.
lsResultat = lcstSql.getString(1);
16 avril 2017 - Page 35
Java - JDBC
1.11 ACCES A LA METABASE
1.11.1
Objectif et démarche
Accéder aux informations de la Metabase : pilote, url, bds, tables, colonnes, clés primaires,
…
Utiliser les méthodes de la classe DatabaseMetadata.
1.11.2
Quelques méthodes
Méthode
cn.getMetaData()
Fonctionnalité
Renvoie un ResultsetMetadata
md.getDriverName()
md.getUserName()
md.getURL()
Renvoie le nom du pilote
Renvoie le nom de l'utilisateur connecté
md.getCatalogs()
md.getTables()
md.getColumns()
Renvoie la liste des BD d'un serveur
Renvoie la liste des tables d'une BD
Renvoie la liste des colonnes d'une table
Renvoie l'URL de la base (jdbc:odbc:dsn_…)
Il en existe une trentaine d'autres.
Exemple de résultat attendu avec MySQL :
Editeur de la base : MySQL
Driver
: JDBC-ODBC Bridge (myodbc3.dll)
URL JDBC : jdbc:odbc:dsn_mysql_cours
Ut
: root
BDs :
information_schema,___ajax_bd,cours,cqrsa,elections,exos_sql,forum,immobilier,libra
irie,locations_films,mysql,nouvelle,pariscope,portail_infos,test,unilog,
Tables de librairie : clients(TABLE),commandes(TABLE),cours_livres(TABLE),
Colonnes de Villes : cp(varchar),nom_ville(varchar),photo(varchar),site(varchar),
Notes
Avec Oracle getCatalogs() ne renvoie rien donc getTables() et getColumns() non plus.
Donc il faut utiliser des curseurs et des SELECT sur la metabase (SELECT * FROM cat WHERE …, SELECT * FROM user_tables
WHERE …).
16 avril 2017 - Page 36
Java - JDBC
1.11.3
Script
import java.sql.*;
public class MetaData
{
public static void main(String[] args)
{
Connection
lcn
= null;
DatabaseMetaData ldbmd
= null;
ResultSet
lrsCatalogues = null;
ResultSet
lrsTables
= null;
ResultSet
lrsCols
= null;
String
lsInfos
= "";
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
lcn = DriverManager.getConnection("jdbc:odbc:dsn","ut","mdp");
// --- Accès à la métabase, au dictionnaire
ldbmd = lcn.getMetaData();
lsInfos += "\nEditeur de la base : " + ldbmd.getDatabaseProductName();
lsInfos += "\nDriver
: " + ldbmd.getDriverName();
lsInfos += "\nURL JDBC : " + ldbmd.getURL();
lsInfos += "\nUt
: " + ldbmd.getUserName();
lsInfos += "\nBDs : ";
lrsCatalogues = ldbmd.getCatalogs();
while(lrsCatalogues.next()) lsInfos += lrsCatalogues.getString(1) +
",";
lsInfos += "\nTables : ";
String tTypes[] = null;
// --getTables("Catalogue","SchemaPattern","TablePattern",tableTypes)
// --- SchemaPattern : ???
// --- Exemple : les tables de la bd dont le nom commence par C tous
types confondus.
// --- TableType : Tables, Views, Synonyms, …
// --- Le RS a cette structure (cat, tableSchema, tableName,
tableType, ...)
lrsTables = ldbmd.getTables("librairie", "", "C",tTypes);
while(lrsTables.next()) lsInfos += lrsTables.getString(3) + "(" +
lrsTables.getString(4) + "),";
lsInfos += "\nColonnes de Villes : ";
// --getColumns("Catalogue","SchemaPattern","TablePattern","ColumnPattern")
// --- SchemaPattern : ???
// --- Exemple : les colonnes de la table villes de la bd librairie.
// --- TableType : Tables, Views, Synonyms,..
// --- Le RS a cette structure (cat, tableSchema, tableName,
columnName, ...)
lrsCols = ldbmd.getColumns("librairie", "", "Villes", "");
while(lrsCols.next()) lsInfos += lrsCols.getString(4) + "(" +
lrsCols.getString(6) + "),";
System.out.println(lsInfos);
lcn.close();
}
catch(Exception erreur) { System.err.println(erreur); }
}
}
16 avril 2017 - Page 37
Java - JDBC
1.12 UN CURSEUR "SCROLLABLE"
Pour scroller un curseur il faut, lors de sa création, le définir comme "scrollable".
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Cf les annexes pour les détails du paramétrage.
Il faut aussi que le pilote le permette.
Les méthodes previous(), first() et last() sont alors applicables en plus de la méthode next().
import java.sql.*;
public class CurseurScrollable
{
public static void main(String[] args)
{
// --- Pour une connexion avec un pilote natif oracle
String lsPilote
= "oracle.jdbc.driver.OracleDriver";
String lsConnexion = "jdbc:oracle:thin:@localhost:1521:xe";
String lsSelect
= "SELECT * FROM villes";
try
{
Class.forName(lsPilote);
Connection lcConnexion =
DriverManager.getConnection(lsConnexion,"p","b");
Statement lstSql =
lcConnexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet lrs
= lstSql.executeQuery(lsSelect);
while(lrs.next())
{
System.out.println(lrs.getString(1) + "-" + lrs.getString(2));
}
//lrs.previous();
lrs.first();
System.out.println(lrs.getString(1) + "-" + lrs.getString(2));
lrs.close();
lstSql.close();
lcConnexion.close();
}
catch(ClassNotFoundException err) { System.err.println(err.getMessage()); }
catch(SQLException err)
{ System.err.println(err.getMessage()); }
}
}
16 avril 2017 - Page 38
Java - JDBC
1.13 UN CURSEUR "UPDATABLE"
Un curseur "updatable" est un curseur au travers duquel il est possible de faire une mise à
jour.
Encore faut-il que le pilote le permette.
Apparemment via le pilote natif Oracle c'est impossible, mais via ODBC une table Oracle est
modifiable via un curseur.
Les méthodes de curseur updateString() et updateRow() sont alors utilisables.
Mais avec Oracle les résultats sont parfois inattendus sur les varchar2 !!!
import java.sql.*;
public class CurseurUpdatable
{
public static void main(String[] args)
{
// --- Pour une connexion avec un pilote natif oracle
//String lsPilote
= "oracle.jdbc.driver.OracleDriver";
//String lsConnexion = "jdbc:oracle:thin:@localhost:1521:xe";
// --- Connexion ODBC
String lsPilote
= "sun.jdbc.odbc.JdbcOdbcDriver";
String lsConnexion = "jdbc:odbc:dsn_oracle_xe";
String lsSelect
= "SELECT * FROM villes";
try
{
Class.forName(lsPilote);
Connection lcConnexion = DriverManager.getConnection(lsConnexion,"p","b");
Statement lstSql =
lcConnexion.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet lrs = lstSql.executeQuery(lsSelect);
while(lrs.next())
{
System.out.println(lrs.getString(1) + "-" + lrs.getString(2));
if(lrs.getString(1).equals("75011"))
{
lrs.updateString(2,"Paris XI");
lrs.updateRow();
}
}
lrs.close();
lstSql.close();
lcConnexion.close();
}
catch(ClassNotFoundException err) { System.err.println(err.getMessage()); }
catch(SQLException err)
{ System.err.println(err.getMessage()); }
}
}
16 avril 2017 - Page 39
Java - JDBC
CHAPITRE 2 - ANNEXES
16 avril 2017 - Page 40
Java - JDBC
2.1
UNE SEULE CLASSE POUR LA GESTION BD
2.1.1 Objectif
Créer une classe de "services" pour manipuler n'importe quelle table d'une BD.
Les méthodes plus en détail
Méthode
StringBuilder seConnecter(String asBd, String asUt,
String asMdp)
StringBuilder seConnecter(String asBd, String asUt,
String asMdp, String asPilote)
boolean seDeconnecter()
Connection getConnexion()
ResultSet getCurseur(String asSelect)
ResultSet getCurseur(String asSelect, int aiScroll, int
aiLecture)
StringBuilder getCurseurCSV(String asSelect)
Vector getCurseur1D(String asSelect)
TreeMap getCurseur2D(String asSelect)
int supprimer(String asTable, String asColonne, String
asValeur)
int inserer(String asTable, String[] asColonnes, String[]
asValeurs)
int inserer(String asTable, String asColonnes, String
asValeurs)
String executerPs(String asProcedure, String asValeurs)
String valider()
String annuler()
…
16 avril 2017 - Page 41
Description
Java - JDBC
2.1.2
Exemple
75012;Paris 12
75011;Paris 11
24200;Sarlat
14000;Caen
package packageBD;
// ---------------public class BdTest
// ---------------{
public static void main(String[] args)
{
BD bd = new BD();
StringBuilder lsb = bd.seConnecter();
if(lsb.toString().equals("OK"))
{
System.out.println(bd.getCurseurCSV("SELECT * FROM villes"));
System.out.println(bd.seDeconnecter());
}
else System.out.println(lsb.toString());
}
}
2.1.3 Code de la classe
Code dans le fichier BD.java.
16 avril 2017 - Page 42
Java - JDBC
2.2 QUELQUES METHODES POUR LA MANIPULATION DES BD
2.2.1 Class.forName
Objectif
La méthode forName de la classe Class permet de définir le pilote utilisé. Que le pilote soit
natif ou qu'il utilise ODBC.
Syntaxe
Class.forName("Nom_du_pilote")
Exemples
Class.forName("oracle.jdbc.driver.OracleDriver"); // --- Pilote natif Oracle
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // --- Pilote JDBC-ODBC
Class.forName("org.gjt.mm.mysql.Driver"); // --- Pilote natif MySQL
2.2.2 DriverManager.getConnection
Objectif
Se connecter.
La méthode getConnection() de la classe DriverManager permet de tenter de se connecter.
Cette méthode renvoie un booléen. L'adresse de la BD est composée comme une URL.
Syntaxe
Connection = DriverManager.getConnection(urlBD, utilisateur, motDePasse)
Exemples
cn = DriverManager.getConnection("jdbc:oracle:@140.0.0.68:1521:orac","p","b");
cn = DriverManager.getConnection("jdbc:odbc:dsn_oracle","p","b");
cn = DriverManager.getConnection("jdbc:mysql://localhost/cours","root","");
16 avril 2017 - Page 43
Java - JDBC
2.2.3 Connection.close
Objectif
La méthode close de la classe Connection permet de fermer une connexion.
Syntaxe
Connection.close()
Exemples
cn.close();
2.2.4 createStatement
Objectif
La méthode createStatement de la classe Connection permet de définir un objet pour
l'exécution de commandes SQL. Cette méthode renvoie un objet de type statement.
Syntaxe
statement = Connection.createStatement([Sens, Lecture/Ecriture])
Sens :
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE
Lecture/Ecriture :
ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE
Exemples
gInstruction = cn.createStatement();
gInstruction = cn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
gInstruction = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
16 avril 2017 - Page 44
Java - JDBC
2.2.5 executeQuery
Objectif
La méthode executeQuery() de la classe Statement permet d'exécuter une requête de type
SELECT. Cette méthode renvoie un objet de type RecordSet.
Syntaxe
RecordSet = Statement.executeQuery("OrdreSQLSelect")
Exemple
gRs = gInstruction.executeQuery("SELECT * FROM villes");
Remarques
La méthode executeUpdate("ordreSQL") exécute un ordre SQL de mise à jour. Elle renvoie
un int.
La méthode execute("ordreSQL") exécute un ordre SQL. Elle renvoie un booléen (true si c'est
un SELECT et false si c'est un ordre CUD).
16 avril 2017 - Page 45
Java - JDBC
2.2.6
Tableaux des Classes et Interfaces
2.2.6.1 La classe java.sql.DriverManager
Méthodes
Connection = DriverManager.getConnection("url")
Connection = DriverManager.getConnection("url", "login", "pwd")
Fonctionnalités
Permet de se connecter
idem
2.2.6.2 L'interface java.sql.Connection
Méthodes
Connection.close()
Booléen = conection.isClosed()
Statement = connection.createStatement()
Statement = connection.createStatement(type_result_set, acces_concurrent)
PreparedStatement = connection.preparedStatement("sql")
PreparedStatement = connection.preparedStatement("sql", type_result_set,
acces_concurrent)
CallableStatement = connection.prepareCall("sql")
CallableStatement = connection.prepareCall("sql", type_result_set,
acces_concurrent)
DatabaseMetatData = connection.getMetaData()
Connection.setAutoCommit(booléen)
Booléen = connection.getAutoCommit()
Connection.commit()
Connection.rollback()
Connection.setTransactionIsolation(niveau)
Int = Connection.getTransactionIsolation()
Connection.setReadOnly(booléen)
Booléen = connection.isReadOnly()
SQLWarnings = Connection.getWarnings()
Connection.clearWarnings()
Connection.setCatalog("catalogue")
String = Connection.getCatalog()
Map = connection.getTypeMap()
Connection.setTypeMap(map)
(*)
resultSetType :
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency :
ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE
16 avril 2017 - Page 46
Fonctionnalités
Permet de se déconnecter
Tester l'état de la connexion
Créer une instruction
Idem (*)
Prépare une requête compilée
Idem
Prépare une procédure stockée
idem
Récupérer des informations sur la
métabase
Modifier l'état de l'autocommit
Récupèrer l'état de l'autocommit
Valider la transaction
Invalider la transaction
Modifier le niveau d'isolation
Récupérer le niveau d'isolation
Modifier l'état RW de la bd
Récupérer l'état RW
Récupère les avertissements
Supprime les avertissements
Définir un catalogue
Récupérer un catalogue
Permettre de récupérer les mappages
de types SQL-JAVA de la session
Permettre de créer les mappages de
types SQL-JAVA pendant une session
Java - JDBC
2.2.6.3 L'interface java.sql.Statement
Objet pour exécuter un ordre SQL statique et récupérer un curseur.
Méthodes
ResultSet executeQuery("SELECT SQL")
Int executeUpdate("Ordre SQL")
Booléen execute("OrdreSQL")
Connection getConnection()
ResultSet getResultSet()
Void close()
Fonctionnalités
Idem pour un ordre Select
Idem pour un ordre de MAJ
Un ordre du LMD ou du LDD
Récupère une connexion
Récupère un curseur
Ferme l'instruction et réinitialise
2.2.6.4 L'interface java.sql.PreparedStatement
Objet qui représente un ordre SQL précompilé ou une procédure stockée IN (Hérite de
Statement).
Méthodes
Void setXXX(index du paramètre, valeur)
XXX getXXX(index du paramètre)
Boolean execute("Ordre SQL")
ResultSet executeQuery("Ordre SQL")
Int executeUpdate("Ordre SQL")
Fonctionnalités
Affecte une valeur
Récupère une valeur
Exécute un ordre SQL
Exécute un ordre Select
Exécute un ordre de MAJ
Cf l'objet connection et la méthode preparedStatement
2.2.6.5 L'interface java.sql.CallableStatement
Interface pour exécuter une procédure stockée SQL OUT (Hérite de PreparedStatement).
Méthodes
Void setXXX(nom_du_paramètre, valeur)
XXX getXXX(index du paramètre)
Fonctionnalités
Affecte une valeur
Récupère une valeur
Cf l'objet connection et la méthode prepareCall
16 avril 2017 - Page 47
Java - JDBC
2.2.6.6 L'interface java.sql.ResultSet
Un curseur
Méthodes
Boolean
Boolean
Boolean
Boolean
first()
previous()
next()
last()
Déplace
Déplace
Déplace
Déplace
le
le
le
le
Boolean
Boolean
Boolean
Boolean
isFirst()
isLast()
isBeforeFirst()
isAfterLast()
Renvoie
Renvoie
Renvoie
Renvoie
vrai
vrai
vrai
vrai
Void
Void
Void
Void
insertRow()
moveToInsertRow()
deleteRow()
updateRow()
pointeur
pointeur
pointeur
pointeur
si
si
si
si
le
le
le
le
Fonctionnalités
sur la première ligne
sur la ligne précédente
sur la ligne suivante
sur la dernière ligne
pointeur
pointeur
pointeur
pointeur
est
est
est
est
au début
à la fin
avant le début (BOF)
après la fin (EOF)
Insère une ligne
Déplace le pointeur sur la nouvelle ligne
Supprime une ligne
Met à jour une ligne
String getString(indexColonne)
String getString("nomColonne")
XXX = getXXX(indexColonne ou
"nomColonne")
Récupère la valeur d'une colonne
Récupère la valeur d'une colonne
Récupère la valeur d'une colonne (String,
numérique,…)
Void updateString("Champ", valeur)
Void updateXXX("Champ", valeur)
Met à jour la colonne de l'enregistrement courant
Idem pour les autres types
Void close()
Boolean absolute(numero de ligne)
Int getRow()
Void refreshRow()
Statement = getStatement()
ResultSetMetaData = getMetaData()
Ferme le curseur
Déplace le pointeur sur la ligne
Renvoie le numéro de la ligne courante
Actualise la ligne
Renvoie le statement
Renvoie la structure du curseur
16 avril 2017 - Page 48
Java - JDBC
2.3
2.3.1
TRANSFERTS BD-FICHIER
Bd2Fichier
Objectif : transfert table BD vers fichier CSV.
package packageSQL;
import java.sql.*;
import java.io.*;
import packageBD.BD;
// --------------------public class BdToFichier
// --------------------{
// ----------------------------------public static void main(String[] args)
// ----------------------------------{
// --- Déclarations
Connection
lcConnexion = null;
String
lsTable
= "genres";
String
lsSelect
= "";
StringBuilder lsbSortie
= new StringBuilder("");
FileWriter lfwFichier;
try
{
BD bd = new BD();
bd.seConnecter();
lcConnexion = bd.getConnexion();
lfwFichier = new FileWriter(lsTable + ".csv");
lsSelect = "SELECT * FROM " + lsTable;
lsbSortie = bd.getCurseurCSV(lsSelect);
lfwFichier.write(lsbSortie.toString());
lfwFichier.flush();
lfwFichier.close();
lcConnexion.close();
lcConnexion = null;
}
catch(IOException err)
{
System.err.println("Erreur IO : " + err.getMessage());
}
catch (SQLException err)
{
System.err.println("\nErreur SQL : " + err.getMessage());
}
System.out.println("Création du fichier CSV réussie");
}
}
16 avril 2017 - Page 49
Java - JDBC
La méthode getCurseurCSV() de la classe BD.
// ------------------------------public StringBuilder getCurseurCSV(String asSelect)
// ------------------------------{
StringBuilder lsbResultat = new StringBuilder("");
ResultSet lrs
= null;
ResultSetMetaData lrsmd; // --- Objet structure de curseur
// --- Récupération du contenu du curseur
lrs = this.getCurseur(asSelect);
try
{
// --- La structure du curseur
lrsmd = lrs.getMetaData();
// --- Les en-têtes
for(int i=1; i<=lrsmd.getColumnCount(); i++)
{
lsbResultat.append(lrsmd.getColumnName(i) + ";");
}
lsbResultat.deleteCharAt(lsbResultat.length()-1);
lsbResultat.append("\r\n");
while(lrs.next())
{
for(int i=1; i<=lrsmd.getColumnCount(); i++)
{
lsbResultat.append(lrs.getString(i) + ";");
}
lsbResultat.deleteCharAt(lsbResultat.length()-1);
lsbResultat.append("\r\n");
}
}
catch(SQLException err)
{
lsbResultat.append(err.getMessage());
}
return lsbResultat;
}
16 avril 2017 - Page 50
Java - JDBC
2.3.2
Fichier2Bd
Objectif : transférer un fichier dans une table d'une BD.
package packageSQL;
import java.sql.*;
import java.io.*;
import packageBD.BD;
// --------------------public class FichierToBD
// --------------------{
// ----------------------------------public static void main(String[] args)
// ----------------------------------{
// --- Déclarations
BD bd = new BD();
Connection lcConnexion;
String lsColonnes = "";
String lsLigne
= "";
FileReader lfrFichier;
BufferedReader lbrBuffer;
try
{
bd.seConnecter();
lcConnexion = bd.getConnexion();
// --- Le constructor(argument : nom du fichier)
lfrFichier = new FileReader("genres.csv");
lbrBuffer = new BufferedReader(lfrFichier);
lsColonnes = lbrBuffer.readLine();
lsColonnes = lsColonnes.replaceAll(";", ",");
while((lsLigne = lbrBuffer.readLine()) != null)
{
lsLigne = lsLigne.replaceAll(";", "','");
lsLigne = "'" + lsLigne + "'";
System.out.println(bd.insererSimple("genres", lsColonnes, lsLigne));
}
// --- La méthode close pour fermer le canal
lfrFichier.close();
lcConnexion.close();
lcConnexion = null;
}
catch(FileNotFoundException err)
{
System.err.println(err.getMessage());
}
catch(IOException err)
{
System.err.println(err.getMessage());
}
catch (SQLException err)
{
System.err.println("\nErreur SQL");
System.err.println(err);
}
System.out.println("Transfert Fichier-BD terminé");
}
}
16 avril 2017 - Page 51
Java - JDBC
2.4
2.4.1
LES PILOTES
ODBC (Open DataBase Connectivity)
Il faut au préalable créer un DSN.
Le DSN
16 avril 2017 - Page 52
Java - JDBC
Montage du pilote
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connexion
lsConnexion
= "jdbc:odbc:dsn_oracle_xe";
Connection cn = DriverManager.getConnection(lsConnexion, lsUt, lsMdp);
16 avril 2017 - Page 53
Java - JDBC
2.4.2
JDBC-MySQL
Correspond à mysql-connector-java-5.1.12-bin.jar (Type 4 : natif, direct).
Bibliothèque à ajouter au projet.
Procédure
Téléchargez mysql-connector-java-5.1.12 chez MySQL AB.
Décompactez-le.
Copiez le fichier mysql-connector-java-5.1.12-bin.jar dans un dossier.
Ajoutez ce jar au projet.
Sous NetBeans
Montage du pilote
Class.forName("org.gjt.mm.mysql.Driver");
Connexion
Protocole:sous-protocole://hôte/bd
String lsConnexion = "jdbc:mysql://localhost/cours";
Connection cn = DriverManager.getConnection(lsConnexion, lsUt, lsMdp);
16 avril 2017 - Page 54
Java - JDBC
2.4.3
JDBC-Oracle (Java DataBase Connectivity)
Cette configuration ne nécessite pas, côté client, de fonctionnalité Middleware OracleNET.
D'abord télécharger le package Oracle pour le pilote : pour le JDK 1.4 et supérieur c'est le jar
suivant : ojdbc14.jar
Et ensuite l'inclure dans la liste des bibliothèques utilisées.
Sous NetBeans
Montage du pilote
Class.forName("oracle.jdbc.driver.OracleDriver");
Connexion
lsConnexion = "jdbc:oracle:thin:@localhost:1521:xe";
Connection cn = DriverManager.getConnection(lsConnexion, lsUt, lsMdp);
16 avril 2017 - Page 55
Java - JDBC
2.5
2.5.1
LE PATTERN DAO ET LES BEANS
Le modèle
Ce modèle (donc ces classes : Villes.java et VillesDao.java) sera réutilisé avec
SWING, les servlets et JSP …
Le modèle DAO et les beans sont utilisés dans le cadre du modèle MVC (Modèle, Vue,
Contrôleur).
Le modèle MVC a pour objectif de modéliser en couches. Les interfaces pour les vues, les
données pour le Modèle et le contrôleur pour la gestion des appels, des flux de l'application.
16 avril 2017 - Page 56
Java - JDBC
2.5.2
Le JavaBean
2.5.2.1 Définition
Un JavaBean est une classe qui représente une ressource stockée (table, fichier, …).
Il est composé de propriétés, de getters et de setters et souvent d'un constructeur vide.
C'est une classe sérialisable. C'est le standard de Sun.
Pour plus de détails cf le support java_jsp.doc.
2.5.2.2 Exemple : le JavaBean Villes
La structure de la table Villes(cp, nom_ville, site, photo, id_pays).
package packageBD;
import java.io.Serializable;
import java.util.ArrayList;
// --- Le JavaBean -----------------------public class Villes implements Serializable
// ---------------------------------------{
// --- Les attributs
private String cp;
private String nomVille;
private String site;
private String photo;
private String idPays;
VillesDAO dao = null;
// --- Le constructeur
public Villes()
{
this.dao = new VillesDAO();
}
// --- Les méthodes accédant au DAO
public String inserer()
{
return this.dao.inserer(this);
}
public String supprimer()
{
return this.dao.supprimer(this.cp);
}
public String modifier()
{
return this.dao.modifier(this);
}
public void selectUn(String cp)
{
Villes ville = this.dao.selectUn(cp);
this.cp
= ville.cp;
this.nomVille = ville.nomVille;
this.site
= ville.site;
this.photo
= ville.photo;
this.idPays
= ville.idPays;
}
16 avril 2017 - Page 57
Java - JDBC
public ArrayList selectTous()
{
return dao.selectTous();
}
// --- Les getters et setters
public String getCp() {
return cp;
}
public void setCp(String cp) {
this.cp = cp;
}
public String getIdPays() {
return idPays;
}
public void setIdPays(String idPays) {
this.idPays = idPays;
}
public String getNomVille() {
return nomVille;
}
public void setNomVille(String nomVille) {
this.nomVille = nomVille;
}
public String getPhoto() {
return photo;
}
public void setPhoto(String photo) {
this.photo = photo;
}
public String getSite() {
return site;
}
public void setSite(String site) {
this.site = site;
}
}
16 avril 2017 - Page 58
Java - JDBC
2.5.3
Le DAO
2.5.3.1 Définition
Le DAO (Data Access Objet) est un modèle d'accès aux données. Il implémente le CRUD.
En principe les classes DAO implémentent une interface IDAO.
Les méthodes sont les suivantes :
Insérer avec en argument un objet.
Supprimer avec en argument une clé.
Modifier avec en argument un objet.
SelectUn avec en argument une clé et en retour un objet.
SelectTous avec en retour une liste d'objets.
16 avril 2017 - Page 59
Java - JDBC
2.5.3.2 Exemple : le DAO VillesDAO
Le DAO
package packageBD;
import java.sql.*;
import java.util.*;
// ------------------public class VillesDAO
// ------------------{
private Connection cn;
// --------------public VillesDAO()
{
try
{
Class.forName("org.gjt.mm.mysql.Driver");
this.cn =
DriverManager.getConnection("jdbc:mysql://localhost/cours","root","");
}
catch(ClassNotFoundException err) { System.err.println(err.getMessage()); }
catch(SQLException err)
{ System.err.println(err.getMessage()); }
}
// -------------------------------public String inserer(Villes ville)
{
String lsMessage = "";
String lsInsert = "INSERT INTO villes(cp,nom_ville,site,photo,id_pays)
VALUES(?,?,?,?,?)";
try
{
PreparedStatement pst = this.cn.prepareStatement(lsInsert);
pst.setString(1, ville.getCp());
pst.setString(2, ville.getNomVille());
pst.setString(3, ville.getSite());
pst.setString(4, ville.getPhoto());
pst.setString(5, ville.getIdPays());
pst.executeUpdate();
lsMessage = "Insertion OK";
}
catch(SQLException e) { lsMessage = e.getMessage(); }
return lsMessage;
}
// ------------------------------public String supprimer(String cp)
{
String lsMessage = "";
String lsDelete = "DELETE FROM villes WHERE cp=?";
try
{
PreparedStatement pst = this.cn.prepareStatement(lsDelete);
pst.setString(1, cp);
pst.executeUpdate();
lsMessage = "Suppression OK";
}
16 avril 2017 - Page 60
Java - JDBC
catch(SQLException e) { lsMessage = e.getMessage(); }
return lsMessage;
}
// --------------------------------public String modifier(Villes ville)
{
String lsMessage = "";
String lsUpdate = "UPDATE villes SET nom_ville=?, photo=? WHERE cp=?";
try
{
PreparedStatement pst = this.cn.prepareStatement(lsUpdate);
pst.setString(1, ville.getNomVille());
pst.setString(2, ville.getPhoto());
pst.setString(3, ville.getCp());
pst.executeUpdate();
lsMessage = "Modification OK";
}
catch(SQLException e) { lsMessage = e.getMessage(); }
return lsMessage;
}
// -----------------------------public Villes selectUn(String cp)
{
Villes uneVille = new Villes();
String lsSelect = "SELECT * FROM villes WHERE cp=?";
try
{
PreparedStatement pst = this.cn.prepareStatement(lsSelect);
pst.setString(1, cp);
ResultSet rs = pst.executeQuery();
rs.next();
uneVille.setCp(rs.getString(1));
uneVille.setNomVille(rs.getString(2));
uneVille.setSite(rs.getString(3));
uneVille.setPhoto(rs.getString(4));
uneVille.setIdPays(rs.getString(5));
}
catch(SQLException e) { }
return uneVille;
}
// -------------------------public ArrayList selectTous()
{
ArrayList tVilles = new ArrayList();
String lsSelect = "SELECT * FROM villes";
try
{
PreparedStatement pst = this.cn.prepareStatement(lsSelect);
ResultSet rs = pst.executeQuery();
while(rs.next())
{
Villes uneVille = new Villes();
uneVille.setCp(rs.getString(1));
uneVille.setNomVille(rs.getString(2));
uneVille.setSite(rs.getString(3));
uneVille.setPhoto(rs.getString(4));
uneVille.setIdPays(rs.getString(5));
tVilles.add(uneVille);
16 avril 2017 - Page 61
Java - JDBC
}
}
catch(SQLException e) { }
return tVilles;
}
}
16 avril 2017 - Page 62
Java - JDBC
TEST DAO
package packageBD;
import java.util.ArrayList;
public class VillesDAOTest
{
public static void main(String[] args)
{
// --Villes ville
= new Villes();
ArrayList tVilles = new ArrayList();
// --- Remplissage du Bean
ville.setCp("75021");
ville.setNomVille("Paris XXI");
ville.setSite("www.paris21.fr");
ville.setPhoto("parisXXI.jpg");
ville.setIdPays("033");
//
//
//
//
//
// --- Insertion via le Bean
//System.out.println(ville.inserer());
// --- Suppression via le Bean
//System.out.println(ville.supprimer());
//
//
// --- Modification via le Bean
ville.modifier();
System.out.println(ville.getNomVille());
//
//
//
//
//
//
// --- Visu UN via le bean
ville.selectUn("75012");
System.out.println(ville.getCp());
System.out.println(ville.getNomVille());
System.out.println(ville.getSite());
System.out.println(ville.getPhoto());
System.out.println(ville.getIdPays());
// --- Visu Tous via le bean
tVilles = ville.selectTous();
for(int i=0; i<tVilles.size(); i++)
{
ville = (Villes)tVilles.get(i);
System.out.println(ville.getNomVille());
}
}
}
16 avril 2017 - Page 63
Java - JDBC
2.6
INTROSPECTION ET SQL
Cf le support java_intro.doc pour l'introspection.
16 avril 2017 - Page 64