SQL응용 - JDBC(PreparedStatemen)

 

* try-catch문 자동완성 시 나오는 주석문 삭제하는 법

window -> priference -> 사진 속 위치 -> catch block body -> edit -> 주석문 삭제하기 

 

저번 시간 만들어보았던 Member객체의 MVC패턴을 이용한 프로그램에서 DAO를 수정해보자.

1. JDBC용 객체 

- Connection : DB에 연결 정보를 담고 있는 객체 (ip주소, port번호, 계정명, 비밀번호)

- (Prepared)Statement : 해당 DB에 SQL문을 전달하고 실행한 후 결과를 받아내는 객체 

- ResultSet : 실행한 SQL문이 SELECT문일 경우 조회된 결과들이 담겨있는 객체 

1)  Statement(부모)와 PreparedStatement(자식)관계 

2) PreparedStatement의 특징 : SQL문을 바로 실행하지 않고 바로 보관하는 개념

-  미완성된 SQL문을 전달하고 실행하기 전에 완성 형태로 만든 뒤 실행만 함

- 미완성된 SQL문을 먼저 만들고 사용자가 입력한 값들이 들어갈 수 있는 공간을 ?(위치홀더)로 확보 각 위치홀더에 맞는 값들을 셋팅 

3) 차이점

(1) Statement는 완성된 SQL문, PreparedStatment는 미완성된 SQL문 

(2) Statement 객체 생성 시  stmt = conn.createStatement();
     PreparedStatement 객체 생성 시  pstmt = conn.prepareStatement();

(3)  Statement로 SQL문 실행 시  결과 = stmt.excuteXXX(sql);

PreparedStatement SQL문 실행 시 ?로 빈공간을 실제 값으로 채워준 뒤 실행한다. 

- 실제 값 채우기

pstmt.setString(?의 위치, 실제값);

pstmt.setInt(?의 위치, 실제값);

결과 = pstmt.executeXXX();

4) JDBC 처리 순서

 

 

 

JDBC처리순서

1) JDBC Driver등록 : 해당 DBMS가 제공하는 클래스 등록

2) Connection 객체 생성 : 접속하고자 하는 DB의 정보를 입력해서 DB에 접속하면서 생성(url, 계정, 비밀번호)

 3_1) PreparedStatement 객체 생성 : Connection객체를 이용해서 생성(미완성된 SQL문 담아서)

 3_2) 현재 미완성된 SQL문을 완성형태로 채우기
 -> 미완성된 경우에만 해당 / 완성된 경우에는 생략 가능 
 4) SQL문 실행 : executeXXX() => SQL매개변수 없음 
> SELECT문의 경우 : executeQuery()
> 나머지 DML문의 경우 : executeUpdate()
5) 결과 받기

> SELECT문의 경우 : ResultSet객체(조회한 데이터들이 담겨있음)로 받기 => 6_1)
> 나머지 DML문의 경우 : int형(처리된 행의 갯수)으로 받기 => 6_2) 
6_1) ResultSet에 담겨있는 데이터들을 하나씩 뽑아서 VO객체에 담기 (많으면 ArrayList로 관리)
6_2) 트랜잭션처리(성공하면 COMMIT, 실패하면 ROLLBACK)

7) 다 쓴 JDBC용 객체들은 반드시 자원 반납(close()) => 생성된 순서의 역순으로 
8) 결과반환(Controller) 
 > SELECT문의 경우 6_1에서 만들어진 결과

 > 나머지 DML문의 경우 처리된 행의 갯수 

 

2. MemberDao 

- insertMember

0) 필요한 변수 셋팅

package com.kh.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.kh.model.vo.Member;

public class MemberDao {
	public int insertMember(Member m) {
 int result = 0; //처리된 결과(처리된 행의 갯수)를 담아줄 변수
 Connection conn = null; //접속된 DB의 연결정보를 담는 변수
 PreparedStatement pstmt = null; //SQL문 실행 후 결과를 받기 위한 변수

+ 필요한 변수 : 실행할 SQL문

INSERT INTO MEMBER VALUES (SEQ_USERNO.NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxxx', x, 'xxx@xxx',                              'xxx', 'xxx', 'xxx', DEFAULT);

String sql = "INSERT INTO MEMBER "
+ "VALUES(SEQ_USERNO.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)"; //미완성된 sql문

1) JDBC Driver 등록

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");

2) Connection 객체 생성 (DB와 연결 -> url, 계정명, 비밀번호)

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");

3_1) PreparedStatement 객체 생성 (SQL문을 미리 넘겨줌)

pstmt = conn.prepareStatement(sql);

3_2) 미완성된 SQL문일 경우 완성시켜주기

pstmt.setXXX(?의 위치, 실제값);
pstmt.setString(홀더 순번, 대체할 값); => '대체할 값'(양 옆에 홑따옴표를 감싸준 상태로 알아서 들어감) 
pstmt.setInt(홀더 순번, 대체할 값); => 대체할 값(따옴표 없이)

