コード例 #1
0
  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 {
      if (stmt != null) {
        stmt.close();
      }
      this.con.setAutoCommit(true);
    }
  }
コード例 #2
0
  public static void viewTable(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("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 + ", " + supplierID + ", " + price + ", " + sales + ", " + total);
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) {
        stmt.close();
      }
    }
  }
コード例 #3
0
  public void insertRow(String coffeeName, int supplierID, float price, int sales, int total)
      throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery("SELECT * FROM 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 {
      if (stmt != null) {
        stmt.close();
      }
    }
  }
コード例 #4
0
  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

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

    String updateStatement = "update 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 {
      if (updateSales != null) {
        updateSales.close();
      }
      if (updateTotal != null) {
        updateTotal.close();
      }
      con.setAutoCommit(true);
    }
  }
コード例 #5
0
  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 {
      if (getPrice != null) {
        getPrice.close();
      }
      if (updatePrice != null) {
        updatePrice.close();
      }
      con.setAutoCommit(true);
    }
  }
コード例 #6
0
 public void dropTable() throws SQLException {
   Statement stmt = null;
   try {
     stmt = con.createStatement();
     if (this.dbms.equals("mysql")) {
       stmt.executeUpdate("DROP TABLE IF EXISTS COFFEES");
     } else if (this.dbms.equals("derby")) {
       stmt.executeUpdate("DROP TABLE COFFEES");
     }
   } catch (SQLException e) {
     JDBCTutorialUtilities.printSQLException(e);
   } finally {
     if (stmt != null) {
       stmt.close();
     }
   }
 }
コード例 #7
0
 public void populateTable() throws SQLException {
   Statement stmt = null;
   try {
     stmt = con.createStatement();
     stmt.executeUpdate("insert into COFFEES " + "values('Colombian', 00101, 7.99, 0, 0)");
     stmt.executeUpdate("insert into COFFEES " + "values('French_Roast', 00049, 8.99, 0, 0)");
     stmt.executeUpdate("insert into COFFEES " + "values('Espresso', 00150, 9.99, 0, 0)");
     stmt.executeUpdate("insert into COFFEES " + "values('Colombian_Decaf', 00101, 8.99, 0, 0)");
     stmt.executeUpdate(
         "insert into COFFEES " + "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
   } catch (SQLException e) {
     JDBCTutorialUtilities.printSQLException(e);
   } finally {
     if (stmt != null) {
       stmt.close();
     }
   }
 }
コード例 #8
0
 public Set<String> getKeys() throws SQLException {
   HashSet<String> keys = new HashSet<String>();
   Statement stmt = null;
   String query = "select COF_NAME from COFFEES";
   try {
     stmt = con.createStatement();
     ResultSet rs = stmt.executeQuery(query);
     while (rs.next()) {
       keys.add(rs.getString(1));
     }
   } catch (SQLException e) {
     JDBCTutorialUtilities.printSQLException(e);
   } finally {
     if (stmt != null) {
       stmt.close();
     }
   }
   return keys;
 }
コード例 #9
0
  public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

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

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) {
        stmt.close();
      }
    }
  }
コード例 #10
0
 public void createTable() throws SQLException {
   String createString =
       "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))";
   Statement stmt = null;
   try {
     stmt = con.createStatement();
     stmt.executeUpdate(createString);
   } catch (SQLException e) {
     JDBCTutorialUtilities.printSQLException(e);
   } finally {
     if (stmt != null) {
       stmt.close();
     }
   }
 }
コード例 #11
0
  public static void main(String[] args) {
    JDBCTutorialUtilities myJDBCTutorialUtilities;
    Connection myConnection = null;

    if (args[0] == null) {
      System.err.println("Properties file not specified at command line");
      return;
    } else {
      try {
        myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]);
      } catch (Exception e) {
        System.err.println("Problem reading properties file " + args[0]);
        e.printStackTrace();
        return;
      }
    }

    try {
      myConnection = myJDBCTutorialUtilities.getConnection();

      // Java DB does not have an SQL create database command; it does require createDatabase
      //      JDBCTutorialUtilities.createDatabase(myConnection,
      //                                           myJDBCTutorialUtilities.dbName,
      //                                           myJDBCTutorialUtilities.dbms);
      //
      //      JDBCTutorialUtilities.initializeTables(myConnection,
      //                                             myJDBCTutorialUtilities.dbName,
      //                                             myJDBCTutorialUtilities.dbms);

      CoffeesTable myCoffeeTable =
          new CoffeesTable(
              myConnection, myJDBCTutorialUtilities.dbName, myJDBCTutorialUtilities.dbms);

      System.out.println("\nContents of COFFEES table:");
      CoffeesTable.viewTable(myConnection);

      System.out.println("\nRaising coffee prices by 25%");
      myCoffeeTable.modifyPrices(1.25f);

      System.out.println("\nInserting a new row:");
      myCoffeeTable.insertRow("Kona", 150, 10.99f, 0, 0);
      CoffeesTable.viewTable(myConnection);

      System.out.println("\nUpdating sales of coffee per week:");
      HashMap<String, Integer> salesCoffeeWeek = new HashMap<String, Integer>();
      salesCoffeeWeek.put("Colombian", 175);
      salesCoffeeWeek.put("French_Roast", 150);
      salesCoffeeWeek.put("Espresso", 60);
      salesCoffeeWeek.put("Colombian_Decaf", 155);
      salesCoffeeWeek.put("French_Roast_Decaf", 90);
      myCoffeeTable.updateCoffeeSales(salesCoffeeWeek);
      CoffeesTable.viewTable(myConnection);

      System.out.println("\nModifying prices by percentage");

      myCoffeeTable.modifyPricesByPercentage("Colombian", 0.10f, 9.00f);

      System.out.println("\nCOFFEES table after modifying prices by percentage:");

      myCoffeeTable.viewTable(myConnection);

      System.out.println("\nPerforming batch updates; adding new coffees");
      myCoffeeTable.batchUpdate();
      myCoffeeTable.viewTable(myConnection);

      //      System.out.println("\nDropping Coffee and Suplliers table:");
      //
      //      myCoffeeTable.dropTable();
      //      mySuppliersTable.dropTable();

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      JDBCTutorialUtilities.closeConnection(myConnection);
    }
  }