JDBC - CallableStatement

1. 필요한 Class

- DBConnection Class

- CallableStatement 수행 Class

 

1-1. DBConnection Class

package com.kosa;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnection {

	private static Connection conn;

	private DBConnection() {
	}

	static {
		// 환경설정 파일을 읽어오기 위한 객체 생성
		Properties properties  = new Properties();
		Reader reader;
		try {
			reader = new FileReader("lib/oracle.properties");  // 읽어올 파일 지정
			properties.load(reader);                           // 설정 파일 로딩하기
		} catch (FileNotFoundException e1) {
			System.out.println("예외: 지정한 파일을 찾을수없습니다 :" + e1.getMessage());
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		String driverName = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String user = properties.getProperty("user");
		String pwd = properties.getProperty("password");

		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, user, pwd);
			System.out.println("connection success");
		}

		catch (ClassNotFoundException e) {
			System.out.println("예외: 드라이버로드 실패 :" + e.getMessage());
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("예외: connection fail :" + e.getMessage());
			e.printStackTrace();
		}
	}

	public static Connection getConnection() {
		return conn;
	}
}

1-2. CallableStatement Class

#InsertT2 : T2 테이블에 insert

package com.kosa;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

public class InsertT2 {

	public static void main(String[] args) {

		Scanner sc = new Scanner(System.in);
		
		System.out.print("사원번호 입력: ");
		long empno = sc.nextInt();	
		
		System.out.print("급여 입력: ");
		Double sal = sc.nextDouble();
		
		System.out.print("부서번호 입력: ");
		long deptno = sc.nextInt();	
		
				
		String runSP = "{ call sp_insert_t2_new_emp(?, ?, ?) }";

		try {
			Connection conn = DBConnection.getConnection();
			CallableStatement callableStatement = conn.prepareCall(runSP);
			callableStatement.setLong(1, empno);
			callableStatement.setDouble(2, sal);
			callableStatement.setLong(3, deptno);
			callableStatement.executeUpdate();	
			System.out.println("성공");
			
		} catch (SQLException e) {
			System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sc.close();
		}
	}

}

 

 

2. 1-2번 컴파일 전 SQL에 해당 Procedure 있어야함

2-1. t1, t2 테이블 생성

  drop table t2 purge;
  drop table t1 purge;

  create table t1 as select * from dept;
  create table t2 as select empno, sal, deptno from emp;

  alter table t1 add (sum_sal number);

  update t1 
  set sum_sal = (select nvl(sum(sal), 0) from t2
                 where deptno = t1.deptno);

  select * from t1;
  select * from t2;

2-2. procedure 생성

create or replace procedure sp_insert_t2_new_emp
  (p_empno  t2.empno%type,
   p_sal    t2.sal%type,
   p_deptno t2.deptno%type)
  is
    e_sal_range_check exception;
  begin
    if p_sal between 1000 and 5000 then
      insert into t2(empno, sal, deptno)
      values(p_empno, p_sal, p_deptno);

      commit;
    else
      raise e_sal_range_check;
    end if;

    -- sal 값이 부적절한 경우
    -- 수행되면 안되는 코드들 
  exception
    when  e_sal_range_check then
      dbms_output.put_line('급여가 정해진 범위 밖입니다!');  
    when others then
      dbms_output.put_line('기타 에러 발생!');  
  end;
  /

 

3. Eclipse 에서 컴파일

: 1-2번 Class 컴파일