Chapter 4. Database 99
st.close();
con.close();
} catch (SQLException e1) {
}
}
}
return results;
}
/**
* For stand-alone use we must create and populate the database (we can
still sync with server later)
* @return Results as a String
* @see
com.itso.rentals.database.ITSORentalsDatabaseService#createLocalData()
*/
public String createLocalData() {
String msg = "";
//only allow one thread to access DB2e
synchronized (syncLock) {
try {
con = DriverManager.getConnection(driver);
st = con.createStatement();
try {
st.executeUpdate("DROP TABLE CUSTOMERS");
st.executeUpdate("DROP TABLE CONTRACTS");
System.out.println("Tables have been dropped");
} catch (SQLException sqlEx) {
System.out.println(
"Error dropping 1 or more tables. Probably because it did
not exist");
}
st.executeUpdate("CREATE TABLE CUSTOMERS(CUSTOMERID VARCHAR(15)
NOT NULL PRIMARY KEY, FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(25), PHOTO
BLOB(10000))");
st.executeUpdate("CREATE TABLE CONTRACTS(CONTRACTID VARCHAR(10)
NOT NULL PRIMARY KEY, CUSTOMERID VARCHAR(15), FLIGHT VARCHAR(10), STATUS
VARCHAR(15), LOCATION VARCHAR(10))");
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( '374906' ,
'Jeffrey', 'Martin', null)");
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( 'GHJ453' ,
'David', 'Reich', null)");
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( 'JW3G56' ,
'Gianfranco', 'Rutigliano', null)");
100 WCTME: Application Development and Case Study
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( 'DI4F56' , 'Joel',
'Wickham', null)");
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( 'XT345G' , 'Sid',
'Perera', null)");
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( 'FT3H78' ,
'Guillermo', 'Villavicencio', null)");
st.executeUpdate("INSERT INTO CUSTOMERS VALUES( 'JK34S5' , 'Juan',
'Rodriguez', null)");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('1', '374906' ,
'ITSO 4678', '" + Customer.PENDING + "', '?')");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('2', 'GHJ453' ,
'ITSO 1982', '" + Customer.PENDING + "', '?')");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('3', 'JW3G56' ,
'ITSO 329', '" + Customer.PENDING + "', '?')");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('4', 'DI4F56' ,
'ITSO 6', '" + Customer.PENDING + "', '?')");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('5', 'XT345G' ,
'ITSO 107', '" + Customer.PENDING + "', '?')");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('6', 'FT3H78' ,
'ITSO 1245', '" + Customer.PENDING + "', '?')");
st.executeUpdate("INSERT INTO CONTRACTS VALUES('7', 'JK34S5' ,
'ITSO 11', '" + Customer.PENDING + "', '?')");
msg = "The CUSTOMERS and CONTRACTS tables have been successfully
created!";
} catch (SQLException sqlEx) {
System.out.println("SQL EXCEPTION: ERROR CODE: " +
sqlEx.getErrorCode());
sqlEx.printStackTrace();
msg = "The local tables were not successfully created!";
} finally {
try {
st.close();
con.close();
} catch (SQLException e1) {
}
}
}
System.out.println(msg);
return msg;
}
/* (non-Javadoc)
Chapter 4. Database 101
* @see
com.itso.rentals.database.ITSORentalsDatabaseService#updateVechicleLocation(jav
a.lang.String, java.lang.String)
*/
public boolean updateVechicleLocation(String contractID, String
vechicleLocation) {
//update the contract indicated with the location indicated
boolean results = false;
//only allow one thread to access DB2e
synchronized (syncLock) {
try {
con = DriverManager.getConnection(driver);
st = con.createStatement();
st.executeUpdate("UPDATE CONTRACTS SET LOCATION='" +
vechicleLocation + "' WHERE CONTRACTID='" + contractID + "'");
results = true;
} catch (SQLException sqlEx) {
System.out.println("SQL EXCEPTION: ERROR CODE: " +
sqlEx.getErrorCode() + " state: " + sqlEx.getSQLState());
sqlEx.printStackTrace();
} finally {
try {
st.close();
con.close();
} catch (SQLException e1) {
}
}
}
return results;
}
/**
* For stand-alone mode, this method will insert pictures into the database
* @param customerID
* @param filename
* @return
*/
public boolean storeBlob(String customerID, String filename) {
FileInputStream fis = null;
int size = 0;
try {
fis = new FileInputStream(filename);
size = fis.available();
} catch (IOException e) {
102 WCTME: Application Development and Case Study
System.out.println("Picture store (file = "+ filename +") for
customerID = "+ customerID + " failed because " + e.getMessage());
}
if (fis != null) {
PreparedStatement pstmt = null;
//only allow one thread to access DB2e
synchronized (syncLock) {
try {
con = DriverManager.getConnection(driver);
byte[] indata = new byte[size];
// create a stream to read the file
DataInputStream in = new DataInputStream(fis);
try {
in.readFully(indata);
in.close();
} catch (IOException e1) {
e1.printStackTrace();
} // Read file contents into array
pstmt = con.prepareStatement(
"UPDATE CUSTOMERS SET PHOTO = ? WHERE CUSTOMERID = '" +
customerID + "'");
pstmt.setBytes(1, indata);
pstmt.executeUpdate();
} catch (SQLException sqlEx) {
System.out.println("SQL EXCEPTION: ERROR CODE: " +
sqlEx.getErrorCode() + " state: " + sqlEx.getSQLState());
sqlEx.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
pstmt.close();
con.close();
} catch (SQLException e1) {
}
}
}
} else {
return false;
}
return true;
}
/* (non-Javadoc)
Chapter 4. Database 103
* @see
com.itso.rentals.database.ITSORentalsDatabaseService#getPicture(java.lang.Strin
g)
*/
public InputStream getPicture(String customerID) {
InputStream is = null;
Blob data = null;
//only allow one thread to access DB2e
synchronized (syncLock) {
try {
con = DriverManager.getConnection(driver);
st = con.createStatement();
rs = st.executeQuery("SELECT PHOTO FROM CUSTOMERS WHERE CUSTOMERID
= '" + customerID + "'");
while (rs.next()) { // should only be 1
data = rs.getBlob(1);
}
} catch (SQLException sqlEx) {
System.out.println("SQL EXCEPTION: ERROR CODE: " +
sqlEx.getErrorCode() + " state: " + sqlEx.getSQLState());
sqlEx.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
st.close();
con.close();
} catch (SQLException e1) {
}
}
}
if (data != null) {
try {
is = data.getBinaryStream();
} catch (SQLException e) {
e.printStackTrace();
}
}
return is;
}
Get IBM Workplace Client Technology Micro Edition Version 5.7.1: Application Development and Case Study now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
