1 / 11

Architecture JDBC

Architecture JDBC. Deux types de drivers JDBC en oracle: Thin driver: 100 % java OCI driver:Java & C. Préparation de la base de données. Au niveau d’Oracle chaque utilisateur a son propre schéma dans lequel il peut créer ses objets(tables, indexes, etc)

max
Télécharger la présentation

Architecture JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Architecture JDBC • Deux types de drivers JDBC en oracle: • Thin driver: 100 % java • OCI driver:Java & C

  2. Préparation de la base de données • Au niveau d’Oracle chaque utilisateur a son propre schéma dans lequel il peut créer ses objets(tables, indexes, etc) • Une instance de oracle est identifiée par son SID

  3. Etablir la connexion • Etablir une connection avec le moteur de la BD se fait en deux étapes: • 1. chargement du driver adéquat: Class.forName("oracle.jdbc.driver.OracleDriver"); • 2. Créer l’objet connection: Connection con = DriverManager.getConnection(url,"myLogin", "myPassword"); le format d’une URL dépend du type du driver ainsi que du SGBD utilisé. Pour Oracle avec un driver thin, le format est : jdbc:oracle:thin:@<host>:<port>:<sid> exemple String url = "jdbc:oracle:thin:@titan.iro.umontreal.ca:1521:a99"; Connection conn = DriverManager.getConnection(url,serhanim, "dift3030");

  4. Corps du programme pour un accès JDBC import java.net.*; import java.sql.*; public class Application1 { //Main method public static void main(String[] args) { try { String url = "jdbc:oracle:thin:@titan.iro.umontreal.ca:1521:a99"; String usr = « serhanim"; String pwd = "dift3030"; // Load the driver Class.forName("oracle.jdbc.driver.OracleDriver"); // Connect to database System.out.println("Connecting to Database URL = " + url); Connection conn = DriverManager.getConnection(url, usr, pwd); System.out.println("Connected...Now creating a statement"); // ici commence les requêtes LDD et LMD } catch (Exception ex) {} } }

  5. LDD avec JDBC • Création d’une table: String createTableCoffees = "CREATE TABLE COFFEES " + "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " + "SALES INTEGER, TOTAL INTEGER)"; Statement stmt = con.createStatement(); stmt.executeUpdate(createTableCoffees);

  6. Insertion de données stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Colombian', 101, 7.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('French_Roast', 49, 8.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Espresso', 150, 9.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)"); stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)");

  7. Select avec JDBC String query = "SELECT COF_NAME, PRICE FROM COFFEES"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String s = rs.getString("COF_NAME"); // column 1 float n = rs.getFloat(2); // column PRICE System.out.println(s + " " + n); }

  8. Mise à jour des tables String updateString = "UPDATE COFFEES " + "SET SALES = 75, " +   "TOTAL = TOTAL +75 " + "WHERE COF_NAME LIKE 'Colombian’ "; stmt.executeUpdate(updateString);

  9. Requêtes préparées: PreparedStatement updateSales = conn.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?"); int [] salesForWeek = {175, 150, 60, 155, 90}; String [] coffees = {"Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"}; int len = coffees.length; for(int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); }

  10. Jointures: String createSUPPLIERS = "create table SUPPLIERS " + "(SUP_ID INTEGER, SUP_NAME VARCHAR(40), " + "STREET VARCHAR(40), CITY VARCHAR(20), " + "STATE CHAR(2), ZIP CHAR(5))"; stmt.executeUpdate(createSUPPLIERS); stmt.executeUpdate("INSERT INTO SUPPLIERS " + "VALUES (101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199') "); stmt.executeUpdate("Insert into SUPPLIERS values (49," + "'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', " + "'95460')"); stmt.executeUpdate("Insert into SUPPLIERS values (150, " + "'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', " + "'93966')");

  11. String query = "SELECT COFFEES.COF_NAME, SUPPLIERS.SUP_NAME " + "FROM COFFEES, SUPPLIERS " + "WHERE SUPPLIERS.SUP_ID = COFFEES.SUP_ID"; ResultSet rs = stmt.executeQuery(query); System.out.println("Vendeurs des cafes : "); while (rs.next()) { String coffeeName = rs.getString("COF_NAME"); String supplierName = rs.getString("SUP_NAME"); System.out.println(coffeeName +" Vendue par " + supplierName ); }

More Related