Bases de données Multimédia Oracle Objet-relationnel et JBDC Oracle Objet-relationnel et JBDC JDBC pour les objets Rappel des bases de jdbc API Oracle (jdbc – sql) Mappings des objets Gestion des références Gestion des collections Les outils : JPublisher BD Multimédia http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html Contenu du paquetage java.sql de JDBC (3.0) BD Multimédia java.sql.Driver java.sql.Connection Pilotes JDBC pour la connexion aux sources de données SQL java.sql.Statement java.sql.PreparedStatement java.sql.CallableStatement Construction d'ordres SQL java.sql.ResultSet Gestion des résultats des requêtes java.sql.DriverManager (Classe) Gestion des pilotes de connexion java.sql.SQLException (Classe) Gestion des erreurs SQL java.sql.DataBaseMetaData java.sql.ResultSetMetaData Gestion des méta-informations (description de la base de donnée, des tables, etc.) Java.sql.SavePoint Gestion des transactions. BD Multimédia 3 Oracle Objet-relationnel et JBDC Rappel des bases 4 Oracle Objet-relationnel et JBDC Rappel des bases Structure d'un programme import java.sql.*; import oracle.jdbc.driver.*; public class JDBCTest { public static void main(String args[]) throws SQLException {try { //Chargement d'un pilote JBDC // Création d'une connexion // Ordres SQL } catch (SQLException e) {System.out.println("Erreur" + e;} }} BD Multimédia 2 Oracle Objet-relationnel et JBDC Rappel des bases – Interfaces/Classes Oracle 11g ojbdc6.jar (avec jdk6, jdk7 et jdk8) Oracle 12c : ojdbc7.jar (avec jdk7 et jdk8) Ces paquetages sont inclus dans la plate-forme Java Standard Edition (Java SE) . 1 API spécifiques JBDC Oracle java.sql (gestion des connexions, transmission des requêtes et traitement des résultats) javax.sql (côté serveur) Oracle propose également une API pour jdbc Oracle Objet-relationnel et JBDC Rappel des bases JDBC fournit un accès à des données de différentes sources ( bases de données) à partir d’un programme java. L'API JDBC comporte des classes et des interfaces dans les paquetages BD Multimédia JDBC (Java DataBase Connectivity) Connexion à une base Utilisation de la classe DriverManager Chargement du pilote Mise en place de la connexion Instructions DriverManager.registerDriver(new oracle.jdbc.driver.OrcaleDriver()); Connection conn=DriverManager.getConnection(...); Connexion sur Butor 5 (si l’instance de la base est ensb2017) jdbc:oracle:thin:@butor:1521:ensb2017 BD Multimédia 6 1 Oracle Objet-relationnel et JBDC Rappel des bases Oracle Objet-relationnel et JBDC Rappel des bases Méthodes de l'interface Connection createStatement Création d'un objet pour recevoir un ordre SQL non paramétré prepareStatement(String) Pré-compilation d'un ordre SQL avec paramètres prepareCall(String) Appel d'une procédure cataloguée void setAutoCommit(boolean) Fixe la validation automatique void commit() Valide la transaction void rollBack() Invalide la transaction void close() Ferme la connexion BD Multimédia BD Multimédia setInt, setString, ... Il existe les méthodes inverses, pour récupérer les valeurs getXXX getInt, getString, ... Et des méthodes Méthodes de l'interface Statement ResulSet executeQuery(String) Exécute une requête et renvoie un ensemble de lignes int executeUpdate(String) Exécute une instruction SQL (insert, update ou delete) et renvoie le nombre de lignes traitées. boolean execute(String) Exécute une instruction SQL. Connection getConnexion() Renvoie l'objet de la connexion. void setMaxRows(int) Fixe le nombre max d'enregistrements à extraire int getUpdateCount() Nombre de lignes traitées par l'ordre SQL (ou –1) setXXX (num_param, valeur) updateXXX depuis java vers Oracle. Mise à jour de la base via un curseur java. void close() BD Multimédia 9 Statement st=conn.createStatement(); st.execute("INSERT INTO etudiant VALUES('123','Dupont,'Paul')"); ResultSet res= st.executeQuery("SELECT * FROM etudiant"); Uniquement du début à la fin par next() Il est possible de le rendre navigable Parcours : boolean next() Fermeture du curseur : void close Récupération des valeurs : getXXX(int) Modification de l'enregistrement : updateXXX(...) Métadonnées : ResultSetMetaData getMetaData() BD Multimédia Un curseur de type ResultSet est par défaut ni navigable, ni modifiable. Extraction des données du résultat (pour ResultSet) 10 Oracle Objet-relationnel et JBDC Rappel des bases Exemples Ferme l'état BD Multimédia Oracle Objet-relationnel et JBDC Rappel des bases 8 Oracle Objet-relationnel et JBDC Rappel des bases Quand un état paramétré est créé, les paramètres sont insérés par les méthodes génériques Statement : pour les ordres SQL statiques. Cet état est construit par la méthode createStatement appliquée à la connexion. PreparedStatement pour les ordres paramétrés SQL, construits par prepareStatement sur la connexion. CallableStatement pour les procédures ou fonctions cataloguées PL/SQL, C, java, etc., construit par la méthode prepareCall sur la connexion. 7 Oracle Objet-relationnel et JBDC Rappel des bases Interfaces pour l'encapsulation d'ordres SQL en Java Déplacement avant, arrière, depuis le début, la fin ou la position courante Et modifiable Création d’un curseur navigable et modifiable La base peut être modifiée par le curseur. Statement createStatement (int typeCur, int modifCur) 11 ResultSet.TYPE_SCROLL_INSENSITIVE (navigable, non sensible aux modifications) ResultSet.TYPE_SCROLL_SENSITIVE (navigable, sensible aux modifications) BD Multimédia 12 2 Oracle Objet-relationnel et JBDC Rappel des bases Oracle Objet-relationnel et JBDC Rappel des bases Un curseur modifiable permet de mettre à jour la base de données Il existe un ensemble de méthodes pour parcourir un curseur navigable. Modification de colonnes, suppressions et insertions d'enregistrements. Valeurs du 2ème paramètre de createStatement setFetchDirection, getFetchDirection beforeFirst, afterLast, previous, next, last, isBeforeFirst, isAfterLast, isFirst, isLast, absolute(int), relative(int) ResultSet.CONCUR_READ_ONLY, ResultSet.CONCUR_UPDATABLE Pour utiliser un curseur de type modifiable, il ne faut avoir ni de jointure, ni de regroupement dans la requête SELECT. Méthodes de l ’interface « Statement » int getResultSetType() int getResultSetConcurrency() BD Multimédia Les instructions paramétrées : interface PreparedStatement int getType() Renseigne sur la navigabilité d'un curseur donné int getConcurrency() Renseigne sur le caractère modifiable d'un curseur donné void deleteRow() Supprime l'enregistrement en cours void updateRow() Modifie la table avec l'enregistrement courant. void moveToInsertRow() Déplace le curseur vers un nouvel enregistrement. void insertRow() Insère dans la table l'enregistrement courant void moveToCurrentRow() Retour à l'enregistrement courant (après insertion par exemple) Les paramètres sont indiqués par le symbole ? Les valeurs sont affectées par les méthodes setXXX Les méthodes de l'interface : ResultSet executeQuery(), int executeUpdate () pour INSERT, UPDATE ou DELETE -> nombre de lignes traitées boolean execute() void close() BD Multimédia 15 Oracle Objet-relationnel et JBDC Rappel des bases PreparedStatement pst=conn.prepareStatement("SELECT * FROM etudiant where idE=? "); pst.setInt(1,100); ResultSet res= pst.executeQuery(); Appel de sous-programmes Extraction des données du résultat (pour ResultSet) Parcours : boolean next() Fermeture du curseur : void close Récupération des valeurs : getXXX(int) L'interface CallableStatement permet d'appeler des sous-programmes (fonctions ou procédures PL/SQL, java, C, etc.) Création des états par prepareCall CallableStatement prepareCall(String) Paramètre de la méthode BD Multimédia 16 Oracle Objet-relationnel et JBDC Rappel des bases Exemple 14 Oracle Objet-relationnel et JBDC Rappel des bases Méthodes applicables aux curseurs modifiables de l’interface « ResultSet » BD Multimédia Renseigne sur le caractère modifiable des curseurs d'un état BD Multimédia 13 Oracle Objet-relationnel et JBDC Rappel des bases Renseigne sur la navigabilité des curseurs d'un état 17 {? = call nomFonction([?,?,...])} {call nomProcedure ([?,?,...])} BD Multimédia 18 3 Oracle Objet-relationnel et JBDC Rappel des bases Oracle Objet-relationnel et JBDC Rappel des bases Les paramètres d'entrée sont affectés par les méthodes setXXX Les paramètres de sortie sont extraits par les méthodes getXXX. Lorsque l'état est créé, il faut : répertorier le type des paramètres de sortie passer les paramètres d'entrée, appeler le sous-programme, analyser les résultats. Les méthodes sont similaires à PreparedStatement excepté la méthode ci-dessus. BD Multimédia BD Multimédia 19 Oracle Objet-relationnel et JBDC API Oracle (jdbc – sql) Oracle fournit une implémentation (ojdbc7.jar pour Oracle 12c) de l'API qui inclut les paquetages : oracle.sql et oracle.jdbc Il faut aussi inclure le paquetage nls_charset12.jar (selon la version de JDK) pour les drivers « thin » et « oci » BD Multimédia 22 Oracle Objet-relationnel et JBDC Mappings d'objets CREATE TYPE etudiant_type AS OBJECT (IdE VARCHAR(15), nom VARCHAR(20), adresse VARCHAR(100)); / oracle.sql.CHAR, oracle.sql.DATE, oracle.sql.NUMBER, etc. oracle.sql.STRUCT (pour les objets structurés) oracle.sql.REF (pour les références) oracle.sql.ARRAY (pour les tableaux) 21 Oracle Objet-relationnel et JBDC Mappings d'objets Il permet d'accéder directement à des données au format SQL (types reconnus dans la base). OracleDriver, OracleConnection, OracleStatement, OraclePrepareStatement, OracleCallableStatement, OracleResultSet, OracleResultSetMetaData, OracleDatabaseMetaData, OracleTypes BD Multimédia Le paquetage Oracle.sql Le paquetage oracle.jdbc comporte les classes/interfaces suivantes 20 Oracle Objet-relationnel et JBDC Extensions de l'API d'Oracle API Oracle create function testFunc( a IN VARCHAR2) return integer is … CallableStatement stmt= conn.prepareCall("{? = call testFunc(?)}"); stmt.registerOutParameter(1, Types.INTEGER); stmt.setString(2, "test "); int i=stmt.getInt(1); méthode RegisterOutParameter(int,int) Exemple Appel d’une fonction PL/SQL qui prend en paramètre (en lecture) une chaîne de caractères et retourne un entier. CREATE TABLE etudiant of etudiant_type; INSERT INTO etudiant VALUES ('123', 'DUPONT', 'Dijon'); Exemple : Récupération des résultats d’une requête OracleResultSet rset= (OracleResultSet) st.executeQuery("select e.IdE, e.nom, e.adresse from etudiant e where e.IdE='123'; while (rset.next()) { int id=rset.getInt(1); String n=rset.getString(2); String ad=reset.getString(3); ... } BD Multimédia 23 BD Multimédia 24 4 Oracle Objet-relationnel et JBDC Mappings d'objets Oracle Objet-relationnel et JBDC Mappings d'objets Exemple : Mise à jour d'un étudiant OraclePreparedStatement ps = conn.prepareStatement("UPDATE etudiant set adresse=? where idE=?"); ps.setString(1,'Dijon'); ps.setString(2,'123'); ps.execute(); Mécanismes pour faire correspondre des objets d'Oracle (persistants) avec des objets java (non persistants). Les objets stockés dans la base peuvent être manipulés à l'aide des interfaces oracle.sql.STRUCT pour l’API Oracle ou java.sql.Struct pour l’API java. BD Multimédia Pour récupérer les attributs du type SQL Retourne la connexion « Oracle » courante BD Multimédia API Oracle Type composé : avec une adresse de type adresse_type OracleResultSet rset= (OracleResultSet) st.executeQuery("select value(e) from etudiant e where e.IdE='123'; while (rset.next()) {oracle.sql.STRUCT stEtu= rset.getSTRUCT(1); oracle.sql.Datum[] tabAttEtu=stEtu.getOracleAttributes(); String nom = tabAttEtu[1]; oracle.sql.STRUCT stAdr = (STRUCT) tabAttEtu[2]; oracle.sql.Datum[] tabAttAdr=stAdr.getOracle.Attributes(); int noRue=(int) tabAttrAdr[0]; String rue=tabAttrAdr[1].toString; String ville=tabAttrAdr[2].toString; ... La classe oracle.sql.Datum est une sous-classe de la classe java.lang.Object. Il est aussi possible d’écrire : Object[] tabAtt=stEtu.getOracleAttributes(); BD Multimédia 28 Oracle Objet-relationnel et JBDC Mappings d'objets OracleResultSet rset= (OracleResultSet) st.executeQuery("select value(e) from etudiant e where e.IdE='123'; while (rset.next()) { oracle.sql.STRUCT stEtu=rset.getSTRUCT(1); oracle.sql.Datum[] tabAtt=stEtu.getOracleAttributes(); } INSERT INTO etudiant VALUES ('123', 'DUPONT', 'Dijon'); BD Multimédia 27 Oracle Objet-relationnel et JBDC Mappings d'objets CREATE TABLE etudiant of etudiant_type; Pour récupérer le descripteur du type java.sql.OracleConnection getOracleConnection() CREATE TYPE etudiant_type AS OBJECT (IdE VARCHAR(15), nom VARCHAR(20), adresse adresse_type); / Pour récupérer le nom du type oracle.sql.StructDescriptor getDescriptor() CREATE TYPE adresse_type as object (numero INTEGER, rue VARCHAR(50), ville VARCHAR(50)); / String getSQLTypeName() oracle.sql.Datum[] getOracleAttributes() 26 Oracle Objet-relationnel et JBDC Mappings d'objets Interface Oracle STRUCT Les principales méthodes sont Typage fort À l’aide de oracle.sql.ORAData pour l’API Oracle ou java.sql.SQLData pour l’API java. BD Multimédia 25 Oracle Objet-relationnel et JBDC Mappings d'objets Typage faible ou bien, il est possible de construire des types personnalisés avec les interfaces } 29 BD Multimédia 30 5 Oracle Objet-relationnel et JBDC Mappings d'objets Oracle Objet-relationnel et JBDC Mappings d'objets L'utilisation des primitives de bas niveau ne permet pas une gestion automatisée aisée des objets java. Il est possible de faire des correspondances (typage fort) avec les interfaces java.sql.SQLData ou oracle.sql.ORAData. java.sql.SQLData est plus portable. Le chargement et la mise à jour d'un objet java se fait par les méthodes getObject : utilisation de la méthode readSQL setObject : utilisation de la méthode writeSQL. BD Multimédia BD Multimédia Définition de la classe java Etudiant qui implémente l'interface SQLData import java.sql.*; public class etudiant implements SQLData Utilisation de java.util.Map Extraction par getObject { Implémentation de la méthode writeSQL de l'interface SQLData Mise à jour par setObject BD Multimédia 34 Oracle Objet-relationnel et JBDC Mappings d'objets // méthode readSQL public void readSQL(SQLInput stream , String typeName) throws SQLException { sql_type = typeName; idE = stream.readString(); nom = stream.readString(); adresse = stream.readString(); } BD Multimédia String sql_type; public String idE , nom, adresse; public etudiant() {} public String getSQLTypeName() throws SQLException {return "etudiant_type"; } BD Multimédia 33 Oracle Objet-relationnel et JBDC Mappings d'objets 32 Implémentation de la méthode readSQL de l'interface SQLData Implémente l'objet de la base à partir de l'objet java Oracle Objet-relationnel et JBDC Mappings d'objets Création du type de mapping associé à la connexion Implémente l'objet java à partir de la base void writeSQL(SQLOutput stream) 31 Etapes de mise en place du mapping String getSQLTypeName() void readSQL(SQLInput stream, String type_name) Oracle Objet-relationnel et JBDC Mappings d'objets Méthodes de l'interface java.sql.SQLData // méthode writeSQL public void writeSQL(SQLOutput stream) throws SQLException { stream.writeString(idE); stream.writeString(nom); stream.writeString(adresse); } } 35 BD Multimédia 36 6 Oracle Objet-relationnel et JBDC Mappings d'objets Oracle Objet-relationnel et JBDC Mappings d'objets Chargement d'un objet java La définition de la correspondance est faite à l'aide de l'interface Map, associée à la connexion. La méthode getMapType renvoie l'objet de correspondance associé à la connexion. La méthode put permet d'associer une classe java à un type SQL. BD Multimédia try { Connection conn= DriverManager.getConnection(...); java.util.Map maMap =conn.getTypeMap(); maMap.put("etudiant_type",Class.forName("Etudiant"); Statement st=conn.createStatement(); ResultSet rset=st.executeQuery("select value(e) from etudiant e"); while (rset.next()) { Etudiant etu = rset.getObject(1,maMap); System.out.println(etu.idE+" "+etu.nom+" " etu.adresse); } rset.close(); st.close(); }} catch ... BD Multimédia 37 Oracle Objet-relationnel et JBDC Mappings d'objets Oracle Objet-relationnel et JBDC Gestion des références Stockage d'un objet java (writeSQL) try { Connection conn= DriverManager.getConnection(...); java.util.Map maMap =conn.getTypeMap(); PreparedStatement st=conn.prepareStatement("insert into etudiant values(?)"); Etudiant e=new etudiant(); e.idE="124"; e.nom="durand", e.adresse="dijon"; st.setObject(1,e); ps.executeUpdate(); } catch ... BD Multimédia Les références peuvent manipulées par les API java.sql.Ref ou oracle.sql.REF qui offre plus de fonctionnalités. Les méthodes d'accès getRef(int) ou getREF(int) pour oracle permettent d'extraire une référence dans un objet ResultSet ou OracleResultSet. On peut aussi utiliser les méthodes setRef(int) ou setREF(int) pour passer en paramètre une référence à un objet paramétré. Les méthodes de l'interface oracle.sql.REF : BD Multimédia CREATE TYPE conseil_type as object CREATE personne_type as object (idP integer, nomP varchar2(20), membreC REF conseil_type); On suppose que la classe "Conseil" java correspondant au type conseil_type est définie. Implémentation de l'interface SQLData Code java pour afficher les objets référencés par membreC dans la table personne. java.util.Map map=conn.getTypeMap(); map.put("conseil_type",Class.forNAme("conseil")); OracleResultSet rset = (..) st.executeQuery("select p.membreC from personne p;"); while (rset.next()) oracle.sql.REF refC = rset.getREF(1); Conseil cs=refC.getValue(); System.out.println(cs.getBaseTypeName() +cs.nomC); Avec définition des méthodes readSQL et writeSQL BD Multimédia 40 Oracle Objet-relationnel et JBDC Gestion des références (idC integer, nomC varchar2(20)); String getBaseTypeName() -> nom du type SQL de la cible de la référence Object getValue() -> extrait l'objet cible de la référence void setValue(Objet) -> affecte l'objet cible de la référence 39 Oracle Objet-relationnel et JBDC Gestion des références 38 41 ... BD Multimédia 42 7 Oracle Objet-relationnel et JBDC Gestion des références Oracle Objet-relationnel et JBDC Gestion des collections Mise à jour d'une référence Le principe est identique mais il faut utiliser la méthode setREF (ou setRef), OracleResultSet rset = (..) st.executeQuery("select ref(c) from conseil c where c.idC=1"); oracle.sql.REF refC=rset.getREF(1); PreparedStatement ps = (..) conn.prepareStatement("update personne p set p.membreC =? where idP=23"); ps.setREF(1,refC); ps.excuteUpdate(); ... BD Multimédia Utilisation de getArray BD Multimédia 45 Oracle Objet-relationnel et JBDC Gestion des collections Mise à jour d'une collection L'interface ArrayDescriptor permet de créer un objet de type ARRAY. Descriptions des méthodes de l'interface oracle.sql.ArrayDescriptor createDescriptor(String, Connection) -> constructeur int getBaseType() -> code du type associé au descripteur (atomique, STRUCT ou REF) int getArrayType() -> nature de la collection (varray ou nested_table) int getMaxLength -> nombre max de la collection pour varray (0 pour nested table) Connection getJavaSQLConnection() -> instance de connexion utilisée lors de la création du descripteur. BD Multimédia 46 Oracle Objet-relationnel et JBDC Gestion des collections Affichage des éléments d'une collection (extraction) ResultSet rset = st.executeQuery("select ex.nomE, ex.docs from expose ex"); while (rset.next()) {sop(rset.getString(1)); Array ar =(Array) rset.getArray(2); ResultSet docsSet=ar.getResultSet(); while (docsSet.next()) System.out.println( docsSet.getString(1)); ...} Extraction de la collection simple BD Multimédia 44 Oracle Objet-relationnel et JBDC Gestion des collections CREATE TYPE document_type as table of varchar2(20);/ CREATE TABLE expose as objet Object getArray(int) -> renvoie la collection Objetc getArray(Map) -> même méthode mais avec un mapping String getBaseTypeName() renvoie le nom du type cible de la référence ResultSet getResultSet() renvoie un curseur java ResultSet getResultSet(Map) renvoie un curseur java avec un mapping BD Multimédia 43 (nomE varchar2(20), docs document_type) nested table docs store as tabdocs; setArray ou setARRAY getArray ou getARRAY. Les méthodes de l'interface java.sql.Array sont : Oracle Objet-relationnel et JBDC Gestion des collections Les interfaces java.sql.Array et oracle.sql.ARRAY permettent de manipuler des collections sous formes de tableaux ou de curseurs (ResultSet). Ces collections sont extraites ou passées en paramètres par 47 Mise à jour de l'attributs docs (nested table) de la table expose pour l'expose de nom 'expose1' String nvDoc[]={"journal", "revue", "magazine"}; oracle.sql.ArrayDescriptor desar=ArrayDescriptor("document_type", conn); oracle.sql.ARRAY ar =new ARRAY(desar, conn, nvDoc); PreparedStatement ps=conn.prepareStatement("Update expose set docs = ? where nomE='expose1' "); ((OraclePreparedStatement) ps).setARRAY(1, ar); ((OraclePreparedStatement) ps).executeUpdate(); BD Multimédia 48 8 Oracle Objet-relationnel et JBDC JPublisher JPublisher est un outil de l'offre Oracle écrit en java Qui permet de générer des classes (.java) pour préparer la programmation de mappings entre les objets de la base et les objets de l'application. Ces classes sont déduites à partir des types SQL (objets, références, collections nested table ou varray) du schéma Oracle. Principe : Créer des types sous Oracle, Générer des classes avec JPublisher et les utiliser dans des paquetages applicatifs si nécessaire Importer ces classes dans l'application générale et utiliser les méthodes de classes générées Compiler toutes les classes et exécuter l'application. BD Multimédia 49 9