[JDBC] Statement 객체의 PrearedStatement, CallableStatement

🔻PreparedStatement

• 미리 컴파일된 SQL 문을 실행한다. (SQL Injection을 방지하고 성능을 향상시킵니다)
• 기능이 더 향상된 개량형이다.
• Statement 에서 매개 변수 처리를 특화했다.
• 안정성과 가독성이 높다.
• 매개변수를 자동으로 유효성 처리를 해준다.

 

Statement 와 PrearedStatement 비교

Statement는 매개변수가 없는 정적 SQL을 다루기 편하고,

PreparedStatement는 매개변수가 있는 동적 SQL을 다루기 편하다.

 

 

정적 SQL과 동적SQL

정적 SQL

미리 작성된 고정된 SQL 문장을 의미한다.

String sql = "insert into tblAddress (seq, name, age, gender, address, regdate) values (seqAddress.nextVal, '이순신', 25, 'm', '서울시 강남구', default)";

sql = String.format("insert into tblAddress (seq, name, age, gender, address, regdate) values "+ "(seqAddress.nextVal, '%s', %s, '%s', '%s', default)", name, age, gender, address);

 

동적 SQL

실행 시간에 생성되고 실행되는 SQL 문장을 의미한다. 

'?'은 오라클 변수로서 상황에 따라 값이 변한다고 인식한다.

PreparedStatement를 사용하는 가장 큰 이유 중 하나는 매개변수를 자동으로 유효성 처리를 해주는 것인데 '?'가 그 기능을 수행한다.

sql = String.format("insert into tblAddress (seq, name, age, gender, address, regdate) values (seqAddress.nextVal, ?, ?, ?, ?, default)", name, age, gender, address);

 

