JSP 게시판 MS-SQL

1. 요구사항 분석

• 리스트 출력: JSP에서 데이터 목록을 테이블 형식으로 출력.
• CRUD 기능:
  • 생성 (Create): 새 데이터를 추가하는 폼 제공.
  • 조회 (Read): 저장된 데이터를 리스트로 출력.
  • 수정 (Update): 기존 데이터를 수정하는 기능.
  • 삭제 (Delete): 선택한 데이터를 삭제.

---

2. 프로젝트 구조

/WEB-INF/
 ├── views/
 │   ├── list.jsp   (리스트 출력)
 │   ├── form.jsp   (데이터 입력 폼)
 │   ├── edit.jsp   (데이터 수정 폼)
 │   ├── delete.jsp (데이터 삭제)
 ├── web.xml
 ├── controller/
 │   ├── StudentServlet.java (전체 CRUD 처리)
 ├── model/
 │   ├── Student.java (데이터 모델)
 ├── repository/
 │   ├── StudentRepository.java (데이터 관리)

---

3. 코드 구현

(1) Student.java (데이터 모델)

package model;

public class Student {
    private int id;
    private String name;
    private String email;

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

    public int getId() { return id; }
    public String getName() { return name; }
    public String getEmail() { return email; }

    public void setName(String name) { this.name = name; }
    public void setEmail(String email) { this.email = email; }
}

---

(2) StudentRepository.java (데이터 저장소)

MS-SQL 테이블 생성

먼저 MS-SQL에서 students 테이블을 생성합니다.

 

CREATE TABLE students ( id INT IDENTITY(1,1) PRIMARY KEY,

name NVARCHAR(100) NOT NULL,

email NVARCHAR(100) NOT NULL );

 

 

DbConnectionUtil.java (DB 연결 관리)

package repository;

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

public class DbConnectionUtil {
    private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=your_database;encrypt=true;trustServerCertificate=true";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";

    static {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("SQL Server JDBC 드라이버를 찾을 수 없습니다.", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
}

 

 

package repository;

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

public class StudentRepository {

    public List<Student> findAll() {
        List<Student> students = new ArrayList<>();
        String query = "SELECT id, name, email FROM students";

        try (Connection conn = DbConnectionUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                students.add(new Student(rs.getInt("id"), rs.getString("name"), rs.getString("email")));
            }
        } catch (SQLException e) {
            throw new RuntimeException("학생 목록 조회 중 오류 발생", e);
        }
        return students;
    }

    public Student findById(int id) {
        String query = "SELECT id, name, email FROM students WHERE id = ?";
        try (Connection conn = DbConnectionUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new Student(rs.getInt("id"), rs.getString("name"), rs.getString("email"));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("학생 조회 중 오류 발생", e);
        }
        return null;
    }

    public void save(Student student) {
        String query = "INSERT INTO students (name, email) VALUES (?, ?)";
        try (Connection conn = DbConnectionUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setString(1, student.getName());
            stmt.setString(2, student.getEmail());
            stmt.executeUpdate();

            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    student = new Student(generatedKeys.getInt(1), student.getName(), student.getEmail());
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("학생 등록 중 오류 발생", e);
        }
    }

    public void update(int id, String name, String email) {
        String query = "UPDATE students SET name = ?, email = ? WHERE id = ?";
        try (Connection conn = DbConnectionUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setString(1, name);
            stmt.setString(2, email);
            stmt.setInt(3, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("학생 수정 중 오류 발생", e);
        }
    }

    public void delete(int id) {
        String query = "DELETE FROM students WHERE id = ?";
        try (Connection conn = DbConnectionUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setInt(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("학생 삭제 중 오류 발생", e);
        }
    }
}​

 

---

(3) StudentServlet.java (컨트롤러)

@WebServlet("/students")
public class StudentServlet extends HttpServlet {
    private final StudentRepository repository = new StudentRepository();

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

        if ("edit".equals(action)) {
            int id = Integer.parseInt(request.getParameter("id"));
            request.setAttribute("student", repository.findById(id));
            request.getRequestDispatcher("/WEB-INF/views/edit.jsp").forward(request, response);
        } else {
            List<Student> students = repository.findAll();
            request.setAttribute("students", students);
            request.getRequestDispatcher("/WEB-INF/views/list.jsp").forward(request, response);
        }
    }

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

        if ("add".equals(action)) {
            String name = request.getParameter("name");
            String email = request.getParameter("email");
            repository.save(new Student(0, name, email));
        } else if ("update".equals(action)) {
            int id = Integer.parseInt(request.getParameter("id"));
            String name = request.getParameter("name");
            String email = request.getParameter("email");
            repository.update(id, name, email);
        } else if ("delete".equals(action)) {
            int id = Integer.parseInt(request.getParameter("id"));
            repository.delete(id);
        }

        response.sendRedirect("students");
    }
}

---

4. JSP 페이지

(4) list.jsp (리스트 페이지)

<%@ page contentType="text/html; charset=UTF-8" %>
<html>
<head>
    <title>학생 목록</title>
</head>
<body>
    <h2>학생 목록</h2>
    <table border="1">
        <tr><th>ID</th><th>이름</th><th>이메일</th><th>액션</th></tr>
        <c:forEach var="student" items="${students}">
            <tr>
                <td>${student.id}</td>
                <td>${student.name}</td>
                <td>${student.email}</td>
                <td>
                    <a href="students?action=edit&id=${student.id}">수정</a>
                    <form action="students" method="post" style="display:inline;">
                        <input type="hidden" name="id" value="${student.id}">
                        <input type="hidden" name="action" value="delete">
                        <button type="submit">삭제</button>
                    </form>
                </td>
            </tr>
        </c:forEach>
    </table>
    <a href="form.jsp">새 학생 추가</a>
</body>
</html>

 

---

(5) form.jsp (학생 추가 페이지)

<%@ page contentType="text/html; charset=UTF-8" %>
<html>
<head>
    <title>학생 추가</title>
</head>
<body>
    <h2>학생 추가</h2>
    <form action="students" method="post">
        <input type="hidden" name="action" value="add">
        이름: <input type="text" name="name"><br>
        이메일: <input type="text" name="email"><br>
        <button type="submit">추가</button>
    </form>
</body>
</html>

---

(6) edit.jsp (학생 수정 페이지)

<%@ page contentType="text/html; charset=UTF-8" %>
<html>
<head>
    <title>학생 수정</title>
</head>
<body>
    <h2>학생 수정</h2>
    <form action="students" method="post">
        <input type="hidden" name="action" value="update">
        <input type="hidden" name="id" value="${student.id}">
        이름: <input type="text" name="name" value="${student.name}"><br>
        이메일: <input type="text" name="email" value="${student.email}"><br>
        <button type="submit">수정</button>
    </form>
</body>
</html>