A Desktop Book Management System

A university coursework project: a Java Swing desktop application for managing a bookshop's stock. The data lives in MySQL and is reached through plain JDBC — no ORM.

Four roles, three doors#

There is no single all-in-one screen. Every row in the Account table carries a role, and Login.checkLogin() decides which window opens: Admin for the manager, NhapKho for goods-in staff, XuatKho for goods-out staff. The status column locks an account without deleting it. Re-reading the repo today I noticed the seed data also has a warehouse-manager role that checkLogin() has no branch for — a loose end I never spotted while I was building it.

Three layers: view, dao, model#

src splits into view (NetBeans-generated JFrames), model, dao and controller. The piece I still like is dao/DAOInterface.java, a generic interface with exactly five methods — insert, update, delete, selectAll, selectById. Every DAO implements it, so by the time I wrote PhieuXuatDAO I already knew what it would look like.

public int updateSoLuong(String id, int soluong) {
    int ketQua = 0;
    try {
        Connection con = JDBCUtils.getConnection();
        String sql = "UPDATE Sach SET stock_quantity=? WHERE id=?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setInt(1, soluong);
        pst.setString(2, id);
        ketQua = pst.executeUpdate();
        JDBCUtils.closeConnection(con);
    } catch (SQLException ex) {
        Logger.getLogger(SachDAO.class.getName()).log(Level.SEVERE, null, ex);
    }
    return ketQua;
}

Every query under dao/ goes through a PreparedStatement with ? placeholders; nowhere is SQL concatenated from a text field. The trade-off is that JDBCUtils.getConnection() opens a fresh connection on every DAO call, with the MySQL password hardcoded in it.

Stock moves with the receipts#

The on-hand count is the stock_quantity column on Sach, while the individual lines live in ChiTietPhieuNhap and ChiTietPhieuXuat, keyed back to both the receipt and the book:

ALTER TABLE `ChiTietPhieuXuat`
  ADD CONSTRAINT `FK_ChiTietPhieuXuat_PhieuXuat`
      FOREIGN KEY (`maPhieu`) REFERENCES `PhieuXuat` (`maPhieu`),
  ADD CONSTRAINT `FK_ChiTietPhieuXuat_Sach`
      FOREIGN KEY (`id`) REFERENCES `Sach` (`id`);

On confirming a goods-out, the form writes the receipt, then walks the lines and subtracts from stock:

PhieuXuatDAO.getInstance().insert(pn);
SachDAO sdao = SachDAO.getInstance();
for (var i : CTPhieu) {
    ChiTietPhieuXuatDAO.getInstance().insert(i);
    sdao.updateSoLuong(i.getId(),
            sdao.selectById(i.getId()).getStockQuantity() - i.getSoLuong());
}

This is the part I would rewrite first. Those writes are three separate transactions: lose the connection mid-loop and the receipt exists while the stock count is wrong. There is not a single setAutoCommit(false) in the repository.

Printing receipts, loading them from Excel#

controller/WritePDF.java prints receipts with iText 5. The trap was fonts: iText's default font strips every Vietnamese diacritic, so I had to embed Roboto with BaseFont.IDENTITY_H encoding. Going the other way, the receipt forms read .xlsx files through Apache POI to load many lines at once instead of typing each title. Login uses BCrypt (gensalt(12)), and password recovery mails an OTP over Gmail's SMTP.

Outcome#

• A complete working app: role-based login, book CRUD, goods-in/goods-out receipts that adjust stock, search, and Vietnamese-correct PDF output
• The habit of PreparedStatement everywhere and a view/dao/model split, both of which I carried into later projects
• The limits I only saw on re-reading: multi-table work with no transaction, controller/SearchProduct.java calling selectAll() and filtering with contains() in Java instead of letting MySQL do WHERE ... LIKE, and a Gmail app password sitting in plain text in controller/SendEmailSMTP.java, pushed straight to GitHub — the most expensive lesson of the whole project