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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.