[JSP] 49. <JSP와 MVC 패턴을 사용한 to-do 프로젝트> (8)

[JSP] 49. <JSP와 MVC 패턴을 사용한 to-do 프로젝트> (8)

 

1. TodoDAOImpl

package com.tenco.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.mysql.cj.protocol.Resultset;

public class TodoDAOImpl implements TodoDAO {

	private DataSource dataSource;

	public TodoDAOImpl() {

		try {
			InitialContext ctx = new InitialContext();
			dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/MyDB");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void addTodo(TodoDTO dto, int principalId) {
		String sql = " INSERT INTO todos "
				+ "(user_id,title,description,due_date,completed) values (?,?,?,?,?); ";
		try(Connection conn=dataSource.getConnection()) {
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt = conn.prepareStatement(sql)){
				pstmt.setInt(1, principalId);
				pstmt.setString(2, dto.getTitle());
				pstmt.setDate(3, dto.getDueDate());
				pstmt.setBoolean(3, dto.isCompleted());
				pstmt.executeUpdate();
				conn.commit();
			} catch (Exception e) {
				conn.rollback();
			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	@Override
	public TodoDTO getTodoById(int id) {
		String sql=" SELECT * FROM todos where id = ? ";
		TodoDTO dto=null;
		try (Connection conn=dataSource.getConnection()){
			PreparedStatement pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			try (ResultSet rs = pstmt.executeQuery()){
				if(rs.next()) {
					dto=new TodoDTO();
					dto.setId(rs.getInt("id"));
					dto.setUserId(rs.getInt("user_id"));
					dto.setTitle(rs.getString("title"));
					dto.setDescription(rs.getString("description"));
					dto.setDueDate(rs.getDate("due_date"));
					dto.setCompleted(rs.getBoolean("completed"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			pstmt.executeQuery();
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		return null;
	}

	@Override
	public List<TodoDTO> getTodosByUserId(int userId) {
		String sql=" select * from todos where user_id= ? ";
		List<TodoDTO> todos=new ArrayList<TodoDTO>();
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setInt(1, userId);
				ResultSet rs=pstmt.executeQuery();
				if(rs.next()) {
					TodoDTO dto=new TodoDTO();
					dto.setId(rs.getInt("id"));
					dto.setUserId(userId);
					dto.setTitle(rs.getString("title"));
					dto.setDescription(rs.getString("description"));
					dto.setDueDate(rs.getDate("dueDate"));
					dto.setCompleted(rs.getBoolean("completed"));
					todos.add(dto);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("UserList  : "+todos.toString());
		return todos;
	}

	@Override
	public List<TodoDTO> getAllTodos() {
		String sql=" select * from todos ";
		List<TodoDTO> todos= new ArrayList<TodoDTO>();
		try(Connection conn=dataSource.getConnection()) {
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				ResultSet rs=pstmt.executeQuery();
				if(rs.next()) {
					TodoDTO dto=new TodoDTO();
					dto.setId(rs.getInt("id"));
					dto.setUserId(rs.getInt("userId"));
					dto.setTitle(rs.getString("title"));
					dto.setDescription(rs.getString("description"));
					dto.setDueDate(rs.getDate("dueDate"));
					dto.setCompleted(rs.getBoolean("completed"));
					todos.add(dto);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("UserList All : "+todos.toString());
		return todos;
	}

	@Override
	public void updateTodo(TodoDTO dto, int principalId) {
		int rowCount=0;
		String sql=" update todos set title = ? , description = ? where user_id = ? ;";
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setString(1, dto.getTitle());
				pstmt.setString(2, dto.getDescription());
				pstmt.setInt(3, principalId);
				rowCount=pstmt.executeUpdate();
				conn.commit();
			} catch (Exception e) {
				conn.rollback();
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("수정 "+rowCount);
	}

	@Override
	public void deleteTodo(int id, int principalId) {
		int rowCount=0;
		String sql = " delete from todos where user_id = ? ";
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setInt(1, principalId);
				rowCount=pstmt.executeUpdate();
				conn.commit();
			} catch (Exception e) {
				conn.rollback();
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("수정 "+rowCount);
	}
	
}

 

 

2. TestController

package com.tenco.controller;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

import com.tenco.model.TodoDAO;
import com.tenco.model.TodoDAOImpl;
import com.tenco.model.TodoDTO;
import com.tenco.model.UserDAO;
import com.tenco.model.UserDAOImpl;
import com.tenco.model.UserDTO;


@WebServlet("/test/*")
public class TestController extends HttpServlet {
	private static final long serialVersionUID = 1L;
    private UserDAO userDAO;
    private TodoDAO todoDAO;
    
    public TestController() {
        super();
    }

    @Override
    public void init() throws ServletException {
    	userDAO=new UserDAOImpl();
    	todoDAO=new TodoDAOImpl();
    }
    
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String action=request.getPathInfo();
		switch (action) {
		case "/byId": {
			// http://localhost:8080/mvc/test/byId
			//userDAO.getUserById(1);
			//userDAO.getUserByUsername("티모");
			//List<UserDTO> list=userDAO.getAllUsers();
			//if(list.size()==0)
			UserDTO dto=UserDTO.builder().password("999").email("h@naver.com").build();
			int count= userDAO.updateUser(dto, 3);
			System.out.println("카운트 :"+count);
			break;
		}
		case "/todoById": {
			TodoDTO result = todoDAO.getTodoById(1);
			System.out.println(result);
			break;
		}
		default:
			break;
		}
	}

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

}