package classes; import java.util.*; import java.sql.*; public class AccountsDB { public static String addAccounts(String nric, String lastname, String firstname, String address, long phone, String clientPIN, String emailAddress, java.sql.Date birthDate, String password, byte gender ) { Connection conn = null; Statement stmt = null; try { // 1. initialize database driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // 2. Create a connection String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" + "DBQ=MovieAccountDB.mdb;"; conn = DriverManager.getConnection(database, "", ""); // 3. Create a statement. stmt = conn.createStatement(); // 4. Prepare a query String sqlQuery = "INSERT INTO Accounts VALUES(" + "'" + nric + "'," + "'" + firstname + "'," + "'" + lastname + "'," + "'" + address + "'," + phone + "," + "'" + clientPIN + "'," + "'" + emailAddress + "'," + birthDate + "'" + password + "'," + gender + "," + ")"; // 5. Execute the query stmt.execute(sqlQuery); } catch (Exception ex) { ex.printStackTrace(); return ex.toString(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } return null; } private static HashMap<Byte,String> retrieveMap(String tableName) { Connection conn = null; Statement stmt = null; HashMap<Byte,String> resultMap = new HashMap<Byte,String>(); try { // 1. initialize database driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // 2. Create a connection String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" + "DBQ=MovieAccountDB.mdb;"; conn = DriverManager.getConnection(database, "", ""); // 3. Create a statement. stmt = conn.createStatement(); // 4. Prepare a query String selTable = "SELECT * FROM " + tableName; // 5. Execute the query and get result stmt.execute(selTable); ResultSet rs = stmt.getResultSet(); while ((rs != null) && (rs.next())) { byte sn = rs.getByte(1); String gender = rs.getString(2); resultMap.put(new Byte(sn), gender); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } return resultMap; } public static HashMap<Byte,String> retrieveGender() { return retrieveMap("Gender"); } public static Accounts validateClient(String nric, String userPIN) { Connection conn = null; Statement stmt = null; try { // 1. Initialize the database driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // 2. Create a connection String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" + "DBQ=MovieAccountDB.mdb;"; conn = DriverManager.getConnection(database, "", ""); // 3. Create a statement. stmt = conn.createStatement(); // 4. Prepare a query String selQuery = "SELECT * FROM Accounts" + " WHERE nric = '" + nric + "'" + " AND clientPIN = '" + userPIN + "'"; // 5. Execute the query and get result stmt.execute(selQuery); ResultSet rs = stmt.getResultSet(); if ((rs != null) && (rs.next())) { String ic = rs.getString("nric"); String lName = rs.getString("lastname"); String fName = rs.getString("firstname"); String add = rs.getString("address"); long Phone = rs.getLong("Phone"); String cPIN = rs.getString("PinNumber"); String eMail = rs.getString("Email"); java.sql.Date bDate = rs.getDate("birthDate"); String password = rs.getString("Password"); byte gen = rs.getByte("gender"); return new Accounts(ic, lName, fName, add, Phone, cPIN, eMail, bDate, password, gen ); } } catch (Exception ex) { } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { } } return null; } }