Java και Βάσεις Δεδομένων (JDBC και MySQL)

Το κείμενο προυποθέτει την ύπαρξη πρόσφατων εκδόσεων του JDK και της MySQL. Προετοιμασία για την εκτέλεση των προγραμμάτων:

  1. Εγκατάσταση του κατάλληλου JDBC driver: για τη MySQL, εγκαθιστούμε το Connector/J. Για άλλα DBMS's συμβουλευτείτε το κατασκευαστή ή τις ιστοσελίδες της Java.

  2. Εγκατάσταση Apache Ant: πρόκειται για ένα Java-based εργαλείο για την εκτέλεση των εφαρμογών που παρουσιάζονται εδώ. Ο σύνδεσμος είναι http://ant.apache.org/. Βεβαιωθείτε οτι το ονομα διαδρομής του εκτελέσιμου του Apache Ant βρίσκεται στη μεταβλητή περιβάλλοντος PATH του συστήματός σας.

  3. Μεταφορτώστε το κώδικα των εφαρμογών: ο κώδικας JDBCTutorial.zip, περιλαμβάνει τα παρακάτω αρχεία:

    • properties
      • javadb-build-properties.xml
      • javadb-sample-properties.xml
      • mysql-build-properties.xml
      • mysql-sample-properties.xml
    • sql
      • javadb
        • create-procedure-show-suppliers.sql
        • create-tables.sql
        • drop-tables.sql
        • populate-tables.sql
      • mysql
        • create-procedure-show-suppliers.sql
        • create-tables.sql
        • drop-tables.sql
        • populate-tables.sql
    • src/com/oracle/tutorial/jdbc
      • CoffeesTable.java
      • JDBCTutorialUtilities.java
      • JoinSample.java
      • StoredProcedureJavaDBSample.java
      • StoredProcedureMySQLSample.java
      • SuppliersTable.java
    • build.xml

    Δημιουργούμε ένα κατάλογο που από εδώ και στο εξής θα τον ονομάζουμε <JDBC tutorial directory>. Αποσυμπιέζουμε το JDBCTutorial.zip στο <JDBC tutorial directory>. Τα αρχεία που σχετίζονται με τη JavaDB δεν μας είναι απαραίτητα για το παρόν κείμενο.

  4. Τροποποίηση του αρχείου build.xml .

    Το αρχείο build.xml χρησιμοποιείται από το Apache Ant για τη μεταγλώττιση κα εκτέλεση των εφαρμογών JDBC. Επίσης χρησιμοποιούνται τ αρχεία properties/mysql-build-properties.xml και properties/mysql-sample-properties.xml.

    1. Στο αρχείο build.xml, τροποποιούμε την επιλογή ANTPROPERTIES ώστε να αναφέρεται στο properties/mysql-build-properties.xml:

      <property name="ANTPROPERTIES" value="properties/mysql-build-properties.xml"/>

        <import file="${ANTPROPERTIES}"/>

    2. Στο αρχείο properties/mysql-build-properties.xml τροποποιύμε τα παρακάτω:

      Ιδιότητα
      		 Περιγραφή
      JAVAC Όνομα διαδρομής του εκτελέσιμου javac (εκτελούμε $ which javac)
      JAVA Όνομα διαδρομής του εκτελέσιμο java (εκτελούμε $ which java)
      PROPERTIESFILE Το όνομα του αρχείου properties/mysql-sample-properties.xml
      MYSQLDRIVER Όνομα διαδρομής του MySQL driver. Τυπικά είναι <Connector/J installation directory>/mysql-connector-java-version-number.jar
      JAVADBDRIVER Όνομα διαδριμης του Derby driver (εκτελούμε $ locate derby.jar.. Αν δεν υπαρχει το μεταφορτώνουμε από το http://db.apache.org/derby/)
      DB.VENDOR Θέτουμε mysql
      DB.DRIVER Θέτουμε com.mysql.jdbc.Driver
      DB.HOST hostname του συστήματος που βρίσκεται το DBMS (εκτελούμε $ cat /etc/hosts)
      DB.PORT port number που συνδέεται το DBMS (εκτελούμε $ cat /etc/services | grep mysql)
      DB.SID Όνομα της βάσης δεδομένων που δημιουργούν και χρησιμοποιούν οι εφαρμογές
      DB.URL URL σύνδεσης με το DBMS. Δεν είναι απαραίτητη η αλλαγή
      DB.USER Όνομα του χρήστη που έχει δικαίωμα δημιουργίας βάσης στο DBMS
      DB.PASSWORD password του χρήστη του DB.USER
      DB.DELIMITER χαρακτήρας διαχωρισμού SQL εντολών. Μη το αλλάξετε. Πρέπει να είναι το ελληνικό ερωτηματικό  ";"

  5. Τροποποίηση του αρχείου ιδιοτήτων εφαρμογής.

    Πρόκειται για το αρχείο properties/mysql-sample-properties.xml file. Οι αλλαγές είναι οι παρακάτω:

    Ιδιότητα Περιγραφή
    dbms Θέτουμε mysql
    jar_file Όνομα διαδρομής για τα class files των εφαρμογών - υποκατάλογος του <JDBC tutorial directory>
    driver Θέτουμε com.mysql.jdbc.Driver.
    database_name Όνομα της βάσης δεδομένων που δημιουργούν και χρησιμοποιούν οι εφαρμογές
    user_name Όνομα του χρήστη που έχει δικαίωμα δημιουργίας βάσης στο DBMS
    password password του χρήστη του user_name.
    server_name hostname του συστήματος που βρίσκεται το DBMS (εκτελούμε $ cat /etc/hosts)
    port_number port number που συνδέεται το DBMS (εκτελούμε $ cat /etc/services | grep mysql)

  6. Μεταγλώττιση των εφαρμογών.

    Μετακινούμαστε στο κατάλογο <JDBC tutorial directory> και στη προτροπή εισάγουμε την εντολή:

    ant compile

  7. Εκτέλεση των εφαρμογών.

    Κάθε αρχείο στόχου στο αρχείο build.xml αντιστοιχεί σε ένα αρχείο Java class ή SQL script εφαρμογής. Ο παρακάτω πίνακας συνδέει το όνομα του αρχείου στόχου του build.xml με το αντίστοιχο Java class ή SQL script αρχείο καθώς και με άλλα εκτελέσιμα που απαιτούνται:

    Αρχείο Στόχου
    		 Java Class ή SQL Script Απαιτούμενα Αρχεία
    drop-tables drop-tables.sql  Κανένα
    build-tables build-tables.sql  drop-tables
    populate-tables populate-tables.sql  build-tables
    javadb-create-procedure javadb-create-procedure.sql  Κανένα
    mysql-create-procedure Στο build.xml φαίνονται οι SQL που εκτελούνται
    		  Κανένα
    run JDBCTutorialUtilities  Κανένα
    runct CoffeesTable JDBCTutorialUtilities
    runst SuppliersTable JDBCTutorialUtilities
    runjs JoinSample JDBCTutorialUtilities, CoffeesTable, SuppliersTable
    runspjavadb

    StoredProcedureJavaDBSample

    Σημείωση: Απαιτείται ιδιαίτερη διαμόρφωση.

    		 JDBCTutorialUtilities, SuppliersTable, CoffeesTable
    runspmysql StoredProcedureMySQLSample JDBCTutorialUtilities, SuppliersTable, CoffeesTable

    Για παράδειγμα, για την εκτέλεση της εφαρμογής CoffeesTable, μετακονούμαστε στο κατάλογο <JDBC tutorial directory>, και στη προτροπή εισάγουμε την εντολή:

    ant runct
Σημεία που χρειάζονται προσοχή: (i) ο χρήστης πρέπει να έχει τα κατάλληλα δικαιώματα (πχ δημιουργίας βάσης, τοπική/απομακρυσμένη πρόσβαση. Το phpMyAdmin βοηθά σε αυτή τη διαχείριση. (ii) η βάση testdb πριν δημιουργηθεί πρέπει να δηλωθεί στη MySql, πιθανότατα μέσω του phpMyAdmin.

Επεξεργασία SQL εντολών με JDBC

Γενικά η επεξεργασία SQL εντολών με JDBC, έχει τα εξής βήματα:

  1. Άνοιγμα σύνδεσης με τη βάση.
  2. Δημιουργία εντολής SQL.
  3. Εκτέλεση εντολής SQL.
  4. Επεξεργασία αντικειμένου ResultSet.
  5. Κλείσιμο σύνδεσης με τη βάση.

Ως παράδειγμα ας δούμε τη μέθοδο CoffeesTable.viewTable, από τις εφαρμογή CoffeesTable.java. H μέθοδος αυτή εμφανίζει τα περιεχόμενα του πίνακα COFFEES. Η μέθοδος θα εξηγηθεί αναλυτικότερα αργότερα:

  public static void viewTable(Connection con, String dbName) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from " + dbName + ".COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }

Άνοιγμα Σύνδεσης με τη Βάση

Η σύνδεση εξαρτάται από το τύπο της βάσης δεδομένων και τον αντίστοιχο JDBC driver. Η επιτυχής σύνδεση αντιπροσωπεύεται από ένα αντικείμενο Connection. Η σύνδεση αναλύεται στην επόμενη ενότητα.

Δημιουργία Εντολής SQL

To Statement είναι μια διεπιφάνεια που αντιπροσωπεύει μια εντολή SQL. Εκτελούμε αντικείμενα Statement, και αυτά δημιουργούν αντικείμενα ResultSet, τα οποία είναι πίνακες δεδομένων που προκύπτουν από τα δεδομένα της βάσης. Για τη δημιουργία του αντικειμένου Statement προαπαιτείται ένα αντικείμενο Connection.

Για παράδειγμα, η μέθοδος CoffeesTable.viewTable δημιουργεί ένα αντικείμενο Statement με το παρακάτω κώδικα:

      stmt = con.createStatement();

Υπάρχουν τρείς διαφορετικοί τύποι εντολών SQL:

Εκτέλεση Εντολής SQL

Για την εκτέλεση ενός ερωτήματος, καλούμε τη μέθοδο execute από τη μέθοδο Statement ως εξής:

Για παράδειγμα, η μέθοδος CoffeesTable.viewTable εκτελεί ένα αντικείμενο Statement με το κώδικα:

      ResultSet rs = stmt.executeQuery(query);

Επεξεργασία Αντικειμένου ResultSet

Η προσπέλαση των δεδομένων στο αντικείμενο ResultSet επιτυγχάνεται μέσω ενός δρομέα, δηλαδή ενός δείκτη που δείχνει σε μια γραμμή δεδομένων στο αντικείμενο ResultSet. Αρχικά, ο δρομέας δείχνει πριν τη πρώτη γραμμή. Υπάρχουν διάφορες μέθοδοι για τη κίνηση του δρομέα που ορίζονται μέσω του αντικειμένου ResultSet.

Για παράδειγμα, η μέθοδος CoffeesTable.viewTable καλεί τη μέθοδο ResultSet.next επαναληπτικά για τη κίνηση του δρομέα από γραμμή σε γραμμή και την εμφάνιση των δεδομένων της γραμμής που ορίζει η μέθοδος  next:

    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);
      }

