[JDBC] Statement: PreparedStatement, CallableStatement

🍁PreparedStatement

---

Statement와 PreparedStatement 차이

Statement는 정적 SQL을 만들 때 사용하며, PreparedStatement는 동적 SQL를 만들 때 사용한다.

매개변수가 없으면 정적 쿼리, 매개변수가 있으면 동적 쿼리를 의미한다.

 

정적 쿼리와 동적 쿼리

정적 SQL

String sql = "INSERT INTO tblAddress (seq, name, age, gender, address, regdate) VALUES (seqAddress.nextVal, 'Sopia', 21, 'f', '서울시 강남구 대치동', default)";

String sql = "INSERT INTO tblAddress (seq, name, age, gender, address, regdate) VALUES (seqAddress.nextVal, '%s', %s, '%s', '%s', default)";

정적 쿼리는 한 번 실행하든, 여러 번 실행하든 결과가 같다.

 

동적 SQL

String sql = "INSERT INTO tblAddress (seq, name, age, gender, address, regdate) VALUES (seqAddress.nextVal, ?, ?, ?, ?, default)"

'?'를 사용하면 동적 쿼리가 된다.

이는 오라클 함수로, '?'에 들어갈 값이 정해져 있지 않고, 상황에 따라서 바뀐다고 생각한다.

 

PreparedStatement의 장점

1. 매개변수를 관리하기 용이하다.

2. 매개변수 유효성 처리를 자동으로 한다.

 

PreparedStatement의 사용

Scanner를 했다는 가정 하에 사용자 입력을 받아 insert 쿼리를 실행해 보도록 하자.

Statement와 PreparedStatement를 동시에 쓰는 경우는 거의 없지만, 설명을 위해 함께 선언하였다.

 

Statement는 홑따옴표 처리가 필요하다.

name = name.replace("'", "''");
address = address.replace("'", "''");

Statement는 매개변수를 넣을 때 홑따옴표 처리를 위해 replace 함수를 적용해 주어야 한다.

반면에 PreparedStatement를 쓰면 홑따옴표 처리를 자동으로 하므로 관리하기에 좋다.

 

setString

sql = "INSERT INTO tblAddress (seq, name, age, gender, address, regdate) VALUES (seqAddress.nextVal, ?, ?, ?, ?, default)";

pstat.setString(번호, 매개변수);

Statement는 모든 쿼리를 만들고 난 뒤에 실행할 때 쿼리를 넣는 반면, PreparedStatement는 만들 때부터 쿼리를 달라고 요구를 한다. 그리고 executeUpdate 메서드를 실행할 때 쿼리를 넣지 않는다.

동적 SQL이므로 '?'를 번호라고 생각하고 번호와 매개변수를 넣어주면 입력이 된다.

 

전체 코드

String name = "John";
String age = "24";
String gender = "m";
String address = "서울시 강남구's 일원동";

String sql = "";
int result;
	
/*
name = name.replace("'", "''");
address = address.replace("'", "''");
*/

Connection conn = null;
Statement stat = null;
PreparedStatement pstat = null;

try {
	conn = DBUtill.open();			
	
	//Statement
	/*
	sql = String.format("INSERT INTO tblAddress (seq, name, age, gender, address, regdate) VALUES (seqAddress.nextVal, '%s', %s, '%s', '%s', default)", name, age, gender, address);
	stat = conn.createStatement();
	result = stat.executeUpdate(sql);
	System.out.println(result);
	*/
	
	//PreparedStatement
	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);
	result = pstat.executeUpdate();
	System.out.println(result);

	pstat.close();
	/* stat.close(); */
	conn.close();
} catch (Exception e) {
	e.printStackTrace();
}

 

🍁CallableStatement

---

인자값(X) 반환값(X)

SELECT * FROM tblAddress;
DROP TABLE tblAddress;

CREATE TABLE tblAddress (
	seq NUMBER PRIMARY KEY,
	name VARCHAR2(30) NOT NULL,
	age NUMBER NOT NULL,
	gender CHAR(1) NOT NULL,
	address VARCHAR2(300) NOT NULL,
	regdate DATE DEFAULT sysdate NOT null
);

CREATE SEQUENCE seqAddress;
DROP SEQUENCE seqAddress;

INSERT INTO tblAddress (seq, name, age, gender, address, regdate)
	VALUES (seqAddress.nextVal, 'Isaac', 24, 'm', '서울시 강남구 역삼동', default);
	
INSERT INTO tblAddress (seq, name, age, gender, address, regdate)
	VALUES (seqAddress.nextVal, 'Sopia', 25, 'f', '서울시 강남구 역삼동', default);

SELECT * FROM tblAddress;
COMMIT;

