[JSP] Java web으로 SQL CRUD 구현하기

코드

#  DB 연동

package com.tenco.todo.utils;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class DBHelper {
    
    private static final String DB_HOST = "localhost";
    private static final String DB_PORT = "3306";
    private static final String DB_DATABASE_NAME = "todo_db";
    private static final String DB_CHARSET = "UTF-8";
    private static final String DB_USER_NAME = "root";
    private static final String DB_PASSWORD = "1234";
    
    private Connection conn;
    
    public Connection getConnection() {
        
        if (conn == null) {
            String urlFormat = "jdbc:mysql://%s:%s/%s?serverTimezone=Asia/Seoul&characterEncoding=%s"; // 쿼리 파라미터 
            String url = String.format(urlFormat, DB_HOST, DB_PORT, DB_DATABASE_NAME, DB_CHARSET);
            
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(url, DB_USER_NAME, DB_PASSWORD);
                System.out.println(">>> DB 연결 완료 <<<");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
    
    public void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
}
 

 

#  TodoDTO

package com.tenco.todo.dto;
 
public class TodolistDTO {
 
    private int id;
    private String title;
    private String description;
    private int priority;
    private boolean completed;
    private String createdAt;
    private int categoryId;
    
    public TodolistDTO(String title, String desription, int priority, int categoryId) {
        this.title = title;
        this.description = desription;
        this.priority = priority;
        this.categoryId = categoryId;
    }
    
    public TodolistDTO() {
        // TODO Auto-generated constructor stub
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String desription) {
        this.description = desription;
    }
    public int getPriority() {
        return priority;
    }
    public void setPriority(int priority) {
        this.priority = priority;
    }
    public boolean getCompleted() {
        return completed;
    }
    public void setCompleted(boolean completed) {
        this.completed = completed;
    }
    public String getCreatedAt() {
        return createdAt;
    }
    public void setCreatedAt(String createdAt) {
        this.createdAt = createdAt;
    }
    public int getCategoryId() {
        return categoryId;
    }
    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }
 
    @Override
    public String toString() {
        return "TodolistDTO [id=" + id + ", title=" + title + ", desription=" + description + ", priority=" + priority
                + ", completed=" + completed + ", createdAt=" + createdAt + ", categoryId=" + categoryId + "]";
    }
    
}
 

 

#  TodoDAO

package com.tenco.todo.repository;
 
import java.util.ArrayList;
 
import com.tenco.todo.dto.TodolistDTO;
 
public interface ITodoRepo {
 
    ArrayList<TodolistDTO> select();
    int updateTrue(int id);
    int updateFalse(int id);
    int insert(TodolistDTO todolistDTO);
    int delete(int id);
    
}
 

 

package com.tenco.todo.repository;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
 
import com.tenco.todo.dto.TodolistDTO;
import com.tenco.todo.utils.DBHelper;
 
public class TodoDAO implements ITodoRepo {
 
    private Connection conn;
    
    public TodoDAO() {
        conn = new DBHelper().getConnection();
    }
        
    @Override
    public ArrayList<TodolistDTO> select() {
        ArrayList<TodolistDTO> resultList = new ArrayList<>();
        String query = " SELECT * FROM todolist ";
 
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = conn.prepareStatement(query);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                TodolistDTO dto = new TodolistDTO();
                dto.setId(rs.getInt("id"));
                dto.setTitle(rs.getString("title"));
                dto.setDescription(rs.getString("description"));
                dto.setPriority(rs.getInt("priority"));
                boolean comp = (rs.getInt("completed") == 0) ? false : true;
                dto.setCompleted(comp);
                dto.setCreatedAt(rs.getString("createdAt"));
                dto.setCategoryId(rs.getInt("categoryId"));
                resultList.add(dto);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultList;
    }
 
    @Override
    public int updateTrue(int id) {
        int resultCount = 0;
        String query = " UPDATE todolist SET completed = 1 WHERE id = ? ";
        
        PreparedStatement pstmt = null;
        
        try {
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, id);
            resultCount = pstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultCount;
    }
    
    @Override
    public int updateFalse(int id) {
        int resultCount = 0;
        String query = " UPDATE todolist SET completed = 0 WHERE id = ? ";
        
        PreparedStatement pstmt = null;
        
        try {
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, id);
            resultCount = pstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultCount;
    }
 
    @Override
    public int insert(TodolistDTO todolistDTO) {
        int resultCount = 0;
        String query = " INSERT INTO todolist (title, description, priority, categoryId) VALUES (?, ?, ?, ?) ";
        
        PreparedStatement pstmt = null;
        
        try {
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, todolistDTO.getTitle());
            pstmt.setString(2, todolistDTO.getDescription());
            pstmt.setInt(3,  todolistDTO.getPriority());
            pstmt.setInt(4, todolistDTO.getCategoryId());
            resultCount = pstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultCount;
    }
 
    @Override
    public int delete(int id) {
        int resultCount = 0;
        String query = " DELETE FROM todolist WHERE id = ? ";
        
        PreparedStatement pstmt = null;
        
        try {
            pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, id);
            resultCount = pstmt.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultCount;
    }
 
 
}
 

 

#  Servlet

package com.tenco.todo.test;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
 
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 com.tenco.todo.dto.TodolistDTO;
import com.tenco.todo.repository.TodoDAO;
 
@WebServlet("/tTest")
public class TodoTest extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    public TodoTest() {
        super();
    }
 
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        TodoDAO dao = new TodoDAO();
 
        String action = request.getParameter("action");
        String tid = request.getParameter("tid");
 
        if ("delete".equals(action)) {
            dao.delete(Integer.parseInt(tid));
            response.sendRedirect("/todo2/tTest");
 
        } else if ("update".equals(action)) {
            boolean tComp = Boolean.parseBoolean(request.getParameter("tComp"));
 
            if (tComp == false) {
                dao.updateTrue(Integer.parseInt(tid));
            } else {
                dao.updateFalse(Integer.parseInt(tid));
            }
 
            response.sendRedirect("/todo2/tTest");
 
        } else {
            ArrayList<TodolistDTO> resultList = dao.select();
            request.setCharacterEncoding("UTF-8");
            request.setAttribute("list", resultList);
            request.getRequestDispatcher("todoTest/todoList.jsp").forward(request, response);
        }
 
    }
 
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String action = request.getParameter("action");
        TodoDAO todoDAO = new TodoDAO();
        int responseCount = 0;
 
        if (action.equals("insert")) {
            String title = request.getParameter("title");
            String description = request.getParameter("description");
            int priority = Integer.parseInt(request.getParameter("priority"));
            int categoryId = Integer.parseInt(request.getParameter("categoryId"));
            TodolistDTO dto = new TodolistDTO(title, description, priority, categoryId);
            responseCount = todoDAO.insert(dto);
        }
 
        response.setContentType("text/plain; charset=utf-8");
        PrintWriter out = response.getWriter();
        out.print("적용된 개수 확인 : " + responseCount);
    }
 
}
 

 

 

---

실행

  - css는 부트스트랩 4 사용