protected collegeTable getCollege(String id, Connection con) throws SQLException {
try {
ResultSet rs = null;
Statement statement = con.createStatement();
rs =
statement.executeQuery(
"SELECT * FROM "
+ TABLECOLLEGES
+ " WHERE "
+ collegeTable.ID
+ " = "
+ id
+ " LIMIT 1");
// if found
if (rs.next()) {
collegeTable table = new collegeTable();
table.setID(id);
table.setShort(rs.getString(collegeTable.SHORTNAME));
table.setFull(rs.getString(collegeTable.FULLNAME));
return table;
} else {
return null; // not found
}
} catch (Exception e) {
log.writeException(e.getMessage());
throw new SQLException(e.getMessage());
}
} // end getCollege
/** moves data into certain tables to simulate the buying process */
private boolean buy(booksTable book, User buyer, Connection con) throws SQLException {
try {
Statement st = con.createStatement();
/*all parameters are considered to be full--
*book is completed and so as buyer object
*/
con.setAutoCommit(false);
// 1st add purchase to buyers table
st.addBatch(
"INSERT INTO "
+ TABLEBUYERS
+ " ("
+ buyersTable.BOOKID
+ ","
+ buyersTable.BUYERID
+ ","
+ buyersTable.DATE
+ ") values (\""
+ book.getID()
+ "\",\""
+ buyer.getID()
+ "\",\""
+ new DateObject().getDate()
+ "\")");
// 2nd add the book to old books
String[] oldbooksFields = {
oldBooksTable.SELLERID, oldBooksTable.BUYERID,
oldBooksTable.BOOKID, oldBooksTable.TITLE,
oldBooksTable.AUTHOR, oldBooksTable.ISBN,
oldBooksTable.CONDITION, oldBooksTable.PRICE,
oldBooksTable.COMMENT, oldBooksTable.COLLEGE,
oldBooksTable.DATE
};
String[] oldbooksValues = {
book.getSellerID(),
buyer.getID(),
book.getID(),
book.getTitle(),
book.getAuthor(),
book.getISBN(),
book.getCondition() + "",
book.getPrice() + "",
book.getComment(),
book.getCollegeID() + "",
new DateObject().getDate()
};
st.addBatch(
"INSERT INTO "
+ TABLEOLDBOOKS
+ " ("
+ sqlUtils.sql_fields(oldbooksFields)
+ ") values ("
+ sqlUtils.sql_values(oldbooksValues)
+ ")");
// 3rd delete record from booksTable
st.addBatch(
"DELETE FROM " + TABLEBOOKS + " WHERE " + booksTable.ID + " = '" + book.getID() + "'");
// execute all 3 steps and set auto commit mode
int[] updateCounts = st.executeBatch();
if (updateCounts[0] == 1 && updateCounts[1] == 1 && updateCounts[2] == 1) {
return true;
} else {
return false;
}
} catch (SQLException e) {
log.writeException(e.getMessage());
throw e;
} finally {
con.setAutoCommit(true);
}
}