Java 5

Create the Database
sql
Copy code
CREATE DATABASE AccountDB;

USE AccountDB;

CREATE TABLE AccountDetails (
accountNumber INT PRIMARY KEY,
accountHolderName VARCHAR(100),
gender CHAR(1),
usoos VARCHAR(10),
dob DATE,
state VARCHAR(50),
city VARCHAR(50),
accountType VARCHAR(10),
balance DOUBLE
);

Create the Database
sql
Copy code
CREATE DATABASE AccountDB;

USE AccountDB;

CREATE TABLE AccountDetails (
accountNumber INT PRIMARY KEY,
accountHolderName VARCHAR(100),
gender CHAR(1),
usoos VARCHAR(10),
dob DATE,
state VARCHAR(50),
city VARCHAR(50),
accountType VARCHAR(10),
balance DOUBLE
);
3. Project Structure
Organize your project as follows:

css
Copy code
AccountManagement
│
├── src
│ ├── model
│ │ └── Account.java
│ ├── dao
│ │ └── AccountDAO.java
│ ├── controller
│ │ └── AccountServlet.java
│ └── util
│ └── DatabaseConnection.java
│
├── WebContent
│ ├── META-INF
│ ├── WEB-INF
│ │ ├── web.xml
│ ├── jsp
│ │ ├── accountForm.jsp
│ │ ├── accountList.jsp
│ │ ├── editAccount.jsp
│ └── index.jsp
│
└── lib
4. Model - Account.java
java
Copy code
package model;

public class Account {
private int accountNumber;
private String accountHolderName;
private String gender;
private String usoos;
private String dob;
private String state;
private String city;
private String accountType;
private double balance;

// Getters and Setters
// ...

}
5. DAO - AccountDAO.java
java
Copy code
package dao;

import java.sql.;
import java.util.;
import model.Account;
import util.DatabaseConnection;

public class AccountDAO {

public void addAccount(Account account) {
    try (Connection conn = DatabaseConnection.getConnection()) {
        String query = "INSERT INTO AccountDetails (accountNumber, accountHolderName, gender, usoos, dob, state, city, accountType, balance) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, account.getAccountNumber());
        pstmt.setString(2, account.getAccountHolderName());
        pstmt.setString(3, account.getGender());
        pstmt.setString(4, account.getUsoos());
        pstmt.setString(5, account.getDob());
        pstmt.setString(6, account.getState());
        pstmt.setString(7, account.getCity());
        pstmt.setString(8, account.getAccountType());
        pstmt.setDouble(9, account.getBalance());
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public List<Account> getAllAccounts() {
    List<Account> accounts = new ArrayList<>();
    try (Connection conn = DatabaseConnection.getConnection()) {
        String query = "SELECT * FROM AccountDetails";
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            Account account = new Account();
            account.setAccountNumber(rs.getInt("accountNumber"));
            account.setAccountHolderName(rs.getString("accountHolderName"));
            account.setGender(rs.getString("gender"));
            account.setUsoos(rs.getString("usoos"));
            account.setDob(rs.getString("dob"));
            account.setState(rs.getString("state"));
            account.setCity(rs.getString("city"));
            account.setAccountType(rs.getString("accountType"));
            account.setBalance(rs.getDouble("balance"));
            accounts.add(account);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return accounts;
}

public Account getAccountById(int accountNumber) {
    Account account = null;
    try (Connection conn = DatabaseConnection.getConnection()) {
        String query = "SELECT * FROM AccountDetails WHERE accountNumber = ?";
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, accountNumber);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            account = new Account();
            account.setAccountNumber(rs.getInt("accountNumber"));
            account.setAccountHolderName(rs.getString("accountHolderName"));
            account.setGender(rs.getString("gender"));
            account.setUsoos(rs.getString("usoos"));
            account.setDob(rs.getString("dob"));
            account.setState(rs.getString("state"));
            account.setCity(rs.getString("city"));
            account.setAccountType(rs.getString("accountType"));
            account.setBalance(rs.getDouble("balance"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return account;
}

public void updateAccount(Account account) {
    try (Connection conn = DatabaseConnection.getConnection()) {
        String query = "UPDATE AccountDetails SET accountHolderName = ?, gender = ?, usoos = ?, dob = ?, state = ?, city = ?, accountType = ?, balance = ? WHERE accountNumber = ?";
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, account.getAccountHolderName());
        pstmt.setString(2, account.getGender());
        pstmt.setString(3, account.getUsoos());
        pstmt.setString(4, account.getDob());
        pstmt.setString(5, account.getState());
        pstmt.setString(6, account.getCity());
        pstmt.setString(7, account.getAccountType());
        pstmt.setDouble(8, account.getBalance());
        pstmt.setInt(9, account.getAccountNumber());
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void deleteAccount(int accountNumber) {
    try (Connection conn = DatabaseConnection.getConnection()) {
        String query = "DELETE FROM AccountDetails WHERE accountNumber = ?";
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, accountNumber);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}
6. Utility Class - DatabaseConnection.java
java
Copy code
package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {

public static Connection getConnection() {
    Connection connection = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/AccountDB", "root", "password");
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

}
7. Controller - AccountServlet.java
java
Copy code
package controller;

import dao.AccountDAO;
import model.Account;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/AccountServlet")
public class AccountServlet extends HttpServlet {

private static final long serialVersionUID = 1L;
private AccountDAO accountDAO;

public void init() {
    accountDAO = new AccountDAO();
}

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    doGet(request, response);
}

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    String action = request.getParameter("action");
    if (action == null) {
        action = "LIST";
    }

    switch (action) {
        case "LIST":
            listAccounts(request, response);
            break;
        case "EDIT":
            getSingleAccount(request, response);
            break;
        case "DELETE":
            deleteAccount(request, response);
            break;
        case "INSERT":
            insertAccount(request, response);
            break;
        case "UPDATE":
            updateAccount(request, response);
            break;
        default:
            listAccounts(request, response);
            break;
    }
}

private void listAccounts(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    List<Account> list = accountDAO.getAllAccounts();
    request.setAttribute("listAccounts", list);
    RequestDispatcher dispatcher = request.getRequestDispatcher("jsp/accountList.jsp");
    dispatcher.forward(request, response);
}

private void getSingleAccount(HttpServletRequest request, HttpServletResponse response)