[Spring] 데이터베이스 연습 ( feat. mariadb, HikariCP)

 

만들어둔 커넥션 유틸로 Select 함수를 만들어보자.

	public List<TodoVO> selectALL() throws Exception {
		String sql = "select * from tbl_todo";
		
		@Cleanup Connection connection = ConnectionUtil.INSTANCE.getConnecton();
		@Cleanup PreparedStatement preparedStatement = connection.prepareStatement(sql);
		@Cleanup ResultSet resultSet = preparedStatement.executeQuery();
		
		List<TodoVO> list = new ArrayList<>();
		
		while(resultSet.next()){
			TodoVO vo = TodoVO.builder()
					.tno(resultSet.getLong("tno"))
					.title(resultSet.getString("title"))
					.dueDate(resultSet.getDate("dueDate").toLocalDate())
					.finished(resultSet.getBoolean("finished"))
					.build();
					
			list.add(vo);
		}
		
		return list;
	}

 

테스트용 함수도 만들어보자.

	@Test
	public void testList() throws Exception {
		List<TodoVO> list = todoDAO.selectALL();
		list.forEach(vo -> System.out.println(vo));
	}

 

저번 글에서 저장했던 데이터가 나온 것을 볼 수 있다.

 

하나만 가져오는 함수도 만들어 보자.

tno 에 해당하는 데이터 하나만 가져오는 함수를 제작 해 보자.

 

답

	public TodoVO selectOne(Long tno) throws Exception {
		String sql = "select * from tbl_todo where tno = ?";
		
		@Cleanup Connection connection = ConnectionUtil.INSTANCE.getConnecton();
		@Cleanup PreparedStatement preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setLong(1, tno);
		
		@Cleanup ResultSet resultSet = preparedStatement.executeQuery();
		
		// 업캐스팅 (자동 형변환)
		List<TodoVO> list = new ArrayList<>();
		
		resultSet.next();
		TodoVO vo = TodoVO.builder()
				.tno(resultSet.getLong("tno"))
				.title(resultSet.getString("title"))
				.dueDate(resultSet.getDate("dueDate").toLocalDate())
				.finished(resultSet.getBoolean("finished"))
				.build();
		
		return vo;
	}

 

테스트 함수

	@Test
	public void testSelectOne() throws Exception {
		
		Long tno = 1L;
		TodoVO vo = todoDAO.selectOne(tno);
		System.out.println(vo);
	}

 

테스트 결과

 

업데이트 함수

	public void updateOne(TodoVO todoVO) throws Exception {
		String sql = "update tbl_todo set title=(?), dueDate = (?), finished= (?) where tno = ?";
		
		@Cleanup Connection connection = ConnectionUtil.INSTANCE.getConnecton();
		@Cleanup PreparedStatement preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, todoVO.getTitle());
		preparedStatement.setDate(2, Date.valueOf(todoVO.getDueDate()));
		preparedStatement.setBoolean(3, todoVO.isFinished());
		preparedStatement.setLong(4, todoVO.getTno());
		
		preparedStatement.executeUpdate();
		
	}

 

삭제 함수

	public void DeleteOne(Long tno) throws Exception {
		String sql = "delete from tbl_todo where tno = ?";
		
		@Cleanup Connection connection = ConnectionUtil.INSTANCE.getConnecton();
		@Cleanup PreparedStatement preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setLong(1, tno);
		
		preparedStatement.executeUpdate();
		
	}

 

실행 함수

	@Deprecated
	public void testUpdateOne() throws Exception {
		TodoVO todoVO = TodoVO.builder()
				.tno(1L)
				.title("sample2")
				.dueDate(LocalDate.of(2024, 8, 29))
				.finished(true)
				.build();
		
		todoDAO.updateOne(todoVO);
	}
	
	@Test
	public void testDeleteOne() throws Exception {
		todoDAO.DeleteOne(1L);
	}