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

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

 

 

 

1. UserDAOImpl

package com.tenco.model;

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

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

import com.tenco.utils.DBUtil;

public class UserDAOImpl implements UserDAO{

	private DataSource dataSource;
	
	public UserDAOImpl() {
		try {
			InitialContext ctx=new InitialContext();
			dataSource=(DataSource)ctx.lookup("java:comp/env/jdbc/MyDB");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public int addUser(UserDTO userDTO) {
		int resultCount = 0;
		String sql= "INSERT INTO users(username,password,email) VALUES (?,?,?)";
		
		try (Connection conn = dataSource.getConnection()){
			// 트랜잭션 시작
			conn.setAutoCommit(false);
			
			try (PreparedStatement pstmt = conn.prepareStatement(sql)){
				pstmt.setString(1, userDTO.getUsername());
				pstmt.setString(2, userDTO.getPassword());
				pstmt.setString(3, userDTO.getEmail());
				resultCount = pstmt.executeUpdate();
				
				// 트랜잭션 커밋
				conn.commit();
				
			} catch (Exception e) {
				conn.rollback();
				e.printStackTrace();
			} // end of preparedStatement
		} catch (Exception e) {
			e.printStackTrace();
		} // end of Connection
		
		return resultCount;
	}

	
	/**
	 * SELECT 에서는 일단 트랜잭션 처리를 하지 말자.
	 * 하지만 팬텀 리드 현상(정합성을 위해서 처리하는 것도 옳은 방법이다.)
	 */
	@Override
	public UserDTO getUserById(int id) {
		String sql=" select * from users where id = ? ";
		UserDTO userDTO=null;
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setInt(1, id);
				ResultSet rs=pstmt.executeQuery();
				if(rs.next()) {
					userDTO=new UserDTO();
					userDTO.setId(rs.getInt("id"));
					userDTO.setUsername(rs.getString("username"));
					userDTO.setPassword(rs.getString("password"));
					userDTO.setEmail(rs.getString("email"));
					userDTO.setCreatedAt(rs.getString("created_at"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("UsetDTO : "+userDTO.toString());
		return userDTO;
	}

	@Override
	public UserDTO getUserByUsername(String username) {
		String sql=" select * from users where username = ? ";
		UserDTO userDTO=null;
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setString(1, username);
				ResultSet rs=pstmt.executeQuery();
				if(rs.next()) {
					userDTO=new UserDTO();
					userDTO.setId(rs.getInt("id"));
					userDTO.setUsername(rs.getString("username"));
					userDTO.setPassword(rs.getString("password"));
					userDTO.setEmail(rs.getString("email"));
					userDTO.setCreatedAt(rs.getString("created_at"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("UsetDTO By Username : "+userDTO.toString());
		return userDTO;
	}

	@Override
	public List<UserDTO> getAllUsers() {
		String sql=" select * from users ";
		// 자료구조를 사용할 때, 일단 생성시키자.
		List<UserDTO> list=new ArrayList<UserDTO>();
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				ResultSet rs=pstmt.executeQuery();
				
				while(rs.next()) {
					UserDTO userDTO=new UserDTO();
					userDTO=new UserDTO();
					userDTO.setId(rs.getInt("id"));
					userDTO.setUsername(rs.getString("username"));
					userDTO.setPassword(rs.getString("password"));
					userDTO.setEmail(rs.getString("email"));
					userDTO.setCreatedAt(rs.getString("created_at"));
					list.add(userDTO);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("UserList All : "+list.toString());
		return list;
	}

	@Override
	public int updateUser(UserDTO userDTO, int principalId) {
		int rowCount=0;
		String sql=" update users set password= ?, email= ? where id= ? ; ";
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			
			
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setString(1, userDTO.getPassword());
				pstmt.setString(2, userDTO.getEmail());
				pstmt.setInt(3, principalId);
				rowCount = pstmt.executeUpdate();
				conn.commit();
			} catch (Exception e) {
				conn.rollback();
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rowCount;
	}

	@Override
	public int deleteUser(int id) {
		int rowCount=0;
		String sql=" delete from users where id = ? ";
		try (Connection conn=dataSource.getConnection()){
			conn.setAutoCommit(false);
			
			
			try (PreparedStatement pstmt=conn.prepareStatement(sql)){
				pstmt.setInt(1, id);
				rowCount = pstmt.executeUpdate();
				conn.commit();
			} catch (Exception e) {
				conn.rollback();
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 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.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;

    public TestController() {
        super();
    }

    @Override
    public void init() throws ServletException {
    	userDAO=new UserDAOImpl();
    }
    
	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;
		}
		default:
			break;
		}
	}

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

}