JSP Servlet CRUD with SQL Server by JDBC API

Bởi -

JSP Servlet CRUD với SQL Server bằng JDBC API



Thêm database:
CREATE DATABASE employeedb
go

USE employeedb
go

CREATE TABLE department(
	dep_id INT PRIMARY KEY IDENTITY,
	dep_name VARCHAR(45),
)

CREATE TABLE employee(
	id INT PRIMARY KEY IDENTITY,
	name VARCHAR(45),
	salary FLOAT,
	dep_id INT REFERENCES department(dep_id)
)

INSERT INTO department VALUES('IT');
INSERT INTO department VALUES('Manager');

INSERT INTO employee VALUES('Nguyen Van A', 50.5, 1);
INSERT INTO employee VALUES('Nguyen Van B', 43.2, 2);

Tạo Web Application:




Cấu trúc source code:

Thêm các thư viện:
    JSTL 1.2.1
    mssql-jdbc 9.2.1

Trong thư mục model tạo các class Department.java và Employee.java:
package model;

public class Employee {

    private int id;
    private String name;
    private Double salary;
    private Department depId;

    public Employee() {
    }

    public Employee(String name, Double salary, Department depId) {
        this.name = name;
        this.salary = salary;
        this.depId = depId;
    }

    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;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }
   
    public Department getDepId() {
        return depId;
    }

    public void setDepId(Department depId) {
        this.depId = depId;
    }
    
    
}



package model;

import java.util.Collection;

public class Department {
    private int depId;
    private String depName;
    private Collection<Employee> employees;
   
    public int getDepId() {
        return depId;
    }

    public void setDepId(int depId) {
        this.depId = depId;
    }

    public String getDepName() {
        return depName;
    }

    public void setDepName(String depName) {
        this.depName = depName;
    }

    public Collection<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(Collection<Employee> employees) {
        this.employees = employees;
    }  
}
Tiếp theo trong thư mục common tạo DBConnection.java:
package common;

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

public class DBConnection {

