JAVA -JDBC 프로그래밍 - VO, DAO 작성 및 구현클래스 작성(Insert , Update)

11 Mar 2021

🔷JDBC 프로그래밍 - VO, DAO 작성 및 구현클래스 작성(Insert , Update)

VO 작성

  package com.javateam.jdbc.member.domain;

  import java.io.Serializable;
  import java.sql.Date;
  import java.util.Objects;
  /**
  * Member 테이블에 대응되는 엔티티(Entity) 자바빈(javaBean) 
  * : VO(값 객체) : POJP(Plain Old Java Object) 형태 
  * : Plain => metadata(xml, annotation 등)이 없이 순수하게 자바 코드만으로 구성 되어있는것
  * : Old => EJB Entity Bean(XML base) ex) EJB 3.0은 annotation 지원 에 비해 예전방식
  *  => VO 가 있어야 데이터 전송이 가능하다
  *    
  *
  * @author mingki
  * @version 1.0
  *
  */
  public class MemberVo implements Serializable{
        
      /**
      * 
      */
      private static final long serialVersionUID = 1L; // 시리얼 고유번호
        
      // 멤버 필드
      /** 회원 아이디 */
      private String memberId;
      /** 회원 패스워드 */
      private String memberPassword;
      /** 회원 닉네임 */
      private String memberNicname;
      /** 회원 이름 */
      private String memberName;
      /** 회원 성별 */
      private char memberGender;
      /** 회원 이메일 */
      private String memberEmail;
      /** 회원 핸드폰번호 */
      private String memberPhone;
      /** 회원 생일*/
      private Date memberBirth;
      /** 회원 우편번호 */
      private String memberZip;
      /** 회원 기본주소  */
      private String memberAddressBasic;
      /** 회원 상세주소 */
      private String memberAddressDetail;
      /** 회원 가입일 */
      private Date memberJoindate;

      // 기본 생성자 / 오버로딩 생성자 작성
        
      public MemberVo(){
          // 기본생성자 => 오버로딩 생성자가 있을경우 작성 필수! -> 작성 x : 오류발생
      } 
        
      // 오버로딩 생성자 작성
      public MemberVo(String memberId, String memberPassword, String memberNicname, String memberName, char memberGender,
              String memberEmail, String memberPhone, Date memberJoindate) {
          this.memberId = memberId;
          this.memberPassword = memberPassword;
          this.memberNicname = memberNicname;
          this.memberName = memberName;
          this.memberGender = memberGender;
          this.memberEmail = memberEmail;
          this.memberPhone = memberPhone;
          this.memberJoindate = memberJoindate;
      }
      public MemberVo(String memberId, String memberPassword, String memberNicname, String memberName, char memberGender,
              String memberEmail, String memberPhone, Date memberBirth, String memberZip, String memberAddressBasic,
              String memberAddressDetail, Date memberJoindate) {
          this.memberId = memberId;
          this.memberPassword = memberPassword;
          this.memberNicname = memberNicname;
          this.memberName = memberName;
          this.memberGender = memberGender;
          this.memberEmail = memberEmail;
          this.memberPhone = memberPhone;
          this.memberBirth = memberBirth;
          this.memberZip = memberZip;
          this.memberAddressBasic = memberAddressBasic;
          this.memberAddressDetail = memberAddressDetail;
          this.memberJoindate = memberJoindate;
      }

        
      // getter/setter
      public String getMemberId() {
          return memberId;
      }
      public void setMemberId(String memberId) {
          this.memberId = memberId;
      }
      public String getMemberPassword() {
          return memberPassword;
      }
      public void setMemberPassword(String memberPassword) {
          this.memberPassword = memberPassword;
      }
      public String getMemberNicname() {
          return memberNicname;
      }
      public void setMemberNicname(String memberNicname) {
          this.memberNicname = memberNicname;
      }
      public String getMemberName() {
          return memberName;
      }
      public void setMemberName(String memberName) {
          this.memberName = memberName;
      }
      public char getMemberGender() {
          return memberGender;
      }
      public void setMemberGender(char memberGender) {
          this.memberGender = memberGender;
      }
      public String getMemberEmail() {
          return memberEmail;
      }
      public void setMemberEmail(String memberEmail) {
          this.memberEmail = memberEmail;
      }
      public String getMemberPhone() {
          return memberPhone;
      }
      public void setMemberPhone(String memberPhone) {
          this.memberPhone = memberPhone;
      }
      public Date getMemberBirth() {
          return memberBirth;
      }
      public void setMemberBirth(Date memberBirth) {
          this.memberBirth = memberBirth;
      }
      public String getMemberZip() {
          return memberZip;
      }
      public void setMemberZip(String memberZip) {
          this.memberZip = memberZip;
      }
      public String getMemberAddressBasic() {
          return memberAddressBasic;
      }
      public void setMemberAddressBasic(String memberAddressBasic) {
          this.memberAddressBasic = memberAddressBasic;
      }
      public String getMemberAddressDetail() {
          return memberAddressDetail;
      }
      public void setMemberAddressDetail(String memberAddressDetail) {
          this.memberAddressDetail = memberAddressDetail;
      }
      public Date getMemberJoindate() {
          return memberJoindate;
      }
      public void setMemberJoindate(Date memberJoindate) {
          this.memberJoindate = memberJoindate;
      }
        
      // toString overriding

      @Override
      public String toString() {
          return String.format(
                  "memberId=%s\n, memberPassword=%s\n, memberNicname=%s\n, memberName=%s\n, memberGender=%s\n, memberEmail=%s\n, memberPhone=%s\n, memberBirth=%s\n, memberZip=%s\n, memberAddressBasic=%s\n, memberAddressDetail=%s\n, memberJoindate=%s]",
                  memberId, memberPassword, memberNicname, memberName, memberGender, memberEmail, memberPhone,
                  memberBirth, memberZip, memberAddressBasic, memberAddressDetail, memberJoindate);
      }

      // hashcode/equals overriding
        
      @Override
      public int hashCode() {
          return Objects.hash(memberAddressBasic, memberAddressDetail, memberBirth, memberEmail, memberGender, memberId,
                  memberJoindate, memberName, memberNicname, memberPassword, memberPhone, memberZip);
      }

      @Override
      public boolean equals(Object obj) {
          if (this == obj)
              return true;
          if (obj == null)
              return false;
          if (getClass() != obj.getClass())
              return false;
          MemberVo other = (MemberVo) obj;
          return Objects.equals(memberAddressBasic, other.memberAddressBasic)
                  && Objects.equals(memberAddressDetail, other.memberAddressDetail)
                  && Objects.equals(memberBirth, other.memberBirth) && Objects.equals(memberEmail, other.memberEmail)
                  && memberGender == other.memberGender && Objects.equals(memberId, other.memberId)
                  && Objects.equals(memberJoindate, other.memberJoindate) && Objects.equals(memberName, other.memberName)
                  && Objects.equals(memberNicname, other.memberNicname)
                  && Objects.equals(memberPassword, other.memberPassword)
                  && Objects.equals(memberPhone, other.memberPhone) && memberZip == other.memberZip;
      }	
  }

