Java 정리 38 | younggeun0.dev

BIND 변수

PreparedStatement에서 값과 묶여지는 변수

String sql = "SELECT colName FROM tabName WHERE colName=?";

컬럼명과 테이블명은 바인드변수로 처리될 수 없다.
- StringBuilder .append()로 변수의 값을 직접 할당하여 사용.

StringBuilder sb = new StringBuilder();
 
// col1, col2는 컬럼명을 가진 변수
sb.append("SELECT ").append(col1).append(", ").append(col2).append(" FROM ...");
 
pstmt = con.prepareStatement(sb.toString());

// 컬럼명을 바인드변수로 사용했을 때
String selectEmp = "SELCT ? FROM emp WHERE empno = ?"; 
                     
pstmt =  con.prepareStatement(selectEmp);
pstmt.setString(1, inputColumn);
pstmt.setInt(2, empno);

// StringBuilder append()로 쿼리문에 직접 넣어 사용한다.
selectEmp
.append("SELECT ").append(inputColumn)
.append(" FROM  emp ").append("WHERE empno=?");
                     
pstmt = con.prepareStatement(selectEmp.toString());
pstmt.setInt(1, empno);

BIND 변수 예제 (새 창에서 열림)

PreparedStatement를 사용하여 테이블 만들기

DDL(Data Definition Language)을 사용하여 테이블 생성
CreateTable (새 창에서 열림)

입력값을 대문자 처리하는건 자바나 SQL 한쪽에서만 하도록 한다.

// java에서 처리하는 예
selectTname.append("SELECT tname FROM tab WHERE tname=?");
 
pstmt =  con.prepareStatement(selectTname.toString());
pstmt.setString(1, tableName.toUpperCase());

// sql에서 처리하는 예
selectTname.append("SELECT tname FROM tab WHERE tname=UPPER(?)");
 
pstmt =  con.prepareStatement(selectTname.toString());
pstmt.setString(1, tableName);

JOptionPane의 ConfirmDialog로 flag값을 받을 수도 있다

boolean flag = false;
if (rs.next()) {
     flag = JOptionPane.showConfirmDialog(null
        , "테이블이 존재합니다. 삭제한 후  생성하시겠습니까?") 
           == JOptionPane.OK_OPTION;
}

Procedure

쿼리문이 제어구조를 가지면서 저장성있게 사용할 때 사용
리턴은 값이 하나만 나갈 수 있지만 프로시저는 Out Parameter로 여러개의 값을 반환할 수 있다.

CREATE OR REPLACE PROCEDURE 프로시저명 (
    -- 프로시저 밖에 있는 값을 받는 In Parameter
    -- IN은 생략가능
    변수명 [IN] 데이터형,
    ...
 
    -- Out Parameter
    변수명 OUT 데이터형,
    ...
)
IS
    변수선언
BEGIN
    코드작성
END;
/

Oracle에서 프로시저 호출 방법
- 바인드 변수 생성
- EXEC로 직접 실행
- 바인드 변수의 값 사용

-- procedure proc_plus
CREATE OR REPLACE PROCEDURE proc_plus (num1 NUMBER,
num2 IN NUMBER, num3 OUT NUMBER)
IS  
BEGIN   
    num3 := num1 + num2;
END;
/

-- Sqlplus에서 직접사용
VAR result NUMBER                 -- BIND 변수 생성
EXECUTE proc_plus(3, 5, :result)  -- 직접실행
PRINT result                      -- BIND 변수 사용

CallableStatement

Procedure를 사용하기위해 제공되는 객체
Java 코드에서 SQL문이 제거되므로 코드가 간단해진다.
- 코드와 쿼리문을 분리하여 작성할 수 있다.
사용법
- Java에선 Oracle에서처럼 Bind변수를 생성할 수 없기 때문에 Out Parameter로 등록해서 설정한다.
- 값을저장할 데이터형은 java.sql.Types의 Field를 사용
  - Java는 모든 DBMS와 연동가능
  - Java에서 제공하는 Types클래스는 특정 DBMS에만 속해지는 객체만 만들지 않고 범용적인 객체를 만들어 두었다.
    - Types 클래스엔 Oracle의 데이터형인 VARCHAR2, NUMBER 없음
    - 수를 저장할 땐 Types.NUMERIC 사용
    - 문자열을 저장할 땐 Types.VARCHAR 사용

