✿∘˚˳°∘°

26일차 : JDBC - PreparedStatement 본문

국비수업/JDBC

26일차 : JDBC - PreparedStatement

_HYE_ 2023. 1. 2. 17:55

20230102

 

 Statement를 PreparedStatement로 바꿔서 만들기
 PreparedStatement : SQL문장이 미리 컴파일되고, 실생히간 동안 인수값을 위한 공간을 확보
                                  지금까지 쿼리문을 "+member_id+""로 작성해야했는데 이제는 ?(위치홀더)로 작성할 수 있다.
 SELECT * FROM MEMBER_TBL WHERE MEMBER_ID = ? (O)
 SELECT * FROM MEMBER_TBL WHERE ? = ? (X)
 ?에 어떤값이 들어가도 에러가 발생하면 안됨, [값]이 들어가는 곳에 사용한다

 

 장점1. 보안성이 높다
    이전 v1프로그램의 경우, id를 검색할 경우 'or'1'='1'를 넣으면 무조건 true가 나오기때문에 전체조회가능
    ?를 사용하면 어떤값이 들어가도 id = ''를 검색함
 장점2. 같은 쿼리문을 재사용 시 속도가 빠르다 
 실제로는 Statement를 잘 사용하지않고, PreparedStatement로 사용

 

PreparedStatement에서는 쿼리문에서 변수값으로 대체될 부분을 ?(위치홀더)로 대체(위치홀더는 리터럴을 대체)
단, 값에대한 부분만 처리가능(테이블이름, 컬럼명, 조건 불가능)
위치홀더를 값으로 대체하기 전이어도 쿼리문은 문법상에 문제가 없어야함


위치홀더(?) : sql구문에 나타내는 토큰, 실제 sql문이 실행되기 전에 값으로 대체되어야 함
위치홀더를 값으로 대체하지않고 쿼리문 실행 시 에러발생(나중에 값을 넣어줄게 하고 보내는것) 

-> 값으로 바꿔주는 작업이 반드시 필요

 

PreparedStatement를 이용하여 회원관리 프로그램 만들기 

[ JAVA 클래스 ] - [ memberStart.java / member.java / memberConroller.java / memberDao.java ]

member.java

package kr.or.member.vo;

import java.sql.Date;

public class Member {

	private String memberId;
	private String memberPw;
	private String memberName;
	private String memberAddr;
	private String memberPhone;
	private int memberAge;
	private String memberGender;
	private Date enrollDate;
	public Member() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Member(String memberId, String memberPw, String memberName, String memberAddr, String memberPhone,
			int memberAge, String memberGender, Date enrollDate) {
		super();
		this.memberId = memberId;
		this.memberPw = memberPw;
		this.memberName = memberName;
		this.memberAddr = memberAddr;
		this.memberPhone = memberPhone;
		this.memberAge = memberAge;
		this.memberGender = memberGender;
		this.enrollDate = enrollDate;
	}
	public String getMemberId() {
		return memberId;
	}
	public void setMemberId(String memberId) {
		this.memberId = memberId;
	}
	public String getMemberPw() {
		return memberPw;
	}
	public void setMemberPw(String memberPw) {
		this.memberPw = memberPw;
	}
	public String getMemberName() {
		return memberName;
	}
	public void setMemberName(String memberName) {
		this.memberName = memberName;
	}
	public String getMemberAddr() {
		return memberAddr;
	}
	public void setMemberAddr(String memberAddr) {
		this.memberAddr = memberAddr;
	}
	public String getMemberPhone() {
		return memberPhone;
	}
	public void setMemberPhone(String memberPhone) {
		this.memberPhone = memberPhone;
	}
	public int getMemberAge() {
		return memberAge;
	}
	public void setMemberAge(int memberAge) {
		this.memberAge = memberAge;
	}
	public String getMemberGender() {
		return memberGender;
	}
	public void setMemberGender(String memberGender) {
		this.memberGender = memberGender;
	}
	public Date getEnrollDate() {
		return enrollDate;
	}
	public void setEnrollDate(Date enrollDate) {
		this.enrollDate = enrollDate;
	}		
}