💡 DAO : Data Access Object 의 약자 => DB의 data에 접근하기 위한 객체, DB 접근을 하기 위한 로직과 비지니스 로직을 분리하기 위해 사용

DAO 작성

  package com.javateam.jdbc.member.dao;

  import java.util.List;

  import com.javateam.jdbc.member.domain.MemberVo;

  /**
  * DAO (Data Access Object) <br>
  * : CRUD(DML(CUD) + DQL(select)) methods들로 구성 한다 <br>
  * 참고) 간혹 DDL에 대응되는 메서드로 구성될 수도 있다 <br>
  * 
  * 참고 1) CRUD(create read update delete) <br>
  * 참고 2) CUD(create update delete) &rarr; 협의의(좁은 의미의) DML <br>
  * 
  * 참고) DB = 영구(영속) 저장소(Persistent Repository) <br>
  * 영속(영구) 객체 : Persistent(/-ce) Object &rarr; VO, DAO(VO없이는 작동 X), (D)TO <br>
  * 
  * @author mingki
  */

  public interface MemberDao {
      /**
      * 개별 회원 정보 삽입(생성)
      * @param member 회원정보 객체
      * @return 회원정보 생성 성공 여부
      */
        
      public boolean insertMember(MemberVo member);
        
      /**
      * 전체 회원 정보들 조회(검색)
      * @return 전체 회원정보 리스트(목록)
      */
      public List<MemberVo> getAllMembers();
        
      /**
      * 개별 회원정보 조회(검색)
      * @param memberId 회원 아이디
      * @return 개별 회원정보(VO)
      */
      public MemberVo getMember(String memberId);

      /**
      * 개별 회원정보 수정(갱신)
      * 
      * @param member 회원정보 객체(VO)
      * @return 회원정보 갱신(수정)성공 여부
      */
      public boolean updateMember(MemberVo member);
        
      /**
      * 개별 회원정보 삭제
      * @param memberId 회원 아이디
      * @return 회원정보 삭제 성공 여부
      */
      public boolean deleteMember(String memberId);
  }

