public List<CustomerEntity> retrieveAllCustomer() { // declare local variables ArrayList<CustomerEntity> list = new ArrayList<CustomerEntity>(); ResultSet rs = null; DBController db = new DBController(); String dbQuery; // Step 1 - connect to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "SELECT * FROM customer where enabled = 1"; // step 3 - using DBController readRequest method rs = db.readRequest(dbQuery); try { while (rs.next()) { CustomerEntity customer = convertToCustomer(rs); list.add(customer); } } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return list; }
public boolean deleteTeller(String uid) { // declare local variables boolean success = false; DBController db = new DBController(); String dbQuery; PreparedStatement pstmt; db.getConnection(); dbQuery = "UPDATE customer SET banker = '0' WHERE uid = ? and enabled = 1"; pstmt = db.getPreparedStatement(dbQuery); try { pstmt.setString(1, uid); if (pstmt.executeUpdate() == 1) success = true; pstmt.close(); } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return success; }
public CustomerEntity getCustomerByUid(String uid) { // declare local variables CustomerEntity customer = new CustomerEntity(); ResultSet rs = null; PreparedStatement pstmt; DBController db = new DBController(); String dbQuery; // Step 1 - connect to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "SELECT * FROM customer where uid = ?"; pstmt = db.getPreparedStatement(dbQuery); try { pstmt.setString(1, uid); rs = pstmt.executeQuery(); if (rs.next()) { // first record found customer = convertToCustomer(rs); } } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return customer; }
// -------------------------TELLER-------------------------------- public boolean createTellerWithUid(String uid) { boolean success = false; DBController db = new DBController(); String dbQuery; PreparedStatement pstmt; // get connection db.getConnection(); // update customer 'banker' attribute with specified uid // helps with login dbQuery = "UPDATE customer SET banker = '1' WHERE uid = ? and enabled = 1"; pstmt = db.getPreparedStatement(dbQuery); try { pstmt.setString(1, uid); if (pstmt.executeUpdate() == 1) { success = true; } System.out.println(pstmt.executeUpdate()); pstmt.close(); } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return success; }
public CustomerEntity getTellerById(String id) { // declare local variables CustomerEntity teller = null; ResultSet rs = null; DBController db = new DBController(); String dbQuery; PreparedStatement pstmt; // step 1 - connect to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "SELECT * FROM customer WHERE id = ? and enabled = 1"; pstmt = db.getPreparedStatement(dbQuery); // step 3 - execute query try { pstmt.setString(1, id); rs = pstmt.executeQuery(); if (rs.next()) { // first record found teller = convertToCustomer(rs); } } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return teller; }
public List<CustomerEntity> getTopFiveCustomerWithdraw() { // declare local variables ArrayList<CustomerEntity> list = new ArrayList<CustomerEntity>(); ResultSet rs = null; DBController db = new DBController(); String dbQuery; // Step 1 - connect to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "select * from customer group by withdraw DESC limit 5;"; // step 3 - using DBController readRequest method rs = db.readRequest(dbQuery); try { while (rs.next()) { CustomerEntity customer = convertToCustomer(rs); list.add(customer); } } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return list; }
public boolean deleteCustomerByUid(String uid) { // declare local variables boolean success = false; DBController db = new DBController(); String dbQuery; PreparedStatement pstmt; // step 1 - establish connection to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "DELETE FROM customer WHERE uid = ?"; pstmt = db.getPreparedStatement(dbQuery); // step 3 - to delete record using executeUpdate method try { pstmt.setString(1, uid); if (pstmt.executeUpdate() == 1) success = true; pstmt.close(); } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return success; }
public boolean updateCustomerClassByUid(String classs, String uid) { boolean success = false; DBController db = new DBController(); String dbQuery; PreparedStatement pstmt; // step 1 - establish connection to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "UPDATE customer SET classs = ? WHERE uid = ?"; pstmt = db.getPreparedStatement(dbQuery); try { pstmt.setString(1, classs); pstmt.setString(2, uid); // step 3 - to update record using executeUpdate method if (pstmt.executeUpdate() == 1) success = true; pstmt.close(); } catch (Exception e) { e.printStackTrace(); } System.out.println(success); // step 4 - close connection db.terminate(); return success; }
public List<CustomerEntity> retrieveAllCustomerByName(String name) { // declare local variables ArrayList<CustomerEntity> list = new ArrayList<CustomerEntity>(); ResultSet rs = null; PreparedStatement pstmt = null; DBController db = new DBController(); String dbQuery; // Step 1 - connect to database db.getConnection(); // step 2 - declare the SQL statement dbQuery = "SELECT * FROM customer where name LIKE ? and enabled = 1"; pstmt = db.getPreparedStatement(dbQuery); try { pstmt.setString(1, "%" + name + "%"); System.out.println(name); rs = pstmt.executeQuery(); while (rs.next()) { CustomerEntity customer = convertToCustomer(rs); list.add(customer); } } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return list; }
public static boolean updateCreditCard(CreditCard cc, int ID) throws SQLException { String cardNumber = cc.getCardNumber(); int expiryMonth = cc.getExpiryMonth(); int expiryYear = cc.getExpiryYear(); String expiryDate = cc.getExpiryDate(); String cardHolderName = cc.getCardHolderName(); String country = cc.getCountry(); int CVC = cc.getCVC(); String streetAddress = cc.getStreetAddress(); String cardType = cc.getCardType(); boolean success = false; DBController db = new DBController(); String dbQuery = "UPDATE creditcard set CardNumber = '" + cardNumber + "'," + "ExpiryMonth='" + expiryMonth + "',ExpiryYear='" + expiryYear + "'," + "ExpiryDate='" + expiryDate + "',CardHolderName='" + cardHolderName + "'," + "Country='" + country + "',CVC='" + CVC + "',StreetAddress='" + streetAddress + "'," + "cardType='" + cardType + "'WHERE ID = '" + ID + "'"; db.getConnection(); if (db.updateRequest(dbQuery) == 1) { success = true; } db.terminate(); return success; }
public boolean toggleAccount(String nric) { DBController db = new DBController(); boolean success = false; String dbQuery; PreparedStatement pstmt; db.getConnection(); // step 2 - declare the SQL statement dbQuery = "UPDATE customer SET enabled = !enabled WHERE nric = ?"; pstmt = db.getPreparedStatement(dbQuery); // step 3 - to delete record using executeUpdate method try { pstmt.setString(1, nric); if (pstmt.executeUpdate() == 1) success = true; pstmt.close(); } catch (Exception e) { e.printStackTrace(); } // step 4 - close connection db.terminate(); return success; }