Η ανάκτηση και επεξεργασία δεδομένων αναλύεται σε ξεχωριστή ενότητα.

Κλείσιμο Σύνδεσης με τη Βάση

Όταν τελειώσουμε με την επεξεργασία του αντικειμένου Statement, καλούμε τη μέθοδο Statement.close για να αποδεσμεύσουμε τους πόρους που έχουν δεσμευτεί. Με τη κλήση αυτής της μεθόδου τα αντικείμενα ResultSet κλείνουν.

Για παράδειγμα, η μέθοδος CoffeesTable.viewTable διασφαλίζει οτι το αντικείμενο Statement κλείνει, ανεξάρτητα από την ύπαρξη αντικειμένων SQLException, περιτυλίγοντάς τη σε ένα block finally:

    } finally {
      stmt.close();
    }

To JDBC παράγει SQLException όταν συναντήσει σφάλμα κατά την επικοινωνία με τη βάση. Η διαχείριση των σφαλμάτων συζητείται παρακάτω.


Άνοιγμα Σύνδεσης με τη Βάση

Κατ' αρχή πρέπειο να συνδεθούμε με τη βάση δεδομένων. Τυπικά μια εφαρμογή JDBC συνδέεται με τη βάση μέσω δύο κλάσεων:

Σημείωση: Εδώ χρησιμοποιείται κυρίως η κλάση DriverManager επειδή είναι πιο εύκολη στη χρήση.

Χρήση Κλάσης DriverManager

Η σύνδεση στο DBMS με τη κλάση DriverManager απαιτεί κλήση της μεθόδου DriverManager.getConnection. Η μέθοδος JDBCTutorialUtilities.getConnection ανοίγει μια σύνδεση με τη βάση δεδομένων:

  public Connection getConnection() throws SQLException {
    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", this.userName);
    connectionProps.put("password", this.password);

    conn = DriverManager.
      getConnection("jdbc:" + this.dbms + "://" + this.serverName +
                    ":" + this.portNumber + "/", connectionProps);
    
    System.out.println("Connected to database");
    return conn;
  }

Η μέθοδος απαιτεί ένα URL της μορφής: jdbc:mysql://localhost:3306/, όπου localhost είναι το hostname του συστήματος που βρίσκεται το DBMS, και 3306 είναι το port number. Ακόμη ορίζονται και όνομα χρήστη και password, όλα με βάση το αρχείο Properties.

Τυπικά, στο URL προσθέτουμε και το όνομα μιας υπάρχουσας βάσης με την οποία θέλουμε να συνδεθούμε. Για παράδειγμα, το URL jdbc:mysql://localhost:3306/my_data είναι ένα URL για μια βάση δεδομένων MySQL με όνομα my_data. Στο παράδειγμα παραπάνω δεν ορίζουμε όνομα επειδή θα δημιουργήσουμε νέα βάση.

Η μέθοδος επιστρέφει ένα αντικείμενο Connection, που αναπαριστά τη σύνδεσή μας με τη βάση. Επικοινωνούμε με τη βάση μέσω αυτού του αντικειμένου.

Σύνταξη ενός URL βάσης δεδομένων MySQL

H ακριβής σύνταξη του πλήρους URL για το MySQL Connector/J είναι η εξής: 
jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Το εγχειρίδιο MySQL Reference Manual έχει περισσότερες πληροφορίες.

Χρήση Αντικειμένου DataSource

Η μέθοδος JDBCTutorialUtilities.getConnectionWithDataSource δημιουργεί ένα αντικείμενο DataSource και στη συνέχεια έμα αντικείμενο Connection, που αναπαριστά μια σύνδεση με το αντικείμενι DataSource:

  public Connection getConnectionWithDataSource(String dbmsName, String dbNameArg, String userName, String password, 
    String serverName, int portNumber) throws SQLException {
    Connection conn = null;

      com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds = null;
      ds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
      ds.setUser(userName);
      ds.setPassword(password);
      ds.setServerName(serverName);
      ds.setPortNumber(portNumber);
      conn = ds.getConnection();
    
    System.out.println("Connected to database");
    return conn;
  }

Σε αντίθεση με το αντικείμενο DriverManager, στο αντικείμενο DataSource ο χρήστης μπορεί να ορίσει μέσω μεταβλητών διάφορα στοιχεία όπως το host name και το port number. Έτσι η εφαρμογή είναι πιο ευέλικτη.

Διαχείριση Εξαιρέσεων SQL

Όταν το JDBC συναντά ένα σφάλμα κατά την επικοινωνία με τη βάση δεδομένων, τότε παράγει ένα στιγμιότυπο της κλάσης SQLException σε αντίθεση με τη Exception. Το στιγμιότυπο SQLException περιέχει τις παρακάτω πληροφορίες που χαρακτηρίζουν το σφάλμα:

Εμφάνιση Εξαιρέσεων

Η μέθοδος JDBCTutorialUtilities.printSQLException εμφανίζει τις παραπάνω πληροφορίες:

  public static void printSQLException(SQLException ex) {
    for (Throwable e : ex) {
      if (e instanceof SQLException) {
        if (ignoreSQLException(((SQLException)e).getSQLState()) == false) {
          e.printStackTrace(System.err);
          System.err.println("SQLState: " + ((SQLException)e).getSQLState());
          System.err.println("Error Code: " + ((SQLException)e).getErrorCode());
          System.err.println("Message: " + ((SQLException)e).getMessage());
          Throwable t = ex.getCause();
          while(t != null) {
              System.out.println("Cause: " + t);
              t = t.getCause();
          }
        }
      }
    }
  }
Μπορούμε να επεξεργαστούμε την SQLException, αν πρώτα ανακαλέσουμε το κωδικό SQLState και ενεργήσουμε ανάλογα. Για παράδειγμα, η μέθοδος JDBCTutorialUtilities.ignoreSQLException επιστρέφει true αν ο κωδικός είναι ένας από δύο συγκεκριμένους: 
  public static boolean ignoreSQLException(String sqlState) {
    // X0Y32: Jar file already exists in schema
    if (sqlState.equalsIgnoreCase("X0Y32")) return true;
    // 42Y55: Table already exists in schema
    if (sqlState.equalsIgnoreCase("42Y55")) return true;
    return false;
  }

Εμφάνιση Ειδοποιήσεων

Τα αντικείμενα SQLWarning είναι υποκλάση της SQLException. Οι ειδοποιήσεις δε σταματούν την εκτέλεση της εφαρμογής, όπως  οι εξαιρέσεις, απλά ενημερώνουν το χρήστη οτι κατι δεν εκτελέστηκε ακριβώς όπως είχε σχεδιαστεί. 