DAO 구현클래스 작성

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

  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  import java.sql.Statement;
  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
      public boolean insertMember(MemberVo member) {
            
          // 리턴(반환값) 처리
          boolean result = false; // 결과값 초기화
            
          // 실행 메서드명(reflection 사용)
          String methodName 
          = new Exception().getStackTrace()[0].getMethodName();
            
          // DB 연결
          Connection con = DbUtil.connect();
            
          // SQL 처리 객체
          PreparedStatement pstmt = null; // 1)
          // Statement pstmt = null; // 2)
          // 참고) Statement pstmt = null; // ? (와일드 카드) 사용할 수 없다
          // 참고) CallableStatment : PL/SQL 전용
            
          // SQL 구문
          String sql = "INSERT INTO member VALUES "  
                  + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate)";		
            
          // SQL, 인자 (선)처리
          try {
              pstmt = con.prepareStatement(sql); // 1) SQL(prepare) 선처리
              // pstmt = con.createStatment(); // 2) SQL 후처리
              pstmt.setString(1, member.getMemberId());
              pstmt.setString(2, member.getMemberPassword());
              pstmt.setString(3, member.getMemberNicname());
              pstmt.setString(4, member.getMemberName());
              pstmt.setString(5, member.getMemberGender() + ""); // char -> String 변환 위해 뒤에 "" 붙힌다
              pstmt.setString(6, member.getMemberEmail());
              pstmt.setString(7, member.getMemberPhone());
              pstmt.setDate(8,member.getMemberBirth());
              pstmt.setString(9, member.getMemberZip());
              pstmt.setString(10, member.getMemberAddressBasic());
              pstmt.setString(11, member.getMemberAddressDetail());
                
              // SQL 실행, 리턴값 처리, 예외처리
              if (pstmt.executeUpdate() == 1) { // 1 : DML 실행 성공한 행(tuple, record)의 수
                    
                  System.out.println("회원정보 저장에 성공하였습니다.");
                  result = true;
                    
              } else {
                    
                  System.out.println("회원정보 저장에 실패하였습니다.");
                  // result = false;
              }
          } catch (SQLException e) {
              System.err.println(methodName + " : " + " 회원정보 저장시 예외가 발생하였습니다.");
              System.err.println(methodName + " : " + e.getMessage());
              // System.err.println(methodName + " : " + "회원정보 저장시 예외가 발생하였습니다.");
              e.printStackTrace();
          } finally {
              // 자원 반납
              DbUtil.close(con, pstmt, null);
          } //
            
          // 리턴(반환)
          return result;
      }

      @Override
      public List<MemberVo> getAllMembers() {
          // TODO Auto-generated method stub
          return null;
      }

      @Override
      public MemberVo getMember(String memberId) {
          // TODO Auto-generated method stub
          return null;
      }

      @Override
      public boolean updateMember(MemberVo member) {
          // 리턴 처리
          boolean result = false;
            
          // DB연결
          Connection con = DbUtil.connect();
            
          // SQL 처리 객체
          PreparedStatement pstmt = null;
            
          // SQL 구문
          String sql = "UPDATE member SET "
                      + "member_password=?,member_nickname = ?,member_birth=?,"
                      + "member_address_basic=?,member_address_detail=?"
                      + "WHERE MEMBER_ID='hyun'";
          // SQL, 인자(선) 처리
            
          try {
              pstmt = con.prepareStatement(sql);
                
              pstmt.setString(1, member.getMemberPassword());
              pstmt.setString(2, member.getMemberNicname());
              pstmt.setDate(3, member.getMemberBirth());
              pstmt.setString(4, member.getMemberAddressBasic());
              pstmt.setString(5, member.getMemberAddressDetail());

              // SQL 실행, 리턴값 처리, 예외처리
              if(pstmt.executeUpdate()==1) {
                  System.out.println("회원정보 수정에 성공하였습니다.");
                  result = true;
              }else {
                  System.out.println("회원정보 수정에 실패하였습니다.");
              }
                    
          } catch (SQLException e) {
              System.err.println(" 회원정보 저장시 예외가 발생하였습니다.");
              System.err.println(e.getMessage());
              e.printStackTrace();
          }finally {
              // 자원 반납
              DbUtil.close(con, pstmt, null);
          }
          return result;
      }

      @Override
      public boolean deleteMember(String memberId) {
          // TODO Auto-generated method stub
          return false;
      }

  }