    private static final String URL = "jdbc:mysql://localhost:3306/employeedb";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static Connection getConnect() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public static void main(String[] args) {
        Connection connection = DBConnection.getConnect();
        try {
            if (connection != null && !connection.isClosed()) {
                System.out.println("Yes");
            } else {
                System.out.println("No");
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}
Trong thư mục service tạo DepartmentService.java:
package service;

import common.DBConnection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Department;

public class DepartmentService {

    public List<Department> getAll() {
        try {
            List<Department> departments = new ArrayList<Department>();
            String sql = "select * from department";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Department department = new Department();
                department.setDepId(resultSet.getInt("dep_id"));
                department.setDepName(resultSet.getString("dep_name"));
                departments.add(department);
            }
            return departments;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }
    
    public Department getById(int id){
        try {
            Department department = new Department();
            String sql = "select * from department where dep_id = ?";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                department.setDepId(resultSet.getInt("dep_id"));
                department.setDepName(resultSet.getString("dep_name"));
            }
            return department;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }
}
Tiếp theo tạo class EmployeeService.java:
package service;

import common.DBConnection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Department;
import model.Employee;

public class EmployeeService {
    
    public List<Employee> getAll() {
        try {
            List<Employee> employees = new ArrayList<Employee>();
            String sql = "select * from employee";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Employee employee = new Employee();
                employee.setId(resultSet.getInt("id"));
                employee.setName(resultSet.getString("name"));
                employee.setSalary(resultSet.getDouble("salary"));
                Department department = new DepartmentService().getById(resultSet.getInt("dep_id"));
                employee.setDepId(department);
                employees.add(employee);
            }
            return employees;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }   
    public Employee getById(int id) {
        try {
            Employee employee = new Employee();
            String sql = "select * from employee where id = ?";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                employee.setId(resultSet.getInt("id"));
                employee.setName(resultSet.getString("name"));
                employee.setSalary(resultSet.getDouble("salary"));
                Department department = new DepartmentService().getById(resultSet.getInt("dep_id"));
                employee.setDepId(department);
            }
            return employee;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    public boolean insert(Employee employee) {
        try {
            String sql = "insert into employee(name, salary, dep_id) values(?, ?, ?)";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            preparedStatement.setString(1, employee.getName());
            preparedStatement.setDouble(2, employee.getSalary());
            preparedStatement.setInt(3, employee.getDepId().getDepId());
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return false;
        }
    }

    public boolean update(Employee employee) {
        try {
            String sql = "update employee set name = ?, salary = ?, dep_id = ? where id = ?";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            preparedStatement.setString(1, employee.getName());
            preparedStatement.setDouble(2, employee.getSalary());
            preparedStatement.setInt(3, employee.getDepId().getDepId());
            preparedStatement.setInt(4, employee.getId());
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return false;
        }
    }
    public boolean delete(int id) {
        try {
            String sql = "delete from employee where id = ?";
            PreparedStatement preparedStatement = DBConnection.getConnect().prepareStatement(sql);
            preparedStatement.setInt(1, id);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return false;
        }
    }}
Hiện tất cả dữ liệu
index.jsp
<jsp:forward page="ShowServlet"/>
show.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Show Page</title>
    </head>
    <body>
        <h1>Show Page</h1>
        <a href="CreateServlet">Create new employee</a>
        <table border="1">
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Salary</th>
                <th>Department</th>
                <th>Action</th>
            </tr>
            <c:forEach items="${employees}" var="employee">
                <tr>
                    <td>${employee.id}</td>
                    <td>${employee.name}</td>
                    <td>${employee.salary}</td>
                    <td>${employee.depId.depName}</td>
                    <td>
                        <a href="DeleteServlet?id=${employee.id}">Delete</a>
                        <a href="EditServlet?id=${employee.id}">Edit</a>
                    </td>
                </tr>
            </c:forEach>
        </table>
    </body>
</html>
ShowServlet.java
package controller;

import java.io.IOException;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
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 service.EmployeeService;

@WebServlet(name = "ShowServlet", urlPatterns = {"/ShowServlet"})
public class ShowServlet extends HttpServlet {

    public void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        request.setAttribute("employees",new EmployeeService().getAll());
        request.getRequestDispatcher("show.jsp").forward(request, response);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(ShowServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(ShowServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
package controller;

import java.io.IOException;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
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 service.EmployeeService;

@WebServlet(name = "ShowServlet", urlPatterns = {"/ShowServlet"})
public class ShowServlet extends HttpServlet {

    public void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        request.setAttribute("employees",new EmployeeService().getAll());
        request.getRequestDispatcher("show.jsp").forward(request, response);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(ShowServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(ShowServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Và kết quả:


Chuyển sang trang create:

Create.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Create Page</title>
    </head>
    <body>
        <h1>Create new employee</h1>
        <form action="InsertServlet" method="POST">
            <table border="1">
                <tr>
                    <td>Name</td>
                    <td><input type="text" name="name"></td>
                </tr>
                <tr>
                    <td>Salary</td>
                    <td><input type="text" name="salary"></td>
                </tr>
                <tr>
                    <td>Department</td>
                    <td>
                        <select name="depId">
                            <c:forEach items="${departments}" var="department">
                                <option value="${department.depId}">${department.depName}</option>
                            </c:forEach>
                        </select>
                    </td>
                </tr>
            </table>
            <input type="submit" value="Insert">
        </form>
    </body>
</html>

Trong class CreateServlet.java:

package controller;
import java.io.IOException;
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 service.DepartmentService;

@WebServlet(name="CreateServlet", urlPatterns={"/CreateServlet"})
public class CreateServlet extends HttpServlet {
   
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        request.setAttribute("departments", new DepartmentService().getAll());
        request.getRequestDispatcher("create.jsp").forward(request, response);
    }

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

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

}

Kết quả:


Tiếp theo tạo InsertSevlet.java:

package controller;
import java.io.IOException;
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 model.Department;
import model.Employee;
import service.DepartmentService;
import service.EmployeeService;

@WebServlet(name="InsertServlet", urlPatterns={"/InsertServlet"})
public class InsertServlet extends HttpServlet {
   
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        String name = request.getParameter("name");
        double salary = Double.parseDouble(request.getParameter("salary"));
        int depId = Integer.parseInt(request.getParameter("depId"));
        Department department = new DepartmentService().getById(depId);
        Employee employee = new Employee(name, salary, department);
        new EmployeeService().insert(employee);
        response.sendRedirect("ShowServlet");
    }

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

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

}

Kết quả:



Để chuyển sang page edit tạo như sau:

edit.jsp:

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Create Page</title>
    </head>
    <body>
        <h1>Update employee</h1>
        <form action="UpdateServlet" method="POST">
            <table border="1">
                <tr>
                    <td>Id</td>
                    <td><input type="text" name="id" value="${employee.id}" readonly></td>
                </tr>
                <tr>
                    <td>Name</td>
                    <td><input type="text" name="name" value="${employee.name}"></td>
                </tr>
                <tr>
                    <td>Salary</td>
                    <td><input type="text" name="salary" value="${employee.salary}"></td>
                </tr>
                <tr>
                    <td>Department</td>
                    <td>
                        <select name="depId">
                            <c:forEach items="${departments}" var="department">
                                <option value="${department.depId}" ${employee.depId.depId == department.depId ? "selected" : ""}>${department.depName}</option>
                            </c:forEach>
                        </select>
                    </td>
                </tr>
            </table>
            <input type="submit" value="Insert">
        </form>
    </body>
</html>

EditServlet.java:

package controller;
import java.io.IOException;
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 model.Department;
import model.Employee;
import service.DepartmentService;
import service.EmployeeService;

@WebServlet(name="EditServlet", urlPatterns={"/EditServlet"})
public class EditServlet extends HttpServlet {
   
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        int id = Integer.parseInt(request.getParameter("id"));
        Employee employee = new EmployeeService().getById(id);     
        request.setAttribute("employee", employee);
        request.setAttribute("departments", new DepartmentService().getAll());
        request.getRequestDispatcher("edit.jsp").forward(request, response);
    }

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

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

}

Kết quả:




Tạo UpdateServlet.java:

package controller;

import java.io.IOException;
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 model.Department;
import model.Employee;
import service.DepartmentService;
import service.EmployeeService;

@WebServlet(name = "UpdateServlet", urlPatterns = {"/UpdateServlet"})
public class UpdateServlet extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        int id = Integer.parseInt(request.getParameter("id"));
        Employee employee = new EmployeeService().getById(id);
        String name = request.getParameter("name");
        double salary = Double.parseDouble(request.getParameter("salary"));
        int depId = Integer.parseInt(request.getParameter("depId"));
        Department department = new DepartmentService().getById(depId);
        employee.setName(name);
        employee.setSalary(salary);
        employee.setDepId(department);
        new EmployeeService().update(employee);
        response.sendRedirect("ShowServlet");
    }

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

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

}

Kết quả:



Delete 1 employee:

DeleteServlet.java:

Google Account Video Purchases Hồ Chí Minh, Thành phố Hồ Chí Minh, Việt Nam

Nhận xét

Đăng nhận xét

Bài đăng phổ biến từ blog này

Java EE Web Application (JSP/Servlet, EJB, JPA, SQL Server, Glassfish) Full Tutorial

Bởi -
Hình ảnh
Hướng dẫn đầy đủ về Ứng dụng Web Java EE (JSP / Servlet, EJB, JPA, SQL Server, Glassfish) Vũ khí cần chuẩn bị để thực hiện ở bài viết này: NetBeans IDE 8.2 Glassfish 4.1.2 SQL Server 2021 Management Studito mssql-jdbc-9.2.1.jre8.jar Nội dung chủ yếu: - Tạo database với SQL Server 2021 Management Studito - Tạo JDBC Connection Pool và JDBC Resource riêng để tái sử dụng với Glassfish Server - Tạo project EJB CRUD
Đọc thêm

Build validation using VanillaJS for Form Submit

Bởi -
Hình ảnh
Xây dựng bắt lỗi form bằng VanillaJS để gửi biểu mẫu Chuẩn bị: - VSCode - Kiến thức HTML/CSS/JS Để bắt lỗi form trước khi gửi tới server xử lý ta làm như sau: Cấu trúc source: Tập chung vào các file sau: -   Common.js: xây dựng các hàm global sử dụng nhiều lần - Validation.js: xây dựng các hàm bắt lỗi trong đối tượng Validation - Main.js: xử lý dữ liệu được lấy từ form để submit Giao diện được xây dựng: Form: Thông báo:
Đọc thêm

Java EE Web Application (JavaServer Faces, EJB, JPA, SQL Server, Glassfish) Full Tutorial

Bởi -
Hình ảnh
Ứng dụng Web Java EE (JavaServer Faces, EJB, JPA, SQL Server, Glassfish) Hướng dẫn đầy đủ Xem phần khởi tạo connection pool với grassfish4 và tạo project EJB tại đây:  Java EE Web Application (JSP/Servlet, EJB, JPA, SQL Server, Glassfish) Full Tutorialot.com/2021/06/java-ee-web-application-jsp-servlet-ejb-jpa-sql-server-glassfish.html Tạo database tên Shopee và copy đoạn script bên dưới chạy: USE [Shopee] GO /****** Object: Table [dbo].[category] Script Date: 6/1/2021 11:13:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ category ]( [ id ] [ int ] IDENTITY (1,1) NOT NULL , [name] [ varchar ](50) NULL , CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED ( [ id ] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[product] ...
Đọc thêm