[JDBC] 과제_0825 (4)

앞선 내용을 바탕으로 전체 조회 및 단건 조회, 조인 조회 메소드를 생성한다.

	public List<MembersVO> selectAllMembers (){

		Connection conn = makeConnection();

		//쿼리 생성
		StringBuffer query = new StringBuffer();
		query.append(" SELECT MEMBER_ID ");
		query.append("      , MEMBER_NAME ");
		query.append("      , MEMBER_GRADE ");
		query.append("      , PROFILE_IMG ");
		query.append("   FROM MEMBERS ");

		//쿼리 실행 준비
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(query.toString());
		} catch (SQLException e) {
			System.out.println("쿼리에 문제가 있습니다.");
			System.out.println(e.getMessage());
			closeConnection(conn);
			return null;
		}

		//쿼리 실행 결과 받아오기
		ResultSet rs = null;
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			System.out.println("쿼리 실행 결과를 받아오는 중에 문제가 생겼습니다."); 
			System.out.println("사유: " + e.getMessage());
			closePreparedStatement(pstmt);
			closeConnection(conn);
			return null;
		}

		List<MembersVO> members = new ArrayList<>();
		MembersVO memberVO = null;

		try {
			while (rs.next()) {
				memberVO = new MembersVO();
				memberVO.setMemberId( rs.getString("MEMBER_ID") );
				memberVO.setMemberName( rs.getString("MEMBER_NAME") );
				memberVO.setMemberGrade( rs.getString("MEMBER_GRADE") );
				memberVO.setProfileImg( rs.getString("PROFILE_IMG") );

				members.add(memberVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}

		return members;
	}

	public MembersVO selectOneMemberByMemberId (String memberId) {

		Connection conn = makeConnection();

		//쿼리 생성
		StringBuffer query = new StringBuffer();
		query.append(" SELECT MEMBER_ID ");
		query.append("      , MEMBER_NAME ");
		query.append("      , MEMBER_GRADE ");
		query.append("      , PROFILE_IMG ");
		query.append("   FROM MEMBERS ");
		query.append("  WHERE MEMBER_ID = ? ");

		//쿼리 실행 준비
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(query.toString());
		} catch (SQLException e) {
			System.out.println("쿼리에 문제가 있습니다.");
			System.out.println(e.getMessage());
			closeConnection(conn);
			return null;
		}

		//바인딩
		try {
			pstmt.setString(1, memberId);
		} catch (SQLException e) {
			System.out.println("파라미터 바인딩 과정에서 예외가 발생했습니다.");
			System.out.println("사유: " + e.getMessage());
			closePreparedStatement(pstmt);
			closeConnection(conn);
			return null;
		}

		//쿼리 실행 결과 받아오기
		ResultSet rs = null;
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			System.out.println("쿼리 실행 결과를 받아오는 중에 문제가 생겼습니다."); 
			System.out.println("사유: " + e.getMessage());
			closePreparedStatement(pstmt);
			closeConnection(conn);
			return null;
		}

		MembersVO memberVO = null;

		try {
			if (rs.next()) {
				memberVO = new MembersVO();
				memberVO.setMemberId( rs.getString("MEMBER_ID") );
				memberVO.setMemberName( rs.getString("MEMBER_NAME") );
				memberVO.setMemberGrade( rs.getString("MEMBER_GRADE") );
				memberVO.setProfileImg( rs.getString("PROFILE_IMG") );
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}

		return memberVO;
	}

	public MembersVO selectOneMemberAndArticleByMemberId (String memberId) {

		Connection conn = makeConnection();

		//쿼리 생성
		StringBuffer query = new StringBuffer();
		query.append(" SELECT M.MEMBER_ID ");
		query.append("      , M.MEMBER_NAME ");
		query.append("      , M.MEMBER_GRADE ");
		query.append("      , M.PROFILE_IMG ");
		query.append("      , A.ARTICLE_ID ");
		query.append("      , A.BOARD_ID ");
		query.append("      , A.ARTICLE_TITLE ");
		query.append("      , TO_CHAR(A.UPLOAD_DATE, 'YYYY-MM-DD') UPLOAD_DATE ");
		query.append("      , A.ARTICLE_MAIN ");
		query.append("      , A.LIKE_COUNT ");
		query.append("      , A.VIEW_COUNT ");
		query.append("   FROM MEMBERS M ");
		query.append("   JOIN ARTICLES A ");
		query.append("     ON M.MEMBER_ID = A.MEMBER_ID ");
		query.append("  WHERE M.MEMBER_ID = ? ");

		//쿼리 실행 준비
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(query.toString());
		} catch (SQLException e) {
			System.out.println("쿼리에 문제가 있습니다.");
			System.out.println(e.getMessage());
			closeConnection(conn);
			return null;
		}

		//바인딩
		try {
			pstmt.setString(1, memberId);
		} catch (SQLException e) {
			System.out.println("파라미터 바인딩 과정에서 예외가 발생했습니다.");
			System.out.println("사유: " + e.getMessage());
			closePreparedStatement(pstmt);
			closeConnection(conn);
			return null;
		}

		//쿼리 실행 결과 받아오기
		ResultSet rs = null;
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			System.out.println("쿼리 실행 결과를 받아오는 중에 문제가 생겼습니다."); 
			System.out.println("사유: " + e.getMessage());
			closePreparedStatement(pstmt);
			closeConnection(conn);
			return null;
		}

		MembersVO memberVO = null;
		ArticlesVO articleVO = null;

		try {
			if (rs.next()) {
				memberVO = new MembersVO();
				memberVO.setMemberId( rs.getString("MEMBER_ID") );
				memberVO.setMemberName( rs.getString("MEMBER_NAME") );
				memberVO.setMemberGrade( rs.getString("MEMBER_GRADE") );
				memberVO.setProfileImg( rs.getString("PROFILE_IMG") );

				articleVO = new ArticlesVO();
				articleVO.setArticleId( rs.getString("ARTICLE_ID") );
				articleVO.setMemberId( rs.getString("MEMBER_ID") );
				articleVO.setBoardId( rs.getString("BOARD_ID") );
				articleVO.setArticleTitle( rs.getString("ARTICLE_TITLE") );
				articleVO.setUploadDate( rs.getString("UPLOAD_DATE") );
				articleVO.setArticleMain( rs.getString("ARTICLE_MAIN") );
				articleVO.setLikeCount( rs.getInt("LIKE_COUNT") );
				articleVO.setViewCount( rs.getInt("VIEW_COUNT") );

				memberVO.setArticles(null);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}

		return memberVO;

	}