MemberController.java 의 전역변수선언과 생성자

	private Scanner sc;
	private MemberDao dao;

	public MemberController() {
		super();
		sc = new Scanner(System.in);
		dao = new MemberDao();
	}

0. MemberController - main()

	public void main() {
		while (true) {
			System.out.println("\n------ 회원 관리 프로그램 v2 ------\n");
			System.out.println("1. 전체 회원 조회");
			System.out.println("2. 아이디로 회원 조회");
			System.out.println("3. 이름으로 회원 조회");
			System.out.println("4. 회원 가입");
			System.out.println("5. 정보 수정");
			System.out.println("6. 회원 탈퇴");
			System.out.println("0. 프로그램 종료");
			System.out.print("선택 > ");
			int sel = sc.nextInt();

			switch (sel) {
			case 1:
				selectAllMember();
				break;
			case 2:
				selectMemberId();
				break;
			case 3:
				selectMemberName();
				break;
			case 4:
				insertMember();
				break;
			case 5:
				updateMember();
				break;
			case 6:
				deleteMember();
				break;
			case 0:
				System.out.println("프로그램을 종료합니다.");
				return;
			default:
				System.out.println("잘못입력하셨습니다.");
			}
		}
	}

 

1. 전체 회원 조회

MemberController.java

	private void selectAllMember() {
		// 쿼리문 : select * from member_tbl;
		ArrayList<Member> list = new ArrayList<Member>();
		list = dao.selectAllMember();

		if (list.size() > 0) {
			System.out.println("\n----- 전체 회원 조회 -----\n");
			System.out.println("아이디\t비밀번호\t이름\t주소\t\t전화번호\t\t나이\t성별\t가입일");
			System.out.println("------------------------------------------------");
			for (Member m : list) {
				System.out.print(m.getMemberId() + "\t");
				System.out.print(m.getMemberPw() + "\t");
				System.out.print(m.getMemberName() + "\t");
				System.out.print(m.getMemberAddr() + "\t");
				System.out.print(m.getMemberPhone() + "\t");
				System.out.print(m.getMemberAge() + "\t");
				System.out.print(m.getMemberGender() + "\t");
				System.out.println(m.getEnrollDate());

			}
		}
	}

MemberDao.java