Ειδοποιήσεις μπορεί να εμφανιστούν στα αντικείμενα Connection, Statement (PreparedStatement και CallableStatement), ή ResultSet. Κάθε μια από αυτές τις κλάσεις έχει μια μέθοδο getWarnings, μέσω της οποίας εμφανίζεται η πρώτη σχετική ειδοποίηση. Αν η getWarnings επιστρέψει μια ειδοποίηση, τότε η μέθοδος SQLWarning με getNextWarning εμφανίζει άλλες ειδοποιήσεις, αν υπάρχουν. Αν προχωρήσουμε στην εκτέλεση νέας εντολής SQL οι πιθανές ειδοποιήσεις της τρέχουσας εκτέλεσης διαγράφονται.

Οι παρακάτω μέθοδοι από τη βιβλιοθήκη JDBCTutorialUtilities εξηγούν την εμφάνιση ειδοποιήσεων που προκύπτουν σε αντικείμενα Statement ή ResultSet:

  public static void getWarningsFromResultSet(ResultSet rs) throws SQLException {
    JDBCTutorialUtilities.printWarnings(rs.getWarnings());
  }

  public static void getWarningsFromStatement(Statement stmt) throws SQLException {
    JDBCTutorialUtilities.printWarnings(stmt.getWarnings());
  }

  public static void printWarnings(SQLWarning warning) throws SQLException {
    if (warning != null) {
      System.out.println("\n---Warning---\n");
      while (warning != null) {
        System.out.println("Message: " + warning.getMessage());
        System.out.println("SQLState: " + warning.getSQLState());
        System.out.print("Vendor error code: ");
        System.out.println(warning.getErrorCode());
        System.out.println("");
        warning = warning.getNextWarning();
      }
    }
  }

Η συνηθέστερη ειδοποίηση είναι η DataTruncation, υποκκάση της SQLWarning. 'Ολα τα αντικείμενα DataTruncation έχουν κωδικό SQLState 01004, που σημαίνει ύπαρξη προβλήματος στην ανάγνωση ή εγγραφή δεδομένων. Οι μέθοδοι DataTruncation μας επιτρέπουν να δούμε σε ποιά δεδομένα έγινε το σφάλμα, αν ήταν σφάλμα εγγραφής ή ανάγνωσης, πόσα bytes έπρεπε να μεταφερθούν και πόσα τελικά μεταφέρθηκαν.

Υποκλάσεις SQLExceptions


Δημιουργία Πινάκων και Εισαγωγή Δεδομένων

Το πρόγραμμα CoffeesTable.java δημιουργεί το παρακάτω πίνακα και εισάγει τα αντίστοιχα δεδομένα:

COF_NAME SUP_ID PRICE SALES TOTAL
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0

Περιγραφή των στηλών του πίνακα COFFEES:

Ο δεύτερος πίνακας της βάσης, SUPPLIERS, αποθηκεύει πληροφορίες προμηθευτών:

SUP_ID SUP_NAME STREET CITY STATE ZIP
101 Acme, Inc. 99 Market Street Groundsville CA 95199
49 Superior Coffee 1 Party Place Mendocino CA 95460
150 The High Ground 100 Coffee Lane Meadows CA 93966

Περιγραφή των στηλών του πίνακα SUPPLIERS:

Δημιουργία Πινάκων

Δημιουργία με Apache Ant

Μετακινηθείτε στο κατάλογο <JDBC tutorial directory> και εκτελέστε την εντολή:

ant create-tables

Η εντολή εκτελεί το παρακάτω σενάριο Ant (από το αρχείο build.xml):

  <target name="build-tables" description="Create database tables">
    <sql
      driver="${DB.DRIVER}"
      url="${DB.URL}"
      userid="${DB.USER}"
      password="${DB.PASSWORD}"
      classpathref="CLASSPATH"
      delimiter="${DB.DELIMITER}" autocommit="false" onerror="abort">
       <transaction src="./sql/${DB.VENDOR}/create-tables.sql"/>
    </sql>
  </target>

Το σενάριο Ant ορίζει τις παραμέτρους sql, όπως έχουν οριστεί στο αρχείο properties/mysql-build-properties.xml με επιπλέον τις:

Παράμετρος
 Περιγραφή
autocommit Boolean; στη τιμή false, όλες οι εντολές SQL εκτελούνται σε μια συναλλαγή.
onerror Ενέργεια σε περίπτωση σφάλματος.Επιτρεπόμενες τιμές: continue, stop, και abort. 

Η εισαγωγή των παραμέτρων στο αρχείο build.xml γίνεται με την εντολή import:

<import file="${ANTPROPERTIES}"/>

Το τμήμα transaction ορίζει ένα αρχείο όπου βρίσκονται οι προς εκτέλεση εντολές SQL. Το αρχείο create-tables.sql περιέχει δύο εντολές SQL για τη δημιουργία πινάκων SUPPLIERS και COFFEES:

create table SUPPLIERS
  (SUP_ID integer NOT NULL,
  SUP_NAME varchar(40) NOT NULL,
  STREET varchar(40) NOT NULL,
  CITY varchar(20) NOT NULL,
  STATE char(2) NOT NULL,
  ZIP char(5),
  PRIMARY KEY (SUP_ID));

create table COFFEES
  (COF_NAME varchar(32) NOT NULL,
  SUP_ID int NOT NULL,
  PRICE numeric(10,2) NOT NULL,
  SALES integer NOT NULL,
  TOTAL integer NOT NULL,
  PRIMARY KEY (COF_NAME),
  FOREIGN KEY (SUP_ID) REFERENCES SUPPLIERS (SUP_ID));

Σημείωση: Το αρχείο build.xml περιέχει και ένα σενάριο Ant με όνομα drop-tables που διαγράφει τους πίνακες SUPPLIERS και COFFEES. Το εκτελούμε αν θέλουμε να διαγράψουμε τους πίνακες.

Δημιουργία πινάκων με JDBC API