pstmt.setString(1, m.getUserId()); 
pstmt.setString(2, m.getUserPwd());
pstmt.setString(3, m.getUserName());
pstmt.setString(4, m.getGender());
pstmt.setInt(5, m.getAge());
pstmt.setString(6, m.getEmail());
pstmt.setString(7, m.getPhone());
pstmt.setString(8, m.getAddress());
pstmt.setString(9, m.getHobby());

preparedStatement의 단점 -> 완성된 sql문을 볼 수 없음

4, 5) DB에 SQL문을 실행 후 결과(처리된 행 갯수)받기 

result = pstmt.executeUpdate(); //절대 sql문 넘기지 말아야 함

6_2) 트랜잭션처리

if(result > 0) {
	conn.commit();
} else {
	conn.rollback();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();

7) 다 쓴 JDBC용 객체 자원 반납 => 생성된 순서의 역순으로 close()

finally {
		try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

8) controller에게 처리된 행의 갯수 반환

return result;
	}

- selectAll()

멤버만 들어갈 수 있는 리스트를 만들고 현재 텅 빈 리스트 

ArrayList<Member> list = new ArrayList();
		//List는 Object배열에 담는 것.

처음에 실질적으로 담길 객체 

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
		
String sql = "SELECT * FROM MEMBER";

1) JDBC Driver 등록

try {
			Class.forName("oracle.jdbc.driver.OracleDriver");

2) Connection 객체 생성

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");

3) PreparedStatement 객체 생성

pstmt = conn.prepareStatement(sql);

4, 5) SQL(SELECT)를 실행 후 결과(ResultSet)받기 

rset = pstmt.executeQuery();

6) 현재 조회결과가 담긴 RESULTSET에서 한 행씩 뽑아내서 VO객체에 담기 

rset.next(): 커서를 한 줄 아래로 옮겨주고 해당 행이 존재할 경우  true, 아니면 false로 반환 

while(rset.next()) {
			Member m = new Member();
			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"));
			
			list.add(m);
           }
		} catch (ClassNotFoundException e) {
			e.printStackTrace(); 
		} catch (SQLException e) {
				e.printStackTrace();
		} finally {

list에 주소값 추가 

참조자료형은 주소값을 가르키는 것이 없으면 가비지컬렉터로 인해 사라지는데, list가 가르키기 때문에 사라지지 않음. 

7) 다 쓴 JDBC용 객체 반납(생성된 순서의 역순으로) 

try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

8) Controller에게 결과 반환 (결과가 담겨있는/없는 List)

return list;
	}

- selectByUserId

	public Member selectByUserId(String userId) {
		//Member m = null;
		Member m = new Member();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		String sql = "SELECT * FROM MEMBER "
				 	+ " WHERE USERID = ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		
		conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, userId);
		rset = pstmt.executeQuery();
		if(rset.next()) {
			//1. setter()

		//2) constructor() 매개변수를 이용한 생성자 
		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"));
		}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return m;
	}

- selectByUserName

	public ArrayList<Member> selectByUserName(String keyword){
		ArrayList<Member> list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;

방법1) sql문 안에서 문자열을 합치기

String sql = "SELECT * FROM MEMBER"
                  + " WHERE USERNAME LIKE '%'||?||'%'" ;

방법2) 미완성 sql문을 완성할 때 합치기

String sql = "SELECT * FROM MEMBER"
				+ " WHERE USERNAME LIKE ?" ;

try {
//1) 
Class.forName("oracle.jdbc.driver.OracleDriver");
//2) 
conn =  DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");	
//3_1) 
pstmt = conn.prepareStatement(sql);

방법1) 

pstmt.setString(1, keyword);

방법2) 

pstmt.setString(1, "%" + keyword + "%");

//4,5)
		rset = pstmt.executeQuery();
		
		//6-1) 
		while(rset.next()) {
		list.add(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")
				));
		}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try { //7) 
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} //8) 
		return list;
	}

- updateMember

	public int updateMember(Member m) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		//실행할 SQL문
		
		String sql = "UPDATE MEMBER "
				+ " SET USERPWD = ?, EMAIL = ?, PHONE = ?, ADDRESS = ? "
				+ " WHERE USERID = ?";
		//1)
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
		//2)
		conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
			
		//3) 
			pstmt = conn.prepareStatement(sql);
			
			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) 
			result = pstmt.executeUpdate();
			
			//6)
			if(result>0) {
				conn.commit();
			}else {
				conn.rollback();
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {//7)
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}//8)
		return result;
	}

- deleteMember

public int deleteMember(String userId) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "DELETE FROM MEMBER WHERE USERID = ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
		conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "JDBC", "JDBC");
		
		pstmt = conn.prepareStatement(sql);
		
		pstmt.setString(1, userId);
		
		result = pstmt.executeUpdate();
		
		if(result > 0 ) {
			conn.commit();
		} else {
			conn.rollback();
		}
		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	
}