public ArrayList<Member> selectAllMember() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		ArrayList<Member> list = new ArrayList<Member>();
		
		String query = "select * from member_tbl";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			rset = pstmt.executeQuery();
			while(rset.next()) {
				Member m = new Member();
				m.setEnrollDate(rset.getDate("enroll_date"));
				m.setMemberAddr(rset.getString("member_addr"));
				m.setMemberAge(rset.getInt("member_age"));
				m.setMemberGender(rset.getString("member_gender"));
				m.setMemberId(rset.getString("member_id"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberPhone(rset.getString("member_phone"));
				m.setMemberPw(rset.getString("member_pw"));
				
				list.add(m);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
				rset.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		return list;
	}

 

2. 아이디로 회원 조회

MemberController.java

	public void selectMemberId() {
		// 쿼리문 SELECT * FROM MEMBER_TBL WHERE MEMBER_ID = '아이디'
		System.out.print("조회할 회원 아이디 입력 : ");
		String memberId = sc.next();
		Member m = dao.selectMemberId(memberId);
		if (m != null) {
			System.out.println("아이디 : " + m.getMemberId());
			System.out.println("비밀번호 : " + m.getMemberPw());
			System.out.println("이름 : " + m.getMemberName());
			System.out.println("주소 : " + m.getMemberAddr());
			System.out.println("전화번호 : " + m.getMemberPhone());
			System.out.println("나이 : " + m.getMemberAge());
			System.out.println("성별 : " + m.getMemberGender());
			System.out.println("가입일 : " + m.getEnrollDate());
		} else {
			System.out.println("회원정보를 찾을 수 없습니다.");
		}
	}

MemberDao.java

	public Member selectMemberId(String memberId) {
		Connection conn = null;
		PreparedStatement pstmt = null; //쿼리문을 수행하고 결과를 가져오는 객체
		ResultSet rset = null;
		
		Member m = null;
		
		
		String query = "select * from member_tbl where member_id = ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
			/* 기존
			stmt = conn.createStatement();
			rset = stmt.executeQuery(query);
			*/
			//PreparedStatement객체 생성 시 쿼리문을 전달
			//추후에 ?(위치홀더)를 값으로 대체해주는 역할도 PreparedStatement가 해준다.
			pstmt = conn.prepareStatement(query);//이때 문법이 정상인지 검사
			
			//위치홀더에 값 대입
			pstmt.setString(1, memberId); //첫번째 위치홀더에 memberId를 줄게 - 이시점에서 쿼리문완성
			
			//쿼리문 실행 후 결과를 받아올때는 query를 매개변수로 주지않음(이미알고있는 상태이므로 그냥실행)
			rset = pstmt.executeQuery(); 
			if(rset.next()) {
				m = new Member();
				m.setMemberId(rset.getString("member_id"));
				m.setMemberPw(rset.getString("member_pw"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberAddr(rset.getString("member_addr"));
				m.setMemberPhone(rset.getString("member_phone"));
				m.setMemberAge(rset.getInt("member_age"));
				m.setMemberGender(rset.getString("member_gender"));
				m.setEnrollDate(rset.getDate("enroll_date"));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
				rset.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return m;
	}

 

3. 이름으로 회원 조회

MemberController.java

	public void selectMemberName() {
		System.out.println("\n----- 이름으로 회원 조회 -----\n");
		System.out.print("조회할 회원의 이름을 입력하세요 : ");
		String memberName = sc.next();
		ArrayList<Member> list = dao.selectMemberName(memberName); 
		if(!list.isEmpty()) {
			System.out.println("아이디\t비밀번호\t이름\t주소\t\t전화번호\t\t나이\t성별\t가입일");
			System.out.println("------------------------------------------------");
			for (Member m : list) {
				System.out.print(m.getMemberId() + "\t");
				System.out.print(m.getMemberPw() + "\t");
				System.out.print(m.getMemberName() + "\t");
				System.out.print(m.getMemberAddr() + "\t");
				System.out.print(m.getMemberPhone() + "\t");
				System.out.print(m.getMemberAge() + "\t");
				System.out.print(m.getMemberGender() + "\t");
				System.out.println(m.getEnrollDate());
			}
		}else {
			System.out.println("");
		}
	}

MemberDao.java

	public ArrayList<Member> selectMemberName(String memberName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		ArrayList<Member> list = new ArrayList<Member>();
		 
		String query = "select * from member_tbl where member_name like '%'||?||'%'";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, memberName);
			//pstmt.setString(1, "%"+memberName+"%");
			rset = pstmt.executeQuery();
			while(rset.next()) {
				Member m = new Member();
				m.setEnrollDate(rset.getDate("enroll_date"));
				m.setMemberAddr(rset.getString("member_addr"));
				m.setMemberAge(rset.getInt("member_age"));
				m.setMemberGender(rset.getString("member_gender"));
				m.setMemberId(rset.getString("member_id"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberPhone(rset.getString("member_phone"));
				m.setMemberPw(rset.getString("member_pw"));
				
				list.add(m);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
				rset.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
				
		return list;
	}

 

4. 회원가입

MemberConroller.java

	public void insertMember() {
		System.out.println("\n----- 회원가입 -----\n");
		System.out.print("아이디 : ");
		String memberId = sc.next();
		System.out.print("비밀번호 : ");
		String memberPw = sc.next();
		System.out.print("이름 : ");
		String memberName = sc.next();
		System.out.print("주소 : ");
		sc.nextLine();
		String memberAddr = sc.nextLine();
		System.out.print("전화번호 : ");
		String memberPhone = sc.next();
		System.out.print("나이 : ");
		int memberAge = sc.nextInt();
		System.out.print("성별[남/여] : ");
		String memberGender = sc.next();
		
		Member m = new Member(memberId, memberPw, memberName, memberAddr, memberPhone, memberAge, memberGender);
	
		int result = dao.insertMember(m);
		if(result > 0) {
			System.out.println("가입성공!");
		}else {
			System.out.println("가입실패!");
		}
	}

MemberDao.java

	public int insertMember(Member m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		int result = 0;
		
		String query = "insert into member_tbl values(?,?,?,?,?,?,?,sysdate)";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, m.getMemberId());
			pstmt.setString(2, m.getMemberPw());
			pstmt.setString(3, m.getMemberName());
			pstmt.setString(4, m.getMemberAddr());
			pstmt.setString(5, m.getMemberPhone());
			pstmt.setInt(6, m.getMemberAge());
			pstmt.setString(7, m.getMemberGender());
			result = pstmt.executeUpdate();
			
			if(result > 0) {
				conn.commit();
			}else {
				conn.rollback();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}				
		return result;
	}

 

5. 회원 정보 변경

MemberController.java

	public void updateMember() {
		System.out.println("\n----- 회원 정보 수정 -----\n");
		System.out.print("아이디 입력 : ");
		String memberId = sc.next();
		if(dao.selectMemberId(memberId) == null) {
			System.out.print("일치하는 회원이 없습니다.");
			return;
		}
		System.out.print("변경할 비밀번호 입력 : ");
		String memberPw = sc.next();
		System.out.print("변경할 주소 입력 : ");
		sc.nextLine();
		String memberAddr = sc.nextLine();
		System.out.print("변경할 전화번호 입력 : ");
		String memberPhone = sc.next();
		
		Member m = new Member();
		m.setMemberId(memberId);
		m.setMemberPw(memberPw);
		m.setMemberAddr(memberAddr);
		m.setMemberPhone(memberPhone);
		
		int result = dao.updateMember(m);
		
		if(result > 0) {
			System.out.println("수정 성공!");
		}else {
			System.out.println("수정 실패!");
		}
	}

MemberDao.java

	public int updateMember(Member m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		int result = 0;
		
		String query = "update member_tbl set member_pw = ?, member_addr = ?, member_phone = ? where member_id = ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, m.getMemberPw());
			pstmt.setString(2, m.getMemberAddr());
			pstmt.setString(3, m.getMemberPhone());
			pstmt.setString(4, m.getMemberId());
			result = pstmt.executeUpdate();
			
			if(result > 0) {
				conn.commit();
			}else {
				conn.rollback();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}				
		return result;
	}

 

6. 회원탈퇴

MemberController.java

	public void deleteMember() {
		System.out.println("\n----- 회원 탈퇴 -----\n");
		System.out.print("아이디를 입력하세요 : ");
		String memberId = sc.next();
		if(dao.selectMemberId(memberId) == null) {
			System.out.println("회원정보를 찾을 수 없습니다.");
			return;
		}
		int result = dao.deleteMember(memberId);
		
		if(result > 0) {
			System.out.println("탈퇴 성공");
		}else {
			System.out.println("탈퇴 실패");
		}
	}

MemberDao.java

	public int deleteMember(String memberId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		int result = 0;
		
		String query = "delete from member_tbl where member_id = ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberId);
			result = pstmt.executeUpdate();
			if(result > 0) {
				conn.commit();
			}else {
				conn.rollback();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return result;
	}

 

[ 후기 ]

Statement를 배우면서 +연결로 쿼리문을 작성할때는 오타찾기도 쉽지않고 가독성이 정~~말 안좋아서 힘들었는데

PreparedStatement를 사용하니까 코드도 훨신 깔끔하고 간단해져서 너무 재미있었다. 

 

오늘 실수한 점

없음! 

저작자표시

'국비수업 > JDBC' 카테고리의 다른 글

29일차 : JDBC 실습1. LIBRARY(도서대여) 프로그램  (0) 2023.01.06
28일차 : JDBC 실습1 - 커뮤니티( 로그인 / 회원가입 / 게시판 )  (0) 2023.01.05
27일차 : JDBC - Singleton  (0) 2023.01.03
25일차 : JDBC - Statement  (0) 2022.12.30
'국비수업/JDBC' Related Articles more
Comments