Η μέθοδος SuppliersTable.createTable δημιουργεί το πίνακα SUPPLIERS:

  public void createTable() throws SQLException {
    String createString = "create table " + dbName + ".SUPPLIERS " +
						"(SUP_ID integer NOT NULL, " +
						"SUP_NAME varchar(40) NOT NULL, " +
						"STREET varchar(40) NOT NULL, " +
						"CITY varchar(20) NOT NULL, " +
						"STATE char(2) NOT NULL, " +
            "ZIP char(5), " +
            "PRIMARY KEY (SUP_ID))";
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Η μέθοδος CoffeesTable.createTable δημιουργεί το πίνακα COFFEES:

  public void createTable() throws SQLException {
    String createString = "create table " + dbName + ".COFFEES " +
              "(COF_NAME varchar(32) NOT NULL, " +
              "SUP_ID int NOT NULL, " +
              "PRICE float NOT NULL, " +
              "SALES integer NOT NULL, " +
              "TOTAL integer NOT NULL, " +
              "PRIMARY KEY (COF_NAME), " +
              "FOREIGN KEY (SUP_ID) REFERENCES " + dbName + ".SUPPLIERS (SUP_ID))";
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Και στις δυο μεθόδους, το con είναι αντικείμενο Connection και το dbName είναι το όνομα της βάσης όπου δημιουργούμε το πίνακα.

Για να εκτελέσουμε την εντολή SQL, που περιέχεται στο String createString, χρησιμοποιόυμε ένα αντικείμενο Statement. Η δημιουργία του αντικειμένου Statement γίνεται με κλήση της μεθόδου Connection.createStatement μέσα από ένα υπάρχον αντικείμενο Connection. Η εκτέλεση της εντολής SQL γίνεται μέσω της μεθόδου Statement.executeUpdate.

Όλα τα αντικείμενα Statement κλείνουν μαζί με τη σύνδεση που τα δημιούργησε. Παρ' όλα αυτά είναι καλή τακτική να κλείνουμε ρητά τα αντικείμενα Statement μόλις ολοκληρώσουμε τη χρήση τους.  Με αυτό το τρόπο αποδεσμεύονται άμεσα οι πόροι του συστήματος. Η εντολή κλείνει με τη μέθοδο Statement.close. Θέτουμε τη κλήση σε δομή finally για να βεβαιωθούμε οτι η εντολή θα κλείσει ακόμη και αν η κανονική ροή του προγράμματος διακοπεί από μια εξαίρεση (όπως η SQLException).

Σημείωση: Ο πίνακας SUPPLIERS πρέπει να δημιουργηθεί πριν το πίνακα COFFEES γιατί ο COFFEES περιέχει το ξένο κλειδί SUP_ID που αναφέρεται στο SUPPLIERS.

Εισαωγή Δεδομένων

Εισαγωγή Δεδομένων με Apache Ant

Για την εισαγωγή δεδομένων στους πίνακες SUPPLIERS και COFFEES μετακινούμαστε στο κατάλογο <JDBC tutorial directory> και στη προτροπή εισάγουμε την εντολή:

ant populate-tables

Το σενάριο Ant target εκτελεί τις εντολές SQL του αρχείου populate-tables.sql:

insert into SUPPLIERS values(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460');
insert into SUPPLIERS values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199');
insert into SUPPLIERS values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966');
insert into COFFEES values('Colombian', 00101, 7.99, 0, 0);
insert into COFFEES values('French_Roast', 00049, 8.99, 0, 0);
insert into COFFEES values('Espresso', 00150, 9.99, 0, 0);
insert into COFFEES values('Colombian_Decaf', 00101, 8.99, 0, 0);
insert into COFFEES values('French_Roast_Decaf', 00049, 9.99, 0, 0);

Εισαγωγή Δεδομένων με JDBC API

Η μέθοδος SuppliersTable.populateTable εισάγει στοιχεία στο πίνακα SUPPLIERS:

  public void populateTable() throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate("insert into " + dbName + ".SUPPLIERS " +
                         "values(49, 'Superior Coffee', '1 Party Place', " +
                         "'Mendocino', 'CA', '95460')");
      stmt.executeUpdate("insert into " + dbName + ".SUPPLIERS " +
                          "values(101, 'Acme, Inc.', '99 Market Street', " +
                          "'Groundsville', 'CA', '95199')");
      stmt.executeUpdate("insert into " + dbName + ".SUPPLIERS " +
                         "values(150, 'The High Ground', '100 Coffee Lane', " +
                         "'Meadows', 'CA', '93966')");
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Η μέθοδος CoffeesTable.populateTable, εισάγει στοιχεία στο πίνακα COFFEES:

  public void populateTable() throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate("insert into " + dbName + ".COFFEES " +
             "values('Colombian', 00101, 7.99, 0, 0)");
      stmt.executeUpdate("insert into " + dbName + ".COFFEES " +
             "values('French_Roast', 00049, 8.99, 0, 0)");
      stmt.executeUpdate("insert into " + dbName + ".COFFEES " +
             "values('Espresso', 00150, 9.99, 0, 0)");
      stmt.executeUpdate("insert into " + dbName + ".COFFEES " +
             "values('Colombian_Decaf', 00101, 8.99, 0, 0)");
      stmt.executeUpdate("insert into " + dbName + ".COFFEES " +
             "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
      } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
      } finally {
        stmt.close();
      }
  }

Ανάκτηση και Ενημέρωση Δεδομένων

Η μέθοδος CoffeesTable.viewTable εμφανίζει τα περιεχόμενα του πίνακα COFFEES, και δείχνει τη χρήση των αντικειμένων ResultSet και των δρομέων:

  public static void viewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from " + dbName + ".COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Ένα αντικείμενο ResultSet είνα ένας πίνακας δεδομένων που περιέχει το αποτέλεσμα της εκτέλεσης του αντίστοιχου ερωτήματος SQL προς τη βάση. Για παράδειγμα, η μέθοδος CoffeeTables.viewTable δημιουργεί το αντικείμενο ResultSet rs, όταν εκτελεί την εντολή SQL μέσω του αντικειμένου Statement stmt. Σημειώστε οτι ένα αντικείμενο ResultSet μπορεί να δημιουργηθεί μέσω οποιουδήποτε αντικειμένου υλοποιεί τη διεπιφάνεια Statement, όπως PreparedStatement, CallableStatement και RowSet.

Η προσπελαση στα δεδομένα του ResultSet επιτυγχάνεται με ένα δρομέα. Σημειώστε οτι αυτός ο δρομέας δεν είναι ίδιος με αυτόν της βάσης δεδομένων. Ο δρομέας αυτός δείχνει σε μια γραμμή δεδομένων του ResultSet. Αρχικά ο δρομέας δείχνει στη πρώτη γραμμή. Η μέθοδος ResultSet.next μετακινεί το δρομέα στην επόμενη γραμμή. Η μέθοδος επιστρέφει false αν ο δρομέας ξεπεράσει τη τελευταία γραμμή. Στο  παραπάνω πρόγραμμα η ResultSet.next καλείται μέσα σε ένα βρόχο while ώστε να προσπελάσει όλες τις γραμμές του  ResultSet.

Διεπιφάνεια ResultSet

Η διεπιφάνεια ResultSet παρέχει μεθόδους διαφορετικής λειτουργικότητας που επενεργούν στα δεδομένα των αντικειμένων ResultSet.

ResultSet Types

Προεπιλεγμένο: TYPE_FORWARD_ONLY.

ResultSet Concurrency

Προεπιλεγμένο: CONCUR_READ_ONLY.

Η μέθοδος CoffeesTable.modifyPrices που σχολιάζεται παρακάτω επιδεινύει τη χρήση ενός ResultSet με CONCUR_UPDATABLE.

Cursor Holdability

Η κλήση της μεθόδου Connection.commit μπορεί να κλείσει τα αντικείμενα ResultSet που δημιουργούνται σε μια συναλλαγή.  Η ιδιότητα της διεπιφάνειας ResultSet holdability δίνει στην εφαρμογή τη δυνατότητα επιλογής για το αν ένα αντικείμενο ResultSet θα κλείσει και ο δρομέας του θα χαθεί.

Οι παρακάτω σταθερές ResultSet μπορούν να περάσουν στις μεθόδους Connection (createStatement, prepareStatement, και prepareCall):

Η προεπιλεγμένη τιμή εξαρτάται από το DBMS. Η μέθοδος JDBCTutorialUtilities.cursorHoldabilitySupport, εμφανίζει τη προεπιλεγμένη τιμή για τα αντικείμενα ResultSet και διευκρινίζει να υποστηρίζονται οι επιλογές HOLD_CURSORS_OVER_COMMIT και CLOSE_CURSORS_AT_COMMIT:

  public static void cursorHoldabilitySupport(Connection conn) throws SQLException {
    DatabaseMetaData dbMetaData = conn.getMetaData();
    System.out.println("ResultSet.HOLD_CURSORS_OVER_COMMIT = " + ResultSet.HOLD_CURSORS_OVER_COMMIT);
    System.out.println("ResultSet.CLOSE_CURSORS_AT_COMMIT = " + ResultSet.CLOSE_CURSORS_AT_COMMIT);
    System.out.println("Default cursor holdability: " +
      dbMetaData.getResultSetHoldability());
    System.out.println("Supports HOLD_CURSORS_OVER_COMMIT? " +
      dbMetaData.supportsResultSetHoldability(
        ResultSet.HOLD_CURSORS_OVER_COMMIT));
    System.out.println("Supports CLOSE_CURSORS_AT_COMMIT? " +
      dbMetaData.supportsResultSetHoldability(
        ResultSet.CLOSE_CURSORS_AT_COMMIT));
  }

Αρίθμηση Στηλών

Η διεπιφάνεια ResultSet ορίζει μεθόδους (για παράδειγμα getBoolean και getLong) για την ανάκτηση τιμών από τη τρέχουσα γραμμή. Οι στήλες (πεδία) μπορούν να προσπελαστούν είτε με το όνομά τους (όπως στη μέθοδο που ήδη παρουσιάστηκε) ή με αριθμό στήλης. Ο αριθμός στήλης συνήθως είναι πιο αποδοτικός. Η αρίθμηση ξεκινά από το 1. Για λόγους φορητότητας, οι στήλες είναι καλό να διαβάζονται από αριστερά προς τα δεξιά και μόνο μια φορά.

Η μέθοδος CoffeesTable.alternateViewTable χρησιμοποιεί αριθμούς στηλών:

  public static void alternateViewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString(1);
        int supplierID = rs.getInt(2);
        float price = rs.getFloat(3);
        int sales = rs.getInt(4);
        int total = rs.getInt(5);
        System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Τα ονόματα των στηλών είναι case-insensitive. Αν πολλές στήλες έχουν το ίδιο όνομα τότε επιστρέφεται η πρώτη ταύτιση.  Γενικά, αν τα ονόματα δεν επιβάλονται από την εφαρμογή, είναι καλύτερα να χρησιμοποιούμε αριθμούς. Αν απαιτείται χρήση ονομάτων η φράση AS  της SQL μπορεί να λύσει πιθανές αμφισημίες.

Υπαρρχουν μέθοδοι ανάκτησης τιμών για όλους του τύπους δεδομένων SQL. Επίσης, η μέθδος getString είναι ιδιαίτερα χρήσιμη αφού μπορεί να χρησιμοποιηθεί για την ανάκτηση χαρακτήρων, strings αλλά και αριθμητικών τιμών, τις οποίες μετατρέπει αυτόματα σε αντικείμενα String της Java. 

Χειρισμός Δρομέα

Παρακάτω αναφέρονται μέθοδοι που σχετίζονται με το χειρισμός του δρομεά για το συγκεκριμένο αντικείμενο ResultSet:

Η μέθοδος CoffeesTable.modifyPrices, που παρουσιάζεται παρακάτω, δείχνει τη χρήση μερικών μεθόδων χειρισμού δρομέα.

Ενημέρωση Δεδομένων

Στις προεπιλεγμένες (default) συνθήκες, ένα αντικείμενο ResultSet, δεν τροποποιείται και ο δρομέας του προχωρά μόνο μπροστά. Όμως, όπως ειδαμε, μπορούμε να δημιουργήσουμε αντικείμενα ResultSet που μπορούν να προσπελαστούν τυχαία και να τροποποιηθούν.

Η μέθοδος CoffeesTable.modifyPrices πολλαπλασιάζει τη στήλη PRICE κάθε γραμμής με το όρισμα percentage:

  public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt = con.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery(
        "SELECT * FROM " + dbName + ".COFFEES");

      while (uprs.next()) {
        float f = uprs.getFloat("PRICE");
        uprs.updateFloat("PRICE", f * percentage);
        uprs.updateRow();
      }

    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Οι επιλογές ResultSet.TYPE_SCROLL_SENSITIVE και ResultSet.CONCUR_UPDATABLE έχουν εξηγηθεί παραπάνω. Στο ResultSet Javadoc μπορείτε να δείτε και άλλες επιλογές που επηρεάζουν τη συμπεριφορά των αντικειμένων ResultSet.

Η μέθοδος ResultSet.updateFloat ενημερώνει τη στήλη που δείχνει ο δρομέας (στο παράδειγμα, η στήλη PRICE περιέχει τιμή float). Υπάρχουν αντίστοιχες μέθοδοι για άλλους τύπους δεδομένων.  Σημειώστε οτι η ενημέρωση της βάσης πραγματοποιείται μόνο μετά τη κλήση της μεθόδου ResultSet.updateRow.

Δέσμη Εντολών

Τα αντικείμενα Statement, PreparedStatement και CallableStatement σχετίζονται με μια λίστα εντολών. Η λίστα αυτή μπορεί να περιέχει εντολές ενημέρωσης, εισαγωγής ή διαγραφής γραμμής (πχ INSERT INTO, UPDATE, DELETE). Επίσης εντολές όπως CREATE TABLE, DROP TABLE, ALTER TABLE. Δε μπορεί όμως να περιέχει εντολές που δημιουργούν ένα νέο αντικείμενο ResultSet (SQL queries).

Η λίστα εντολών του αντικειμένου Statement είναι αρχικά κενή. Προσθέτουμε εντολές με τη μέθοδο addBatch και αδειάζουμε τη λίστα με τη μέθοδο clearBatch. Όταν προσθέσουμε όλες τις εντολές στη λίστα, καλούμε τη μέθοδο executeBatch που εκτελεί τις εντολές ως μια δέσμη.

Η μέθοδος CoffeesTable.batchUpdate προσθέτει τέσσερις γραμμές στο πίνακα COFFEES με δέσμη εντολών:

  public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {

      this.con.setAutoCommit(false);
      stmt = this.con.createStatement();

      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Amaretto', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Hazelnut', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

      int [] updateCounts = stmt.executeBatch();
      this.con.commit();

    } catch(BatchUpdateException b) {
      JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch(SQLException ex) {
      JDBCTutorialUtilities.printSQLException(ex);
    } finally {
      this.con.setAutoCommit(true);
      stmt.close();
    }
  }

Στις ενημερώσεις δέσμης, για σωστή διαχείριση σφαλμάτων, πρέπει να αναστέλεται το auto-commit mode για το αντικείμενο Connection. Η παρακάτω γραμμή πραγματοποιεί αυτή την αναστολή:

      this.con.setAutoCommit(false);

Η μέθοδος Statement.addBatch προσθέτει εντολές στη λίστα που σχετίζεται με το αντικείμενο Statement stmt. Στο παράδειγμα, οι εντολές είναι όλες INSERT INTO, όπου κάθε μια προσθέτει μια γραμμή με πέντε τιμές για τις στήλες COF_NAME, SUP_ID, PRICE, SALES και TOTAL.

Η γραμμή που ακολουθεί εκτελεί τις εντολές SQL που έχουν προστεθεί στη λίστα ως δέσμη εντολών:

      int [] updateCounts = stmt.executeBatch();

Η μέθοδος executeBatch εκτελεί ολόκληρη δέσμη εντολών, ενώ η μέθοδος executeUpdate θα εκτελούσε μόνο μια εντολή από τη λίστα. Οι εντολές εκτελούνται με τη σειρά εισαγωγής στη λίστα. Σε κάθε επιτυχή ενημέρωση DBMS επιστρέφει ένα μετρητή που δείχνει πόσες γραμμές τροποποιήθηκαν. Οι μετρητές αποθηκεύονται στο πίνακα updateCounts.

Αν όλες οι εντολές εκτελεστούν με επιτυχία ο πίνακας updateCounts θα περιέχει 4 τιμές, όλες ίσες με 1 γιατί κάθε εισαγωγή τροποποιεί μια γραμμή. Η λίστα των εντολών που σχετίζονται με τη stmt θα είναι πλέον άδεια αφού οι 4 ενυολές στάλθηκαν προς εκτέλεση όταν η stmt κάλεσε τη μέθοδο executeBatch. Αν θέλουμε μπορούμε να αδειάσουμε τη λίστα των εντολών με τη μέθοδο clearBatch.

Η μ'έθοδος Connection.commit επιβεβαιώνει τις αλλαγές στο πίνακα COFFEES.  Η μέθοδος αυτή πρέπει να κληθεί ρητά γιατί προηγούμενα είχαμε αναστείλει το auto-commit mode.

Στο τέλος επαναφέρουμε το auto-commit στο αντικείμενο Connection.

      this.con.setAutoCommit(true);

Παραμετροποιημένη Δέσμη Εντολών

Το ίδιο παράδειγμα με παραμετροποιημένη δέσμη εντολών, όπου con είναι ένα αντικείμενο Connection :

  con.setAutoCommit(false);
  PreparedStatement pstmt = con.prepareStatement(
                   "INSERT INTO COFFEES VALUES(
                                ?, ?, ?, ?, ?)");
  pstmt.setString(1, "Amaretto");
  pstmt.setInt(2, 49);
  pstmt.setFloat(3, 9.99);
  pstmt.setInt(4, 0);
  pstmt.setInt(5, 0);
  pstmt.addBatch();

  pstmt.setString(1, "Hazelnut");
  pstmt.setInt(2, 49);
  pstmt.setFloat(3, 9.99);
  pstmt.setInt(4, 0);
  pstmt.setInt(5, 0);
  pstmt.addBatch();

  // ... and so on for each new type of coffee

  int [] updateCounts = pstmt.executeBatch();
  con.commit();
  con.setAutoCommit(true);

Διαχείριση Εξαιρέσεων Δέσμης

Μια εξαίρεση BatchUpdateException προκύπτει όταν καλέσουμε μια μέθοδο executeBatch και (1) μια από τις εντολές SQL είναι ερώτημα (SQL query)  ή (2) μια από τις SQL εντολές αποτύχει για κάποιο λόγο.

Μια εξαίρεση BatchUpdateException περιέχει ένα πίνακα μετρητών όμοιο με αυτό της μεθόδου executeBatch. Έτσι φαίνεται πόσες εντολές εκτελέστηκαν με επιτυχία και σε ποιά εντολή παρουσιάστηκε πρόβλημα.

Η κλάση BatchUpdateException παράγεται από την SQLException, επομένως μπορεί να χρησιμοποιήσει τις μεθόδους που διατίθενται σε ένα αντικείμενο SQLException. Η μέθοδος JDBCTutorialUtilities.printBatchUpdateException εμφανίζει όλες τις πληροφορίες ενός αντικειμένου SQLException και το πίνακα μετρητών του αντικειμένου BatchUpdateException:

  public static void printBatchUpdateException(BatchUpdateException b) {
    System.err.println("----BatchUpdateException----");
    System.err.println("SQLState:  " + b.getSQLState());
    System.err.println("Message:  " + b.getMessage());
    System.err.println("Vendor:  " + b.getErrorCode());
    System.err.print("Update counts:  ");
    int [] updateCounts = b.getUpdateCounts();
    for (int i = 0; i < updateCounts.length; i++) {
      System.err.print(updateCounts[i] + "   ");
    }
  }

Εισαγωγή Γραμμών σε ResultSet

Σημείωση: Δεν υποστηρίζεται από όλους τους JDBC drivers. Μπορεί να προκαλέσει εξαίρεση SQLFeatureNotSupportedException.

Η μέθοδος CoffeesTable.insertRow εισάγει μια γραμμή στο πίνακα COFFEES μέσω ενός αντικειμένου ResultSet:

  public void insertRow(String coffeeName, int supplierID, float price, int sales, int total)
    throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt = con.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery(
        "SELECT * FROM " + dbName + ".COFFEES");

      uprs.moveToInsertRow();

      uprs.updateString("COF_NAME", coffeeName);
      uprs.updateInt("SUP_ID", supplierID);
      uprs.updateFloat("PRICE", price);
      uprs.updateInt("SALES", sales);
      uprs.updateInt("TOTAL", total);

      uprs.insertRow();
      uprs.beforeFirst();

    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Η μέθοδος ResultSet.moveToInsertRow μετακινεί το δρομέα σε ένα ειδικό buffer όπου εισάγονται τα δεδομένα της νέας γραμμής με χρήση των μεθόδων ενημέρωσης. Για παράδειγμα, η μέθοδος ResultSet.updateString ενημερώνει τη στήλη COF_NAME με τη τιμή της παραμέτρου cofeeName.

Η μέθοδος ResultSet.insertRow εισάγει τη νέα γραμμή στο αντικείμενο ResultSet και στη βάση δεδομένων.


Ενώσεις (Joins)

Ένωση είναι μια λειτουργία βάσεων δεδομένων που συσχετίζει δεδμένα δύο ή περισσότερων πινάκων με βάση κοινά κλειδιά. Στο παράδειγμά μας, οι πίνακες COFFEES και SUPPLIERS έχουν κοινό κλειδί τη στήλη SUP_ID. Για να ξεχωρίσουμε σε ποιά στήλη SUP_ID αναφερόμαστε βάζουμε μπροστά το όνομα του πίνακα, δηλαδή COFFEES.SUP_ID ή SUPPLIERS.SUP_ID.

Η μέθοδος JoinSample.getCoffeesBoughtBySupplier επιλέγει τους καφέδες που προμηθεύει συγκεκριμένος προμηθευτής:

  public static void getCoffeesBoughtBySupplier (String supplierName, Connection con, String dbName)
    throws SQLException {
    Statement stmt = null;
    String query = "SELECT COFFEES.COF_NAME " +
       "FROM COFFEES, SUPPLIERS " +
       "WHERE SUPPLIERS.SUP_NAME LIKE '" + supplierName + "' " +
       "and SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("Coffees bought from " + supplierName + ": ");
      while (rs.next()) {
          String coffeeName = rs.getString(1);
          System.out.println("     " + coffeeName);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
        stmt.close();
    }
  }

Αν η μέθοδος κληθεί με όρισμα supplierName ίσο με Acme, Inc., θα παράγει το παρακάτω αποτέλεσμα:

Coffees bought from Acme, Inc.:
     Colombian
     Colombian_Decaf

Η ακόλουθη γραμμή ανακτά τη τιμή της πρώτης στήλης του αντικειμένου ResultSet:

          String coffeeName = rs.getString(1);

Σημειώστε οτι το αντικείμενο ResultSet στο παράδειγμα έχει μόνο μια στήλη.

Προετοιμασμένες Εντολές

Η κλάση PreparedStatement είναι παράγωγη της γενικότερης κλάσης Statement, που έχουμε ήδη συναντήσει. Αν πρέπει να εκτελέσουμε ένα αντικείμενο Statement πολλές φορές, η χρήση ενός αντικειμένου PreparedStatement βελτιώνει την απόδοση.

Το κύριο χαρακτηριστικό ενός αντικειμένου PreparedStatement είναι, οτι αντίθετα με ένα αντικείμενο Statement, εκτελεί συγκεκριμένη precompiled εντολή SQL. Έτσι η εντολή SQL στέλνεται απ'εθυείας στο DBMS για εκτέλεση.  Τα αντικείμενα PreparedStatement χρησιμοποιούνται τόσο για εντολές SQL χωρίς παραμέτρους όσο και για εντολές με παραμέτρους.

Η μέθοδος CoffeesTable.updateCoffeeSales ενημερώνει τη ποσότητα καφέ που πωλήθηκε τη τρέχουσα εβδομάδα (στήλη SALES)  και ενημερώνει το σύνολο της ποσότητας (στήλη TOTAL) για κάθε τύπο καφέ:

  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString = "update " + dbName + ".COFFEES " +
                          "set SALES = ? where COF_NAME = ?";

    String updateStatement = "update " + dbName + ".COFFEES " +
                             "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch(SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      updateSales.close();
      updateTotal.close();
      con.setAutoCommit(true);
    }
  }

