Current Development Challenges

During the initial developmant phase, several architectural and usability limitations were identified that require attention in future iterations:

• Layout and Z-Index Issues: The interface currently utilizes HTML iframes to merge different sections. When the application is windowed, clicking the taskbar causes the display content to render behind the taskbar, indicating a z-index stacking context problem that requires an alternative implementation strategy.
• User Interface Design: The overall aesthetic of the layout needs improvement. Future versions should integrate CSS frameworks or layout libraries to achieve a more professional appearance.
• Input Validation: Data entry is currently unstructured. There is a lack of enforced formats or input controls, such as date pickers for temporal data or dropdowns for specific categories.
• Functionality Scope: The feature set is sparse compared to mobile equivalents. The interface density is low, relying on text labels where icons would be more effective for a compact, user-friendly experience.

Data Access Layer Implementation

The data access object (DAO) handles all interactions with the underlying database. This refactored version utilizes PreparedStatement to prevent SQL injection and improve resource management.

package com.repository;

import com.db.ConnectionManager;
import com.model.NoteRecord;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoteRepository {

    public static void saveRecord(String title, String amount, String location, String timestamp) throws SQLException {
        String query = "INSERT INTO records(title, amount, location, log_time) VALUES (?, ?, ?, ?)";
        try (Connection conn = ConnectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            
            stmt.setString(1, title);
            stmt.setString(2, amount);
            stmt.setString(3, location);
            stmt.setString(4, timestamp);
            stmt.executeUpdate();
        }
    }

    public static List<NoteRecord> getAllRecords() throws SQLException {
        List<NoteRecord> dataList = new ArrayList<>();
        String query = "SELECT * FROM records";
        
        try (Connection conn = ConnectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                NoteRecord record = new NoteRecord(
                    rs.getString("title"),
                    rs.getString("amount"),
                    rs.getString("location"),
                    rs.getString("log_time")
                );
                dataList.add(record);
            }
        }
        return dataList;
    }

    public static void modifyRecord(String title, String amount, String location, String timestamp) throws SQLException {
        String query = "UPDATE records SET amount=?, location=?, log_time=? WHERE title=?";
        try (Connection conn = ConnectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            
            stmt.setString(1, amount);
            stmt.setString(2, location);
            stmt.setString(3, timestamp);
            stmt.setString(4, title);
            stmt.executeUpdate();
        }
    }

    public static void removeRecord(String title, String timestamp) throws SQLException {
        String query = "DELETE FROM records WHERE title=? AND log_time=?";
        try (Connection conn = ConnectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            
            stmt.setString(1, title);
            stmt.setString(2, timestamp);
            stmt.executeUpdate();
        }
    }

    public static List<NoteRecord> searchRecords(String criteria, String keyword) throws SQLException {
        List<NoteRecord> results = new ArrayList<>();
        String query = "SELECT * FROM records WHERE " + criteria + " = ?";
        
        try (Connection conn = ConnectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            
            stmt.setString(1, keyword);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    NoteRecord item = new NoteRecord(
                        rs.getString("title"),
                        rs.getString("amount"),
                        rs.getString("location"),
                        rs.getString("log_time")
                    );
                    results.add(item);
                }
            }
        }
        return results;
    }
}

Application Controller

The following HttpServlet acts as the controller, routing requests based on the action parameter and delegating logic to the repository layer.

package com.controller;

import com.model.NoteRecord;
import com.repository.NoteRepository;
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("/noteController")
public class NoteController extends HttpServlet {

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String action = request.getParameter("action");

        try {
            switch (action) {
                case "add":
                    processAdd(request, response);
                    break;
                case "list":
                    processList(request, response);
                    break;
                case "edit":
                    processEdit(request, response);
                    break;
                case "delete":
                    processDelete(request, response);
                    break;
                case "search":
                    processSearch(request, response);
                    break;
                default:
                    break;
            }
        } catch (Exception e) {
            throw new ServletException("Database error", e);
        }
    }

    private void processSearch(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String field = request.getParameter("field");
        String value = request.getParameter("query");
        List<NoteRecord> results = NoteRepository.searchRecords(field, value);
        
        request.setAttribute("results", results);
        request.getRequestDispatcher("view_records.jsp").forward(request, response);
    }

    private void processDelete(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String title = request.getParameter("title");
        String time = request.getParameter("timestamp");
        NoteRepository.removeRecord(title, time);
        // Redirect or forward to list view
    }

    private void processEdit(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String title = request.getParameter("title");
        String amount = request.getParameter("amount");
        String location = request.getParameter("location");
        String time = request.getParameter("timestamp");
        
        NoteRepository.modifyRecord(title, amount, location, time);
    }

    private void processList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        List<NoteRecord> allNotes = NoteRepository.getAllRecords();
        request.setAttribute("results", allNotes);
        request.getRequestDispatcher("view_records.jsp").forward(request, response);
    }

    private void processAdd(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String title = request.getParameter("title");
        String amount = request.getParameter("amount");
        String location = request.getParameter("location");
        String time = request.getParameter("timestamp");
        
        NoteRepository.saveRecord(title, amount, location, time);
        request.getRequestDispatcher("add_record.jsp").forward(request, response);
    }
}

Main Layout View

The main layout uses a frame-based structure to divide the screen into a header, navigation sidebar, and main content area.

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<html>
<head>
    <meta charset="UTF-8">
    <title>Ledger Management</title>
    <style type="text/css">
        .header-frame {
            width: 100%;
            height: 120px;
            border: none;
        }
        .nav-frame {
            float: left;
            width: 240px;
            height: 600px;
            border: none;
        }
        .content-frame {
            float: right;
            width: calc(100% - 240px);
            height: 600px;
            border: none;
        }
    </style>
</head>
<body>
    <iframe src="header.jsp" class="header-frame" scrolling="no"></iframe>
    <iframe src="sidebar.jsp" class="nav-frame" scrolling="no"></iframe>
    <iframe src="main_content.jsp" name="mainDisplay" class="content-frame"></iframe>
</body>
</html>

Data Display View

This JSP renders the list of records retrieved from the database, providing links to update or delete specific entries.

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<html>
<head>
    <meta charset="utf-8">
    <title>Record View</title>
</head>
<body>
    <div align="left">
        <h1>Transaction History:</h1>
        
        <form action="noteController?action=list" method="post">
            <table border="1">
                <tr>
                    <th>Item Name</th>
                    <th>Cost</th>
                    <th>Location</th>
                    <th>Date</th>
                    <th>Actions</th>
                </tr>
                <c:forEach items="${results}" var="record">
                    <tr>
                        <td>${record.title}</td>
                        <td>
                            <a href="edit_record.jsp?title=${record.title}&amount=${record.amount}&location=${record.location}&timestamp=${record.time}">
                                ${record.amount}
                            </a>
                        </td>
                        <td>${record.location}</td>
                        <td>${record.time}</td>
                        <td>
                            <a href="noteController?action=delete&title=${record.title}&timestamp=${record.time}">Remove</a>
                        </td>
                    </tr>
                </c:forEach>
            </table>
        </form>
    </div>
</body>
</html>