package com.oracle.tutorial.jdbc;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
public
class
CoffeesTable {
private
String dbName;
private
Connection con;
private
String dbms;
public
CoffeesTable(Connection connArg, String dbNameArg, String dbmsArg) {
super();
this.con = connArg;
this.dbName = dbNameArg;
this.dbms = dbmsArg;
}
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(); }
}
}
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(); }
}
}
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);
}
}
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(); }
}
}
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
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(); }
}
}
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);
}
}
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(); }
}
}
public
static
void alternateViewTable(Connection con) throws SQLException {
Statement stmt = null;
String query =
"select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"
;
try
(Statement stmt = con.createStatement()) {
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 +
", "
+ supplierID +
", "
+ price +
", "
+ sales +
", "
+ total);
}
}
catch
(SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
}
}
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;
}
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(); }
}
}
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();
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);
}
catch
(SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
JDBCTutorialUtilities.closeConnection(myConnection);
}
}
}
</string></string></string></string></string></string></string>