JAVA -JDBC 프로그래밍 - DAO 작성 및 구현클래스 작성(isEnablePhone(Phone/ id,Phone),search)

17 Mar 2021

🔷JDBC - DAO 작성 및 구현클래스 작성(isEnablePhone(Phone/ id,Phone),search)

DAO 구현클래스 작성

   package com.javateam.jdbc.member.dao.Impl;

  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 com.javateam.jdbc.member.dao.MemberDao;
  import com.javateam.jdbc.member.domain.MemberVo;
  import com.javateam.jdbc.member.util.DbUtil;

  /**
  * MemberDao의 구현 클래스(concrete class)<br><br>
  * 
  *
  * 용례) MemberDao dao = MemberdaoImpl.getInstance();<br>
  * : 싱클턴(singleton:독신자) 패턴 적용 &rarr; 보안<br><br>
  *  
  * @author mingki
  *
  */
  public class MemberDaoImpl implements MemberDao {
        
      /** DAO 객체 : 싱클턴(singleton) 생성 패턴 적용*/
      private static MemberDaoImpl instance = null;
      private MemberDaoImpl() {}
        
      /**
      * 싱글턴 객체(DAO singleton object) 생성 메서드 
      * 
      * @return DAO 객체
      */
      public static final MemberDaoImpl getInstance() {
          // final : 오버라이드 금지
            
          if(instance == null) {
              instance = new MemberDaoImpl();
          }
          return instance;
      }

     	@Override
      @Override
      public boolean isEnablePhone(String memberPhone) {
      // 리턴(반환값) 처리
       boolean result = false;
      // 실행 메서드명  
       ExceptionMetadata emd = new ExceptionMetadata(new Exception().getStackTrace()[0]);
		 
      // DB 연결
       Connection con = DbUtil.connect();
		 
      // SQL 처리 객체
       PreparedStatement pstmt = null;
		 
      // 결과셋 객체 (DOL : select)
       ResultSet rs = null;

      // SQL 구문
       String sql = "SELECT count(*) FROM member "
       		+ "WHERE member_phone = ? ";

      // SQL, 인자 (선)처리
       try {
          // SQL 결과셋 객체 생성 			 
          pstmt=con.prepareStatement(sql);
			
          pstmt.setString(1, memberPhone);
			
          rs = pstmt.executeQuery();
          // SQL 실행, 예외처리
			
          if(rs.next()) {
              result = rs.getInt(1) == 1 ? false:true ;
          }

      } catch (SQLException e) {
          emd.printErr(e, con, false);
			
      // 자원 반납
      } finally {
          DbUtil.close(con, pstmt, rs);
			
      }
      // 리턴(반환)
      return result;
      }

      @Override
      public boolean isEnablePhone(String memberId, String memberPhone) {
      // 리턴(반환값) 처리
       boolean result = false;
		 
      // 실행 메서드명  
        ExceptionMetadata emd = new ExceptionMetadata(new Exception().getStackTrace()[0]);

      // DB 연결
        Connection con = DbUtil.connect();

      // SQL 처리 객체
        PreparedStatement pstmt = null;

      // 결과셋 객체 (DOL : select)
        ResultSet rs = null;

      // SQL 구문
        String sql = "SELECT count(*) FROM member " + 
        		"WHERE member_id != ? AND member_phone = ? ";

      // SQL, 인자 (선)처리
		  
        try {
          pstmt = con.prepareStatement(sql);
			
          pstmt.setString(1,memberId);
          pstmt.setString(2,memberPhone);
			
          // SQL 결과셋 객체 생성
          rs = pstmt.executeQuery();
			
	
          if(rs.next()) {
              result = rs.getInt(1)==0 ?true : false;
          }else {
              result = false;
          }
          // SQL 실행, 예외처리
      } catch (SQLException e) {
          emd.printErr(e, con, false);
			
          // 자원 반납
      }finally {
          DbUtil.close(con, pstmt, rs);
      }

      // 리턴(반환)
      return result;
      }

      /**
      * 논리적 검색어를 물리 필드명으로 변환<br><br>
      * ex) 이름 &rarr; member_name<br><br>
      * 
      * @param searchWord 검색어
      * @return 검색어에 해당되는 물리 필드
      */
      private String getFieldBySearchWord(String searchWord) {
            
          String result = "";
            
          searchWord = searchWord.trim(); // 검색어 공백 제거
            
          result = searchWord.contentEquals("이름") ? "member_name" : 
                  searchWord.contentEquals("아이디") ? "member_id" :
                  searchWord.contentEquals("별명") ? "member_nickname" :
                  searchWord.contentEquals("연락처") ? "member_phone" : 
                  searchWord.contentEquals("이메일") ? "member_email" : 
                  searchWord.contentEquals("기본주소") ? "member_address_basic" :	 
                  searchWord.contentEquals("상세주소") ? "member_address_detail" :
                  searchWord.contentEquals("생일") ? "member_birth" :
                  searchWord.contentEquals("가입일") ? "member_joindate" : null;	 
                        
          return result;
      }

  @Override
  public List<MemberVo> getMembersBySearching(String searchKey, String searchValue, boolean isEquivalentSearch,
          String sortDirection, int page, int limit) {
		
      // 리턴(반환값) 처리
      List<MemberVo> members = new ArrayList<>();
		 
      // 실행 메서드명  
      MemberVo member = null;
		
      // 트랜잭션 / 예외처리 객체 생성
      ExceptionMetadata emd = new ExceptionMetadata(new Exception().getStackTrace()[0]);

      // DB 연결
      Connection con = DbUtil.connect();
		
      // SQL 처리 객체
      PreparedStatement pstmt = null;

      // 결과셋 객체
      ResultSet rs = null;
		
		
      // SQL 구문
      // Where절 변수
      // 조건에 따라 동등/유사  검색 WHERE절(clause) 선택적 조정
      try {
      String searchField = this.getFieldBySearchWord(searchKey);
      if(searchField == null) {
          throw new SQLException("검색어에 해당되는 테이블 필드가 없습니다.");
      }
		

	
      // 동등 및 유사 검색일 경우를 구분하여 표현식 구성
      // 1)
      String whereClause = isEquivalentSearch == true ? searchField + " = " + searchValue + " " : 
                                                        searchField + " like '%' || '" + searchValue + "' || '%' ";
		
      // 2) 
      if (searchField.equals("member_birth") || searchField.equals("member_joindate")) {

          // TO_CHAR(member_joindate, 'YYYY-MM-DD') 형식 적용
          // ex) WHERE TO_CHAR(member_joindate, 'YYYY-MM-DD') = TO_DATE('2021-03-16') 
			
          searchField = "TO_CHAR(" + searchField + ", 'YYYY-MM-DD') ";
			
          whereClause = isEquivalentSearch == true ? searchField + " = '" + searchValue + "' " : 
      												searchField + " like '%' || '" + searchValue + "' || '%' ";
			
          System.out.println("where절 : " + whereClause);
			
          String sql = "SELECT * " + 
                   "FROM (SELECT ROWNUM, " + 
                   "             m.*, " + 
                   "             FLOOR((ROWNUM - 1) / ? + 1) page " + 
                   "      FROM (" + 
                   "             SELECT * FROM member " + 
                   " 			   WHERE " + whereClause + " " +
                   "             ORDER BY member_id " + sortDirection + " " +
                   "           ) m " + 
                   "      ) " + 
                   "WHERE page = ?";
  //	String sql = "SELECT * " + 
  //			"FROM (SELECT ROWNUM,  " + 
  //			"             m.*,   " + 
  //			"             FLOOR((ROWNUM - 1) / ? + 1) page  " + 
  //			"      FROM (  " + 
  //			"             SELECT * FROM member " + 
  //			"             W WHERE member_name like"+ "'%" +searchKey + "%' " + 
  //			"             ORDER BY member_id " + sortDirection + 
  //			"           ) m    " + 
  //			"      )    " + 
  //			"WHERE page = ? ";
      // SQL, 인자 (선)처리
        
          pstmt = con.prepareStatement(sql);
            
          // SQL 경고 메세지
          pstmt.setInt(1, limit);
          pstmt.setInt(2, page);
            
          System.out.println("sql : " +sql);
            
          rs = pstmt.executeQuery();
            
          while(rs.next()) {
              member = new MemberVo();
                
              member.setMemberId(rs.getString("member_Id"));
              member.setMemberPassword(rs.getString("member_Password"));
              member.setMemberNicname(rs.getString("member_Nickname"));
              member.setMemberName(rs.getString("member_Name"));
              member.setMemberGender(rs.getString("member_Gender").charAt(0)); // char로 변환
              member.setMemberEmail(rs.getString("member_Email"));
              member.setMemberPhone(rs.getString("member_Phone"));
              member.setMemberBirth(rs.getDate("member_Birth"));
              member.setMemberZip(rs.getString("member_Zip"));
              member.setMemberAddressBasic(rs.getString("member_Address_Basic"));
              member.setMemberAddressDetail(rs.getString("member_Address_Detail"));
              member.setMemberJoindate(rs.getDate("member_JoinDate"));
                
                
              // VO -> List 로 이관(add) : 개별 회원정보 추가
              members.add(member);
          }
				
      }
		
      } catch (SQLException e) {
          emd.printErr(e, con, false);
      }finally {
          DbUtil.close(con, pstmt, rs);
      }		
      return members;
  }	

isEnablePhone(memberPhone) 테스트 클래스

  package com.javateam.jdbc.member.test;

  import com.javateam.jdbc.member.dao.MemberDao;
  import com.javateam.jdbc.member.dao.Impl.MemberDaoImpl;

  public class isEnablePhoneTest {

      public static void main(String[] args) {

          MemberDao dao = MemberDaoImpl.getInstance();
            
          System.out.println("있는 번호 확인 (010-3078-0694) : " + dao.isEnablePhone("010-3078-0694"));
            
          System.out.println("없는 번호 확인 (010-1111-1111) : " + dao.isEnablePhone("010-1111-1111"));

      }

  }

▶ 출력 화면 isEnablePhone

isEnablePhone(memberId,memberPhone) 테스트 클래스

 package com.javateam.jdbc.member.test;

  import com.javateam.jdbc.member.dao.MemberDao;
  import com.javateam.jdbc.member.dao.Impl.MemberDaoImpl;

  public class isEnablePhoneTest2 {

      public static void main(String[] args) {

          MemberDao dao = MemberDaoImpl.getInstance();
            

          System.out.println("아이디 O,본인 전화번호 O : " + dao.isEnablePhone("hyun", "010-3078-0694"));
          System.out.println("아이디 O,전화번호 X : " + dao.isEnablePhone("hyun", "010-3078-0644"));
          System.out.println("아이디 O,타인 전화번호 O : " + dao.isEnablePhone("hyun", "010-1234-1001"));
      }

  }

▶ 출력 화면 isEnablePhone2

getMembersBySearching 테스트 클래스

 package com.javateam.jdbc.member.test;

  import java.util.ArrayList;
  import java.util.List;

  import com.javateam.jdbc.member.dao.MemberDao;
  import com.javateam.jdbc.member.dao.Impl.MemberDaoImpl;
  import com.javateam.jdbc.member.domain.MemberVo;
  /**
  * getMembersBySearching 메서드 단위 테스트 케이스(Unit TestCase)<br>
  * : 검색어를 통한 페이징 지원 회원정보 조회 테스트<br><br>
  * 
  * @author mingki
  *
  */
  public class getMemberBySearchingTest {

      public static void main(String[] args) {
            
          // DAO 객체 생성
          MemberDao dao = MemberDaoImpl.getInstance();
            
          // 각종 검색어(검색 필드)를 적용하여 검색합니다.
            
              // List<MemberVo> members = dao.getMembersBySearching("이름", "길동", false, "ASC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("이름2", "길동", false, "ASC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("이름", "길동", false, "DESC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("기본주소", "종로2", false, "DESC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("기본주소", "종로", false, "DESC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("생일", "1990-01-02", false, "DESC", 1, 10); 
              // List<MemberVo> members = dao.getMembersBySearching("생일", "1990-01-02", true, "DESC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("생일", "1990-01-02", true, "DESC", 1, 10);
              // List<MemberVo> members = dao.getMembersBySearching("가입일", "1990-01-02", true, "DESC", 1, 10);
              List<MemberVo> members = dao.getMembersBySearching("가입일", "2021-03-15", false, "DESC", 1, 10);
              //List<MemberVo> members = dao.getMembersBySearching("가입일", "2021-03-11", true, "DESC", 1, 10);

              for (MemberVo m : members) {
                  System.out.println(m);
              }
                
          // 내가 작성한 코드
          //System.out.println(dao.getMembersBySearching("member_name", "순", true, "ASC", 1, 10));
      }
  }

▶ 출력 화면 getMemberBySearchingTest