633-2.2 Dialogue avec la base de données
publicité
633-2.2 Dialogue avec la base de données 3. Manipulation des données avec un langage hôte [email protected] GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 1 Références • Filière – Bachelor of Science en informatique de gestion • Domaine – Technologies informatiques • Module – 633 – Architectures distribuées • Unité d’enseignement – 633-2.1 – Applications réseaux et services applicatifs – 633-2.2 – Dialogue avec la base de données GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 2 Objectifs • Objectifs du module – comprendre et mettre en œuvre les services applicatifs fondamentaux; – développer, déployer et configurer des composants métiers sur un serveur d’applications. • Objectifs de l’unité d’enseignement « Dialogue avec la base de données » – comprendre les différences entre l'architecture "serveur de fichiers" et "client/serveur"; – maîtriser le principe du dialogue à sessions; – comprendre la notion de concurrence; – savoir manipuler des données à l'aide d'un langage hôte. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 3 Découpage de l’unité d’enseignement 1. Architecture client/serveur 2. Transactions et verrous 3. Manipulation des données avec un langage hôte GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 4 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 5 Quizz • Quel est le lien « visible » entre l’utilisateur et votre base de données? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 6 Interface utilisateur • Un utilisateur final n’utilise pas SQL pour manipuler des données. • L’informaticien de gestion doit lui proposer une interface utilisateur adaptée à ses besoins. – Pour se faire, il doit développer une interface utilisateur (IHM) à l’aide d’un langage de programmation. – Java, C#, PHP, Oracle Apex… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 7 Exemple d’interface utilisateur Proconcept ERP GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 8 Exemple d’interface utilisateur GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch Interface de FUTURA – Université de Fribourg 9 Positionnement de l’IHM dans une architecture logicielle Utilisateur final Transactions IHM Logique applicative Logique métier Services Logique de présentation Programmes Logique de persistance GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch BD 10 Simplifions • De quoi est constitué le code de l’application? – ? Utilisateur final Code de l’application BD GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 11 Qu’allons-nous apprendre dans ce cours? Utilisateur final Code de l’application Code de l’application • IHM • Code métier • Règles de navigation • Accès à la base de données • Requêtes SQL BD • L’accès à la BD et les requêtes SQL que vous développez sont du « SQL programmé ». SQL programmé Permet le dialogue avec la base de données! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 12 SQL programmé • SQL programmé – Intégrer le LMD dans un programme que vous développez. • Mélange d’instructions SQL, de boucles, de classes, etc… – Les requêtes doivent pouvoir gérer des variables. – Le langage permettant d’intégrer des instructions SQL se nomme « langage hôte » Utilisateur final Code de l’application GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch BD Langage hôte 13 SQL interactif • SQL interactif – Utiliser le SQL LMD sans l’encapsuler dans un programme. – Très intéressant pour les professionnels. • Contrôle de la BD, interrogation du dico… • Utilisé depuis SQL*Plus (pour Oracle), SQLDeveloper, Toad… SQL interactif BD GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch Développeur 14 SQL programmé <> SQL interactif SQL Programmé SQL interactif Sans paramètre • A vous de Avec paramètres spécifier les différences… – 5 minutesPeuvent être construite puis restitution en classe ‐ dynamiquement Ca se compile pas On voit les erreurs directement Utilisé par les utilisateurs finaux Utilisé par les développeurs Accès au curseur Pas accès au curseur Prédéfinies et fixes (avec paramètre) Liberté totale Une méthode pour chaque requête Requête résultat SQL + langage hote On ne peut connaitre «que» SQL CRUD LMD LDD LCD GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 15 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 16 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 17 Langage hôte et API • Tous les langages « modernes » permettent d’intégrer du SQL. Ils sont donc considérés comme langage hôte. – Java, C#, PHP… • Les langages hôtes peuvent intégrer du SQL via différentes méthodes. • L’utilisation d’API est l’une des méthodes les plus utilisée. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 18 Application Programming Interface • Une API est un ensemble de classes et méthodes permettant de faire appel à des services de différents fournisseurs de façon unique. – SAX API, DOM, Java RMI, etc… • Il peut aussi s’agir de méthodes publiées par un fournisseur pour interagir avec son système. • Dans le cadre des BD, une API permet l’utilisation de classes et méthodes pour simplifier et standardiser l’appel à des sources de données. – JDBC, ODBC, OLE DB GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 19 Application Programming Interface API JDBC, ODBC… Source de données Client base de données SQLNet… Protocol TCP… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 20 Des normes ? Des accords ? • Le principe est toujours le même… • Un groupe d’experts composés de fournisseurs et d’indépendants se mettent d’accord sur la signature des méthodes de leur API. – Par exemple pour une API de BD: méthode connect() avec en paramètre le nom de la BD, méthode lanceSelect() avec en paramètre le select… – Chaque fournisseur implémente ces méthodes (en respectant la signature) et fournit une implémentation de l’API. • Principe de l’Interface en Java. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 21 Quelques API pour accéder aux données • ODBC – Open Database Connectivity est une API permettant l’accès et la manipulation de données d’un très grand nombre de fournisseurs de bases de données. • OLE DB – OLE DB est une API permettant l’accès à différentes sources de données. OLE DB n'est pas réservée aux sources de données relationnelles, mais est capable de traiter n'importe quel type de données indépendamment de leur format ou de leur méthode de stockage. – OLEDB et ODBC sont très utilisés dans le monde Microsoft pour accéder à des données (depuis VB, Excel…) • JDBC – API standard permettant l’utilisation de bases de données depuis Java. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 22 Avantages et inconvénients des APIs • Niveau d’abstraction – Apporte une couche supplémentaire générique. • Permet l’accès à des services de différents fournisseurs de façon unique. • Librairie supplémentaire à maîtriser pour le développeur. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 23 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 24 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 25 Travaux pratiques • Série 3, exercices 1 et 2 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 26 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC – JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 27 JCP et JSR • Java Community Process (JCP)? “The JCP is the mechanism for developing standard technical specifications for Java technology. Anyone can register for the site and participate in reviewing and providing feedback for the Java Specification Requests (JSRs), and anyone can sign up to become a JCP Member and then participate on the Expert Group of a JSR or even submit their own JSR Proposals” Source: http://jcp.org/en/home/index GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 28 JCP et JSR • Java Specification Requests (JSR)? “A JSR is a Java Specification Request. This is the document submitted to the PMO1 by one or more members to propose the development of a new specification or significant revision to an existing specification. There are currently more than 90 Java technology specifications in development in the JCP program. “ Source: http://jcp.org/en/introduction/faq 1. The Program Management Office is the group within Sun designated to oversee the Java Community Process and manage the daily running of the program. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 29 A quoi ça ressemble? • JDBC évolue sur des JSR… • Et aussi à l’aide du monde open source… • Voyons à quoi ça ressemble… – http://jcp.org/en/home/index GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 30 JDBC introduction • JDBC = Java DataBase Connectivity – – – – JDBC JDBC JDBC JDBC 1.0 2.0 3.0 4.0 (Java 1) (J2SE 1.2) (J2SE 1.4) (Java SE 6) • API standard permettant l’utilisation de bases de données relationnelles depuis le monde Java. • Il s’agit d’un ensemble de classes pouvant être utilisé depuis une application Java et permettant: – la gestion de la connexion à la base de données; – l’utilisation de commandes SQL (DDL, DML, DCL); – l’exploitation des résultats de requêtes (données). GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 31 JDBC rend indépendant… • JDBC permet aux développeurs d’utiliser des classes et méthodes indépendantes du SGBDR. – JDBC utilise les drivers fournis par chaque constructeurs de bases de données. – A vous, en tant que développeur, d’utiliser les bons drivers et de les tester ! Application java jdbc driver Oracle Oracle driver MySQL MySQL GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 32 Types de drivers • Les drivers JDBC peuvent être de 4 types: – – – – pilotes pilotes pilotes pilotes de de de de types types types types 1; 2; 3; 4. • 2 cas de figure : GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 33 Pilotes de type 1 • Pilotes non autonomes… Ils accèdent aux bases de données par une passerelle (via une autre technologie). • « JDBC-ODBC » est le plus connu. Ce type de pilote convertit les ordres Java JDBC en ODBC valide. C’est ODBC qui ensuite exécute les ordres. – Ce pilote est livré en standard par Oracle (Sun) avec JDBC. – Permet une connexion vers des bases de données ne disposant pas d’un driver JDBC. – Peu performant GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 34 Pilotes de type 2 • Il s’agit de pilotes natifs. – Aussi nommé drivers « OCI » chez Oracle. – classes12.jar (J2SE 1.2), ojdbc14.jar (J2SE 1.5, Java SE 5) et ojdbc6.jar (Java SE 6) contiennent tous un driver de type 2 pour se connecter à Oracle. – Nécessite l’installation du client Oracle sur la machine qui lance des commandes JDBC. – Très bonnes performances • Les commandes Java JDBC sont traduites en appels natifs vers le serveur de base de données. GES-DP130227-633-2.2_Dialogue_BD_3 PDA-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 35 Pilotes de type 3 • Il s’agit de pilotes entièrement écrit en java convertissant les appels JDBC en un protocole indépendant du SGBD. • Ce type de pilote fait appel à intermédiaire pour accéder le SGBD. un serveur • A ce jour, je n’en ai encore jamais rencontré. GES-DP130227-633-2.2_Dialogue_BD_3 PDA-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 36 Pilotes de type 4 • Pilotes écrits en java se connectant au SGBD. – Aussi nommé drivers « thin » chez Oracle. – classes12.jar (J2SE 1.2), ojdbc14.jar (J2SE 1.5, Java SE 5) et ojdbc6.jar (Java SE 6) contiennent tous un driver de type 4 pour se connecter à Oracle. – Facile à installer. – Indépendant à la plateforme. – Performance identique aux drivers de type 2. – Ne nécessite pas d’installation d’autres médiateurs. GES-DP130227-633-2.2_Dialogue_BD_3 PDA-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 37 Jar et implémentation JDBC d’Oracle • Un JAR (Java ARchive) est un ensemble de classes, interfaces, fichiers Java compilés et compressés dans un fichier le .jar • Chaque fournisseur de BD livre une librairie .jar contenant son implémentation JDBC pour sa base de données. – Oracle fournit les jar classes12.jar, ojdbc14.jar, ojdbc6.jar… – PostgreSQL fournit les jar postgresql-9.0dev-800.jdbc4.jar… – IBM fournit db2jcc.jar pour DB2 • Chaque fournisseur livre son implémentation de JDBC. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 38 Compatibilité des drivers Oracle Version de Java Driver Java 1.2 et 1.3 classes12.jar Java 1.4 ojdbc14.jar Java 5 ojdbc5.jar Java 6 ojdbc6.jar GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 39 JDBC packages • JDBC 4.0 est réparti dans 2 packages – java.sql • Package permettant l’accès et la manipulation de données provenant d’une base de données relationnelle via le langage hôte Java. – javax.sql • Package pour la partie serveur (utilisation de datasource d’un serveur d’applications par exemple). GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 40 java.sql • java.sql contient des interfaces (au sens java). • C’est au fournisseur d’implémenter ces interfaces. – L’implémentation doit être faite par les constructeurs de SGBDR. • Certaines interfaces peuvent ne pas être implémentées mais seront disponibles tout de même… – En simplifiant, chez Oracle c’est le jar ojdbc6.jar qui implémente les interfaces définies chez Sun. – C’est donc à Oracle de développer et livrer un driver JDBC. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 41 Les interfaces et classes importantes de l’API JDBC • DriverManager – Classe de base pour gérer les drivers JDBC et pour créer une connexion. • Connection – C’est une session à la base de données. – Cet objet (une fois instancié…) va permettre de gérer les transactions, de créer des « statements »… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 42 Les interfaces et classes importantes de l’API JDBC • Statement – Interface à utiliser pour exécuter des instructions SQL statiques. Retourne le résultat produit dans un ResultSet. • PreparedStatement – Objet à utiliser pour exécuter des requêtes paramètrées. • CallableStatement – Objet à utiliser pour exéctuer des procédures stockées. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 43 Les interfaces et classes importantes de l’API JDBC • ResultSet – Un “set” de données resultant de la base de données. – Notion de curseur… (rappel PL/SQL…) • ResultSetMetaData – Cet classe peut être instanciée pour obtenir les métadonnées d’un ResultSet. • … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 44 Relation entre les interfaces JDBC • DriverManager • Connection • Statement • ResultSet GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 45 JDBC avantages • Portabilité. • Implémenté par tous les fournisseurs de BD. • Relativement facile d’utilisation. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 46 JDBC désavantages 1/2 • API de bas niveau. • Pas de contrôle de SQL lors du développement. – Erreur à l’exécution… • Attention, certaines méthodes peuvent ne pas être implémentées par des fournisseurs… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 47 JDBC désavantages 2/2 • Du code propriétaire à une base de données n’est pas portable! – « select connect by » sur Oracle – « select * from employe, departement where employe.dep_id = departement.id » jointure non standard • Problème paradigme Objet Relationnel. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 48 Les bonnes façons de faire… • Il ne faut pas oublier les bonnes pratiques liées aux bases de données: – gérer correctement les transactions ; – ne pas oublier les verrous; – … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 49 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 50 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 51 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC – JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 52 Schéma de principe de connexion Programme (langage hôte) Requêtes SQL Curseurs … Base de données Connexion GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 53 Langage hôte utilisé dans ce cours • Nous allons utiliser Java comme langage hôte pour illustrer les concepts de ce cours. • JDBC sera l’API à utiliser… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 54 Connexion à une base de données à l’aide de JDBC • DriverManager – Classe de base pour gérer les drivers JDBC et pour créer une connexion. – La méthode getConnection permet de retourner une instance de « Connection ». Connection myConn = DriverManager.getConnection(url, user, password); • Connection – Connection est une interface – Cet objet (une fois instancié…) va permettre de gérer les transactions, de créer des « statements »… • C’est une session à la base de données. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 55 Schéma de principe de connexion en Java Programme Java Requêtes SQL Curseurs … Base de données Connection try{ Connection myConn = DriverManager.getConnection(url, user, password); catch(SQLException sqle) { System.out.println("Erreur SQL:" + sqle); } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 56 Utilisation du bloc try… catch • Veuillez pour l’instant encapsuler vos instructions JDBC (connexion, appel de requêtes SQL, etc.) dans un bloc try – catch. – Nous traiterons plus en détail dans un prochain chapitre la gestion des exceptions en Java (lié aux BD) try{ Connection myConn = DriverManager.getConnection(url, user, password); catch(SQLException sqle) { System.out.println("Erreur SQL:" + sqle); } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 57 Quelques méthodes de l’interface Connection • createStatement – Creates a Statement object for sending SQL statements to the database prepareStatement(String) • preparedStatement – Creates a PreparedStatement object for parameterized SQL statements to the database sending GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 58 Quelques méthodes de l’interface Connection • getMetaData – Retrieves a DatabaseMetaData object that contains metadata about the database to which this Connection object represents a connection • close() – Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 59 Quelques méthodes de l’interface Connection • setAutoCommit(boolean) – Sets this connection's auto-commit mode to the given state • commit() – Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object • rollback() – Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object * Explications tirées de la document Java GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 60 Schéma de principe de connexion en Java Statement Programme Java PreparedSt atement Base de données Connection …myConn.createStatement()… …myConn.preparedStatement()… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 61 Avant JDBC 4.0 • JDBC 4.0 existe depuis Java SE 6.0. • Avant cette version, il était nécessaire de charger le pilote JDBC avant d’effectuer une connexion à la base de données. – Class.forName() pour les pilotes de type 1. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); – DriverManager.registerDriver() pour les pilotes de type 2, 3 et 4. DriverManager.registerDriver( new Oracle.jdbc.driver.OracleDriver()); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 62 Fermeture des connexions JDBC • Une connexion à la base de données via JDBC est fermée automatiquement via le garbage collector. • Toutefois, afin de libérer les ressources dès que possible, il est nécessaire de fermer correctement les connexions. Connection myConn; … myConn.close(); … • Afin de s’assurer de la fermeture correcte des connexions, une bonne pratique consiste à fermer les connexions dans la partie finally des exceptions. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 63 Utilisation de librairie • Pour que votre programme Java puisse fonctionner, il est nécessaire qu’il puisse référencer votre librairie JDBC. – ojdbc6.jar par exemple avec Java SE 6. • N’oubliez donc pas de configurer votre EDI… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 64 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 65 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 66 Travaux pratiques • Série 3, exercice 3 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 67 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC – Statements, PreparedStatement et CallableStatement GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 68 Schéma de principe de connexion en Java – Rappel! Statement Programme Java PreparedSt atement Base de données Connection …myConn.createStatement()… …myConn.preparedStatement()… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 69 Connexion et objets associés • Lorsqu’une connexion est établie entre votre programme Java et votre base de données, vous pouvez utiliser des objets permettant l’exécution d’instructions SQL: – Statement – PreparedStatement – CallableStatement GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 70 Statement « The object used for executing a static SQL statement and returning the results it produces» • L’interface Statement fournit les méthodes nécessaires à la soumission d’une instruction SQL statique. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 71 Statement Création • La création d’un objet de type Statement s’effectue à l’aide de votre objet de type Connection par la méthode createStatement(). Connection myConn = DriverManager.getConnection(url, user, password); Statement stmt = myConn.createStatement(); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 72 Statement Manipulation • Une fois un objet de type Statement instancié, vous pouvez le manipuler. Voici les méthodes principales: Method summary ResultSet executeQuery(String sql) Executes the given SQL statement, which returns a single ResultSet object int executeUpdate(String sql) Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement void close() Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch Source: http://download.oracle.com/javase/6/docs/api/ 73 Statement Manipulation • executeUpdate ou executeQuery? Instruction SQL Méthode à utiliser LDD executeUpdate SELECT executeQuery LMD (autre que SELECT) executeUpdate LCD executeUpdate GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 74 Statement Manipulation - exemple … Statement stmt = myConn.createStatement(); ResultSet curseurJava = stmt.executeQuery("SELECT id, nom, prenom FROM EMPLOYES "); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 75 Statement Mais encore? • En 2 fois! • La soumission d’une instruction SQL s’effectue en 2 phases: – Contrôle des tables et colonnes nécessaires (Metadata) – Exécution de l’instruction GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 76 PreparedStatement « An object that represents a precompiled SQL statement » • L’interface PreparedStatement hérite de Statement et la spécialise en fournissant les méthodes nécessaires à la soumission d’une instruction SQL dynamique. • Un PreparedStatement permet l’exécution d’une instruction SQL paramétrée. – Permet de passer des paramètres liés à un ordre SQL (bind parameters). – Les paramètres sont passés par position. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 77 PreparedStatement Création • La création d’un objet de type PreparedStatement s’effectue à l’aide de votre objet de type Connection par la méthode prepareStatement(String). Connection myConn = DriverManager.getConnection(url, user, password); PreparedStatement stmt = myConn.prepareStatement("Ordre SQL"); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 78 PreparedStatement Manipulation • Une fois un objet de type PreparedStatement instancié, vous pouvez le manipuler : Method summary ResultSet executeQuery(String sql) Executes the given SQL statement, which returns a single ResultSet object int executeUpdate(String sql) Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement void close() Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed void setXXX(int,XXX) Sets the designated parameter to the given Java XXX type value setString(int, String), setDate(int, Date), setDouble(int, double)… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch Source: http://download.oracle.com/javase/6/docs/api/ 79 PreparedStatement Manipulation - exemple … PreparedStatement stmt = myConn.prepareStatement("update employe set salaire = ?"); stmt.setBigDecimal(1, new BigDecimal(5500)); stmt.executeUpdate(); … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 80 PreparedStatement Vs Statement • Il est possible de passer des paramètres à des simples Statement sans utiliser de PrepardStatement. – En concaténant les paramètres aux ordres SQL dans des chaînes de caractères. stmt = MyConn.createStatement(); query = "UPDATE PROJ_TACHE_MAINTENANCE " + "set description = '" + parametre1 + "',proj_projet_id = " + parametre2 + ", proj_etat_id = " + parametre3 + " where id = " + parametre4; • Cette façon de procéder est mauvaise! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 81 Avantages des PreparedStatement par rapport aux Statement • Performance – Il n’est compilé qu’une seule fois même si les paramètres changent gain si la commande SQL est lancée n fois. • Facilité d’utilisation – L’utilisation de variables liées (ou paramètres liés) est plus adéquat. • Portabilité – Par exemple , les setDate() permettent de masquer les différents types de format de dates des BD. • Sécurité – Eviter l’injection de code SQL. • Gestion des caractères spéciaux dans les setString() GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 82 CallableStatement « The interface used to execute SQL stored procedures » • L’interface CallableStatement PreparedStatement. hérite de • Elle fournit les méthodes nécessaires à la soumission d’un sous-programme (fonction, procédure stockée) de votre base de données. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 83 CallableStatement Création • La création d’un objet de type CallableStatement s’effectue à l’aide de votre objet de type Connection par la méthode prepareCall(). Connection myConn = DriverManager.getConnection(url, user, password); CallableStatement proc = myConn.prepareCall ("{call paieSalaire(?)}"); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 84 CallableStatement Appels • Fonction {?= call <procedure‐name>[(<arg1>,<arg2>, ...)]} • Procédure {call <procedure‐name>[(<arg1>,<arg2>, ...)]} GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 85 CallableStatement Manipulation • Une fois un objet de type CallableStatement instancié, vous pouvez le manipuler. Les méthodes importantes sont semblables aux méthodes de l’interface PreparedStatement. … CallableStatement proc = myConn.prepareCall ("{call paieSalaire(?)}"); proc.setLong(1,7622702); proc.execute(); … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 86 Libération des objets • Il est important de libérer les objets de type Statement, PreparedStatement et CallableStatement lorsque vous les avez utilisé. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 87 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 88 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 89 Travaux pratiques • Série 3, exercice 4 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 90 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC – Curseurs statiques – Autres types de curseurs GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 91 Interfaçage relationnel / procédural • Une requête sur une base de données retourne généralement plusieurs lignes. • Le résultat d’une requête depuis un langage procédural doit s’effectuer dans une structure adaptée aux traitements de plusieurs lignes. notion de curseur GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 92 Notion de curseur « Un curseur est une structure pouvant stocker le résultat de requêtes et pouvant le manipuler ce résultat via un langage hôte. » • Un curseur est comme un pointeur parcourant un tableau d'enregistrements, avec lequel on navigue via le langage hôte en extrayant les lignes une à une. • C’est une structure adaptée à la manipulation de données en mémoire provenant d’une base de données. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 93 Types de curseurs (ANSI) • ANSI1 spécifie 4 types de curseurs – – – – 1. Curseurs Curseurs Curseurs Curseurs statiques (lecture seule) en avant seulement dynamiques (lecture / écriture) d’ensemble de valeurs clés (pointeurs) American National Standards Institute. http://www.ansi.org/ GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 94 Les curseurs en Java • Comme tous les langages hôtes, Java propose une structure adaptée aux traitements de plusieurs lignes. – java.sql.ResultSet Pointeur id nom prenom 12 bbb ccc 4 asdf cccc 23 bbb adsf Base de données Instance de ResultSet GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 95 java.sql.ResultSet “A table of data representing a database result set, which is usually generated by executing a statement that queries the database” Source : http://download.oracle.com/javase/6/docs/api/ • Un ResultSet est une interface Java. • Un objet de type ResultSet est instancié et alimenté par le résultat de l’exécution d’un Statement ou PreparedStatement. … Statement stmt = myConn.createStatement(); ResultSet curseurJava = stmt.executeQuery("SELECT id, nom, prenom FROM EMPLOYES "); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 96 ResultSet statique en avant seulement • Un objet de type ResultSet est par défaut non navigable et non modifiable. – Il s’agit donc d’un curseur « statique en avant seulement ». • Ce type de curseur est bien adapté au parcours d’un ensemble de lignes résultant d’une requête SQL (SELECT) pour affichage. Sens de la lecture id nom prenom 12 bbb ccc 4 asdf cccc 23 bbb adsf GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 97 Parcours d’un ResultSet statique en avant seulement • Le parcours d’un ResultSet « statique en avant seulement » s’effectue à l’aide d’une boucle. • La méthode next() charge le prochain enregistrement en retournant true tant qu’il y a des éléments dans le curseur. ème Avant la boucle Après la boucle ème er2 31(instanciation du next() next() (plus d’éléments) next() curseur) … while(curseurJava.next()) { … } id nom prenom 12 bbb ccc 4 asdf cccc 23 bbb adsf GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 98 Récupération de données • Les méthodes getXxx(int) et getXxx(String), où Xxx est le type de la colonne, permettent de récupérer la valeur d’une colonne de l’enregistrement courant. – getXxx(int) avec en paramètre le numéro de la colonne. – getXxx(String) avec en paramètre le nom de la colonne. … while(curseurJava.next()) { System.out.println(curseurJava.getString("nom") + " " + curseurJava.getString("prenom")); } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch id nom prenom 12 bbb ccc 4 asdf cccc 23 bbb adsf 1er tour de la boucle, getString("nom"); 99 Fermeture d’un ResultSet (curseur) • Un curseur se ferme implicitement lorsque le Statement ou PreparedStatement est fermé. • Attention, une bonne pratique consiste à fermer le curseur explicitement! … curseurJava.close(); … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 100 Résumé des classes et interfaces de base JDBC REESE, George. Database Programming with JDBC and Java. USA: O'Reilly, 2000. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 101 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 102 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 103 Travaux pratiques • Série 3, exercice 5 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 104 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC – Curseurs statiques – Autres types de curseurs, navigables et dynamiques GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 105 Autres types de curseurs en Java • Les curseurs « statiques en avant seulement », ResultSet, sont disponibles depuis JDBC 1.0. • Afin de suivre la norme SQL-02 (SQL92) qui propose des curseurs navigables, Java se dote d’une nouvelle version de son API JDBC. – JDBC 2.0 • JDBC 2.0 propose aussi de rendre les curseurs modifiable (dynamiques). – Lecture / écriture. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 106 ResultSet navigable • Un curseur « statique en avant seulement » peut être parcouru seulement du début jusqu’à la fin. • Un curseur navigable peut être parcouru dans tous les sens. id nom prenom 12 bbb ccc 4 asdf cccc 23 bbb adsf GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 107 ResultSet navigable • Un objet de type ResultSet navigable est instancié et alimenté par le résultat de l’exécution d’un Statement ou PreparedStatement. – Une constante de navigation doit être spécifiée lors de la création du Statement ou PreparedStatement. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 108 ResultSet navigable • Il existe 3 constantes de navigation: – ResultSet.TYPE_FORWARD_ONLY • Curseur non navigable (par défaut) – ResultSet.TYPE_SCROLL_INSENSITIVE • Curseur navigable et non sensible aux modifications dans les autres transactions. – ResultSet.TYPE_SCROLL_SENSITIVE • Curseur navigable et sensible aux modifications des les autres transactions. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 109 Sensibilité aux autres transactions • Une curseur ResultSet.TYPE_SCROLL_INSENSITIVE n’est pas mis à jour automatiquement lorsque les données sont modifiées par une autre transaction. • Une curseur ResultSet.TYPE_SCROLL_SENSITIVE est mis à jour automatiquement lorsque les données sont modifiées et validées (commit) par une autre transaction. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 110 Création d’un curseur navigable • La constante de navigation doit être spécifiée lors de la création du Statement ou PreparedStatement. • Exemple: PreparedStatement stmt = myConn.prepareStatement("SELECT a, b, c" + "FROM d WHERE a = ? " + "ORDER BY b, c, a", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 111 Parcours d’un ResultSet navigable • Le parcours d’un ResultSet navigable peut s’effectuer dans une boucle mais aussi à l’aide de méthodes spécifiques dont: – – – – – last() first() isLast() absolute(int position) … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 112 ResultSet modifiable • Un ResultSet modifiable permet de mettre à jour les données d’un ResultSet (en mémoire) et de répliquer ces changements au niveau de la base de données. – Il est donc possible d’effecuter des insertions, des modifications et des suppressions de données. • Afin de rendre un ResultSet modifiable, il est nécessaire de spécifier la constante de modification: – ResultSet.CONCUR_UPDATABLE PreparedStatement stmt = myConn.prepareStatement("SELECT a, b, c" + "FROM d WHERE a = ? " + "ORDER BY b, c, a", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 113 Suppression • La méthode deleteRow() permet de supprimer la ligne courante d’un curseur (pas dans la BD !). – Le commit sur la connexion permettra de terminer la transaction et donc appliquer les changements aux niveaux de notre base de données. … /* se positionner sur la 4ème ligne qui est celle que l’on désire supprimer…*/ curseurJava.absolute(4); curseurJava.deleteRow(); myConn.commit(); – Attention, il est toujours possible d’exécuter une instruction DELETE à l’aide de executeUpdate(). Cette fonctionnalité deleteRow() doit être utilisée à bon escient. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 114 Modification • Afin d’effectuer des mises à jour dans un curseur modifiable, il est nécessaire de suivre quelques points: 1. 2. 3. 4. se positionner sur la ligne à mettre à jour; mise à jour des valeurs; propagation dans la base de données; validation ou annulation de la transaction. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 115 Modification • Exemple … /* 1. se positionner sur la ligne que l’on désire modifier…*/ curseurJava.absolute(4); /* 2. mise à jour des valeurs */ curseurJava.updateString("a", "Toto"); /* 3. propagation dans la base de données */ curseurJava.updateRow(); /* 4. validation de la transaction */ myConn.commit(); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 116 Insertion • Afin d’insérer des données dans un curseur modifiable, il est nécessaire de suivre quelques points: 1. 2. 3. 4. se positionner afin de pouvoir insérer des données ; mise à jour des valeurs ; propagation dans la base de données ; validation ou annulation de la transaction. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 117 Insertion • Exemple … /* 1. se positionner afin de pouvoir insérer des données */ curseurJava.moveToInsertRow(); /* 2. mise à jour des valeurs curseurJava.updateString("a", curseurJava.updateString("b", curseurJava.updateString("c", */ 546); "Test"); "Zoé"); /* 3. propagation dans la base de données */ curseurJava.insertRow(); /* 4. validation de la transaction */ myConn.commit(); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 118 Verrous? • Nous verrons la problématique « multiutilisateurs » dans un prochain chapitre. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 119 Limitations • Impossible de rendre un ResultSet updatable dans plusieurs cas: – Jointures – Requêtes de groupe – Colonne mise à jour non présente dans la liste des colonnes (au niveau SELECT ) – … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 120 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 121 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 122 Travaux pratiques • Série 3, exercices 6 et 7 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 123 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC – Curseurs statiques – Autres types de curseurs, RowSet GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 124 RowSet • RowSet est une interface héritant de ResultSet. • Cette structure est disponible dans le package javax.sql. • Un RowSet permet de manipuler des données provenant d’une base de données ou d’une autre source de données tabulaire. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 125 Avantage d’un RowSet • Il existe 2 avantages à l’utilisation d’un RowSet par rapport à un ResultSet : – Un RowSet est un Javabean. – Un RowSet permet de manipuler des données en mode « connecté » et en mode « déconnecté »… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 126 RowSet Un JavaBean ? • JavaBean? “The JavaBeans™ architecture is based on a component model which enables developers to create software units called components. Components are self-contained, reusable software units that can be visually assembled into composite components, applets, applications, and servlets using visual application builder tools. JavaBean components are known as beans.” http://download.oracle.com/javase/tutorial/javabeans/whatis/index.html GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 127 JavaBean • Un JavaBean est une classe Java qui doit : – posséder un constructeur par défaut ; – encapsuler l’ensemble ses propriétés privées à l’aide accesseurs respectant la convention get ou set suivi du nom de la propriété avec la première lettre en majuscule ; – être Serializable ; – peut contenir des listeners spécifiques. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 128 Exemple de JavaBean Source : http://fr.academic.ru/dic.nsf/frwiki/850799 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 129 RowSet connecté et déconnecté • Différentes implémentations des RowSet sont disponibles pour répondre aux multiples besoins des applications de gestion : – RowSet connectés ; – RowSet déconnectés. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 130 RowSet connecté et déconnecté • Un RowSet connecté utilise une connexion JDBC tout au long de son cycle de vie… – Un JdbcRowSet est un RowSet connecté. C'est une enveloppe légère (wrapper) autour d'un ResultSet permettant d’utiliser un RowSet comme un bean java. • Un RowSet déconnecté ?!? Pour quoi faire? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 131 RowSet déconnecté • Comment fonctionne déconnecté? généralement un RowSet 1. demande une connexion pour aller “récupérer” les données ; 2. se déconnecte (généralement); • plus besoin de réseau, de driver JDBC… 3. le client l’utilise localement (suppression, mise à jour, insertion de données). Le RowSet maintient des méta données sur les données qu’il contient et sur l’état du RowSet. 4. le client se reconnecte et va mettre à jour les données de la base de données. • en pensant aux conflits… (vous n’êtes pas seul sur votre BD !) GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 132 Les différents types de RowSet déconnectés • RowSet est l’interface mère. • Un CachedRowSet est un RowSet déconnecté et stocke ses données en mémoire. – adapté aux petites quantités de données ; – adapté aux clients légers tels que les PDA. • Un WebRowSet est un RowSet déconnecté. – adapté aux applications web; – adapté à la lecture de fichiers XML ou de flux de données. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 133 Les différents types de RowSet déconnectés • Un FilteredRowSet est un RowSet déconnecté. – permet de filtrer les données présentées à l'utilisateur grâce à l’interface Predicate. • Un JoinRowSet est un RowSet déconnecté. – permet d’effectuer des jointures avec des objets implémentant Joinable. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 134 Comparaison avec les ResultSet • RowSet extends the ResultSet interface, so you can use the same as using the ResultSet RowSet. • RowSet extends the ResultSet interface and functionality than the ResultSet so more richer. • By default, all RowSet objects are scrollable and updatable. The ResultSet is rolling forward only and read-only. • RowSet can be non-links, and ResultSet is connected. Therefore, interfaces can be offline use CacheRowSet data. Source : http://www.softcov.com/programming-and-testing/teach-you-to-use-java-in-the-rowset.html GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 135 Comparaison avec les ResultSet • RowSet interfaces added to the JavaBeans component model of the JDBC API support. Rowset can be used as visual development environment for JavaBeans Bean components. • CacheRowSet can be serialized. • RowSet and ResultSet represents the data rows, attributes, and related methods of operation. • He thinks, should tend to RowSet as something unrelated to the database, it is only a representative of rows of data objects, and ResultSet is a close link with the database stuff. Source : http://www.softcov.com/programming-and-testing/teach-you-to-use-java-in-the-rowset.html GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 136 Exemple d’utilisation d’un RowSet • Un (une) volontaire pour commenter ce code... CachedRowSetImpl crs = new CachedRowSetImpl(); crs.setUrl(url); crs.setUsername(user); crs.setPassword(password); crs.setType(ResultSet.TYPE_SCROLL_INSENSITIVE); crs.setConcurrency(ResultSet.CONCUR_UPDATABLE); crs.setCommand("SELECT * FROM AAAA"); crs.execute(); … // Suite slide suivant GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 137 Exemple d’utilisation d’un RowSet • Un (une) volontaire pour commenter ce code... // voir slide précédent while (crs.next()) { String name = crs.getString(1); int id = crs.getInt(2); Clob comment = crs.getClob(3); short dept = crs.getShort(4); System.out.println(name + " " + id + " " + comment + " " + dept); } crs.setTableName("AAAA"); crs.updateShort(3, 58); crs.updateInt(4, 150000); crs.updateRow(); crs.acceptChanges(); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 138 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 139 RowSet, gestion des conflits Que se passe t‐il lorsqu’un autre utilisateur effectue des modifications sur les données que je traite dans mon RowSet déconnecté ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 140 RowSet, gestion des conflits • Le traitement des conflits varie d’une implémentation à une autre du RowSet déconnecté. • Généralement, le blocage optimiste est utilisé. – En cas de conflit, acceptChanges() lève une exception javax.sql.rowset.spi.SyncProviderException. – A vous de gérer les conflits selon vos cas (règles de gestion) • L’utilisation d’un listener peut être intéressant pour « écouter » si des changements sont effectués dans votre RowSet. – rowChanged() – rowSetChanged() GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 141 RowSet, exemple de gestion des conflits … crs.acceptChanges(); }catch(SyncProviderException spe) { SyncResolver resolver = spe.getSyncResolver(); int myStatus = resolver.getStatus(); switch(myStatus){ case SyncResolver.INSERT_ROW_CONFLICT : System.out.println("Conflit à l’insertion"); break; case SyncResolver.DELETE_ROW_CONFLICT : System.out.println("Conflit à la suppression"); break; case SyncResolver.UPDATE_ROW_CONFLICT : System.out.println("Conflit à la mise à jour"); break; } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 142 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 143 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 144 Travaux pratiques • Série 3, exercice 8 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 145 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 146 Rappel: transaction = concept fondamental Toutes applications manipulant un grand ensemble de données, et exploitées dans un environnement multi-utilisateurs se doit d'être transactionnelle. • Sans système transactionnel, la base de données risque d’être incohérente – Rappel des missions du SII !! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 147 Rappel: définition d'une transaction • Une transaction doit vérifier les propriétés – – – – (A) Atomicité (C) Consistance (I) Isolation (D) Durabilité • Moyen mnémotechnique -> ACID GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 148 Rappel: ACID - Atomicité • L'ensemble des opérations d'une transaction apparaît comme une seule opération atomique • Soit toutes les opérations sont validées ou toutes annulées (tout ou rien) • Cf exemple : débit/crédit GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 149 Rappel: ACID Consistance (cohérence) • L'exécution de la transaction fait passer la base de données d'un état consistant à un autre état consistant (ceci en accord avec l’ensemble de la base de données et de ses contraintes). • Un état incohérent ne doit pas être possible ! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 150 Rappel: ACID Isolation • Chaque transaction est indépendante des autres transactions concurrentes. – Semblable à une exécution en série des transactions. On parle de « sérialisation » des transactions. • Les concurrences sont parfaitement contrôlées GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 151 Rappel: ACID Durabilité • C'est la persistance des mises à jour d'une transaction validée. • Les effets d'une transaction validée sont durables et permanents, quelques soient les problèmes logiciels ou matériels, notamment après la fin de la transaction. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 152 Rappel: principales utilisations des transactions • Traitement des opérations sémantiquement liées – Doit garantir le "tout ou rien" • Exemple : débit crédit, • Soit toutes les opérations sont validées soit annulées. • Gestion des concurrences • Reprise sur pannes – Acquisition de verrous sur les enregistrements traités, empêchant une utilisation malencontreuse des données – Utilisation du système transactionnel pour la reconstitution d'un état cohérent de la base au redémarrage d'un système après une panne, quel que soit le type de panne. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 153 Rappel: commandes SQL • COMMIT – Validation de la transaction • ROLLBACK – Annulation de la transaction COMMIT ; Etat cohérent de la base de données i Etat cohérent de la base de données j ROLLBACK ; GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 154 JDBC et les transactions • Les transactions sont contrôlées avec les méthodes commit() et rollback() de la classe Connection. • Ne pas oublier de prendre en considération les propriétés ACID d’une transaction! … Connection myConn = DriverManager.getConnection( jdbc:oracle:thin:@NA-PO:1521:orcli1", "baudet", "*****"); myConn.setAutocommit(false); … myConn.commit(); … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 155 Support des transactions par JDBC • Certains drivers ne supportent pas les transactions. • Vous pouvez vérifier si votre driver supporte les transactions en utilisant la classe DatabaseMetaData. … DatabaseMetaData dbMetaData = myConn.getMetaData(); boolean supporteLesTransactions; supporteLesTransactions = dbMetaData.supportTransactions(); … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 156 AutoCommit • Lors de la création d’une connexion, le mode autocommit est activé. • Chaque instruction est donc considérée comme une transaction! • Ce principe est totalement contraire à une bonne gestion des transactions! • Une bonne pratique consiste à désactiver l’automyConn.setAutoCommit(false); commit. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 157 Rappel: découpage de la transaction Oracle • Découpage de la transaction en insérant des points de repère (savepoint). SAVEPOINT point ; • Possibilité d'annulation un sous-ensemble d'opérations de la transaction. ROLLBACK [TO SAVEPOINT] point ; GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 158 Rappel: exemple instruction 1 ; SAVEPOINT toto ; instruction 2 ; Annulation des instructions 2 et 3 Ne termine pas la transaction !!! instruction 3 IF test THEN ROLLBACK TO SAVEPOINT toto ; END IF ; instruction 4 ; Annule la totalité de la transaction et lâche les verrous ! instruction 5 ROLLBACK ; GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 159 JDBC et les Savepoint • Comme pour SQL sans JDBC, il est possible d’utiliser des Savepoints depuis JDBC 3.0. • L’interface Savepoint permet de découper les transactions. instruction 1; GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch Savepoint sp1 = myConn.setSavepoint(toto); instruction 2; instruction 3; if (test) { myConn.rollback(sp1); } instruction 4; instruction 5; myConn.rollback(); 160 Java Transaction API (JTA) • Si vous voulez aller plus loin… JTA… • Ok, mais pas cette année… – On l’utilisera avec un serveur d’applications l’année prochaine. “Java Transaction API (JTA) specifies standard Java interfaces between a transaction manager and the parties involved in a distributed transaction system: the resource manager, the application server, and the transactional applications.“ Source : Oracle GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 161 Où finir la transaction ? • ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 162 162 Où finir la transaction ? • Pas après chaque commande SQL ! • Pour l’instant dans votre main (java) de façon transactionnelle. • L’année prochaine dans une couche logicielle spécifique. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 163 163 Questions? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 164 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 165 JDBC et les verrous • JDBC n’est qu’une API permettant d’utiliser votre base de données. • Vous pouvez (et devez!) donc utiliser les verrous de votre DB de façon standard. • Exemple: Stmt = myConn.createStatement(); … // Création de la requête query = "SELECT * FROM PROJ_PROJET FOR UPDATE"; //sauvegarder et relâcher le verrou myConn.commit(); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 166 Rappel: niveaux d'isolation • Le niveau d'isolation indique le comportement de la transaction par rapport aux autres transactions. • Plus le niveau d'isolation est faible, plus les autres transactions peuvent agir sur les données concernées par la première. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 167 Rappel: niveaux d'isolation • READ UNCOMITED – Les verrous ne sont pas maintenus entre les instructions Aucune isolation, toutes les erreurs possibles. • READ COMMITED – Verrou exclusif maintenus jusqu'à la fin de la transaction. Les verrous partagés ne sont pas maintenus. Prévient l'apparition de l'erreur de lecture inconsistante, autres erreurs possibles GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 168 Rappel: niveaux d'isolations • REPETABLE READ – Les verrous partagés sont maintenus. – Les verrous exclusifs ne sont pas maintenus Erreur de lecture inconsistante et non répétitive impossible, erreur de lignes fantômes possibles. • SERIALIZABLE – Tous les verrous sont maintenus Pas de problème possible GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 169 JDBC et les niveaux d’isolation • JDBC prévois les mêmes niveaux d’isolation! – – – – – TRANSACTION_NONE TRANSACTION_READ_COMMITTED TRANSACTION_READ_UNCOMMITTED TRANSACTION_REPEATABLE_READ TRANSACTION_SERIALIZABLE • Ces niveaux d’isolations sont définis par des constantes sur la classe Connection. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Connection myConn; … myConn.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE); … Cedric.Baudet@he‐arc.ch 170 Fermeture des connexions JDBC • Une connexion à la base de données via JDBC est fermée automatiquement via le garbage collector. • Toutefois, afin de libérer les ressources dès que possible, il est nécessaire de fermer correctement les connexions. Connection myConn; … myConn.close(); … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 171 Fermeture des connexions JDBC • Afin de s’assurer de la fermeture correcte des connexions, une bonne pratique consiste à fermer les connexions dans la partie finally des exceptions. Connection myConn; … } finally { try { myConn.close(); } catch (SQLException sqlex) { System.out.println("Erreur SQL:" + sqlex); } } } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 172 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 173 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 174 Travaux pratiques • Série 3, exercice 9 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 175 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 176 Réutilisation des connexions • Réutilisation… • Création d’une classe de connexion. – OracleConnection • Quelles sont ses propriétés et ses méthodes? – Avis des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 177 Travaux pratiques • Série 3, exercice 10 – 15 minutes GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 178 Système de cache et de pool • Un système de cache ou de pool permet d’éviter de créer ou de supprimer des objets dans le but d’améliorer les performances d’un système. • On utilise des systèmes de cache ou pool pour: – réutiliser des connexions à des bases de données; – réutiliser des objets en mémoire; • objets métiers, servlets, etc… – réutiliser des threads; – etc… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 179 Pool Vs Cache 1/2 • On parle de pool pour une collection stateless d’objets. – – – – Pool de connexions Pool de threads Pool de servlets … • On parle de cache pour une collection stateful d’objets. – Entity Bean – Session Bean stateful – … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 180 Pool Vs Cache 2/2 • On utilise un pool lorsque l’on veut utiliser un objet de ce pool quel que soit cet objet. (instance de classe!) – Je désire obtenir une instance d’une servlet. N’importe laquelle. • On utilise un cache lorsque l’on veut utiliser un objet particulier. – Le bean xyz que j’ai dans mon cache. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 181 Pool de connexions, contexte • Une application de gestion effectue de nombreux appels à des sources de données. Il s’agit généralement de bases de données relationnelles. • De part l’architecture client/serveur et le principe de dialogue à sessions, de nombreuses requêtes SQL transitent sur le réseau. • Sur le web (entre autre) et dans le cas d’utilisation de sessions courtes, de nombreuses connexions et déconnexions s’effectuent dans les applications. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 182 Pool de connexions • Un pool de connexions: – est utilisé pour éviter la création de nombreuses connexions à une source de données; – permet d’améliorer les performances du système. • Le chargement des drivers JDBC est gourmand en ressources. • Une application n’utilisant pas de pool de connexions créée une connexion à la base de données à chaque demande de connexion. • Une application utilisant un pool permet de réutiliser des connexions non utilisées. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 183 Pool de connexions Schéma simplifié Instances d’applications Système de pooling Source de données GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 184 Pool de connexion, utilisations • Il y a plusieurs façons d’utiliser des pools de connexions: – – – – implémentation manuelle ; utilisation du pool natif d’un driver JDBC ; utilisation de librairies ; utilisation du pool d’un serveur d’applications. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 185 Pool de connexion, implémentation manuelle • Il s’agit d’implémenter un pool de connexions avec de simples classes Java. – sans librairie, sans pool de serveur d’applications. • Avantages désavantages: – vous réinventez la roue ; – vous pouvez développer le comportement exact que vous attendez ; – plus compliqué que d’utiliser des pools déjà développés ; • Ceci n’est pas conseillé pour une application à mettre en production! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 186 Pool de connexion, pool natif d’un driver JDBC • Il s’agit d’utiliser un pool de connexions « livré » par le driver JDBC de votre base de données. • Avantages désavantages: – Utilisation d’un pool fiable ; – Fort couplage avec la base de données. • Ceci n’est pas conseillé pour une application à mettre en production! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 187 Pool de connexion, utilisation de librairies • Il existe des frameworks ou des librairies permettant d’utiliser des pools de connexions. • Citons pour JDBC: – – – – Apache DBCP; SourceForge c3p0 GNU Library; SourceForge Proxool; etc… • Avantages désavantages: – utilisation d’une librairie éprouvée; – facile d’utilisation; – vous êtes lié avec le fournisseur de la librairie. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 188 Pool de connexion, utilisation du pool du serveur d’applications • Les serveurs d’applications possèdent un pool de connexions. – Il est possible d’utiliser ce pool de connexions dans vos applications. • Avantages désavantages: – fonctionnement éprouvé; – facile d’utilisation; – vous êtes lié avec le fournisseur; • Quoique… Vous utilisez JNDI donc vous pouvez changer en tout temps. – ne peut être testé qu’avec le serveur d’applications. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 189 Références • Site de Sun – http://java.sun.com/javase/6/docs/technotes/guides/jdbc/in dex.html – http://java.sun.com/developer/onlineTraining/Programming/ JDCBook/conpool.html • Site de Apache – http://commons.apache.org/dbcp/ • Site de c3p0 • http://sourceforge.net/projects/c3p0 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 190 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 191 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 192 Travaux pratiques • Série 3, exercice 11 – A terminer pour le prochain cours… – Attention : Veuillez me faire parvenir votre diagramme de classe et / ou votre pseudo code (avec des explications si nécessaire) en format PDF avant le cours de la semaine prochaine via Cyberlearn. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 193 Connexion à la base de données • Il existe deux méthodes pour se connecter à une base de données : – DriverManager – DataSource (javax.sql) GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 194 Rappel : Connexion avec la méthode « DriverManager » • DriverManager est utilisé depuis JDBC 1.0 et est largement répandu dans les entreprises. C’est le mécanisme original permettant de se connecter à une base de données en Java. try{ … Connection myConn = DriverManager.getConnection(url, user, password); … catch(SQLException sqle) { System.out.println("Erreur SQL:" + sqle); } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 195 DataSource • DataSource existe depuis JDBC 2.0 et doit être préféré à la méthode « DriverManager » car DataSource offre quelques avantages : – chargement explicite de driver non nécessaire ; – facilité de maintenance ; – capacités étendues. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 196 DataSource : chargement explicite de driver non nécessaire • Pas besoin de charger explicitement le driver de base de données depuis JDBC 2.0. • Pour rappel : les drivers doivent être chargés explicitement à l’aide de DriverManager jusqu’à JDBC 4.0 (Java SE 6). GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 197 DataSource : facilité de maintenance • Vous pouvez configurer une DataSource sur un serveur d’application et la référencer dans votre code source via JNDI. • Ceci vous permettra d’effectuer des changements de propriétés de votre DataSource sans changer votre code source. DataSource Application cliente Serveur d’applications GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 198 DataSource : capacités étendues • Les connexions obtenues via une DataSource vous permettent d’utiliser : – des pools de connexions ; – des transactions distribuées. Instances d’applications Système de pooling Source de données GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 199 Implémentation des DataSources • Les fournisseurs de drivers de base de données proposent des DataSources. • Exemples : – – – – OracleDataSource chez Oracle ; BasicDataSource chez Apace (DBCP) ; SimpleDataSource, PoolingDataSource chez PostgreSQL ; … • Lorsqu’un objet de type DataSource est instancié, vous pouvez obtenir une connexion à une base de données. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 200 DataSource : exemple try{ DataSource ds = new OracleDataSource(); /* Paramétrer la DataSource afin les infos de connexion à la BD */ ((OracleDataSource)ds).setDriverType("thin"); ((OracleDataSource)ds).setServerName("ne-ege-leto.ig.he-arc.ch"); ((OracleDataSource)ds).setDatabaseName("ens"); ((OracleDataSource)ds).setPortNumber(1521); ((OracleDataSource)ds).setUser("prof_baudet"); ((OracleDataSource)ds).setPassword("*****"); … Connection myConn = ds.getConnection(); myConn.setAutoCommit(false); … catch(SQLException sqle) { System.out.println("Erreur SQL:" + sqle); } GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 201 DataSource et pool de connexion 1/2 • Rappel : un pool de connexions: – est utilisé pour éviter la création de nombreuses connexions à une source de données; – permet d’améliorer les performances du système. • Le chargement des drivers JDBC est gourmand en ressources. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 202 DataSource et pool de connexion 2/2 • Les connexions effectuées via une DataSource peuvent générer un pool de connexion. – De nombreuses librairies et serveur d’applications livrent des pools de connexion. C’est le cas par exemple de DBCP de Apache, d’Oracle… • Marche à suivre : 1. création de la DataSource ; 2. création du pool de connexions ; 3. utilisation des connexions du pool. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 203 Exemple de DataSource et de pooling de connexions • Avec les libraires d’Oracle… DataSource dataSource = null; Connection myConn = null; PooledConnection poolConn = null; try{ dataSource = new OracleConnectionPoolDataSource (); /* Paramétrer la DataSource avec les infos de connexion à la BD */ … // Création du pool de connexions poolConn = ((OracleConnectionPoolDataSource )dataSource).getPooledConnection(); // Récupération d’une connexion du pool myConn = poolConn.getConnection(); … catch(SQLException sqle)… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 204 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 205 Eléments clés des étudiants • Il y a 2 méthodes pour se connecter à une BD avec Java: – DriverManager – DataSource • Avec DataSource, on a la possibilité d’obtenir directement un pool de connexions. • DataSource est un DriverManager plus évolué ! • Le DataSource peut ne pas être configuré directement dans le code de l’application. – En utilisant JNDI ! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 206 Travaux pratiques • Série 3, exercice 12 (a, b et c) – DataSource et pool de connexions GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 207 Plan de la présentation • SQL interactif<> SQL programmé • Application Programming Interface pour bases de données • Connexion aux bases de données avec un langage hôte • Statements et curseurs • Transactions et sessions • Verrous • Pools de connexions • Améliorations et limites du JDBC GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 208 Objectifs • Objectifs du module – comprendre et mettre en œuvre les services applicatifs fondamentaux; – développer, déployer et configurer des composants métiers sur un serveur d’applications. • Objectifs de l’unité d’enseignement « Dialogue avec la base de données » – comprendre les différences entre l'architecture "serveur de fichiers" et "client/serveur"; – maîtriser le principe du dialogue à sessions; – comprendre la notion de concurrence; – savoir manipuler des données à l'aide d'un langage hôte. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 209 Concaténation de chaînes de caractères • Les compilateurs permettent l’addition de « String » avec l’opérateur +. • Cet exemple est traduit par le compilateur en: String toto = new String("Au revoir"); String titi := toto + " Nicolas"; String titi = new StringBuilder("Au revoir") .append(" Nicolas").toString(); GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 210 Problème potentiel • Exemple de mauvaise utilisation : public String createStringFromArray(Object[] array) { String _ret = new String(); for (int i = 0; i < array.length; i++) _ret += array[i]; return _ret; } • Quel est le problème potentiel ? Source : http://gfx.developpez.com/tutoriel/java/strings GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 211 Problème potentiel • Le compilateur va traduire _ret +=array[i] par : – _ret = _ret + array[i]. • Puis le compilateur va traduire par: – ret = new StringBuffer(_ret).append(array[i]).toString(); • Cette ligne sera exécutée à chaque passage dans la boucle ! Attention, l’instanciation d’objet est coûteuse. • il faudra donc remplacer les opérateurs de concaténation de chaînes de caractères par un StringBuffer(). Source : http://gfx.developpez.com/tutoriel/java/strings GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 212 Solution • Solution proposée : public String createStringFromArray(Object[] array) { StringBuffer _buf = new StringBuffer(); for (int i = 0; i < array.length; i++) _buf.append(array[i]); return _buf.toString(); } Source : http://gfx.developpez.com/tutoriel/java/strings GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 213 StringBuilder et création de query JDBC • Afin d’améliorer les temps de réponse pour la concaténation des « String », l’utilisation de String.append() de StringBuilder peut être intéressante. • Lors de la création de queries complexes, StringBuilder peut donc vous faire gagner du temps. • La création dynamique de clause « where » dans une boucle peut se révéler coûteuse… • StringBuilder est une classe de la Java SE 5.0. – StringBuffer peut aussi être utilisée. Cette contrairement à StringBuilder, est synchronisée. classe, GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 214 Les APIs de plus haut niveau 1/2 • L’utilisation de JDBC peut être critiquée. • Des APIs de plus haut niveau permettent d’accroître la productivité. • Ces APIs encapsulent JDBC. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 215 Les APIs de plus haut niveau 2/2 • Ces APIs simplifient: – – – – le chargement des drivers JDBC; la création des connexions; le chargement et l’exécution des requêtes; l’exploitation des résultats (ResultSet) • Citons: – DbUtils, Apache Jakarta Commons GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 216 Pré-compilation ? • Le développeur ne peut détecter les erreurs dans les requêtes SQL qu’à l’exécution du programme… – C’est souvent trop tard… • Deux solutions sont possibles pour améliorer cette situation : – SQLJ – Tests unitaires GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 217 SQLj • L’idée est d’intégrer les clauses SQL directement dans Java préfixé par un code spécifique. • L’avantage réside dans l’étape de pré-compilation qui permet au développeur de directement voir les problèmes de compilation de code SQL. – Vérification de la syntaxe SQL – Génération de classes Java (Connection, ResultSet…) • SQLj est peu utilisé en entreprise… Il s’agit pourtant d’un standard ISO. GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 218 SQLj • Exemple : import java.sql.*; import sqlj.runtime.*; … try { #sql (UPDATE personne SET salaire = salaire + 10); } catch (SQLException e){…} … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 219 SQLj comparaison de code JDBC - SQLj int n = 17999; String c = "chaine a"; #sql (INSERT into EMPLOYE (:n,:c)); … SQLj int n = 17999; JDBC String c = "chaine a"; Statement stmt = conn.preparedStatement("INSERT into EMPLOYE values (?,? )"); stmt.setInt(1,n); stmt.setString(2,c); stmt.execute(); stmt.close();… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 220 JDBC, SQLj, et l’avenir? • JDBC est considéré comme fastidieux. – JDBC n’est que la « prise » et devrait avoir une surcouche. • Malheureusement SQLj n’est que peu utilisé en entreprise… • D’autres solutions sont actuellement préférées (qui ne pré-compilent pas…). – Java Data Object – Java Persistance API – … GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 221 Tests unitaires • Dans un bon processus de développement, il faut prévoir d’effectuer des tests unitaires. • Ces tests permettent de « lever » les erreurs avant une mise en production d’une application. • Il faudra finir votre formation bachelor pour avoir plus d’informations sur ce thème passionnant… GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 222 N’oubliez pas… • Utilisez le bon driver • Fermez les Statements… connexions, les resultSet, les • Optimisez à l’aide de pools de connexions • Gérez vos transactions (setAutoCommit(false)), les verrous… • Ecrivez des bonnes requêtes SQL ! GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 223 Questions ? GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 224 Eléments clés des étudiants GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 225 Travaux pratiques • Série 3, exercice 13 GES-DP130227-633-2.2_Dialogue_BD_3 cba-dernière mise à jour : 03.06.2013 Cedric.Baudet@he‐arc.ch 226
