Lửng Code - Bài 3: Hướng dẫn kết nối CSDL Oracle và thực hiện chức năng CRUD cơ bản

HƯỚNG DẪN CẤU HÌNH KẾT NỐI CSDL ORACLE & HIỂN THỊ DỮ LIỆU LÊN JSP

1. Giới thiệu CSDL Oracle

Oracle Database là hệ quản trị cơ sở dữ liệu quan hệ mạnh mẽ, thường dùng trong các hệ thống lớn.
Đặc điểm:
Hỗ trợ ngôn ngữ SQL chuẩn.
Có khả năng mở rộng và bảo mật cao.
Hỗ trợ tính năng identity (tự tăng) từ phiên bản 12c trở lên.

2. Tạo CSDL Oracle

CREATE TABLE categories (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name NVARCHAR2(100)
);

CREATE TABLE products (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name NVARCHAR2(100),
    price NUMBER(10,2),
    image NVARCHAR2(255),
    category_id NUMBER,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

💡 Tip: Thêm vài dòng dữ liệu mẫu để test:

INSERT INTO categories(name) VALUES ('Hoa quả tươi');
INSERT INTO categories(name) VALUES ('Trái cây nhập khẩu');

INSERT INTO products(name, price, image, category_id) VALUES ('Táo Mỹ', 50.5, 'apple.jpg', 2);

3. Tạo dự án Dynamic Web Project

File → New → Dynamic Web Project
Đặt tên: oracle_jsp_demo

Cấu trúc thư mục:

src/main/java       → Java code (entity, services, controller)
src/main/webapp     → JSP, CSS, JS
WEB-INF/lib         → Chứa file thư viện .jar

4. Thêm thư viện JDBC

Tải ojdbc8.jar tại:
- https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
Copy file ojdbc8.jar vào:
```
src/main/webapp/WEB-INF/lib
```

5. Tạo lớp kết nối CSDL

📄 src/main/java/db/MyConnection.java

package db;

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

public class MyConnection {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl"; 
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    // Mở kết nối
    public static Connection openConnect() {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("Kết nối Oracle thành công!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    // Đóng kết nối
    public static void closeConnect(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                System.out.println("Đã đóng kết nối!");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // Test kết nối
    public static void main(String[] args) {
        Connection conn = openConnect();
        closeConnect(conn);
    }
}

6. Tạo Entity

📄 Category.java

package entity;

public class Category {
    private int id;
    private String name;

    public Category() { }

    public Category(int id, String name) {
        this.id = id;
        this.name = name;
    }

    // getter - setter
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }

    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
}

📄 Product.java

package services;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import db.MyConnection;
import entity.Category;

public class CategoryServices {

    // Lấy tất cả danh mục
    public List<Category> getAll() {
        List<Category> list = new ArrayList<>();
        String sql = "SELECT * FROM categories ORDER BY id DESC";

        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Category c = new Category(rs.getInt("id"), rs.getString("name"));
                list.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // Lấy danh mục theo ID
    public Category getById(int id) {
        String sql = "SELECT * FROM categories WHERE id = ?";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new Category(rs.getInt("id"), rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // Xóa danh mục
    public boolean deleteById(int id) {
        String sql = "DELETE FROM categories WHERE id = ?";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // Thêm mới
    public boolean create(Category category) {
        String sql = "INSERT INTO categories (name) VALUES (?)";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, category.getName());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // Cập nhật
    public boolean update(Category category) {
        String sql = "UPDATE categories SET name = ? WHERE id = ?";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, category.getName());
            ps.setInt(2, category.getId());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}

7. Tạo Services

📄 CategoryServices.java

package services;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import db.MyConnection;
import entity.Category;

public class CategoryServices {

    // Lấy tất cả danh mục
    public List<Category> getAll() {
        List<Category> list = new ArrayList<>();
        String sql = "SELECT * FROM categories ORDER BY id DESC";

        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Category c = new Category(rs.getInt("id"), rs.getString("name"));
                list.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // Lấy danh mục theo ID
    public Category getById(int id) {
        String sql = "SELECT * FROM categories WHERE id = ?";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new Category(rs.getInt("id"), rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // Xóa danh mục
    public boolean deleteById(int id) {
        String sql = "DELETE FROM categories WHERE id = ?";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // Thêm mới
    public boolean create(Category category) {
        String sql = "INSERT INTO categories (name) VALUES (?)";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, category.getName());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // Cập nhật
    public boolean update(Category category) {
        String sql = "UPDATE categories SET name = ? WHERE id = ?";
        try (Connection conn = MyConnection.openConnect();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, category.getName());
            ps.setInt(2, category.getId());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}

📄 ProductServices.java Tương tự nhưng xử lý bảng products.

8. Tạo Servlet Controller

📄 CategoryController.java

package controllers;

import entity.Category;
import services.CategoryServices;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.*;

import java.io.IOException;
import java.util.List;

@WebServlet("/category")
public class CategoryController extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private CategoryServices categoryService = new CategoryServices();

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

        switch (action) {
            case "list":
                listCategories(request, response);
                break;
            case "create":
                request.getRequestDispatcher("/category-create.jsp").forward(request, response);
                break;
            case "edit":
                editCategory(request, response);
                break;
            case "delete":
                deleteCategory(request, response);
                break;
            default:
                listCategories(request, response);
                break;
        }
    }

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

        switch (action) {
            case "create":
                createCategory(request, response);
                break;
            case "update":
                updateCategory(request, response);
                break;
        }
    }

    private void listCategories(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        List<Category> list = categoryService.getAll();
        request.setAttribute("list", list);
        request.getRequestDispatcher("/category.jsp").forward(request, response);
    }

    private void createCategory(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String name = request.getParameter("name");
        categoryService.create(new Category(0, name));
        response.sendRedirect("category?action=list");
    }

    private void editCategory(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        int id = Integer.parseInt(request.getParameter("id"));
        Category category = categoryService.getById(id);
        request.setAttribute("category", category);
        request.getRequestDispatcher("/category-edit.jsp").forward(request, response);
    }

    private void updateCategory(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        int id = Integer.parseInt(request.getParameter("id"));
        String name = request.getParameter("name");
        categoryService.update(new Category(id, name));
        response.sendRedirect("category?action=list");
    }

    private void deleteCategory(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        int id = Integer.parseInt(request.getParameter("id"));
        categoryService.deleteById(id);
        response.sendRedirect("category?action=list");
    }
}

9. Tạo JSP View

📄 category.jsp

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.util.List, entity.Category" %>
<%
    List<Category> list = (List<Category>) request.getAttribute("categories");
%>
<h2>Danh sách danh mục</h2>
<table border="1">
<tr><th>ID</th><th>Name</th></tr>
<%
    for (Category c : list) {
%>
<tr>
    <td><%= c.getId() %></td>
    <td><%= c.getName() %></td>
</tr>
<% } %>
</table>

📄 category-create.jsp <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<h2>Thêm danh mục</h2>

<form action="category" method="post">
    <input type="hidden" name="action" value="create">

    <div class="mb-3">
        <label for="name" class="form-label">Tên danh mục</label>
        <input type="text" class="form-control" id="name" name="name" required>
    </div>

    <button type="submit" class="btn btn-primary">Lưu</button>
    <a href="category?action=list" class="btn btn-secondary">Hủy</a>
</form>

📄 category-edit.jsp <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="entity.Category" %> <% Category category = (Category) request.getAttribute("category"); %>

<h2>Sửa danh mục</h2>

<form action="category" method="post">
    <input type="hidden" name="action" value="update">
    <input type="hidden" name="id" value="<%= category.getId() %>">

    <div class="mb-3">
        <label for="name" class="form-label">Tên danh mục</label>
        <input type="text" class="form-control" id="name" name="name"
               value="<%= category.getName() %>" required>
    </div>

    <button type="submit" class="btn btn-success">Cập nhật</button>
    <a href="category?action=list" class="btn btn-secondary">Hủy</a>
</form>

8. Chạy dự án

Chuột phải vào project → Run As → Run on Server.
Chọn Tomcat đã cấu hình → Finish.

Truy cập trình duyệt:

http://localhost:8080/DemoCRUD/category?action=list

9. Kiểm tra CRUD

Thêm mới: vào /category?action=create → điền form → Submit.
Sửa: /category?action=edit&id=1 → chỉnh sửa → Submit.
Xóa: /category?action=delete&id=1.

10. Gợi ý sinh viên

Tự triển khai ProductManager (controller + view) theo mẫu Category.