예를 들어) 사용자 입력 값을 받을 때, 꽃따옴표(')가 들어가있으면 삽입되지않아, 이스케이프시켜서 삽입해야한다.

String address = "떡잎마을's";
address = address.replace("'", "''");

 

하지만 동적 SQL문으로 작성하면, 이스케이프 코드를 작성할 필요없이 그대로 삽입할 수 있도록 도와준다.

 

 

🔻PreparedStatement 예제

사용자에게 매개변수를 입력받은 값으로 insert

 

매개변수 입력받기

사용자에게 값을 입력받았다고 가정하고 이름, 나이, 성별, 주소 변수에 값을 저장한다.

String name = "신짱아";
String age = "2";
String gender = "f";
String address = "떡잎마을's";

 

동적 SQL 작성하기

String sql = "insert into tblAddress (seq, name, age, gender, address, regdate) values "
						+ "(seqAddress.nextVal, ?, ?, ?, ?, default)";

 

PreparedStatement 객체 초기화

생성할 때부터 sql문을 매개변수로 필요로한다.

pstat = conn.prepareStatement(sql);

 

pstat.setXXX()

생성된 PreparedStatement 객체를 담고 있는 변수 pstat는 매개변수를 관리하는 역할을 겸한다.

pstat.setXXX()으로 문자열을 SQL 문의 파라미터로 바인딩하는 역할을 한다.

setString(int parameterIndex, String x)
parameterIndex는 물음표(?)의 위치를 나타내는 인덱스이다. 첫 번째 물음표는 1부터 시작한다.
x는 SQL 문에 바인딩할 문자열이다.

pstat.setString(1, name);
pstat.setString(2, age);
pstat.setString(3, gender);
pstat.setString(4, address);

 

결과값 저장

sql문 실행이 제대로 되었는지 executeUpdate() 메서드로 업데이트된 행의 수를 반환받는다.

int result = pstat.executeUpdate();

 

전체 코드

	private static void m2() {
		
		String name = "신짱아";
		String age = "2";
		String gender = "f";
		String address = "떡잎마을's";
		
		Connection conn = null;
		PreparedStatement pstat = null;
		
		try {
			
			conn = DBUtil.open();
			
			if (!conn.isClosed()) {
				
				String sql = "insert into tblAddress (seq, name, age, gender, address, regdate) values "
						+ "(seqAddress.nextVal, ?, ?, ?, ?, default)";
				
				pstat = conn.prepareStatement(sql);
				
				pstat.setString(1, name);
				pstat.setString(2, age);
				pstat.setString(3, gender);
				pstat.setString(4, address);
				
				int result = pstat.executeUpdate();
				
				if (result == 1) {
					System.out.println("삽입 성공");
				} else {
					System.out.println("삽입 실패");
				}
				
				pstat.close();
				conn.close();
				
			} else {
				System.out.println("DB 접속 실패");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

 

---

🔻CallableStatement

• 데이터베이스 내부의 저장 프로시저를 호출한다.
• 기능이 더 향상된 개량형이다.
• 프로시저 호출 전용이다.
• 인자값과 반환값을 고려해서 작성해야 하는 경우가 다섯 가지가 있다.
  • 1. 인자값과 반환값이 없는 프로시저
  • 2. 인자값이 있고 반환값이 없는 프로시저
  • 3. 인자값이 없고 반환값이 단독반환값인 프로시저
  • 4. 인자값이 없고 반환값이 다중반환값인 프로시저
  • 5. 커서를 반환하는 프로시저

 

🔻CallableStatement 예제

1. 인자값(X), 반환값(X) 프로시저 호출

procM1 프로시저 생성

인자값과 반환값이 없는 프로시저를 생성한다. (sql developer를 통해 생성하였다.)

create or replace procedure procM1
is
begin
    update tblAddress set age = age +1;
end procM1;

 

저장 프로시저 호출하기

{call '프로시저명'} 으로 호출한다.

String sql = "{ call procM1 }";

 

CallableStatement 객체 생성

cstat = conn.prepareCall(sql);

 

결과값 저장

sql문 실행이 제대로 되었는지 executeUpdate() 메서드로 업데이트된 행의 수를 반환받는다.

int result = cstat.executeUpdate();

 

전체 코드

	private static void m1() {
		
		Connection conn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.open();
			
			String sql = "{ call procM1 }";
			cstat = conn.prepareCall(sql);
			
			int result = cstat.executeUpdate();
			
			System.out.println(result);
			
			cstat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

 

 

2. 인자값(o), 반환값(X) 프로시저

procM2 프로시저 생성

인자값이 있고 반환값이 없는 프로시저를 생성한다. (sql developer를 통해 생성하였다.)

create or replace procedure procM2(
    pname tblAddress.name%type,
    page tblAddress.age%type,
    pgender tblAddress.gender%type,
    paddress tblAddress.address%type
)
is 
begin
    insert into tblAddress
        values (seqAddress.nextVal, pname, page, pgender, paddress, default);
end procM2;

 

저장 프로시저를 호출할 때, 물음표(?)를 통해 인자값을 넘긴다.

	private static void m2() {
		
		Connection conn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.open();
			
			String sql = "{call procM2(?,?,?,?)}";
			cstat = conn.prepareCall(sql);
			
			cstat.setString(1, "김철수");
			cstat.setString(2, "5");
			cstat.setString(3, "m");
			cstat.setString(4, "떡잎마을");
			
			int result = cstat.executeUpdate();
			
			System.out.println(result);
			
			cstat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

 

 

3. 인자값(X), 반환값(O) 프로시저

procM3 프로시저 생성

인자값이 없고 단독반환값이 있는 프로시저를 생성한다. (sql developer를 통해 생성하였다.)

create or replace procedure procM3(
    pcnt out number
)
is
begin
    select count(*) into pcnt from tblAddress;
end procM3;

 

저장 프로시저 호출

out 파마미터 하나 당 물음표(?) 하나로 표현해준다.

String sql = "{call procM3(?)}";

 

out 매개변수 접근법

첫 번째 매개 변수는 결과 매개 변수의 인덱스를 나타내며, 두 번째 매개 변수는 결과 매개 변수의 데이터 유형을 나타낸다.

cstat.registerOutParameter(1, OracleTypes.NUMBER);

 

결과값 저장

저장 프로시저를 실행하고, 실행 결과로 영향을 받은 행의 수를 반환한다. 이 경우 ResultSet를 사용하지않는다.

cstat.executeUpdate();

 

out 매개변수 읽기

int cnt = cstat.getInt(1);

 

전체 코드

 

private static void m3() {
		
		Connection conn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.open();
			
			String sql = "{call procM3(?)}";
			
			cstat = conn.prepareCall(sql);
			
			cstat.registerOutParameter(1, OracleTypes.NUMBER);
			
			cstat.executeUpdate();
			
			//rs.getInt(1); 와 유사하지만 rs, cstat는 포지션이 다르다.
			int cnt = cstat.getInt(1);
			
			System.out.println(cnt);
			
			cstat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

 

 

4. 인자값(X), 반환값(O) 프로시저

procM4 프로시저 생성

인자값이 없고 다중반환값이 있는 프로시저를 생성한다. (sql developer를 통해 생성하였다.)

create or replace procedure procM4(
    pname out varchar2,
    page out number,
    paddress out varchar2
)
is
begin
    select name, age, address into pname, page, paddress from tblAddress where rownum=1;
end procM4;

 

다중으로 반환하므로 out 파라미터를 여러번 접근한다.

private static void m4() {
		
		Connection conn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.open();
			
			String sql = "{call procM4(?,?,?)}";
			
			cstat = conn.prepareCall(sql);
			
			cstat.registerOutParameter(1, OracleTypes.VARCHAR);
			cstat.registerOutParameter(2, OracleTypes.NUMBER);
			cstat.registerOutParameter(3, OracleTypes.VARCHAR);
			
			cstat.executeUpdate();

			System.out.println(cstat.getString(1));
			System.out.println(cstat.getInt(2));
			System.out.println(cstat.getString(3));
			
			cstat.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

 

 

5. 커서를 반환하는 프로시저

procM5 프로시저 생성

커서를 반환하는 프로시저를 생성한다. (sql developer를 통해 생성하였다.)

create or replace procedure procM5 (
    pcursor out SYS_REFCURSOR
)
is
begin
    open pcursor
    for
        select * from tblAddress;
end procM5;

 

cursor 접근하기

자바에는 cursor 자료형이 없어 object로 가져온다.
오라클 커서는 결과 테이블을 탐색하는 참조 객체인데 ResultSet 또한 결과 테이블을 탐색하는 참조 객체이므로 오라클 커서와 ResultSet과 동일한 구조라고 볼 수 있다.

private static void m5() {
		
		Connection conn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.open();
			
			String sql = "{call procM5(?)}";
			
			cstat = conn.prepareCall(sql);
			
			cstat.registerOutParameter(1, OracleTypes.CURSOR);
			
			cstat.executeQuery();
			
			rs = (ResultSet)cstat.getObject(1);
			
			while(rs.next()) {
				System.out.println(rs.getString("name"));
				System.out.println(rs.getInt("age"));
				System.out.println(rs.getString("address"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}