--- title: "Java 정리 38" date: "2019-01-09" tags: ["Java"] status: archived verified: false --- --- ## BIND 변수 * PreparedStatement에서 값과 묶여지는 변수 ```java String sql = "SELECT colName FROM tabName WHERE colName=?"; ``` * **컬럼명과 테이블명은 바인드변수로 처리될 수 없다.** * StringBuilder **.append()로 변수의 값을 직접 할당**하여 사용. ```java StringBuilder sb = new StringBuilder(); // col1, col2는 컬럼명을 가진 변수 sb.append("SELECT ").append(col1).append(", ").append(col2).append(" FROM ..."); pstmt = con.prepareStatement(sb.toString()); ``` ```java // 컬럼명을 바인드변수로 사용했을 때 String selectEmp = "SELCT ? FROM emp WHERE empno = ?"; pstmt = con.prepareStatement(selectEmp); pstmt.setString(1, inputColumn); pstmt.setInt(2, empno); ``` ![01](/md/legacy-assets/java/38/01.webp) ```java // StringBuilder append()로 쿼리문에 직접 넣어 사용한다. selectEmp .append("SELECT ").append(inputColumn) .append(" FROM emp ").append("WHERE empno=?"); pstmt = con.prepareStatement(selectEmp.toString()); pstmt.setInt(1, empno); ``` * [BIND 변수 예제](https://github.com/younggeun0/SSangYoung/blob/master/dev/workspace/sistJavaStudy2/src/date190109/DynamicColumn.java) ## PreparedStatement를 사용하여 테이블 만들기 * DDL(Data Definition Language)을 사용하여 테이블 생성 * [CreateTable](https://github.com/younggeun0/SSangYoung/blob/master/dev/workspace/sistJavaStudy2/src/date190109/CreateTable.java) ![02](/md/legacy-assets/java/38/02.webp) * **입력값을 대문자 처리하는건 자바나 SQL 한쪽에서만 하도록 한다.** ```java // java에서 처리하는 예 selectTname.append("SELECT tname FROM tab WHERE tname=?"); pstmt = con.prepareStatement(selectTname.toString()); pstmt.setString(1, tableName.toUpperCase()); ``` ```java // sql에서 처리하는 예 selectTname.append("SELECT tname FROM tab WHERE tname=UPPER(?)"); pstmt = con.prepareStatement(selectTname.toString()); pstmt.setString(1, tableName); ``` * JOptionPane의 ConfirmDialog로 flag값을 받을 수도 있다 ```java boolean flag = false; if (rs.next()) { flag = JOptionPane.showConfirmDialog(null , "테이블이 존재합니다. 삭제한 후 생성하시겠습니까?") == JOptionPane.OK_OPTION; } ``` ## Procedure * **쿼리문이 제어구조를 가지면서 저장성있게 사용할 때 사용** * 리턴은 값이 하나만 나갈 수 있지만 **프로시저는 Out Parameter로 여러개의 값을 반환할 수 있다.** ```sql CREATE OR REPLACE PROCEDURE 프로시저명 ( -- 프로시저 밖에 있는 값을 받는 In Parameter -- IN은 생략가능 변수명 [IN] 데이터형, ... -- Out Parameter 변수명 OUT 데이터형, ... ) IS 변수선언 BEGIN 코드작성 END; / ``` * **Oracle에서 프로시저 호출 방법** * **바인드 변수 생성** * **EXEC로 직접 실행** * **바인드 변수의 값 사용** ```sql -- procedure proc_plus CREATE OR REPLACE PROCEDURE proc_plus (num1 NUMBER, num2 IN NUMBER, num3 OUT NUMBER) IS BEGIN num3 := num1 + num2; END; / ``` ```sql -- Sqlplus에서 직접사용 VAR result NUMBER -- BIND 변수 생성 EXECUTE proc_plus(3, 5, :result) -- 직접실행 PRINT result -- BIND 변수 사용 ``` ![03](/md/legacy-assets/java/38/03.webp) ## 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** 사용 ```java // 3. 프로시저 호출객체 얻기 CallableStatement cstmt = con.prepareCall("{ call 프로시저명(?,?,?) } "); ``` ```java // 4. 바인드 변수에 값 할당 // in parameter는 설정 값 넣을 때 사용 cstmt.setInt(1,2019); cstmt.setString(2, "공선의"); // out parameter는 등록하여 설정 // 값을저장할 데이터형은 데이터형에 따라 Types.NUMERIC, Types.VARCHAR 같이 사용 cstmt.registerOutParameter(3, 값을저장할데이터형); ``` ![04](/md/legacy-assets/java/38/04.webp) ```java // 5. 실행 cstmt.execute(); // 6. 등록된 Out Parameter의 값 얻기 // Out Param으로 등록했던 인덱스 그대로 사용하여 값을 가져온다. // 자바에선 일반적으로 Procedure의 parameter이름을 알 수 없기 때문에 인덱스를 사용 int result = cstmt.getInt(3); // 가져오는 값이 문자라면 cstmt.getString(); ``` ```java 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(); } } } ``` ![05](/md/legacy-assets/java/38/05.webp) ## 숙제 ![06](/md/legacy-assets/java/38/06.webp) * 테이블명만 입력하고 추가 버튼 클릭 시 아래와 같이 JTextArea에 추가 * 끝에 항상 ";" 세미콜론 찍히도록 구현 * 테이블명 test입력 후 추가 시 찍히는 쿼리 ```sql CREATE TABLE test(); ``` * 테이블 생성 후 컬럼명을 선택 후 추가 * name, VARCHAR2, 30, NOT NULL 선택했을 때 ```sql CREATE TABLE test( name VARCHAR2(30) NOT NULL ); ``` * 또 다른 컬럼 추가시 자동으로 "," 붙여서 추가 * 두번째 컬럼으로 age, NUMBER, NOT NULL 선택했을 때 ```sql CREATE TABLE test( name VARCHAR2(30) NOT NULL, age NUMBER NOT NULL ); ``` * PK 컬럼 추가시 * id, VARCHAR2, 16, PK, pk_test 추가 시 ```sql CREATE TABLE test( name VARCHAR2(30) NOT NULL, age NUMBER NOT NULL, id VARCHAR2(16) CONSTRAINT pk_test PRIMARY KEY ); ``` * 테이블 생성 버튼을 누르면 T.A에 있는 내용이 DB에서 수행됨 * 초기화 버튼을 누르면 TA 내용 초기화 [숙제 풀이 패키지](https://github.com/younggeun0/SSangYoung/tree/master/dev/workspace/sistJavaStudy2/src/date190109/hw)