📗 self-study/📗 KH정보교육원 당산지원
[JDBC] Statement와 PreparedStatement
천재강쥐
2022. 9. 7. 10:17
더보기
Statement로 진행했던 기존 프로젝트를 PreparedStatment로 변경해 봄으로써
Statement와 Prestatement의 차이점을 알아보자
<객체 생성 방법>
Statement => conn.createStatement();
PreparedStatement => conn.prepareStatement(sql);
👉🏻 실행되기 전에 sql을 완성시켜 준다는 점이 다름!
<쿼리문의 완성 유무>
Statement => 완성된 상태
PreparedStatement => 완성되든 미완성되든 형태는 상관없음
단, 미완성된 형태일 경우에는 쿼리문이 실행되기 전 시점에 쿼리문을 완성시켜 줘야 함
<Statement 👉🏻 PreparedStatement>
학원에서는 해당 과정을 기존 프로젝트를 복사하여 필요한 코드만 수정하는 방식으로 진행!
<프로젝트 복사하는 법>
*Statement(부모, 정적 바인딩)와 PreparedStatement(자식, 동적 바인딩)의 차이점
👉🏻 정적 바인딩: 컴파일 시간에 성격이 결정되는 것
👉🏻 동적 바인딩: 실행 시간에 성격이 결정되는 것(문자열 넣어 달라고 하면 알아서 홑따옴표 넣어 줌)
- Statement 같은 경우 완성된 SQL문을 바로 실행하는 객체
(즉, SQL문이 완성된 형태로 세팅되어 있어야만 함! => 사용자가 입력했던 값들이 다 채워진 채로 세팅)
> Connection 객체를 가지고 Statement 객체 생성: stmt = conn.createStatement();
> executeXXXXX 메소드를 이용해서 SQL문을 전달하면서 실행: 결과 = stmt.executeXXXXX(sql);
- PreparedStatment 같은 경우 SQL문을 바로 실행하지 않고 잠시 보관해 둘 수 있음
(즉, 완성되든 미완성되든 상관없이 SQL문을 잠시 보관할 수 있음!
👉🏻 단, 미완성된 경우 사용자가 입력한 값들이 들어갈 수 있는 공간을?
(위치 홀더)로 확보해 둬야 함
해당 SQL문을 실행하기 전에 완성 형태로 만든 후 실행만 하면 됨!
> Connection 객체를 가지고 PreparedStatment 객체 생성: pstmt = conn.prepareStatement(sql);
> 현재 담긴 SQL문이 미완성된 형태일 경우
빈 공간 (?) 을 실제 값으로 채워 주는 과정을 거쳐야 함: pstmt.setString / pstmt.setInt(위치홀더의순번, 실제값)
> executeXXXX 메소드를 이용해서 SQL문을 실행: 결과 = pstmt.executeXXXXX();
<처리 순서>
Statement와 PreparedStatement 처리 과정 차이점 한 눈에 보기
<전체 과정 Statement ver.>
<전체 과정 PreparedStatement ver.>
<변수 자료형과 이름 바꿔 주기>
<PreparedStatement ver.로 sql문 바꿔 주기>
이때 PreparedStatement의 유일한 단점을 알 수 있는데
Statement를 사용했을 경우: 출력문을 통해 오라클에 어떤 명령어를 줬는지 확인 가능
👉🏻 SQL문을 완성시킨후 대입하기 때문에 (정적 바인딩)
PreparedStatement를 사용했을 경우: 출력문을 통해 오라클에 어떤 명령어를 줬는지 확인 불가능
👉🏻 SQL문 미완성 단계이기 때문에 (동적 바인딩)
<1~3 과정: statement ver.>
<1~3 과정: PreparedStatement ver.>
<변수 자료형과 sql 빼기>
<변수명 바꾸기>
MemberDao 클래스에서만 바꿔 주면 됨
(1) insertMember()
👉🏻 위의 코드가 insertMember() 기준이므로 확인하기
(2) selectAll()
/**
* 사용자가 회원 전체 조회 요청 시 SELECT문을 실행해 주는 메소드
* @return: 테이블로부터 조회된 전체 회원의 정보를 나타내는 ArrayList
*/
public ArrayList<Member> selectAll() {
// SELECT문 => ResultSet 객체 => 여러 행 조회 (Member 1개당 회원 1명): ArrayList 타입으로 묶기
// 0) 필요한 변수들 먼저 세팅 (선언 및 초기화)
ArrayList<Member> list = new ArrayList<>(); // 조회된 결과를 뽑아서 담아 줄 ArrayList를 생성 (텅 빈 리스트)
Connection conn = null; // 접속된 DB의 연결 정보를 담는 변수
// Statement stmt = null; // SQL문 (DELETE)을 실행 후 결과를 받을 수 있는 변수
PreparedStatement pstmt = null; // SQL문 실행 후 결과를 받기 위한 변수
ResultSet rset = null; // SELECT문이 실행될 조회 결과값들이 처음에 실질적으로 담겨 올 객체
// 실행할 SQL문(완성된 형태로, 세미콜론은 빼고)
// SELECT * FROM MEMBER
String sql = "SELECT * FROM MEMBER";
// => PreparedStatement 는 완성된 쿼리문을 애초에 보내 놔도 무방!!
try {
// 1) JDBC Driver 등록
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2) Connection 객체 생성(url, 계정명, 비밀번호)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
// 이미 try 블럭 안이므로 2번째 선택지인 "Add catch clause to surrounding try"를 선택해 주면 catch문만 생김
// 3_1) PreparedStatement 객체 생성 (Connection 객체로부터 얻어냄)
//stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql); // 애초에 SQL문을 담은 채로 생성
// 3_2) 미완성된 쿼리문을 완성시키기
// => 3_1) 에서 완성된 쿼리문을 미리 넘겨 뒀기 때문에 이 과정은 생략
// 4, 5) SQL문 (SELECT) 실행해서 결과값 받아내기
rset = pstmt.executeQuery();
// => rset에는 조회된 결과물들이 다 담겨 있을 것임
// 6_1) 현재 조회 결과가 담긴 ResultSet에서 한 행씩 뽑아서 VO 객체에 담기
// => ResultSet 객체는 커서를 한 줄씩 아래로 옮겨서 현재 행의 위치를 나타내는 구조
// => 이때, 커서를 rest.next() 메소드로 다음 줄로 옮겨 버리기
while (rset.next()) { // 커서를 한 줄 아애로 움직여 주고
// 해당 행이 존재할 경우 true, 존재하지 않을 경우 false를 반환함
// 적어도 이 중괄호 안에 들어와서 코드가 실행된다라는 것은
// ResultSet 객체로부터 뽑아낼 데이터가 있다라는 뜻!
// 현재 rset의 커서가 가리키고 있는 해당 행의 데이터를 하나씩 뽑아서 Member 객체에 담기
// 한 행의 데이터 == 회원 한 명의 정보 == Member VO 객체 한 개
Member m = new Member();
// rset으로부터 어떤 컬럼에 해당하는 값을 뽑을 건지 제시해서
// 해당 컬럼의 값들을 각 필드로 옮겨 주기
// -> 해당 행의 해당 컬럼의 값을 자바의 지정된 자료형으로 가지고 옴
// rset.getInt(컬럼명/컬럼순번): int형 값을 뽑아낼 때
// rset.getString(컬럼명/컬럼순번): String 형 값을 뽑아낼 때
// rset.getDate(컬럼명/컬럼순번): Date형 값을 뽑아낼 때
// => 컬럼명은 대소문자를 가리지 않음
// => 권장사항: 컬럼명으로 쓰고, 대문자로 쓰는 것을 권장! (가독성 높아져 협업 때 편함)
// m.userNo = rset.getInt("USERNO"); --> private이라 직접 접근 불가
m.setUserNo(rset.getInt("USERNO")); // 모든 컬럼에 대해 이 작업을 다 해 줘야 함
m.setUserId(rset.getString("USERID"));
m.setUserPwd(rset.getString("USERPWD"));
m.setUserName(rset.getString("USERNAME"));
m.setGender(rset.getString("GENDER"));
m.setAge(rset.getInt("AGE"));
m.setEmail(rset.getString("EMAIL"));
m.setPhone(rset.getString("PHONE"));
m.setAddress(rset.getString("ADDRESS"));
m.setHobby(rset.getString("HOBBY"));
m.setEnrollDate(rset.getDate("ENROLLDATE"));
// 컬럼명 오타 주의! get자료형 주의!
// 한 행에 대한 모든 데이터 값들을 하나의 Member 객체로 옮겨 담기 끝!
// 리스트에 해당 Member 객체를 담아 둘 것!
list.add(m);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
// 7) 다 쓴 JDBC용 객체 반납 (생성된 역순)
// 생성 순서: Connection -> Statement -> ResultSet
// 닫는 순서: ResultSet -> Statement -> Connection
rset.close();
pstmt.close();
conn.close();
// "Surround with try/catch" 클릭해서 3개 모두 try ~ catch문에 넣어 주기
} catch (SQLException e) {
e.printStackTrace();
}
}
// 8) 결과 반환
return list; // 회원들의 정보가 담겨 있음!
// 만약 아무도 조회되지 않았다면 list.size() == 0 또는 list.isEmpty() == true 일 것
}
(3) selectByUserId()
/**
* 사용자의 아이디 검색 조회를 위한 SELECT문을 실행할 메소드
* @param userId: 검색하고자 하는 조건에 해당되는 아이디 값
* @return: 검색된 회원 한 명의 정보
*/
public Member selectByUserId(String userId) {
// SELECT문 => ResultSet 객체 => 한 행 조회 (Member 하나로 결과 받기)
// 0) 필요한 변수들 먼저 세팅
Member m = null; // 조회된 한 회원에 대한 정보를 담는 변수
Connection conn = null;
// Statement stmt = null; // SQL문 (DELETE)을 실행 후 결과를 받을 수 있는 변수
PreparedStatement pstmt = null; // SQL문 실행 후 결과를 받기 위한 변수
ResultSet rset = null; // SELECT 문이 실행된 조회 결과들이 처음에 실질적으로 담길 객체
// 실행할 SQL문 (완성된 형태, 세미콜론 X)
// SELECT * FROM MEMBER WHERE USERID = 'XXXX'
// String sql = "SELECT * FROM MEMBER WHERE USERID = '" + userId + "'";
// 실행할 SQL문 (미완성된 형태, 세미콜론 X)
// SELECT * FROM MEMBER WHERE USERID = ?
String sql = "SELECT * FROM MEMBER WHERE USERID = ?";
try {
// 1) JDBC Driver 등록
Class.forName("oracle.jdbc.driver.OracleDriver");
// 잘못되면 ClassNotFoundException
// 2) Connection 객체 생성(url, 계정명, 비밀번호)
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
// 3_1) PreparedStatement 객체 생성
// stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql);
// 3_2) 미완성된 쿼리문일 경우 완성 형태로 바꾸기
pstmt.setString(1, userId); // 'user01'
// 4, 5) SQL문 (SELECT)을 전달해서 실행 후 결과 받기
// rset = stmt.executeQuery(sql);
rset = pstmt.executeQuery();
// 6_1) 현재 조회 결과가 담긴 ResultSet에서 한 행씩 뽑아서 VO 객체에 담기
// while(rset.next()) { // 여러 행 조회일 경우 더 이상 뽑아낼 데이터가 없을 때까지 돌리기
// -> ResultSet 특징: 커서가 제일 위의 행에서 기다리고 있음!
// -> .next(): 바로 밑의 행에 값이 있다면 true, 없으면 false로 행을 뽑아냄
if(rset.next()) { // 어차피 unique 제약 조건에 의해 한 행만 조회되므로 if로 돌리는 게 좋음
// 적어도 이 중괄호 안에 들어왔다는 것은 조회된 결과가 있다는 것!
// => 한 개의 행에 대해서 각 컬럼마다 값을 매번 뽑아서 VO 객체의 필드로 가공
// 조회된 한 행에 대한 데이터값들을 뽑아서 하나의 Member 객체에 담기
m = new Member(rset.getInt("USERNO")
, rset.getString("USERID")
, rset.getString("USERPWD")
, rset.getString("USERNAME")
, rset.getString("GENDER")
, rset.getInt("AGE")
, rset.getString("EMAIL")
, rset.getString("PHONE")
, rset.getString("ADDRESS")
, rset.getString("HOBBY")
, rset.getDate("ENROLLDATE"));
}
// 이 시점 기준으로 봤을 때
// 만약 조회된 회원이 있다면 m이라는 변수에 Member 타입의 객체가 담겨 있음
// 만약 조회된 회원이 없다면 m이라는 변수에 null 값이 담겨 있음
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
// 7) 다 쓴 JDBC용 객체 반납 (생성된 역순)
// ResultSet -> Statement -> Connection
rset.close();
// stmt.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return m;
}
(4) selectByUserName()
/**
* 사용자의 이름 키워드 검색 요청을 실행하기 위해 SELECT문을 수행하는 메소드
* @param keyword: 이름 키워드 검색을 위한 검색어
* @return: 조회된 회원들의 목록
*/
public ArrayList<Member> selectByUserName(String keyword) {
// 다른 곳에서도 호출할 수 있어야 하므로 public
// SELECT문 => ResultSet 객체 => 여러 행 (ArrayList<Member>)
// 0) 필요한 변수 먼저 세팅
ArrayList<Member> list = new ArrayList<>(); // 조회된 결과를 담을 수 있는 텅 빈 리스트
Connection conn = null; // 접속된 DB의 정보를 담아 둘 수 있는 변수
// Statement stmt = null; // SQL문 (DELETE)을 실행 후 결과를 받을 수 있는 변수
PreparedStatement pstmt = null; // SQL문 (SELECT) 실행 및 결과를 받을 수 있는 변수
ResultSet rset = null; // SELECT문 실행 후 결과들이 담길 변수
// 실행할 SQL문(완성된 형태, 세미콜론 X)
// SELECT * FROM MEMBER WHERE USERNAME LIKE '%XXX%'
// String sql = "SELECT * FROM MEMBER WHERE USERNAME LIKE '%" + keyword + "%'";
// 실행할 SQL문 (미완성된 형태, 세미콜론 X)
// SELECT * FROM MEMBER WHERE USERNAME LIKE '%?%'
// String sql = "SELECT * FROM MEMBER WHERE USERNAME LIKE '%?%'";
// 구멍을 메꾸는 순간 문법에 맞지 않는 구문이 될 것임! (오류 발생)
// 해결방법 1)
// String sql = "SELECT * FROM MEMBER WHERE USERNAME LIKE '%' || ? ||'%'";
// => 이 방법을 쓸 경우에는 메꿀 값을 그냥 제시하면 됨
// 해결방법 2) 그냥 통째로 구멍을 뚫는 방법
String sql = "SELECT * FROM MEMBER WHERE USER NAME LIKE ?";
// 단, 이 방법을 쓸 경우에는 메꿀 값 양 사이드에 "%" + keyword + "%" 이런식으로 메꿔 줘야 함
try {
// 1) JDBC 드라이버 등록
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2) Connection 객체 생성
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
// 3_1) PreparedStatement 객체 생성
// stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql);
// 3_2) 미완성된 SQL문 완성시키기
// 해결방법 1) 을 사용했을 경우
// pstmt.setString(1, keyword);
// 해결방법 2) 를 사용했을 경우
pstmt.setString(1, "%" + keyword + "%");
// 4, 5) SQL문 실행 후 결과 받기
// rset = stmt.executeQuery(sql);
rset = pstmt.executeQuery();
// 6_1) 현재 조회 결과가 담긴 ResultSet에서 한 행씩 뽑아서 ArrayList에 담기
while (rset.next()) {
// 한 행에 담긴 데이터들을 한 Member 타입의 객체에 옮겨 담기 => 그 Member를 list에 담기
// 표현법 1)
list.add(new Member(rset.getInt("USERNO")
, rset.getString("USERNAME")
, rset.getString("USERPWD")
, rset.getString("USERNAME")
, rset.getString("GENDER")
, rset.getInt("AGE")
, rset.getString("EMAIL")
, rset.getString("PHONE")
, rset.getString("ADDRESS")
, rset.getString("HOBBY")
, rset.getDate("ENROLLDATE")));
// 이 시점 기준으로 봤을 때
// 조회된 회원이 없다면 list.size() == 0 또는 list.isEmpty() == true
// 표현법 2)
// Member m = new Member();
// m.setUserNo(rset.getInt("USERNO"));
// m.setUserId(rset.getString("USERNAME"));
// m.setUserPwd(rset.getString("USERPWD"));
// m.setUserName(rset.getString("USERNAME"));
// m.setGender(rset.getString("GENDER"));
// m.setAge(rset.getInt("AGE"));
// m.setEmail(rset.getString("EMAIL"));
// m.setPhone(rset.getString("PHONE"));
// m.setAddress(rset.getString("ADDRESS"));
// m.setHobby(rset.getString("HOBBY"));
// m.setEnrollDate(rset.getDate("ENROLLDATE"));
//
// list.add(m);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
// 7) 객체 반납
rset.close();
// stmt.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 8) 결과 반환
return list; // 조회 결과가 없다면 list.size() == 0 또는 list.isEmpty() == true
}
(5) updateMember()
/**
* 회원 정보 수정을 위한 UPDATE 구문을 실행하는 메소드
* @param m: 회원정보 수정을 위한 데이터
* @return: 테이블에 UPDATE 되는 행의 개수
*/
public int updateMember(Member m) {
// UPDATE문 => 처리된 행의 개수가 int형으로 반환 => 트랜잭션 처리
// 0) 필요한 변수 먼저 세팅
int result = 0; // 처리된 행의 개수를 받을 수 있는 변수
Connection conn = null; // 접속된 DB의 정보를 담을 수 있는 변수
// Statement stmt = null; // SQL문 (DELETE)을 실행 후 결과를 받을 수 있는 변수
PreparedStatement pstmt = null; // SQL문(UPDATE) 실행 후 결과를 받을 수 있는 변수
// 실행할 SQL문(완성된 형태, 세미콜론 X)
/* UPDATE MEMBER
* SET USERPWD = 'XXXX'
* , EMAIL = 'XXXXX'
* , PHONE = 'XXXXX'
* , ADDRESS = 'XXXXXX'
* WHERE USERID = 'XXXX';
*/
// String sql = "UPDATE MEMBER "
// + "SET USERPWD = '" + m.getUserPwd() + "'"
// + ", EMAIL = '" + m.getEmail() + "'"
// + ", PHONE = '" + m.getPhone() + "'"
// + ", ADDRESS = '" + m.getAddress() + "' "
// + "WHERE USERID = '" + m.getUserId() + "'";
// 실행할 SQL문 (미완성된 형태, 세미콜론 X)
String sql = "UPDATE MEMBER "
+ "SET USERPWD = ?"
+ ", EMAIL = ?"
+ ", PHONE = ?"
+ ", ADDRESS = ?"
+ " WHERE USERID = ?";
try {
// 1) JDBC Driver 등록
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2) Connection 객체 생성
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
//(주목) conn.setAutoCommit(false);
// 3_1) PreparedStatement 객체 생성
// stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql);
// 3_2) 미완성 SQL문을 완성된 형태로
// pstmt: pstmt에
// .set자료형: 자료형에 해당하는 값을 넣을 건데
// (몇번째구멍, 뭘넣을건지)
pstmt.setString(1, m.getUserPwd());
pstmt.setString(2, m.getEmail());
pstmt.setString(3, m.getPhone());
pstmt.setString(4, m.getAddress());
pstmt.setString(5, m.getUserId());
// 4, 5) SQL문 (UPDATE) 실행 후 결과 받기
// result = stmt.executeUpdate(sql);
//(주목) result = 를 안 쓸 경우 자동 커밋되므로 원래는 setAuroCommit(false)를 해 줘야 함
// 근데 mybatis 가면 얘를 자동으로 1줄 메소드로 진행시켜 주기 때문에... 음...
result = pstmt.executeUpdate();
// 6_2) 트랜잭션 처리
if(result > 0) { // 성공
conn.commit();
} else { // 실패
conn.rollback();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 7) 자원 반납 (생성 순서의 역순)
try {
// stmt.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 8) 결과 반환
return result;
}
(6) deleteMember()
/**
* 사용자의 회원 탈퇴 요청 시 DELETE 구문을 실행하는 메소드
* @param userId: 삭제하고자 하는 아이디값
* @return: 삭제된 행의 개수를 리턴
*/
public int deleteMember(String userId) {
// 0) 필요한 변수 선언
int result = 0; // 처리된 행의 개수를 담을 변수
Connection conn = null; // 접속할 DB의 정보를 담는 변수
// Statement stmt = null; // SQL문 (DELETE)을 실행 후 결과를 받을 수 있는 변수
PreparedStatement pstmt = null;
// 실행할 SQL문 (세미콜론 X)
// DELETE FROM MEMBER WHERE USERID = 'XXX'
// String sql = "DELETE FROM MEMBER WHERE USERID = '"
// + userId + "'";
// 실행할 SQL문 (세미콜론 X)
// DELETE FROM MEMBER WHERE USERID = ?
String sql = "DELETE FROM MEMBER WHERE USERID = ?";
try {
// 1) JDBC Driver 등록
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2) Connection 객체 생성
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
// 3_1) PreparedStatement 객체 생성
// stmt = conn.createStatement();
pstmt = conn.prepareStatement(sql);
// 3_2) 미완성 SQL문을 완성된 형태로
pstmt.setString(1, userId);
// 4, 5) SQL문 (DELETE문) 실행 후 결과 받기
// result = stmt.executeUpdate(sql);
result = pstmt.executeUpdate();
// 6_2) 트랜잭션 처리
if(result > 0) { // 성공
conn.commit();
} else { // 실패
conn.rollback();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
// 7) 자원 반납
// stmt.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 8) 결과 반환
return result;
}