public void registerUser(AppUser user) throws SQLException {
String query = "INSERT INTO APP_USER (USERNAME,PASSWORD) VALUES(?,?)";
try (Connection conn = util.getConnection();
PreparedStatement registerStatement = conn.prepareStatement(query); ) {
registerStatement.setString(1, user.getUserName());
registerStatement.setString(2, user.getPassWord());
registerStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
}
public void registerFlashcard(FlashcardForm flashcardForm) throws SQLException {
String query = "INSERT INTO FLASHCARD (QUESTION,ANSWER,USERNAME) VALUES(?,?,?)";
try (Connection conn = util.getConnection();
PreparedStatement registerStatement = conn.prepareStatement(query); ) {
registerStatement.setString(1, flashcardForm.getQuestion());
registerStatement.setString(2, flashcardForm.getAnswer());
registerStatement.setString(3, flashcardForm.getUsername());
registerStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
}
public List<String> retrieveQuestions(FlashcardForm flashcardform) {
String query = "SELECT * FROM FLASHCARD WHERE USERNAME = ?";
List<String> questionList = flashcardform.getQuestionList();
try (Connection conn = util.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query); ) {
pstmt.setString(1, flashcardform.getUsername());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
questionList.add(rs.getString("QUESTION"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return questionList;
}
public AppUser retrieveUser(String username) {
String query = "SELECT * FROM APP_USER WHERE USERNAME = ?";
AppUser user = null;
try (Connection conn = util.getConnection();
PreparedStatement retrieveUserStatement = conn.prepareStatement(query); ) {
retrieveUserStatement.setString(1, username);
ResultSet rs = retrieveUserStatement.executeQuery();
if (rs.next()) {
user = new AppUser(rs.getString("USERNAME"), rs.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public List<String> retrieveAnswers(FlashcardForm flashcardform) {
String query = "SELECT * FROM FLASHCARD WHERE USERNAME = ?";
List<String> answerList = flashcardform.getAnswerList();
try (Connection conn = util.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query); ) {
pstmt.setString(1, flashcardform.getUsername());
ResultSet rs = pstmt.executeQuery();
String answer;
while (rs.next()) {
answer = rs.getString("ANSWER");
answerList.add(answer);
}
} catch (SQLException e) {
e.printStackTrace();
}
return answerList;
}
public class AppUserDao {
private DSConnUtil util = DSConnUtil.getUtil();
public AppUser retrieveUser(String username) {
String query = "SELECT * FROM APP_USER WHERE USERNAME = ?";
AppUser user = null;
try (Connection conn = util.getConnection();
PreparedStatement retrieveUserStatement = conn.prepareStatement(query); ) {
retrieveUserStatement.setString(1, username);
ResultSet rs = retrieveUserStatement.executeQuery();
if (rs.next()) {
user = new AppUser(rs.getString("USERNAME"), rs.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public void registerUser(AppUser user) throws SQLException {
String query = "INSERT INTO APP_USER (USERNAME,PASSWORD) VALUES(?,?)";
try (Connection conn = util.getConnection();
PreparedStatement registerStatement = conn.prepareStatement(query); ) {
registerStatement.setString(1, user.getUserName());
registerStatement.setString(2, user.getPassWord());
registerStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
}
public void registerFlashcard(FlashcardForm flashcardForm) throws SQLException {
String query = "INSERT INTO FLASHCARD (QUESTION,ANSWER,USERNAME) VALUES(?,?,?)";
try (Connection conn = util.getConnection();
PreparedStatement registerStatement = conn.prepareStatement(query); ) {
registerStatement.setString(1, flashcardForm.getQuestion());
registerStatement.setString(2, flashcardForm.getAnswer());
registerStatement.setString(3, flashcardForm.getUsername());
registerStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
}
Random random = new Random();
public List<String> retrieveQuestions(FlashcardForm flashcardform) {
String query = "SELECT * FROM FLASHCARD WHERE USERNAME = ?";
List<String> questionList = flashcardform.getQuestionList();
try (Connection conn = util.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query); ) {
pstmt.setString(1, flashcardform.getUsername());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
questionList.add(rs.getString("QUESTION"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return questionList;
}
public List<String> retrieveAnswers(FlashcardForm flashcardform) {
String query = "SELECT * FROM FLASHCARD WHERE USERNAME = ?";
List<String> answerList = flashcardform.getAnswerList();
try (Connection conn = util.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query); ) {
pstmt.setString(1, flashcardform.getUsername());
ResultSet rs = pstmt.executeQuery();
String answer;
while (rs.next()) {
answer = rs.getString("ANSWER");
answerList.add(answer);
}
} catch (SQLException e) {
e.printStackTrace();
}
return answerList;
}
/*
* public AppReceipt withdraw(AppUser user, double amount) throws Exception
* { String query1 =
* "INSERT INTO APP_RECEIPT (A_U_ID,OPERATION,AMOUNT,BALANCE ) VALUES(?,?,?,?)"
* ; String query2 = "UPDATE APP_USER SET BALANCE = ? WHERE A_U_ID = ?";
*
* try (Connection conn = util.getConnection(); PreparedStatement pstmt1 =
* conn.prepareStatement(query1); PreparedStatement pstmt2 =
* conn.prepareStatement(query2);) {
*
* if (amount > user.getBalance()) throw new
* Exception("Sorry you don't have enough balance");
*
* pstmt1.setLong(1, user.getUserId()); pstmt1.setString(2, "withdraw");
* pstmt1.setDouble(3, amount); pstmt1.setDouble(4, user.getBalance() -
* amount); pstmt1.executeUpdate();
*
* pstmt2.setDouble(1, user.getBalance() - amount); pstmt2.setLong(2,
* user.getUserId()); pstmt2.executeUpdate();
*
* } catch (SQLException e) { e.printStackTrace(); }
*
* AppReceipt rcp = new AppReceipt(user.getUserId(), amount,
* user.getBalance() - amount, "withdraw");
*
* return rcp;
*
* }
*
* public AppReceipt deposit(AppUser user, double amount) { String query1 =
* "INSERT INTO APP_RECEIPT (A_U_ID,OPERATION,AMOUNT,BALANCE ) VALUES(?,?,?,?)"
* ; String query2 = "UPDATE APP_USER SET BALANCE = ? WHERE A_U_ID = ?";
*
* try (Connection conn = util.getConnection(); PreparedStatement pstmt1 =
* conn.prepareStatement(query1); PreparedStatement pstmt2 =
* conn.prepareStatement(query2);) {
*
* pstmt1.setLong(1, user.getUserId()); pstmt1.setString(2, "deposit");
* pstmt1.setDouble(3, amount); pstmt1.setDouble(4, user.getBalance() +
* amount); pstmt1.executeUpdate();
*
* pstmt2.setDouble(1, user.getBalance() + amount); pstmt2.setLong(2,
* user.getUserId()); pstmt2.executeUpdate();
*
* } catch (SQLException e) { e.printStackTrace(); }
*
* AppReceipt rcp = new AppReceipt(user.getUserId(), amount,
* user.getBalance() + amount, "deposit");
*
* return rcp;
*
* }
*
* public List<AppReceipt> listReceipt(long userId) {
*
* String query = "SELECT * FROM APP_RECEIPT WHERE A_U_ID = ?";
*
* List<AppReceipt> list = new ArrayList<AppReceipt>();
*
* try (Connection conn = util.getConnection(); PreparedStatement pstmt =
* conn.prepareStatement(query);) {
*
* pstmt.setLong(1, userId);
*
* ResultSet rs = pstmt.executeQuery();
*
* while (rs.next()) {
*
* AppReceipt rcp = new AppReceipt(rs.getLong("A_R_ID"),
* rs.getLong("A_U_ID"), rs.getDouble("AMOUNT"), rs.getDouble("BALANCE"),
* rs.getString("OPERATION"), rs.getDate("O_DATE"));
*
* list.add(rcp);
*
* }
*
* } catch (SQLException e) { e.printStackTrace(); }
*
* return list;
*
* }
*/
}