Δημιουργία Προετοιμασμένων Εντολών

Οι παρακάτω γραμμές δημιουργούν ένα αντικείμενο PreparedStatement με δύο παραμέτρους (ορίζονται με ερωτηματικά):

    String updateString = "update " + dbName + ".COFFEES " +
                          "set SALES = ? where COF_NAME = ?";
      updateSales = con.prepareStatement(updateString);

Ανάθεση Τιμών

Πριν την εκτέλεση του αντικειμένου PreparedStatement πρέπει να δώσουμε τιμές στις παραμέτρους. Γι' αυτό το λόγο η κλάση PreparedStatement παρέχει μεθόδους καθορισμού τιμών παραμέτρων, όπως οι παρακάτω που δίνουν τιμές στις παραμέτρους του αντικειμένου updateSales:

        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());

Στο παράδειγμα, η μέθοδος setInt καθορίζει τη τιμή της πρώτης παραμέτρου (γι' αυτό και το όρισμα 1) ενώ η μέθοδος setString δίνει τιμή στη δεύτερη παράμετρο (όρισμα 2).

Μια παράμευτος κρατά τη τιμή της μέχρι να της αποδωθεί νέα τιμή ή να κληθεί η μέθοδος clearParameters. Για παράδειγμα στο κώδικα που ακολουθεί το αντικείμενο PreparedStatement updateSales εκτελείται δύο φορές. Τη δεύτερη φορά τροποποιούμε μόνο τη δεύτερη παράμετρο:

    // changes SALES column of French Roast row to 100

    updateSales.setInt(1, 100);
    updateSales.setString(2, "French_Roast");
    updateSales.executeUpdate();

    // changes SALES column of Espresso row to 100 (the first
    // parameter stayed 100, and the second parameter was reset
    // to "Espresso")

    updateSales.setString(2, "Espresso");
    updateSales.executeUpdate();

Επαναληπτκή Ανάθεση Τιμών

Η ανάθεση τιμών στις παραμέτρους και η εκτέλεση των προετοιμασμένων εντολών πολλές φορές είναι ευχερέστερη με τη χρήση επαναλήψεων for ή while.

Στο παράδειγμα, η μέθοδος CoffeesTable.updateCoffeeSales χρησιμοποιεί βρόχο for-each για την ανάθεση τιμών στις παραμέτρους και την εκτέλεση των PreparedStatement αντικειμένων updateSales και updateTotal:

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {

        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());

        // ...

      }

