JDBC (Java DataBase Connectivity)

-

-

Date : date SQL

Time : heures, minutes, secondes SQL

TimeStamp : date et heure avec une précision à la microseconde

Types : constantes pour désigner les types SQL (pour les conversions avec les types

Java)

Mme CHALOUAH Anissa

Page | 5

3.2. Mise en œuvre de JDBC

Figure : Mise en Œuvre JDBC

Avant toute chose, n’oubliez pas d’importer le package java.sql

import java.sql.*;

1) Chargement du driver JDBC : Disposer du driver spécifique ou utiliser ODBC sinon, car

le pilote ODBC est dans l'API java.

2) Etablir la connexion au SGBD : Connaître l'URL de la base et y s'assurer d'avoir un

compte d'accès username/password.

3) Créer une requête (ou instruction SQL)

4) Exécuter la requête : Mise à feu de la transaction et retour des résultats

5) Traiter les données retournées

6) Fermer la connexion : Prendre soin de fermer les curseurs de dialogue liés à la

connexion

a. Etape 1 : Chargement du Driver

 Pour se connecter à une base de données il est essentiel de charger dans un premier temps le pilote de la base de données à laquelle on désire se connecter grâce à un appel au DriverManager (gestionnaire de pilotes) :

Class.forName("driver_name ");

Exemples

Chargement du pilote JDBC-ODBC :

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Mme CHALOUAH Anissa

Page | 6

Chargement du pilote pour une base Oracle :

Class.forName("oracle.jdbc.driver.OracleDriver");

Chargement du pilote pour une base PostgreSQL :

Class.forName("org.postgresql.Driver");

 Quand une classe Driver est chargée, elle doit créer une instance d’elle même et

s’enregistrer auprès du DriverManager

 Certains compilateurs refusent cette notation et demande plutôt :

Class.forName("driver_name").newInstance();

 La méthode static forName() de la classe Class peut lever l'exception

java.lang.ClassNotFoundException.

Exemple