// 3. 프로시저 호출객체 얻기
CallableStatement cstmt = con.prepareCall("{ call 프로시저명(?,?,?) } ");

// 4. 바인드 변수에 값 할당
// in parameter는 설정 값 넣을 때 사용
cstmt.setInt(1,2019);
cstmt.setString(2, "공선의");
 
// out parameter는 등록하여 설정
// 값을저장할 데이터형은 데이터형에 따라 Types.NUMERIC, Types.VARCHAR 같이 사용
cstmt.registerOutParameter(3, 값을저장할데이터형);

// 5. 실행
cstmt.execute();
 
// 6. 등록된 Out Parameter의 값 얻기
// Out Param으로 등록했던 인덱스 그대로 사용하여 값을 가져온다.
// 자바에선 일반적으로 Procedure의 parameter이름을 알 수 없기 때문에 인덱스를 사용
int result = cstmt.getInt(3); // 가져오는 값이 문자라면 cstmt.getString();

public class UseCallableStatement {
     public UseCallableStatement() throws SQLException {
          
          Connection con = null;
          CallableStatement cstmt = null;
          
          String tempData =  JOptionPane.showInputDialog("숫자 2개 입력\n예)  숫자,숫자");
          String[] numbers = tempData.split(",");
          
          if (numbers.length != 2) {
              JOptionPane.showMessageDialog(null, "두  개의 수만 입력해주세요.");
              return;
          }
          
          int num1 = 0, num2 = 0;
          
          try {
              num1 = Integer.parseInt(numbers[0]);
              num2 = Integer.parseInt(numbers[1]);
          } catch (NumberFormatException nfe) {
              JOptionPane.showMessageDialog(null,  "숫자를 입력해주세요.");
          }
          
          try {
              String url =  "jdbc:oracle:thin:@localhost:1521:orcl";
              String id = "scott";
              String pass = "tiger";
              
              // 2.
              con =  GetConnection.getInstance().getConn(url, id, pass);
              
              // 3.
              cstmt = con.prepareCall("{ call  proc_plus(?,?,?) }");
              
              // 4. 바인드 변수에 값 설정
              // in parameter
              cstmt.setInt(1, num1);
              cstmt.setInt(2, num2);
              
              // out parameter
              cstmt.registerOutParameter(3,  Types.NUMERIC);
              
              // 5. 쿼리(Procedure) 실행 후 결과 얻기
              // 프로시저 실행하면 in parameter와 out  parameter에 값 할당
              cstmt.execute();
              
              int total = cstmt.getInt(3);
              
              JOptionPane.showMessageDialog(null, num1+"  + "+num2+" = "+total);
              
          } finally {
              if (cstmt != null) { cstmt.close(); }
              if (con != null) { con.close(); }
          }
     }
     
     public static void main(String[] args) {
          
          try {
              new UseCallableStatement();
          } catch (SQLException se) {
              se.printStackTrace();
          }
     }
}

숙제

테이블명만 입력하고 추가 버튼 클릭 시 아래와 같이 JTextArea에 추가
- 끝에 항상 ";" 세미콜론 찍히도록 구현
- 테이블명 test입력 후 추가 시 찍히는 쿼리

CREATE TABLE test();

테이블 생성 후 컬럼명을 선택 후 추가
- name, VARCHAR2, 30, NOT NULL 선택했을 때

CREATE TABLE test(
    name VARCHAR2(30) NOT NULL
);

또 다른 컬럼 추가시 자동으로 "," 붙여서 추가
- 두번째 컬럼으로 age, NUMBER, NOT NULL 선택했을 때

CREATE TABLE test(
    name VARCHAR2(30) NOT NULL,
    age NUMBER NOT NULL
);

PK 컬럼 추가시
- id, VARCHAR2, 16, PK, pk_test 추가 시

CREATE TABLE test(
    name VARCHAR2(30) NOT NULL,
    age NUMBER NOT NULL,
    id VARCHAR2(16) CONSTRAINT pk_test PRIMARY KEY
);

테이블 생성 버튼을 누르면 T.A에 있는 내용이 DB에서 수행됨
초기화 버튼을 누르면 TA 내용 초기화

숙제 풀이 패키지 (새 창에서 열림)

BIND 변수

PreparedStatement를 사용하여 테이블 만들기

Procedure

CallableStatement

숙제

댓글