Η μέθοδος CoffeesTable.updateCoffeeSales δέχεται ως όριμσμα ένα HashMap. Κάθε στοιχείο του HashMap περιέχει ένα όνομα καφέ και τη ποσότητα που πωλήθηκε τη τρέχουσα εβδομάδα. Ο βρόχος for-each loop διατρέχει τα στοιχεία του HashMap και αναθέτει τις τιμές τους στις παραμέτρους των αντικειμένων updateSales και updateTotal.

Εκτέλεση Προετοιμασμένων Εντολών

Όπως και με τα αντικείμενα της κλάσης Statement, για να εκτελέσουμε ένα αντικείμενο της κλάσης PreparedStatement, καλούμε την αντίστοιχη μέθοδο εκτέλεσης: executeQuery αν η εντολή επιστρέφει μόνο ένα αντικείμενο ResultSet (όπως μια εντολή SQL SELECT), executeUpdate αν η εντολή δεν επιστρέφει αντικείμενο ResultSet (όπως μια εντολή SQL UPDATE), ή execute αν η εντολή επιστρέφει πολλαπλά αντικείμενα ResultSet. Και τα δύο αντικείμενα PreparedStatement στη μέθοδο CoffeesTable.updateCoffeeSales περιέχουν εντολές SQL UPDATE, επομένως εκτελούνται με κλήση μεθόδων  executeUpdate:

        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();

Δεν απαιτούνται ορίσματα στις μεθόδους executeUpdate όταν εκτελούν τις εντολές updateSales και updateTotals αφού και τα δύο PreparedStatement αντικείμενα περιέχουν τις προετοιμασμένες SQL εντολές με τις παραμέτρους τους.

Σημείωση: Στην αρχή του κώδικα της μεθόδου CoffeesTable.updateCoffeeSales, ο auto-commit mode αναιρείται:

      con.setAutoCommit(false);

Επομένως, οι εντολές SQL δεν οριστικοποιούνται μέχρι να κληθεί μια μέθδος commit, στο τέλος του παραπάνω κώδικα. Περισσότερα στη συζήτηση των συναλλαγών.

Τιμές Επιστροφής της executeUpdate

Ενώ η executeQuery επιστρέφει ένα αντικείμενο ResultSet που περιέχει τα αποτελέσματα της εκτέλεσης της εντολής SQL στο DBMS, η τιμή επιστροφής τηε executeUpdate είναι μια τιμή int που δείχνει πόσες γραμμές του πίνακα ενημερώθηκαν. Για παράδειγμα, στο κώδικα που ακολουθεί δείχνει η τιμή επιστροφής της executeUpdate ανατίθεται στη μεταβλητή n: 

    updateSales.setInt(1, 50);
    updateSales.setString(2, "Espresso");
    int n = updateSales.executeUpdate();
    // n = 1 because one row had a change in it

Ο πίνακας COFFEES ενημερώνεται. Η τιμή 50 αντικαθιστά την υπάρχουσα τιμή στη στήλη SALES της γραμμής Espresso. Η ενημέρωση τροποποεί μόνο μια γραμμής του πίνακα, άρα το n είναι ίσο με 1.