try {

Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { System.err.println("Driver loading error : " + e); }

b. Etape 2 : Connexion à la base

 Etablir une connexion en utilisant la classe java.sql.DriverManager. Son rôle est de créer des connexions en utilisant le driver préalablement chargé. Cette classe dispose d'une méthode statique getConnection() prenant en paramètre l'URL de connexion, le nom d'utilisateur et le mot de passe.

 URL de la forme :

jdbc:<sous-protocole>:<nom-BD>;param=valeur, ...

 L’ URL spécifie :

o

o

o

l'utilisation de JDBC (protocole)

le driver ou le type du SGBDR (sous-protocole)

le nom de la base locale ou distante avec des paramètres de configuration éventuels : nom utilisateur, mot de passe, …

Exemple d’URL

jdbc:odbc:ma_base jdbc:pg95:mabase?username=toto:password=titi

 Ouverture de la connexion :

Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");

 La méthode getConnection() peut

lever une exception de

la classe

java.sql.SQLException.

Mme CHALOUAH Anissa

Page | 7

Exemple

String url = "jdbc:mysql://localhost/tp"; try {

Connection con = DriverManager.getConnection(url, "root", "secret");

} catch (SQLException e) {

System.err.println("Error opening SQL connection: " + e.getMessage()); }

c. Etape 3 : Etablir une requête SQL



Il y a 3 types de requêtes :

 Statement pour les ordres SQL simple. Ces états sont construits par la méthode

createStatement appliquée à la connexion.



PreparedStatement pour les ordres SQL paramétrés. Ces états sont construits par la méthode prepareStatement appliquée à la connexion.

 CallableStatement pour les procédures ou fonctions cataloguées (PL/SQL, C, Java, ..). Ces états sont construits par la méthode prepareCall appliquée à la connexion.

Figure : Connexion et requêtes

 S’il ne doit plus être utilisé dans la suite du code Java, chaque objet de type Statement, PreparedStatement ou CallableStatement devra être fermé à l’aide de la méthode close.

Exemple avec Statement

try {

Statement stmt = conn.createStatement(); }

catch (SQLException e) {

System.err.println("Error creating SQL statement: " + e.getMessage()); } Remarque



Il n'est pas nécessaire de définir un objet Statement pour chaque ordre SQL : il est possible d'en définir un et de le réutiliser

Mme CHALOUAH Anissa

Page | 8

d. Etape 4 : Exécution d'une requête de type Statement



Il y a 3 types d'exécutions :

 executeQuery : pour les requêtes (SELECT) accessibles par la méthode Statement.executeQuery(). Cette méthode retourne un résultat de type java.sql.ResultSet contenant les lignes sélectionnées.



executeUpdate : pour les requêtes INSERT, UPDATE, DELETE, CREATE TABLE et DROP TABLE accessibles par la méthode Statement.executeUpdate(). Cette méthode retourne un résultat de type int correspondant au nombre de lignes affectées par la requête.

 execute : pour quelques cas rares (procédures stockées)

Exemple avec executeQuery

String query = "SELECT CIN, name,email FROM users;"; try {

ResultSet resultSet = stmt.executeQuery(query);

} catch (SQLException e) {

System.err.println("Error executing query: " + e.getMessage());

}

Exemple avec executeUpdate

String query = "UPDATE users SET email='[email protected]' WHERE nom='SMITH';"; try {

int result = stmt.executeUpdate(query);

} catch (SQLException e) {

System.err.println("Error executing query: " +

e.getMessage()); }

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

e. Etape 5 : Traitement des résultats

 L'objet ResultSet permet d'avoir un accès aux données résultantes de notre requête en mode ligne par ligne. La méthode ResultSet.next() permet de passer d'une ligne à la suivante. Cette méthode renvoie false dans le cas où il n'y a pas de ligne suivante. Il est nécessaire d'appeler au moins une fois cette méthode, le curseur est placé au départ avant la première ligne (si elle existe).

Mme CHALOUAH Anissa

Page | 9

 La

classe ResultSet dispose aussi d'un

certain nombres d'accesseurs (ResultSet.getXXX()) qui permettent de récupérer le résultat contenu dans une colonne sélectionnée.



On peut utiliser soit le numéro de la colonne désirée, soit son nom avec l'accesseur. La numérotation des colonnes commence à 1.

 XXX correspond au type de la colonne. Le tableau suivant précise les relations entre type

SQL, type JDBC et méthode à appeler sur l'objet ResultSet.

Types

char varchar integer double, float Date

Blob

Type SQL

Type JDBC

Méthode d'accès

String String Integer Double Float Date

Blob

getString() getString() getInt() getDouble() getDouble() getDate()

getBlob()

 La méthode getString() permet d'obtenir la valeur d'un champ de n'importe quel

type.

Exemple

ResultSet rs = stmt.executeQuery("SELECT * FROM users"); try { while (rs.next()) {

System.out.println(rs.getInt("CIN")+" – " +

rs.getString("NOM") +" – " + rs.getString("EMAIL"));

} }

catch (SQLException e) {

System.err.println("Error browsing query results: " +

e.getMessage()); }

ResultSet rs = stmt.executeQuery("SELECT * FROM users"); // Pour accéder à chacun des tuples du résultat de la requête : … while (rs.next()) {

int cin=rs.getInt(1) ; String nom = rs.getString(2); String prenom = rs.getString(3); java.sql.Date date_nais = rs.getDate(4);

... }

Mme CHALOUAH Anissa

Page | 10

f. Etape 6 : Fermeture des différents espaces

 Pour terminer proprement un traitement, il faut fermer les différents espaces ouverts

sinon le garbage collector s’en occupera mais moins efficace

 Chaque objet possède une méthode close() :

 resultset.close();

 statement.close();

 connection.close();

Exemple de programme :

Le programme ci-dessous utilise une base nommée Refugedb . Cette base de données contient une table nommée ANIMAL; voici un script de création :

CREATE TABLE ANIMAL (

id INTEGER PRIMARY KEY, categorie VARCHAR NOT NULL, nom VARCHAR, race VARCHAR, sexe CHAR, date_nais DATE, id_proprio INTEGER, present BIT

) INSERT INTO ANIMAL VALUES (1,'CRM', 'kiki','berger','M','2000-2-21',21,false) INSERT INTO ANIMAL VALUES (2,'CRM','rex','caniche','M','1996-12-2',11,true) ...

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

