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