Όταν η μέθοδος executeUpdate χρησιμοποιείται για εντολές δημιουργίας, τροποποίησης ή διαγραφής πίνακα, επιστρέφει την int τιμή 0. Επομένως ο κώδικας που ακολουθεί επιστρέφει στο n τιμή 0:

        int n = executeUpdate(createTableCoffees); // n = 0

Συναλλαγές (Transactions)

Στο προηγούμενο παράδειγμα ενημερώνουμε τις τιμές σε δύο πίνακες: στον ένα κρατώνται ποσότητες καφέ που πουλήθηκαν ανά βδομάδα, ενώ στον άλλο οι ποσότητες καφέ που πωλήθηκαν συνολικά. Η ενημέρωση μόνο ενός από τους δύο πίνακες θα δημιουργούσε ασυνέπεια δεδομένων (data inconsistency). Οι συναλλαγές χρησιμοποιούνται για τη διασφάλιση της συνέπειας δεδομένων (data consistency). Μια συναλλαγή είναι ένα σύνολο δύο ή περισσότερων εντολών SQL που εκτελούνται σαν μια εντολή, έτσι ή εκτελούνται όλες οι εντολές ή καμμία.

Ακύρωση Auto-Commit Mode

Όταν δημιουργείται μια σύνδεση, βρίσκεται σε auto-commit mode. Αυτό σημαίνει οτι κάθε εντολή SQL εντιμετωπίζεται ως αυτοδύναμη συναλλαγή και οριστικοποιείται (committed) αμέσως μόλις εκτελεστεί.

Για να ομαδοποιήσουμε δύο ή περισσότερες εντολές σε μια συνολική συναλλαγή πρέπει να ακυρώσουμε το auto-commit mode. Για μια σύνδεση con η ακύρωση γίνεται ως εξής:

con.setAutoCommit(false);

Οριστικοποίηση Συναλλαγών (Commit)

Μετά την ακύρωση του auto-commit mode, δεν οριστικοποιούνται εντολές SQL μέχρι να κληθεί ρητά η μέθοδος commit. Όλες οι εντολές που έχουν εκτελεστεί ενδιάμεσα οριστικοποιούνται ως σύνολο. Η μέθοδος CoffeesTable.updateCoffeeSales όπου η ενεργή σύνδεση είναι η con, φαίνεται η συναλλαγή:

  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString = "update " + dbName + ".COFFEES " +
                          "set SALES = ? where COF_NAME = ?";

    String updateStatement = "update " + dbName + ".COFFEES " +
                             "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch(SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      updateSales.close();
      updateTotal.close();
      con.setAutoCommit(true);
    }
  }

Ο auto-commit mode ακυρώνεται στην αρχή της δομής try-cath, ενώ στο τέλος κάθε επανάληψης for-each το ζεύγος των εντολών updateSales και updateTotal οριστικοποιούνται με τη κλήση της μεθόδου commit. 

Εν τέλει, στη δομή finally καλείται η μέθοδος con.setAutoCommit(true); που ενεργοποιεί πάλι το auto-commit mode, ώστε να επανέλθει η σύνδεση στη κανονική της κατάσταση.

Συναλλαγές και Ακεραιότητα Δεδομένων

Εκτος από τη συνέπεια δεδομένων, οι συναλλαγές είναι χρσήσιμες και στην ακεραιότητα δεδομένων (data integrity). Για παράδειγμα, ενώ ένας χρήστης εισάγει νέες τιμές στα προιόντα προς πώληση, την ίδια στιγμή ένας άλλος χρήστης παράγει ταμειακά σύνολα για τις πωλήσεις που έχουν πραγματοποιηθεί. Θα πρέπει να είναι καθαρό οτι τα ταμειακά σύνολα θα παραχθούν με τις παλιές τιμές. Επομένως (α) αν οι λειτουργίες εκτελούνται ταυτόχρονα θα πρέπει να δωθεί προτεραιότητα στη δεύτερη ενέργεια και (β) αν για κάποιο λόγο εκτελέστηκε πρώτα η πρώτη ενέργεια θα πρέπει να μπορούμε να επιστρέψουμε στις παλιές τιμές (με rollback) ώστε να μη παραχθούν λάθος αποτελέσματα.

Γενικά τέτοιοι τύποι προβλημάτων ανακύπτουν όταν υπάρχει ταυτόχρονη χρήση (ανάγνωση και εγγραφή) δεδομένων από δύο ή περισσότερες μεθόδους.  Για την αποφυγή συγκρούσεων (conflicts) κατα τη διάρκεια μιας συναλλαγής, το DBMS χρησιμοποιεί κλειδώματα (locks), που διασφαλίζουν οτι μόνο μια συναλλαγή έχει πρόσβαση σε συγκεκριμένα δεδομένα σε κάθε χρονική στιγμή. Στο auto-commit mode, όπου κάθε εντολή είναι μια συναλλαγή, τα κλειδώματα διαρκούν για μια εντολή. Τα κλειδώματα μένουν σε ισχύ μέχρι μια συναλλαγή να ολοκληρωθεί (committed) ή να ακυρωθεί (rolled back). Συνήθως τα κλειδώματα αναφέρονται σε μια γραμμή.

Συνηθισμένοι τύποι συγκρούσεων:

Dirty read: η συναλλαγή A τροποποιεί δεδομένα μιας γραμμής ενώ η συναλλαγή B χρησιμοποιεί αυτά τα δεδομένα πριν ολοκληρωθεί η συναλλαγή Α.

Non-repeatable read: η συναλλαγή Α διαβάζει δεδομένα μιας γραμμής. Η συναλλαγή Β κατόπιν τροποποιεί τα δεδομένα αυτά ή διαγράφει τη γραμμή. Στη συνέχεια η Α προσπαθεί να ξαναδιαβάσει τα δεδομένα αλλά αυτά έχουν πια αλλάξει η δεν υπάρχουν.

Phantom read: η συναλλαγή Α διαβάζει τα δεδομένα μια γραμμής με βάση μια συνθήκη. Κατόπιν η συναλλαγή Β τροποποιεί τα δεδομένα άλων γραμμών ή εισάγει γραμμή ώστε τώρα και αυτές ικανοποιούν τη συνθήκη. Η Α όταν προσπαθέι να ξαναεφαρμόσει τη συνθήκη βρίσκει περισσότερες γραμμές.

Η συμπεριφορά των κλειδωμάτων στους παραπάνω τύπους συγκρούσεων καθορίζεται από το επίπεδο απομόνωσης (isolation level) της συναλλαγής. Η διεπιφάνεια Connection ορίζει πέντε επίπεδα απομόνωσης για το JDBC:

Isolation Level Transactions Dirty Reads Non-Repeatable Reads Phantom Reads
TRANSACTION_NONE Not supported Not applicable Not applicable Not applicable
TRANSACTION_READ_COMMITTED Supported Prevented Allowed Allowed
TRANSACTION_READ_UNCOMMITTED Supported Allowed Allowed Allowed
TRANSACTION_REPEATABLE_READ Supported Prevented Prevented Allowed
TRANSACTION_SERIALIZABLE Supported Prevented Prevented Prevented

Ένκάθε DBMS μπορεί να διαφέρει, ή κάποια επίπ The default transaction isolation level depends on your DBMS. For example, for Java DB, it is TRANSACTION_READ_COMMITTED. JDBC allows you to find out what transaction isolation level your DBMS is set to (using the Connection method getTransactionIsolation) and also allows you to set it to another level (using the Connection method setTransactionIsolation).

Σημείωση: Ένας JDBC driver μπορεί να μην υποστηρίζει όλα τα επίπεδα απομόνωσης. Επίσης κάθε DBMS μπορεί να έχει διαφορετική προεπιλογή. Η διεπιφάνεια Connection παρέχει τις μεθόδους getTransactionIsolation, setTransactionIsolation για να χειριστούμε τα επίπεδα απομόνωσης. Επίσης η μεθοδος DatabaseMetaData.supportsTransactionIsolationLevel ελέγχει αν το DBMS υποστηρίζει συγκεκριμένο επίπεδο.

Ορισμός Savepoints και Χρήση Rollback

Η μέθοδος Connection.setSavepoint, ορίζει ένα αντικείμενο Savepoint μέσα στη τρέχουσα συναλλαγή. Η μέθοδος Connection.rollback δέχεται ως όρισμα το αντικείμενο Savepoint.

