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);
}
}
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);
}
}