1 import java.sql.*; 2 3 public class TestAnimal { 4 public void test() { 5 final String driver = "org.hsqldb.jdbcDriver"; 6 final String url = "jdbc:hsqldb:/home/kpu/Refuge/Refugedb"; 7 final String user = "sa"; 8 final String password =""; 9 10 Statement st = null; 11 Connection con = null; 12 ResultSet rs = null; 13 String sql = ""; 14 try { 15 Class.forName(driver).newInstance(); 16 con = DriverManager.getConnection(url, user, password); 17 st = con.createStatement(); 18 sql = "SELECT * FROM ANIMAL";

Mme CHALOUAH Anissa

Page | 11

19 rs = st.executeQuery(sql); 20 System.out.println("ID\tTYPE\tNOM\t\tRACE\t"); 21 while (rs.next()) { 22 System.out.print(rs.getInt(1)+"\t"); 23 // ATTENTION, les indices commencent à 1. 24 System.out.print(rs.getString(2)+"\t"); 25 System.out.print(rs.getString("nom")+"\t\t"); 26 System.out.println(rs.getString("race")+"\t"); 27 }//while 28 } 29 catch (ClassNotFoundException e) { 30 System.err.println("Classe non trouvée : " + driver ); 31 } 32 catch (SQLException e) { 33 System.err.println("SQL erreur : "+ sql + " " + e.getMessage()); 34 } 35 catch (Exception e) { 36 System.err.println("Erreur : "+ e); 37 } 38 39 finally { 40 try { if (con != null) { con.close(); } } 41 catch (Exception e) { System.err.println(e); } 42 } 43 } 44 static public void main(String[] arg) { 45 TestAnimal app = new TestAnimal(); 46 app.test(); }}

Figure : Schéma récapitulatif de la mise en œuvre JDBC

Mme CHALOUAH Anissa

Page | 12

3.3.

Accès aux méta-données

JDBC permet de récupérer des informations :



sur le type de données que l'on vient de récupérer par un SELECT (interface ResultSetMetaData)

 mais aussi sur la base elle-même (interface DatabaseMetaData)

a. Interface ResultSetMetaData

 La méthode getMetaData() permet d’obtenir des informations sur les types de

données du ResultSet

 Elle renvoie des instances de ResultSetMetaData 

on peut connaître entre autres :

le nombre de colonne : getColumnCount() o le nom d’une colonne : getColumnName(int col) o le nom de la table : getTableName(int col) o o le type de donnée SQL de la colonne : int getColumnType(int) o si un NULL SQL peut être stocké dans une colonne : isNullable()

Exemple

ResultSet rs = st.executeQuery("SELECT * FROM users"); ResultSetMetaData rsmd = rs.getMetaData(); int nbColonnes = rsmd.getColumnCount(); for (int i = 1; i <= nbColonnes; i++) {

String typeColonne = rsmd.getColumnType(i); String nomColonne= rsmd.getColumnName(i); System.out.println("Colonne " + i+ " de nom " + nomColonne + " de type " + typeColonne);

}

b. Interface DatabaseMetaData

Elle permet de donner des informations sur la base de données  Méthode getMetaData() de l’objet Connection  Dépend du SGBD avec lequel on travaille  Elle renvoie des instances de DatabaseMetaData  on peut connaître entre autres :

o o

les tables de la base : getTables() le nom de l’utilisateur : getUserName()

Exemple

private DatabaseMetaData metaData; private java.awt.List listTables= new List(10); … String[] types = { "TABLE", "VIEW" }; String nomTables; … metaData = conn.getMetaData(); ResultSet rs = metaData.getTables(null, null, "%", types);

Mme CHALOUAH Anissa

Page | 13

while (rs.next()) { nomTable = rs.getString(3); listTables.add(nomTable); }

3.4.

Requêtes précompilés (PreparedStatement)

 L'interface PreparedStatement définit les méthodes pour un objet qui va encapsuler une requête précompilée. Ce type de requête est particulièrement adapté pour une exécution répétée d'une même requête avec des paramètres différents.

 Cette interface hérite de l'interface Statement.  Lors de l'utilisation d'un objet de type PreparedStatement, la requête est envoyée au

moteur de la base de données pour que celui ci prépare son exécution.

 Un objet qui implémente l'interface PreparedStatement est obtenu en utilisant la méthode prepareStatement() d'un objet de type Connection. Cette méthode attend en paramètre une chaîne de caractères contenant la requête SQL. Dans cette chaine, chaque paramètre est représenté par un caractère ?

Exemple

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users "+ "WHERE nom = ? ");

 Un ensemble de méthode setXXX(int, valeur) (ou XXX représente un type primitif ou certains objets tel que String, Date, Object, ...) permet de fournir les valeurs de chaque paramètre défini dans la requête.

o

Le premier paramètre précise le numéro du paramètre dont la méthode va fournir la valeur.

o Le second paramètre précise cette valeur.

Exemple

PreparedStatement pstmt = conn.prepareStatement( "UPDATE emp SET sal = ? " + "WHERE grade = ?"); for (int i=0; i<10; i++) {

pstmt.setDouble(1,employe[i].getSalaire()); pstmt.setString(2, employe[i].getNom()); int count = pstmt.executeUpdate();

} Exemple

import java.sql.*; public class TestJDBC2 {

private static void arret(String message) {

System.err.println(message); System.exit(99);}

Mme CHALOUAH Anissa

Page | 14

public static void main(java.lang.String[] args) {

Connection con = null; ResultSet resultats = null; String requete = "";

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch (ClassNotFoundException e) { arret("Impossible de charger le pilote jdbc:odbc"); }

System.out.println("connexion a la base de données");

try { String DBurl = "jdbc:odbc:testDB"; con = DriverManager.getConnection(DBurl); PreparedStatement recherchePersonne = con.prepareStatement("SELECT * FROM personnes WHERE nom_personne = ?"); recherchePersonne.setString(1, "Ali"); resultats = recherchePersonne.executeQuery(); affiche("parcours des données retournées"); boolean encore = resultats.next();

while (encore) { System.out.print(resultats.getInt(1) + " : "+resultats.getString(2)+" "+ resultats.getString(3)+"("+resultats.getDate(4)+")"); System.out.println(); encore = resultats.next();

}

resultats.close();

} catch (SQLException e) { arret(e.getMessage()); } System.out.println("fin du programme"); System.exit(0);

} }

3.5.

Procédure stockée (CallableStatement)

Rappel

 Les procédures stockées permettent de fournir la même fonctionnalité à plusieurs

utilisateurs

 Les procédures sont stockées dans la base côté serveur

JDBC offre une interface dédiée  CallableStatement  dérive de l’interface PreparedStatement  gère le retour de valeur avec ResultSet

Mme CHALOUAH Anissa

Page | 15

Comment création d’un objet CallableStatement   en utlisant Connection.prepareCall(…)

La requête doit être formatée

 

encadrée par des accolades {…} utilisation du préfixe call

Trois modes pour l'appel



la procédure renvoie une valeur

{ ? = call nomProcédure(?,?,…)}



la procédure ne renvoie aucune valeur

{ call nomProcédure(?,?,…)}



si on ne lui passe aucun paramètre

{ call nomProcédure }

Passage de paramètres possible comme pour PreparedStatement Exemple

Sans valeur de retour

CallableStatement testCall; testCall = conn.prepareCall("{ call setSalary(?,?) }"); testCall.setString(1, "€ EUR"); testCall.setLong(2, 2000); testCall.execute();

Un PreparedStatement peut retourner des données grâce à un ResultSet

 Les procédures stockées étendent le modèle

o appel de la procédure précédé du passage des paramètres in et out grâce aux

o

méthodes setXXX() type des paramètres out et in/out grâce à la méthode registerOutParameter()

o exécution de la requête grâce à executeQuery(), executeUpdate() ou

execute() récupération des paramètres out et in/out grâce aux méthodes getXXX()

o

Exemple

On considère la procédure stockée

create or replace procedure augmentation (unDept in integer, pourcentage in number, cout out number) is begin update emp set sal = sal * (1 + pourcentage / 100) where dept = unDept;

select sum(sal) * pourcentage / 100 into cout

Mme CHALOUAH Anissa

Page | 16

from emp where dept = unDept; end;

CallableStatement csmt = conn.prepareCall( "{ call augmentation(?, ?, ?) }"); // 2 chiffres après la virgule pour 3ème paramètre csmt.registerOutParameter(3, Types.DECIMAL, 2); // Augmentation de 2,5 % des salaires du dept 10 csmt.setInt(1, 10); csmt.setDouble(2, 2.5); csmt.executeQuery(); double cout = csmt.getDouble(3); System.out.println("Cout total augmentation : " + cout);

Elles peuvent retourner n'importe quel type de données y compris des ResultSet

 utiliser executeQuery() au lieu de execute() 

récupération d'un ResultSet normal o utilisable comme d'habitude o permet de sauvegarder la requête SQL au niveau de la base

CallableStatement csmt; csmt = myConn.prepareCall("{ call getDetails(?) }"); ResultSet rs; csmt.setLong(1, 1000000); rs = csmt.executeQuery();

Mme CHALOUAH Anissa

Page | 17