VO 테스트 클래스

  package com.javateam.jdbc.member.test;

  import java.sql.Date;

  import com.javateam.jdbc.member.domain.MemberVo;



  /**
  * VO 단위 테스트
  * 
  * @author mingki
  *
  */

  public class VoTest {
            
      public static void main(String[] args) {
            
          // 멤버 객체생성
          MemberVo member = new MemberVo();
            
          // 
          member.setMemberId("java");
          member.setMemberPassword("1234");
          member.setMemberNicname("달팽이");
          member.setMemberName("배현");
          member.setMemberGender('m');
          member.setMemberEmail("skfksnsk@gmail.com");
          member.setMemberPhone("010-3073-0694");
            
          // 생일 ) 0000-00-00
          member.setMemberBirth(java.sql.Date.valueOf("1991-01-04"));
          member.setMemberZip("12345");
          member.setMemberAddressBasic("서울시");
          member.setMemberAddressDetail("강서구");
            
          // 금일 날짜 => 가입일 
          member.setMemberJoindate(new Date(System.currentTimeMillis()));
            

          // 전체 내용 확인
          System.out.println(member);
            
      }

  }

▶ 출력 화면 VO테스트

INSERT 테스트 클래스

  package com.javateam.jdbc.member.test;

  import java.sql.Date;

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

  /**
  * InsertMember 메서드 단위 테스트(Unit Test Case) <br>
  * : 회원정보 생성 테스트<br><br>
  * 
  * @author mingki
  *
  */
  public class InsertMemberTest {

      public static void main(String[] args) {
            
          // DAO 객체 생성
          MemberDao dao = MemberDaoImpl.getInstance();
            
          // 생성할 회원정보 생성 (MemberVO)
          MemberVo member = new MemberVo();
            
          member.setMemberId("mingming");		
          member.setMemberPassword("1234");
          member.setMemberNicname("만두");
          member.setMemberName("박00");
          member.setMemberGender('f');
          member.setMemberEmail("skfksnsk@skfk.com");
          member.setMemberPhone("010-1234-5678");
          member.setMemberBirth(Date.valueOf("1993-01-29"));
          member.setMemberZip("12345");
          member.setMemberAddressBasic("부천시");
          member.setMemberAddressDetail("현대홈타운");
            
          // InserMember 테스트(true/false)
          String result = dao.insertMember(member) == true ? 
                  "저장 성공" : "저장 실패";
          System.out.println("insertMember 테스트 결과 : " + result);
      }

  }

▶ 출력 화면 insert

▶ DB
insert2

UPDATE 테스트 클래스

  package com.javateam.jdbc.member.test;

  import java.sql.Date;

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

  /**
  * UpdateMember 메서드 단위 테스트(Unit Test Case) <br>
  * : 회원정보 수정 테스트<br><br>
  * 
  * @author mingki
  *
  */
  public class UpdateMemberTest {

      public static void main(String[] args) {
            
          // DAO 객체 생성
          MemberDao dao = MemberDaoImpl.getInstance();
            
          // 수정할 외원정보 생성(MemberVO)
          MemberVo member = new MemberVo();
            
          member.setMemberPassword("11115");
          member.setMemberNicname("메니");
          member.setMemberBirth(Date.valueOf("1991-07-04"));
          member.setMemberAddressBasic("서울특별시");
          member.setMemberAddressDetail("타워펠리스");
                
          // UpdateMember 테스트(true/false)
          String result = dao.updateMember(member) == true ?
                          "수정 성공" : "수정 실패";
          System.out.println("UpdateMember 테스트 결과 : " + result);
                    
      }

  }

▶ 출력 화면 update

▶ DB update2