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

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

 

1. getTodosById (ID로 Todolist 조회하기)
(1) 내가 쓴 코드

@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.getString("due_date"));
					dto.setCompleted(rs.getInt("completed"));
					todos.add(dto);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("UserList  : "+todos.toString());
		return todos;
	}

 

(2) 선생님이 작성하신 코드

public List<TodoDTO> getTodosByUserId(int userId) {
		String sql = " String * from todos where user_id = ? ";
		List<TodoDTO> todos= new ArrayList<>();
		try (Connection conn=dataSource.getConnection()){
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			try (ResultSet rs=pstmt.executeQuery()){
				while(rs.next()) {
					TodoDTO 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.getString("due_date"));
					dto.setCompleted(rs.getString("completed"));
					todos.add(dto);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

 

 

 

2. UpdateTodo
(1) 내가 쓴 코드

	@Override
	public void updateTodo(TodoDTO dto, int principalId) {
		int rowCount=0;
		String sql=" update todos set title = ? , description = ? where 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);
	}

 

(2) 선생님이 쓰신 코드

public void updateTodo(TodoDTO dto,int principalId){
	// SELECT <-- 해당 사용자가 있는지 없는지 확인 과정 필요
	String sql="Update todos set title = ? , description = ? , due_date = ? , completed = ? where id = ? and 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.setString(3,dto.getDueDate());
			pstmt.setString(4,dto.getCompleted());
			pstmt.setInt(5,dto.getId());
			pstmt.setInt(6,principalId);
			pstmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}

 

3. DeleteTodo
(1) 내가 쓴 코드

	@Override
	public void deleteTodo(int id, int principalId) {
		int rowCount=0;
		String sql = " delete from todos 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();
		}
		
		System.out.println("수정 "+rowCount);
	}
	
}

 

(2) 선생님이 쓰신 코드

public void deleteTodo(int id,int principalId){
	String sql = "DELETE FROM todos where id = ? and user_id = ? ";
	try(Connection conn=dataSource.getConnection()){
		conn.setAutoCommit(false);
		try(PreparedStatement pstmt=conn.prepareStatement(sql)){
			pstmt.setInt(1,id)
			pstmt.setInt(2,principalId);
			pstmt.executeUpdate();
			conn.commit();	
		} catch(Exception e){
			conn.rollback();
			e.printStackTrace();
	} catch(Exception e){
		e.printStackTrace();
	}
}