Java Data Base Connectivity

Java Data Base Connectivity
1
Connexion aux bases de données
Rappels
API JDBC
Pilotes JDBC
Connexion
ResultSet
Requêtes interprétées
Requêtes préparées
2
Modélisation des données
Création d'un schéma entités/associations
Entités :
identifiables ( notion de clé)
● pertinente pour l'application
● caractérisées par des propriétés (attributs)
●
Représentation graphique
●
OMT, Merise, UML
3
Schéma relationnel
schéma entité/association
A
A
attr
schéma relationnel
B
- tables A et B
- la clé de A devient un attribut de B
( clé étrangère)
B
- tables A et B
- table AB pour l'association
avec clé=(clé A,clé B)
- attribut de l'association = attribut de la
table AB
4
Mysql
Types mysql :
CHAR(n)
INTEGER
VARCHAR(n)
DECIMAL(m,n)
DATE
TIME
DATETIME
TEXT
chaîne de longueur n
entier
chaîne de longueur <=n
m= longueur partie entière, n=lg partie décimale
jour, mois, an
heure, minutes et secondes (temps sur 24h)
date et horaire
texte de longueur quelconque
Serveur de bases
de données
Relationnelles
SQL
Expression des contraintes
DEFAULT val
NOT NULL
val = valeur par défaut
valeur obligatoire
Clés
PRIMARY KEY( num)
l'attribut num est la clé primaire
FOREIGN KEY(idad) REFERENCES Personne
la clé étrangère idad référence la clé primaire de la table Personne
5
Correspondances des types de
données SQL/Java
Type SQL
CHAR VARCHAR
Type Java
String
Méthode getter
getString()
INTEGER
int
getInt()
TINYINT
byte
getByte()
SMALLINT
short
getShort()
BIGINT
long
getLong()
BIT
boolean
getBoolean()
REAL
float
getFloat()
FLOAT DOUBLE
double
getDouble()
NUMERIC DECIMAL java.math.BigDecimal getBigDecimal()
DATE
java.sql.Date
getDate()
TIME
java.sql.Time
getTime()
TIMESTAMP
java.sql.Timestamp
getString()
6
SQL
SELECT attr, ... FROM table, ... WHERE condition
où condition est une expression sur les attributs des tables du FROM
les opérateurs applicables sont :
● opérateurs arithmétiques : +,*,-, ...
● opérateurs relationnels : <,>,>=,... ,!=
● opérateurs logiques : AND, NOT, OR
● BETWEEN, IN, LIKE
caractères joker
● '_'
remplace n'importe quel caractère
● '%'
remplace n'importe quelle chaîne de caractères
Destruction
DELETE FROM table WHERE condition
Modification
UPDATE table SET attr1=val1,Ai=vali,... WHERE condition
7
EXEMPLES
SELECT *
FROM Boutique
WHERE nom IN ('Dijon','Lyon')
nom
ventes
date
Dijon
1500€
05-Jan-2009
Lyon
2400€
21-Jan-2009
nom
ventes
Dijon
1500€
SELECT *
FROM Boutique
Chalon 400€
WHERE Date BETWEEN '06-Jan-2009' AND '11-Jan-2009'
SELECT *
FROM Boutique
WHERE nom LIKE '%ON%'
date
05-Jan-2009
09-Jan-2009
nom
ventes
date
Dijon
1500€
05-Jan-2009
Chalon
400€
09-Jan-2009
Lyon
2400€
21-Jan-2009
SELECT nom
FROM Boutique
WHERE ventes > 1000 OR (ventes < 500 AND ventes > 275)
8
Introduction à JDBC
Pour les applications web utilisant une ou plusieurs
bases de données, les servlets et l'API JDBC offrent une solution :
●
●
efficace, la connexion est maintenue durant tout
le cycle de vie de l'application
flexible, l'accès à une base de données dépend peu
de son fournisseur. Il est facile de porter une application
vers une base de données d'un autre fournisseur
9
Principe de fonctionnement de JDBC
Permet à un programme Java d'interagir
localement ou à distance
avec une base de données relationnelle
Fonctionne selon un principe client/serveur
client = le programme Java
serveur = la base de données
Principe
le programme Java ouvre une connexion
il envoie des requêtes SQL
il récupère les résultats
...
il ferme la connexion
10
Architecture 3 tiers
utilisateur
couche
présentation
interface
web
couche
métier
(domaine)
couche
d'accès
aux
données
BD
les trois couches sont rendues indépendantes grâce à l'utilisation
d'interfaces Java
11
API JDBC
l'API JDBC se trouve dans le paquetage java.sql
l'API JDBC est constituée d'un ensemble d'interfaces pour interagir
avec des bases de données
l'API JDBC ne fournit donc aucune implémentation des comportements
spécifiés par les interfaces
l'API JDBC permet d'exécuter des instructions SQL et
de récupérer les résultats
La version la plus récente de l'API est la version 3.0
12
Pilotes JDBC (1/2)
Le pilote JDBC a pour rôle de permettre l'accès à un système de bases de
données particulier. Il gère le détail des communications avec un type de SGBD
A une API JDBC correspond plusieurs implémentations selon les
fournisseurs (un type de driver par SGBD Oracle, Sybase, Access, ...).
Il en existe pour la plupart des SGBD relationnels
Le pilote implémente l'interface java.sql.Driver
Il existe un driver générique JDBC-ODBC pour toutes les données
accessibles par ODBC
- SGBD : MS Access, SQL Server, Oracle, dBase, ...
- tableurs : Excel, ...
- tout autre application conforme ODBC
13
Gestionnaire de pilotes
DriverManager
DriverManager est une classe, gestionnaire de tous les drivers
chargés par un programme Java
●
●
Chaque programme Java charge le (ou les) drivers dont il a
besoin
L'enregistrement des pilotes est automatique
14
Java et les BD
application
Connexion
Connexion
Gestionnaire
Gestionnairede
depilotes
pilotes
Pilote
PiloteOracle
Oracle
Pont
PontJDBC_ODBC
JDBC_ODBC
Pilote
PiloteSybase
Sybase
Pilote
PiloteODBC
ODBC
BD
Oracle
BD
Sybase
BD
ODBC
15
Connexion à une base de données
Disposer de l'URL d'un pilote pour le SGBD
com:mysql:jdbc:Driver
Sun:jdbc:odbc:JdbcOdbcDriver
jdbc:sqlite
Charger le pilote
Class.forName("com:mysql:jdbc:Driver");
// création d'une instance du pilote
// enregistrement automatique auprès du DriverManager
Etablir l'URL (protocole) de la base
String dbUrl="jdbc:mysql://localhost:3306/maBD";
String dbUrl="jdbc:sqlite:mesdata/mesbds/mags.sqlite";
Ouvrir la connexion : le DriverManager choisit le pilote approprié
à cette URL parmi l'ensemble des pilotes disponibles.
Connection dbcon =
DriverManager.getConnection(dbUrl,user,password);
Connection dbcon = DriverManager.getConnection(dbUrl);
16
Code de connexion : exemple (1/2)
try {
//Déclaration du driver :
Class.forName("com.mysql.jdbc.Driver");
//Url de connexion
String dbUrl="jdbc:mysql://localhost:3306/laboBD";
//Profil/mot de passe
String user = "root";
String password = "19AB5";
//connexion à la base
dbcon = DriverManager.getConnection(dbUrl,user,password);
}
17
Code de connexion : exemple (2/2)
catch( ClassNotFoundException e ){
// erreur de chargement du pilote
}
catch( SQLException e ){
// erreur d'obtention de la connexion
}
finally{
// fermeture de la connexion pour libérer
// les ressources de la BD
try{
if (dbcon!=null) dbcon.close();
}
catch(SQLException e){...}
}
18
Sqlite : connexion
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection
("jdbc:sqlite:mesdata.mesbds.magss.sqlite");
Fermeture de la connexion
conn.close();
19
3 types de requêtes
interface
Statement
interface
PreparedStatement
requêtes interprétées à
chaque exécution
requêtes
précompilées
interface
CallableStatement
requêtes stockées
dans la base
20
L'interface ResultSet
Un objet de type ResultSet est une table représentant le résultat d'une
requête à une base de données
Le nombre de lignes du tableau peut être nul.
Les données de la table sont accessibles ligne par ligne grâce à un curseur
Un resultSet gère un curseur sur la ligne courante de la table des données
satisfaisant aux conditions de la requête.
21
Exécution d'une requête SQL de type
Statement (1/2)
●
●
Le type de résultat obtenu dépend de la manière dont on souhaite manipuler
le ResultSet
3 constantes de types :
TYPE_FORWARD_ONLY ( le curseur se déplace uniquement vers l'avant )
TYPE_SCROLL_INSENSITIVE ( le curseur se déplace en avant et en arrière mais le
Resultset est insensible aux modifications apportées à la BD durant le traitement)
TYPE_SCROLL_SENSITIVE ( le curseur se déplace en avant et en arrière mais les
changements faits à la BD durant le traitement sont reflétés dans le ResultSet)
●
2 constantes de concurrence :
CONCUR_READ_ONLY ( les données du Resultset ne peuvent pas être modifiées)
CONCUR_UPDATABLE ( des modifications de la BD peuvent être effectuées via le
Resultset )
22
Exemple
●
//
//
//
//
//
Créer un objet de la classe java.sql.Statement
Statement stmt = dbcon.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
indique que le ResultSet est scrollable et non
modifiable.
ResultSet.CONCUR_UPDATABLE indiquerait
qu'un seul utilisateur à la fois peut modifier
la donnée
Statement stmt = dbcon.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// lorsqu'on apporte un changement au ResultSet, on peut y
// accéder par la suite car le type est "scrollable" et on peut
// récupérer sa valeur car le type est "SENSITIVE"
Pour que les modifications apportées au
Resultset prennent effet dans la base, il faut appliquer
les méthodes updateRow
23
Sqlite : création
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists magasins;");
stat.executeUpdate("create table magasins
(id, magasin,produit,prix);");
24
Exécution d'une requête SQL de type
Statement (2/2)
Par défaut, le ResultSet n'est ni scrollable, ni modifiable
Statement stmt = dbcon.createStatement();
dbcon.createStatement(TYPE_FORWARD_ONLY,
CONCUR_READ_ONLY);
Construire la requête SQL
Soit la table personne(nom,prenom,age)
String reqSQL = "select * from personne";
Exécuter la requête
ResultSet rs = stmt.executeQuery( reqSQL );
25
Exploiter les résultats d'une requête
(1/5)
A la création du ResultSet, le curseur est placé avant la première ligne (N°1).
Pour le déplacer, on dispose des méthodes :
●
next() déplacement d'une ligne en avant
●
previous() déplacement d'une ligne en arrière
●
first() déplacement vers la première ligne
●
last() déplacement vers la dernière ligne
●
●
beforeFirst() déplacement avant la première ligne.
Si le ResultSet est vide, cela n'a aucun effet.
afterLast() déplacement après la dernière ligne.
Si le ResultSet est vide, cela n'a aucun effet.
●
relative(int i) déplace de i lignes par rapport à la position courante
●
absolute(int i) déplace le curseur vers la i-ème ligne
26
Exploiter les résultats d'une requête
(2/5)
Déplacement absolu absolute( int i)
i>0 déplacement vers la ligne numéro i
rs.absolute(1) => déplacement vers la ligne 1
i<0 déplacement à partir de la fin
rs.absolute(-1) => déplacement sur la dernière ligne
rs.absolute(-3)=> si 30 lignes, déplacement vers ligne 28
Déplacement relatif relative( int i)
On spécifie le nombre de déplacements à partir de la ligne courante
i>0 => déplacements en avant
i<0 => déplacements en arrière
rs.absolute(4); // curseur sur la 4ème ligne
rs.relative(-3); // curseur sur la première ligne
rs.relative(2); // curseur sur la 3ème ligne
27
Exploiter les résultats d'une requête
(3/5)
On récupère le contenu d'une ligne du ResultSet colonne par colonne
Les méthodes d'accès sont construites sur le modèle : getXXX (XXX étant
un type primitif ou String)
La colonne est spécifiée soit par son nom, soit par son numéro (la première
colonne est numérotée 1)
par son nom
String getString(String nomAttribut)
float getFloat(String nomAttribut)
par le numéro de la colonne qui lui correspond
String getString(int indexColonne)
float getFloat(int indexColonne)
idem pour les int, double, long, boolean, Object
28
Exploiter les résultats d'une requête
(4/5)
Pour obtenir le n° de la ligne courante
int getRow()
rs.absolute(5);
int i = rs.getRow(); // i=5
Pour tester la position dans le Resultset
isFirst, isLast, isBeforeFirst, isAfterLast.
if (rs.isAfterLast() == false) {
rs.afterLast(); }
29
Exploiter les résultats d'une requête
(4/5)
String reqSQL = "select * from personne";
ResultSet rs = stmt.executeQuery( reqSQL );
while (rs.next()){
String prenom = rs.getString("prenom");
int age = rs.getInt("age");
}
30
Sqlite : interrogation
ResultSet rs = stat.executeQuery
("select * from magasins;");
while (rs.next()) {
System.out.println("magasin = " +
rs.getString("magasin"));
System.out.println("produit = " +
rs.getString("produit"));
System.out.println("prix = " +
rs.getString("prix"));
}
rs.close();
31
Mises à jour de table (1/2)
Il est préférable que le ResultSet soit scrollable lors d'une mise à
jour
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Pour mettre à jour, 2 méthodes:
updateXXX avec 2 paramètres (nom et valeur de la colonne)
updateRow() rendre effective la mise à jour
32
Mises à jour de table (2/2)
Nom
Meyer
Legrand
Dupont
Prénom
René
Léa
Marcel->Robert
Age
54->37
18
43
String reqSQL = "select * from personne";
ResultSet rs = stmt.executeQuery( reqSQL );
rs.next();
rs.updateInt("Age",37);
rs.last();
rs.updateString("Prénom","Robert");
rs.updateRow();
33
L'interface ResultSetMetaData
Permet d'obtenir la description du contenu du ResultSet
ResultSetMetaData metaData = rs.getMetaData();
A partir de cette information, obtenir le nombre de colonnes du résultat
int nbCols = metaData.getColumnCount();
Obtenir le nom de la colonne connaissant son indice (à partir de 1)
String nomCol = metaData.getColumnName( int i );
34
Exemple
ResultSet rs = stmt.executeQuery( "SELECT * FROM personne" );
StringBuffer resultat = new StringBuffer();
ResultSetMetaData metaData = rs.getMetaData();
int nbCols = metaData.getColumnCount();
for ( int i=1;i<=nbCols;i++ )
resultats.append( metaData.getColumnName( i )+"\t" );
resultats.append( "\n" );
while ( rs.next() ){
for ( int i=1;i<=nbCols;i++ )
// pour simplifier, chaque donnée est du type Object
// sinon le type de la valeur est obtenu par getColumnType
resultats.append( rs.getObject( i )+"\t" );
resultats.append( "\n" );
}
35
Requêtes préparées
L'exécution de chaque requête à une BD nécessite 4 étapes :
analyse
compilation
optimisation
Exécution
Pour des requêtes identiques, les 3 premières étapes n'ont pas à être
effectuées de nouveau.
Avec la notion de requête préparée, les 3 premières étapes ne sont
effectuées qu'une seule fois.
JDBC propose l'interface PreparedStatement qui dérive de l'interface
Statement pour modéliser cette notion.
36
Exécution d'une requête SQL de type
PreparedStatement
Avec l'interface Statement, on écrivait :
Statement smt = dbcon.createStatement();
ResultSet rs = smt.executeQuery("SELECT * FROM personne" );
Avec l'interface PreparedStatement, on écrit :
PreparedStatement pSmt =
dbcon.prepareStatement("SELECT * FROM personne" );
ResultSet rs = pSmt.executeQuery();
●
On voit que la requête est préparée à l'avance et n'est donc pas transmise
en argument au moment de l'exécution ( executeQuery()).
37
Exécution d'une requête SQL de type
PreparedStatement
Pour préparer des requêtes paramétrées de la forme :
SELECT nom FROM Personnes WHERE age > ? AND adresse = ?
On utilise les PreparedStatement avec les méthodes
setType(numéroDeLArgument, valeur)
Type représente le type de l'argument
numéroDeLArgument représente le numéros de l'argument (commençant à
1 dans l'ordre d'apparition dans la requête).
valeur représente la valeur qui lui est associée
Exemple :
PreparedStatement pSmt = dbcon.prepareStatement
("SELECT nom FROM Personne WHERE age > ? AND prenom=?" );
pSmt.setInt(1, 22);
pSmt.setString(2, "Adrien");
ResultSet rs = pSmt.executeQuery();
38
Exécution d'une requête SQL de type
PreparedStatement
PreparedStatement psmt = dbcon.prepareStatement(
"update personne set nom = ? where prenom like ?");
psmt.setString(1, "Bauer");
psmt.setString(2, "René");
int i = psmt.executeUpdate():
On a changé en Bauer le nom de la personne dont le prénom est René
La valeur retournée par executeUpdate() est le nombre de lignes mises à
jour
39
Sqlite : insertion
PreparedStatement prep = conn.prepareStatement(
"insert into magasins (magasin,produit,prix)
values (?, ?, ?);");
prep.setString(1, "fdk");
prep.setString(2, "cafe");
prep.setFloat(3, 0.40f);
prep.addBatch();
prep.setString(1, "fdk");
prep.setString(2, "soda");
prep.setFloat(3, 1.10f);
prep.addBatch();
prep.setString(1, "fdk");
prep.setString(2, "barres");
prep.setFloat(3, 0.90f);
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
40
Procédures stockées (1/2)
●
Les procédures stockées permettent d'embarquer du code
procédural directement dans la base
●
Elles exécutent une ou plusieurs requêtes SQL
●
Intérêt :
●
Elles sont précompilées
●
Ne nécessitent pas de trafic réseau
●
JDBC utilise la classe java.sql.CallableStatement
●
Syntaxe d'appel des procédures stockées
●
{call nom-proc-stokée(?,?)}
// procédure sans résultat
●
{?=call nom-proc-stokée(?,?)} // retour d'une valeur
41
Procédures stockées (2/2)
CallableStatement cstmt =
dbcon.prepareCall("{call nom-proc-stokée(?,?)}");
cstmt.registerOutParameter(2,java.sql.Types.FLOAT);
// indique que le second paramètre donne le résultat
cstmt.setInt(1,ID);
// fixe la donnée en entrée
cstmt.execute();
//exécute la procédure stockée
System.out.println("Solde : " +cstmt.getFloat(2);
// affiche le résultat de l'exécution de la procédure
// stockée figurant dans le second paramètre
42
Exercice
●
Soit une BD de nom " mags ", constituée de la seule table
magasins :
(ID, magasin, produit, prix)
●
Cette BD est accessible avec les identifiants (" root ", " 2dwsk8 ")
Inutile pour sqlite
●
Ecrire le morceau de code Java permettant d'afficher le résultat
de la requête : " liste des produits dont le prix est inférieur à 50€ "
sous la forme :
produit
.....
.....
●
●
prix
.....
.....
On utilisera pour cela les requêtes préparées de JDBC
Compléter le code pour modifier la table afin de ramener les prix
43
de 50€ à 49,95€
Utilisation de sqlite
●
SQLite : gestionnaire de BD sans nécessité d'un serveur de BD
●
Télécharger le pilote JDBC pour SQLite : sqlitejdbc-v056.jar
●
Sous firefox, télécharger et installer le add-on :
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
●
Sous firefox, lancer le manager (menu outils)
●
Entrer le nom de la BD
●
Connecter la BD
●
Créer les tables
●
Insérer les valeurs
44
Exporter une BD Mysql
Sous wamp, il faut exporter la base pour obtenir le script sql correspondant
On ajoute ensuite une ligne qui permet de vérifier l'existence d'une Base de même nom
DROP DATABASE IF EXISTS `tp`;
CREATE DATABASE `tp` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci;
USE `tp`;
CREATE TABLE `personne` (`nom` varchar(20) NOT NULL default '',`prenom`
varchar(20) NOT NULL default '',`age` tinyint(4) NOT NULL default '0',PRIMARY KEY
(`nom`,`prenom`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `personne` VALUES ('Meyer', 'Luc', 30);
INSERT INTO `personne` VALUES ('Dupont', 'René', 40);
Il suffit d'enregistrer le script dans un fichier sur votre clé usb
ATTENTION : chaque commande sql est situé sur une seule ligne
45