Η μέθοδος CoffeesTable.modifyPricesByPercentage αυξάνει τη τιμή ενός καφέ κατά ένα ποσοστό, priceModifier. Όμως, αν η νέα τιμή είναι μεγαλύτερη από μια καθορισμένη τιμή, maximumPrice, τότε η αύξηση της τιμής αναιρείται:

   public void modifyPricesByPercentage(String coffeeName, float priceModifier,
                                       float maximumPrice) throws SQLException {
    con.setAutoCommit(false);

    Statement getPrice = null;
    Statement updatePrice = null;
    ResultSet rs = null;
    String query =
      "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE COF_NAME = '" +
      coffeeName + "'";

    try {
      Savepoint save1 = con.setSavepoint();
      getPrice =
          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      updatePrice = con.createStatement();

      if (!getPrice.execute(query)) {
        System.out.println("Could not find entry for coffee named " +
                           coffeeName);
      } else {
        rs = getPrice.getResultSet();
        rs.first();
        float oldPrice = rs.getFloat("PRICE");
        float newPrice = oldPrice + (oldPrice * priceModifier);
        System.out.println("Old price of " + coffeeName + " is " + oldPrice);
        System.out.println("New price of " + coffeeName + " is " + newPrice);
        System.out.println("Performing update...");
        updatePrice.executeUpdate("UPDATE COFFEES SET PRICE = " + newPrice +
                                  " WHERE COF_NAME = '" + coffeeName + "'");
        System.out.println("\nCOFFEES table after update:");
        CoffeesTable.viewTable(con);
        if (newPrice > maximumPrice) {
          System.out.println("\nThe new price, " + newPrice +
                             ", is greater than the maximum " + "price, " +
                             maximumPrice +
                             ". Rolling back the transaction...");
          con.rollback(save1);
          System.out.println("\nCOFFEES table after rollback:");
          CoffeesTable.viewTable(con);
        }
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      getPrice.close();
      updatePrice.close();
      con.setAutoCommit(true);
    }
  }

Στην αρχή η μέθοδος δημιουργεί ένα Savepoint :

        Savepoint save1 = con.setSavepoint();

Η μέθοδος ελέγχει αν η νέα τιμή είναι μεγαλύτερη από τη maximumPrice. Αν ναι, τότε ακυρώνει τη συναλλαγή:

          con.rollback(save1);

Επομένως, όταν η μέθοδος οριστικοποιεί τη συναλλαγή με τη κλήση Connection.commit, δεν θα περιλάβει τις γραμμές που έχουν υποστεί roll back, ενώ θα προχωρήσει κανονικά με τις υπόλοιπες γραμμές.

Ελευθέρωση Savepoints

Η μέθοδος Connection.releaseSavepoint αφαιρεί ένα αντικείμενο Savepoint από τη τρέχουσα συναλλαγή. Αν κατόπιν γίνει προσπάθεια χρήσης του προκαλείται SQLException. Τα savepoints της συναλλαγής ελευθερώνονται αυτόματα μόλις η συναλλαγή οριστικοποιηθεί (commit) ή εφαρμοστεί roll back στι σύνολο της συναλλαγής.

Πότε καλούμε rollback

Αν εκτελείτε μια συναλλαγή που περιλαμβάνει πολλές εντολές SQL και εμφανιστεί SQLException, η κλήση της μεθόδου rollback είναι ο ασφαλέστερος τρόπος τερματισμού της συναλλαγής. Η παραγωγή SQLException μας λέει οτι κάτι πήγε στραβά αλλα δεν μπορεί να μας πεί για το ποιό τμήμα της συναλλαγής ήταν επιτυχές και ποιό όχι. Επομένως, είναι επικίνδυνο να οριστικοποιήσουμε μια τέτοια συναλλαγή, αφού μπορεί να επηρεαστεί η ακεραιότητα των δεδομένων μας. Η κλήση της μεθόδου rollback μας διασφαλίζει οτι επιστρέφουμε στη τελευταία σίγουρη κατάσταση απί πλευράς ακεραιότητας δεδομένων.

Η μέθοδος CoffeesTable.updateCoffeeSales που συζητήθηκε παραπάνω περιλαμβάνει και μια δομή catch όπου, όταν εμφανιστεί SQLException καλείται η μέθοδος rollback.


Αποθηκευμένες Διαδικασίες

Αποθηκευμένη διαδικασία ονομάζεται μια ομάδα εντολών SQL που αποτελούν μια λογική ενότητα και εκτελούν μια ολοκληρωμένη λειτουργία. Για παράδειγμα, έστω μια βάση δεδομένων διαχείρισης προσωπικού: τυποποιημένες λειτουργίες όπως διάφορες καταστάσεις, μισθοδοσία, άδειες, προσλήψεις, προαγωγές, απολύσεις κλπ, αποτελούν καλούς υποψηφίους για αποθηκευμένες διαδικασίες. Οι αποθηκευμένες διαδικασίες μπορεί να είναι παραμετρικές και να δέχονται ορίσματα εισόδου/εξόδου.

Αν και οι αποθηκευμένες διαδικασίες υποστηρίζονται από τα περισσότερα DBMSs, υπάρχουν αρκετές διαφοροποιήσεις. Εδώ παρουσιάζονται αποθηκευμένες διαδικασίες για τη MySQL με τη βοήθεια της κλάσης StoredProcedureMySQLSample.

Αποθηκευμένες Διαδικασίες στη MySQL

Το ακόλουθο SQL script δημιουργεί μια αποθηκευμένη διαδικασία με όνομα SHOW_SUPPLIERS που επιστρέφει το όνομα τπυ προμηθευτή για κάθε τύπο καφέ στο πίνακα COFFEES:

SELECT 'Dropping procedure SHOW_SUPPLIERS' AS ' ';

drop procedure if exists SHOW_SUPPLIERS;

SELECT 'Changing delimiter to pipe' AS ' ';

delimiter |

SELECT 'Creating procedure SHOW_SUPPLIERS' AS ' '|

create procedure SHOW_SUPPLIERS()
  begin
    select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
    from SUPPLIERS, COFFEES
    where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
    order by SUP_NAME;
  end|

delimiter ;

Η εντολή drop procedure διαγράψει τυχόν παλιότερη διαδικασία με όνομα SHOW_SUPPLIERS. Η εντολή delimiter | αλλάζει το χαρακτήρα που χωρίζει εντολές από semicolon (;) σε pipe (|). Στη MySQL, οι εντολές χωρίζονται με semicolon. Όμως πρέπει να επιλογή άλλος χαρακτηρας τερματισμού για το τέλος της εντολής create procedure. Εδώ επιλέγεται ο χαρακτήρας pipe (|). Θα μπορούσατε να επιλέξετε άλλο χαρακτήρα.

Η εντολή create procedure περιλαμβάνει το όνομα της διαδικασίας, λίστα ορισμάτων που χωρίζονται με κόμμα και τις εντολές SQL μεταξύ των λέξεων begin και end. Η αποθηκευμένη διαδικασία SHOW_SUPPLIERS δεν έχει ορίσματα; για πληροφορίες σχετικές με τα ορίσματα δείτε το εγχειρίδιο της MySQL.

Η κλήση μιας αποθηκευμένης διαδικασίας γίνεται με την εντολή CALL:

CALL SHOW_SUPPLIERS();
Το αποτέλεσμα της κλήσης θα είναι το εξής: 
Acme, Inc.: Colombian_Decaf
Acme, Inc.: Colombian
Superior Coffee: French_Roast_Decaf
Superior Coffee: French_Roast
The High Ground: Espresso

Δημιουργία Αποθηκευμένης Διαδικασίας με JDBC API

Η μέθοδος StoredProcedureMySQLSample δημουργεί μια αποθηκευμένη διαδικασία με όνομα SHOW_SUPPLIERS:

  public void createProcedureShowSuppliers() throws SQLException {
    String createProcedure = null;
    createProcedure = "create procedure SHOW_SUPPLIERS() " +
           "begin " +
           "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
           "from SUPPLIERS, COFFEES " +
           "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
           "order by SUP_NAME; " +
           "end";
    Statement stmt = null;

    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Η κλήση stmt.xecuteUpdate(createProcedure) δημιουργεί την αποθηκευμένη διαδικασία:

create procedure SHOW_SUPPLIERS()
  begin
  select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
  from SUPPLIERS, COFFEES
  where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
  order by SUP_NAME;
end

Σημειώστε οτι το αντικείμενο Connection con πρέπει να έχει συνδεθεί στη συγκεκριμένη βάση.

Κλήση Αποθηκευμένης Διαδικασίας με JDBC API

Η μέθοδος StoredProcedureMySQLample.runStoredProcedure καλεί την αποθηκευμένη διαδικασία:

  public void runStoredProcedure() throws SQLException {
    CallableStatement cs = null;

    try {
      cs = con.prepareCall("{call SHOW_SUPPLIERS}");
      ResultSet rs = cs.executeQuery();

      while (rs.next()) {
        String supplier = rs.getString("SUP_NAME");
        String coffee = rs.getString("COF_NAME");
        System.out.println(supplier + ": " + coffee);
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      cs.close();
    }
  }

Η διεπιφάνεια CallableStatement είναι επέκταση της PreparedStatement και χρησιμοποιείται για τη κλήση αποθηκευμένων διαδικασιών.

Σημείωση: ´Οπως και με τα αντικείμενα Statement, η κλήση αποθηκευμένης διδικασίας μπορεί να γίνει με κλήση των μεθόδων execute, executeQuery, ή executeUpdate ανάλογα με πόσα αντικείμενα ResultSet επιστρέφει η διαδικασία. Αν δεν είστε σίγουροι για τον αριθμό των αντικειμένων ResultSet που θα επιστραφούν, καλέστε τη μέθοδο execute.