0305 수요일에 배운 내용.
callableStatment는 무엇인가
CallableStatement는 JDBC (Java Database Connectivity)**에서 **저장 프로시저 (Stored Procedure)나 함수를 실행하기 위해 사용하는 인터페이스이다. 저장 프로시저는 데이터베이스에서 미리 정의된 SQL 명령어 집합으로, 특정 작업을 수행하는 데 사용된다. CallableStatement 인터페이스를 구현한 클래스를 객체로 만들어 사용하면 자바 애플리케이션에서 저장 프로시저를 호출하고 결과를 처리할 수 있다.
저장 프로시저는 미리 정의된 sql 블럭이다. sql 쿼리는 한개씩 실행하는 실행문이다. 저장 프로시저를 사용할땐 비즈니스 로직을 데이터베이스에 두고, 자바 어플리케이션에서 그 프로시저를 호출하는 방식으로 사용한다.
저번 포스팅때 배운 방식으로 java api docs 를 읽어보자.
CallableStatement 는 인터페이스이다.
- JDBC 는 모든 RDBMS 에서 일관되게 저장 프로시저를 호출할 수 있도록 escape syntax 를 제공한다. 데이터베이스 시스템에따라 호출 방식이 다를 수 있지만, escape syntax 를 이용하면 표준화된 방식으로 프로시저를 호출할 수 있다
JDBC 의 escape syntax 에는 두가지 형태가 있다 - 결과 매개변수 (result parameter)가 포함된 형태와 결과 매개변수가 없는 형태이다. 저장 프로시저가 값을 반환한다면 첫번째 형태를, 하지 않는다면 두번째 형태를 쓴다.
자바에서 프로시저를 실행할 때 구체적 문법
- {?= call [(?,?, ...)]}: 결과 매개변수가 있는 형태.
- {call [(,, ...)]}: 결과 매개변수가 없는 형태.
- : 호출할 저장 프로시저의 이름.
- , , ...: 프로시저에 전달되는 매개변수(인자).
SQL 쿼리와 달리 저장 프로시저는 미리 정의된 SQL 블록이다. 복잡한 비즈니스 로직을 데이터베이스 쪽에 두고, 자바 애플리케이션에서는 해당 프로시저를 호출하는 방식으로 사용한다.
저장 프로시저에 입력 파라미터(IN), 출력 파라미터(OUT), 입출력 파라미터(INOUT)를 바인딩할 수 있다. 파라미터를 '바인딩'한다는 것은 SQL 에서 실행될 변수 값을 동적으로 설정하는 것을 말한다.
더 자세히, '파라미터 바인딩'이란 SQL 쿼리문에 직접 값을 넣는게 아니라 프로그램 실행 시점에 변수에 값을 할당하는 것을 말한다.
예를들어, 다음의 sql 스크립트를 보자.
create table CodeTable(
code_id INT,
code_name VARCHAR(50)
);
select * from CodeTable;
insert into CodeTable values (1, 'programming');
-- 저장 프로시저 생성 시작.
delimiter //
create procedure InsertCodeData(
in inputName varchar(255),
in targetTable varchar(255),
out responseMessage varchar(255)
) -- 프로시저의 이름과 파라미터 선언
begin
-- set 으로 동적 SQL 문자열 만들기.
set @query = concat ('inser into', tagetTable, '(code_id, code_name)',
'select coalesce(max(code_id),0) + 1 ? from ', targetTable);
-- 입력값을 바인딩하기.
set @nameValue = inputName;
set responseMsg = "Data Inserted";
-- 동적 sql 실행하기.
prepare dynamicStmt from @query;
execute dynamicStmt using @nameValue;
deallocate prepare dynamicStmt;
-- 트랜잭션 확정하기.
commit;
end //
delimiter ;
-- sql 에서 방금 만든 저장 프로시저 실행하기.
call InsertCodeData('Engineer', 'CodeTable', @outputMsg);
-- 조회
select * from CodeTable;
동적 SQL 을 사용하면 테이블명이나 컬럼명을 동적으로 변경할 수 있다. 조건절도도 동적으로 변경할 수 있다.
Connection conn = null;
CallableStatement. cs = null;
conn = DBUtil.getconnection();
cs = conn.prepareCall("{call prc_insert(?, ?, ?)}"); // prc_insert 는 프로시저 이름
cs.setString(1, "Programmer"); // 자바에서 첫번째 파라미터 바인딩.
cs.setString(2, "Developer"); // 두번째 파라미터 바인딩.
cs.registerOutParameter(3, java.sql.Types.VARCHAR);
boolean flag = cs.execute();
if(cs!= null) cs.close();
if(conn != null) conn.close(); // 연결됐던 걸 끊어주기
<DBUtil.java>클래스는 이렇게 생겼을 것이다.
public class DBUtil {
private static Connection conn = null;
private DBUtil() {}
public static Connection getConnection(){
if(conn != null) return conn; // 기존 연결 존재한다면 그걸 쓰기.
}
// 기존 연결이 존재하지 않으면 디비 정보 이용해 연결 시도하기.
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/exampleDB?useSSL=false&serverTimezone=UTC";
String username = "root";
String password = "root";
conn = DriverManager.getConnection(url, username, password);
// 연결 성공했다면
System.out.println("Connected to the database.");
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
CallableStatement 사용 예제02
USE mydatabase;
DROP PROCEDURE IF EXISTS InsertMember;
CREATE TABLE IF NOT EXISTS Members
(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
points INT DEFAULT 0
);
delimiter //
create procedure InsertMember( -- 프로시저의 파라미터 선언.
IN p_username varchar(30),
IN p_password varchar(255),
IN p_email varchar(100),
IN p_phone varchar(20),
OUT p_result_code INT
)
BEGIN
declare user_count INT;
-- 프로시저를 호출할때 p_username 값을 받을 것임.
select count(*) into user_count from members where username = p_username;
if user_count > 0 then
set p_result_code = 101;
else
insert into Members (username, password, email, phone)
values (p_username, p_password, p_email, p_phone);
set p_result_code = 201;
end if;
commit;
end //
delimiter ;
-- 저장 프로시저 실행
set @result_code = 0;
call InsertMember('blueberry', 'pass1234', 'blueberry@gmail.com', '010-1234-5678', @result_code)
select @result_code; -- result code 보기.
SQL 스크립트 내부가 아닌 자바 어플리케이션에서 프로시저를 실행해보자.
Statement 나 PreparedStatment 는 단순한 SQL 쿼리 (select, insert, update)를 실행할때 사용된다.
CallableStatement 는 특별히 저장 프로시저를 실행하기 위해 만들어진 인터페이스이다.
저장 프로시저를 자바에서 호출할때는 CALL 프로시저명(?,?,?,?) 형식으로 호출해야 한다. prepareCall() 을 사용하면 JDBC가 프로시저를 실행할 준비를 한다.
또한, CallableStatment의 registerOutParameter()는 프로시저의 OUT 파라미터 값을 받을 수 있도록 설정할 수 있다.
import java.sql.*;
public class MemberInsertExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String user = "your_db_user";
String password = "your_db_password";
Connection conn = null;
CallableStatement stmt = null;
try {
// 데이터베이스 연결
conn = DriverManager.getConnection(url, user, password);
// 저장 프로시저 호출 준비
String sql = "{CALL InsertMember(?, ?, ?, ?, ?)}";
stmt = conn.prepareCall(sql);
// IN 파라미터 설정
stmt.setString(1, "blueberry");
stmt.setString(2, "pass1234");
stmt.setString(3, "blueberry@email.com");
stmt.setString(4, "010-1234-5678");
// OUT 파라미터 설정
// JDBCD에게 OUT파라미터가 INT 타입임을 알려주기 위해 Types.INTEGER라고 명시
stmt.registerOutParameter(5, Types.INTEGER);
// 실행
stmt.execute();
// 결과 코드 가져오기
int resultCode = stmt.getInt(5);
System.out.println("결과 코드: " + resultCode);
// 결과 코드에 따른 처리
if (resultCode == 101) {
System.out.println("이미 존재하는 사용자입니다.");
} else if (resultCode == 201) {
System.out.println("회원 가입이 완료되었습니다.");
}
} catch (SQLException e) {
e.printStackTrace();
} finally { // try 에서 성공하든, catch에서 실패하든, 무조건 자원 해제하기.
// 자원 해제
try {
if (stmt != null) stmt.close(); // 쿼리가 끝난 후 CallableStatment 닫아주기.
// 닫아주지 않으면 메모리에 남아있음.
if (conn != null) conn.close(); // 디비와 연결 닫기.
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}