CREATE OR REPLACE PROCEDURE procM1
IS
BEGIN 
	UPDATE tblAddress SET age = age + 1;
END procM1;

Connection conn = null;
CallableStatement stat = null;
ResultSet rs = null;

try {
    conn = DBUtil.open();

    String sql = "{ call procM1 }"; // 호출 방법
    stat = conn.prepareCall(sql);

    int result = stat.executeUpdate();
    System.out.println(result);

    stat.close();
    conn.close();

} catch (Exception e) {
    e.printStackTrace();
}

프로시저를 만들고 쿼리를 실행하면 DB에 저장된 사람들의 age가 1씩 증가한다.

 

인자값(O), 반환값(X)

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;

Connection conn = null;
CallableStatement stat = null;
ResultSet rs = null;

try {
	conn = DBUtil.open();

	String sql = "{ call procM2(?, ?, ?, ?) }"; // 호출 방법
	stat = conn.prepareCall(sql);

	stat.setString(1, "Lee");
	stat.setString(2, "24");
	stat.setString(3, "m");
	stat.setString(4, "서울시 강남구 역삼동");

	int result = stat.executeUpdate();
	System.out.println(result);
	
	stat.close();
	conn.close();
	
} catch (Exception e) {
	e.printStackTrace();
}

PreparedStatement, CallableStatement 둘 다 매개변수를 지원한다는 특징이 동일하다.

 

인자값(X), 반환값(O)

CREATE OR REPLACE PROCEDURE procM3 (
	pcnt OUT NUMBER
)
IS
BEGIN 
	SELECT COUNT(*) INTO pcnt FROM tblAddress;
END procM3;

Connection conn = null;
CallableStatement stat = null;

try {
	conn = DBUtil.open();
	
	String sql = "{ call procM3(?) }";
	
	stat = conn.prepareCall(sql);
	
	//out 매개변수
	stat.registerOutParameter(1, OracleTypes.NUMBER);
	
	//int result = stat.executeUpdate();
	
	stat.executeQuery(); //ResultSet을 받지 않음
	int cnt = stat.getInt(1); //out 매개변수 읽기
	
	System.out.println(cnt);
	
	stat.close();
	conn.close();

} catch (Exception e) {
	e.printStackTrace();
}

 

out 파라미터

stat.registerOutParameter(1, OracleTypes.NUMBER);

out 파라미터를 접근하는 방법은 registerOutParameter 메서드를 사용하여 인자를 전달하는 것이다.

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

데이터 유형으로 작성한 OracleTypes.NUMBER는 숫자 데이터 유형을 나타내며, 이 경우 결과 매개 변수를 숫자 값으로 반환한다.

 

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;

Connection conn = null;
CallableStatement stat = null;
ResultSet rs = null;

try {
	conn = DBUtil.open();

	String sql = "{ call procM4(?, ?, ?) }";
	
	stat = conn.prepareCall(sql);
	
	stat.registerOutParameter(1, OracleTypes.VARCHAR);
	stat.registerOutParameter(2, OracleTypes.NUMBER);
	stat.registerOutParameter(3, OracleTypes.VARCHAR);
	
	stat.executeQuery();
	
	System.out.println(stat.getString(1));
	System.out.println(stat.getInt(2));
	System.out.println(stat.getString(3));
	
	stat.close();
	conn.close();
	
} catch (Exception e) {
	e.printStackTrace();
}

 

인자값(O), 반환값(O)

CREATE OR REPLACE PROCEDURE procM5 (
	pcursor OUT sys_refcursor
)
IS 
BEGIN 
	OPEN pcursor
	FOR 
		SELECT * FROM tblAddress;
END procM5;

Connection conn = null;
CallableStatement stat = null;
ResultSet rs = null;

try {
	conn = DBUtil.open();

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

	stat = conn.prepareCall(sql);
	
	stat.registerOutParameter(1, OracleTypes.CURSOR);
	
	stat.executeQuery();
	
	rs = (ResultSet)stat.getObject(1);
	
	while (rs.next()) {
		System.out.println(rs.getString("name"));
		System.out.println(rs.getInt("age"));
		System.out.println(rs.getString("address"));
		System.out.println();
	}
	
	rs.close();
	stat.close();
	conn.close();
	
} catch (Exception e) {
	e.printStackTrace();
}

오라클의 Cursor는 결과 테이블을 탐색하는 참조 객체이다.

Cursor를 결과 테이블을 탐색하는 참조 객체로 ResultSet으로 형변환해서 사용할 수 있다.

결론적으로 오라클 커서와 ResultSet이 동일한 구조를 가지고 있다는 것을 알 수 있다.