백엔드 개발자의 클라우드 다이브

 Procedure 호출 

- Procedure : 쿼리문을 저장하고 필요한 곳에서 실행해야 할 때 사용하는 DBMS 객체

- DBMS에서 언어적인 요소(제어, 연산)를 가져야 할 때

- Oracle DBMS에서는 user_procedures 딕셔너리에서 생성된 Procedure를 확인할 수 있음

- CallableStatement 객체로 Procedure를 호출할 수 있음

- 자바에서 호출 시, SQL의 프로시저가 컴파일 되어있어야 함

* Oracle의 Procedure *

- PL/SQL : DBMS에서 언어적인 요소를 구현할 때 사용

- 기본문법(데이터형, 변수, 연산자, 제어문), cursor, function, procedure, trigger, package를 지원

- 컴파일을 한 후 실행함

    > 컴파일 : @파일명.sql

    > 함수 : 간접실행 (쿼리문에 함수를 포함시켜 실행하는 방법)

                   직접실행 (실행기를 사용하여 실행하는 방법 -> execute 또는 exec 사용)

- 작성법) 

    create or replace procedure 프로시저명 (매개변수,,,) 

    is  -- 변수 선언, record 선언(java의 VO같은 거), table 선언(java의 배열 같은 거), cursor 선언

    begin -- 코드 작성(연산, 제어, 쿼리작성 등등)

    end;

    /

- 선언 시 주의)

   > 매개변수 종류 : in parameter (프로시저 외부의 값을 프로시저 내부로 전달하는 일)  //  in은 생략하고 선언 가능

                                   our parameter (프로시저 안에서 처리한 값을 외부로 전달하는 일)

   > 매개변수명은 테이블의 컬럼명과 동일하면 절대 안됨! 식별될 수 있도록 선언 

        ex) sal로 쓰면 sal=sal 항상 참이 될 수 있으므로 in paramter면 in_sal로 선언

- 컴파일) @파일명.sql   -- DOS에서 sqlplus에서 로그인하고 하면 됨

- 실행)

   1) bind 변수 선언 (프로시저 내부의 처리된 값을 SQLPlus에서 저장할 변수)

        > 프로시저 내부의 처리된 값은 out parameter를 타고 나옴

        >  bind 변수는 일시적이라 실행될 때 마다 선언해주어야 함

             var | variable 데이터형 변수명 (크기) -- 크기는 넣어도 되고 안 넣어도 됨

    2) 프로시저를 실행 (직접실행)

        execute | exec 프로시저명 (값,,, : 바인드변수명 ,,,) 

        > 값 : in parameter로 들어가는 값 

        > : 바인드면수명 : out parameter로 나오는 값

    3) bind 변수에 저장된 값 출력 (procedure가 처리한 결과값)

        > print 바인드변수명 바인드변수명 바인드변수명 -- 띄어쓰기로 여러 개의 값 줄 수 있음

-프로시저 삭제) drop procedure 프로시저명;

 Java에서의 Procedure 호출 

* CallabeStatement

- procedure를 호출할 때 사용하는 객체

- PreparedStatement Interface의 하위 Interface

- PreparedStatement bind 변수를 사용하여 값을 입력할 수 있음

- SQLPlus의 bind 변수는 CallableStatement의 registerOutparameter로 처리

   > SQLPlus의 bind 변수 : var 변수명 데이터형(크기), procedure에서는 :변수명 으로 사용

- 사용법)

1) 쿼리문 생성객체 얻기

    CallabeStatement cstmt = con.prepareCall(프로시저명);

    // 프로시저명에는 이렇게 !  "{ call 프로시저명(?,?,…) }"

2) CallableStatement 바인드 변수에 값 설정

 ① in parameter에 대응되는 bind 변수 (프로시저에 값 할당)

      - cstmt.setXXX (인덱스, 값);

         number -> cstmt.setInt (인덱스, 값), cstmt.setDouble (인덱스, 값);

         varchar2, char -> cstmt.setString(인덱스, 값);

 ② out parameter에 대응되는 bind 변수 (SQLPlus의 bind 변수를 자바 형식으로 선언하는 것)

      - cstmt.registerOutParameter (인덱스, java.sql.Types.상수) ;

      - Oracle에서는 var 변수명 데이터형(크기) 이렇게 했던 걸 위처럼 처리

    * java.sql.Types 클래스 

       - DBMS의 bind변수를 정의할 때 사용하는 데이터형을 java에서 사용할 수 있도록 구현한 클래스 

       - 특정 DBMS에서만 제공하는 데이터형을 지원하지 않음

            > Oracle의 NUMBER 대신 Types.NUMERIC 사용 

            > Oracle의 VARCHAR2 -> Types.VARCHAR

3) 프로시저 실행 

 - CallableStatement는 실행 method가 없으므로, 부모 Interface의 실행 method를 사용함

     > executeQuery(), executeUpdate(), execute() 셋 중에서 실행했다는 의미로 excute()를 사용!

        (어차피 셋다 반환형(rs,int,boolean)이 제대로 나오지 않음)

 * 프로시저를 실행하면 바인드변수(registerOutParameter)에 프로시저가 실행된 결과가 저장됨

4) 실행결과가 저장된 값 받기

- ResultSet 안 씀.

- CallableStatement의 method 사용 : cstmt.getXXX (바인드변수의 인덱스)

 예) registerOutParameter(Types.NUMERIC) 일 때 : cstmt.getInt(인덱스); cstmt.getDouble(인덱스)

        registerOutParameter(Types.VARCHAR) 일 때 : cstmt.getString(인덱스);

# 오늘의 코딩 #

- Java에서 Oracle에 생성한 procedure를 사용해보자 

# 생성한 프로시저

- in parameter로 name을 받고, out parameter로 msg를 내보냄!

# Java에서 호출

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import javax.swing.JOptionPane;

import test.dao.DbConnection;

/**
 * 이름(varchar2)을 입력하여 (in parameter) 설정된 msg(varcahr2)를 받는 일 (out parameter)
 * @author user
 */
 
public class UseCallableStatement {

	public UseCallableStatement() throws SQLException {

		Connection con = null;
		CallableStatement cstmt = null;

		DbConnection db = DbConnection.getInstance(); // 중복된 일이라 method 만들어 놓음
		
		try {
			// 1.드라이버로딩& 2.커넥션얻기
			con = db.getConn();
			// 3.쿼리문 생성객체 얻기 (프로시저명은 proc_hello)
			cstmt = con.prepareCall("{call proc_hello (?,?)}");
			// 4.바인드 변수 설정
			// in parameter
			cstmt.setString(1, JOptionPane.showInputDialog("이름 입력"));
			// out parameter
			cstmt.registerOutParameter(2, Types.VARCHAR);
			// 5.쿼리수행 후 결과 얻기
			cstmt.execute();
			//프로시저가 실행된 결과는 Out Parameter에 저장됨
			String msg = cstmt.getString(2);
			System.out.println(msg);
			
		} finally {
			// 6.연결 끊기 : CallableStatement는 PreparedStatement의 자식이므로, 종료 method를 부모로 사용 가능
			db.closeDB(null, cstmt, con);
		} // end finally

	}// UseCallableStatement

	public static void main(String[] args) {

		try {
			new UseCallableStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		} // end catch
	}// main

}// class

- name은 다이얼로그를 띄워서 입력받음 (동동)

# 출력 결과 #

-> 완료!! ㅎㅎ 

 1. ResultSetMetaData 

- data dictionary를 사용하지 않고, 실행되는 select 쿼리에 해당하는 컬럼정보를 얻을 때 사용하는 ineterface

- 조회된 inline view에 한하여 컬럼정보 조회 가능

- DBMS가 달라도 정보를 얻을 수 있음

   > Oracle의 user_cons_columns ,,, 등과 같은 다양한 data dictionary는 oracle DB에서만 존재/사용 가능

- ResultSet을 기반으로 생성되며, ResultSet은 연결 끊어야 하나 ResultSetMetadata는 연결 안 끊어도 됨

- 사용법) 

> ResultSet에서 ResultSetMetaData를 얻기 : ResultSetMetaData rsmd = getMetaData();

> 정보 얻기

• 컬럼의 개수 : rsmd.getColumnCount()
• 컬럼명 : rsmd.getColumnName(컬럼 인덱스) or getColumnLabel (oracle의 컬럼 index는 1부터 시작)
• 컬럼 데이터형명 : rsmd.getColumnTypeName(컬럼의 인덱스)
• 컬럼 데이터형크기 : rsmd.getPrecision(컬럼의 인덱스)
• null 허용 : rsmd.isnullable(컬럼의 인덱스) // not null이면 0 null 허용이면 1

 2. Transaction 처리 

- 데이터베이스의 작업 단위 (insert, update, delete)

- 작업이 완료될 때 commit, 작업을 취소할 때 rollback 사용 

- java에서는 Connection Interface가 Transaction을 처리

    > Transaction 대상쿼리문 하나 당 작업완료 (auto commit)

- auto commit 문제 : 

    > Transaction 대상 쿼리문 하나로 작업이 완료되면 auto commit은 문제를 발생시키지 않지만,

       여러 개의 쿼리문이 하나의 Transaction을 구성하면 문제를 발생시킬 수 있음

    > 여러 개의 작업이 모두 성공했을 때에만 DB작업이 완료되어야 하고,

       여러 개의 작업 중 하나라도 실패하면 모든 DB작업이 취소되어야 함 

    > 여러 쿼리가 있을 때 Transaction으로 관리하자

- 작업방법)

1) Connection의 auto commit을 해제

     con.setAutoCommit(false) ;  // 개발자가 transaction 완료 후 commit 또는 rollback 해야 함

    *주의 : method 안에서 Connection을 종료하면 commit이 된 후 Connection이 종료되므로,

                 연결 끊기는 DB작업 method 밖에서 끊음 

     (Connection을 DB작업 method 외부에서 선언 <- method가 2개로 분리됨)

2) 쿼리문 작업 외부에 Connection 선언 

3) DB작업 method가 쿼리 수행 행수를 반환

4) DB작업 method가 개발자가 목표로 한 레코드 수를 반환했을 때에만 commit 또는 rollback을 수행하고 연결을 끊음

- rollback 처리시 차이점:

1) update/delete의 경우 : 쿼리문 수행 실패 시 0건 수행이므로 else 부분에서 rollback 처리

2) insert의 경우 : 쿼리문 수행 실패 시 SQLException 발생하므로 catch 부분에서 rollback 처리

# 오늘의 코딩 #

- Transaction을 처리해보자

- 여러 쿼리문을 실행하고 모두 수행되었을 경우에만 commit!

# DB 구성은 아래와 같다!

- TRANSACTION2 테이블의 주소컬럼의 크기를 작게 설정하여 INSERT 되지 않을 경우/될 경우를 TEST 예정

# Transaction Test할 클래스

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import test.dao.DbConnection;

/**
 * 쿼리문 여러개로 하나의 Transaction이 구성되는 경우의 처리
 * @author user
 */
 
public class TestTransaction {

	/**
	 * transaction을 처리할 쿼리문을 실행하고, 결과를 받아와서 transaction을 완료하는 method
	 */
	@SuppressWarnings("resource")
	public void transaction() {
		Connection con = null;
		try {
			// 2. 커넥션 얻기
			con = DbConnection.getInstance().getConn();
			// autocommit 해제
			con.setAutoCommit(false);
			// 3. 쿼리문 수행 후 결과 얻기
			int cnt = sqlJob(con); // 여러개의 쿼리문이 하나의 transaction을 구성
			// 쿼리문이 실행된 후 목표로 한 행수가 나오면 commit 아니면 rollback
			if (cnt == 2) {
				con.commit(); }// 트랜잭션 완료
		} catch (SQLException e) {
			try {
				 // insert로 트랜잭션이 구성되는 경우 catch 에서 rollback을 처리함
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}//end catch
			e.printStackTrace();
		} finally {
			try {
				if (con != null) {
					con.close();
				} // end if
			} catch (SQLException e) {
				e.printStackTrace();
			} // end catch
		} // end finally
	}// transaction

	/**
	 * Connection을 받아, DB작업을 수행하는 method<br>
	 * transaction1 테이블과 transaction2 테이블에 이름과 주소를 입력하여 추가된 행수를 반환하는 일
	 * 
	 * @param con
	 * @return 쿼리문을 수행한 결과 횟수 (총합)
	 * @throws SQLException
	 */
	public int sqlJob(Connection con) throws SQLException {
		int allCnt = 0;  // 쿼리문 수행 성공 횟수를 담을 변수

		String name = "김동동";
		String addr = "서울시 동작구";

		// 쿼리문 수행객체 얻기
		// transaction1 테이블에 작업
		String insert1 = "insert into transaction1(name,addr) values (?,?)";
		PreparedStatement pstmt = con.prepareStatement(insert1);
		pstmt.setString(1, name);
		pstmt.setString(2, addr);

		int cnt1 = pstmt.executeUpdate();

		// transaction2 테이블에 작업
		String insert2 = "insert into transaction2(name,addr) values (?,?)";
		PreparedStatement pstmt2 = con.prepareStatement(insert2);
		pstmt2.setString(1, name);
		pstmt2.setString(2, addr);

		int cnt2 = pstmt2.executeUpdate();

		allCnt = cnt1 + cnt2;

		return allCnt;
	}// sqlJob

	public static void main(String[] args) {
		TestTransaction tt = new TestTransaction();
		tt.transaction();
	}// main

}// class

# 출력 결과 #

1) INSERT 정상 처리 (이름 : 김동동 / 주소 : 서울)

- 두 테이블 모두 정상 적으로 INSERT 처리되어 커밋 됨!

2) 오류 발생 시 에러 메시지(이름 : 김동동 / 주소 : 서울시 동작구)

- 모든 트랜잭션이 처리되지 않으면 커밋되지 않고 에러 발생!

 DAO 

- 실질적으로 DB에 접근하는 객체

- DB를 사용해 데이터를 조회하거나 조작하는 기능을 전담하도록 만든 오브젝트 (보통 singleton 패턴으로 만듦)

# 오늘의 코딩 #

- 이번엔 SQL Injection이 발생하지 않는 PreparedStatement 활용해보자

- Singleton으로 만들어서 하나의 객체만 생성되고 그 객체가 DB와 연동, Login을 수행해보자

# Login VO 클래스

- Login 사용 클래스에서 설정한 ID와 PW를 저장하고 반환해줄 클래스

public class LoginVO {

	private String id, pass;

	public LoginVO() {
		super();
	}

	public LoginVO(String id, String pass) {
		this.id = id;
		this.pass = pass;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	@Override
	public String toString() {
		return "LoginVO [id=" + id + ", pass=" + pass + "]";
	}
	
}

# Login DAO 클래스

- DB와 연결되어 일을 할 클래스

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import test.dao.DbConnection; 

public class LoginDAO {
	
	public String usePreparedStatement(LoginVO loginVO) throws SQLException {
		String name =""; 
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		DbConnection db = DbConnection.getInstance();
		try {
		//1.드라이버로딩 & 2.Connection얻기 (중복된 일이라 따로 method 만들어놨음)
			con = db.getConn();
		//3.쿼리문 생성 객체 얻기
			StringBuilder selectName = new StringBuilder();
			selectName
			.append("	select	name ")
			.append("	from 	test_login	")
			.append("	where id = ? and pass = ? ");  // ? : 바인드 변수!!
			
			pstmt = con.prepareStatement(selectName.toString());
		//4.바인드 변수에 값 설정 (VO Class에서 가져오기)
			pstmt.setString(1, loginVO.getId());
			pstmt.setString(2, loginVO.getPass());
		//5.쿼리문 수행 후 결과 얻기
			rs = pstmt.executeQuery();
			
			if(rs.next()) {//입력된 id와 p/w에 일치하는 레코드가 존재
				name = rs.getString("name"); // 조회된 이름 가져와서 변수에 할당
			}//end if
			
		}finally{
		//6.연결 끊기 (얘도 method 만들어놨음)
			db.closeDB(rs, pstmt, con);
		}//end finally
		
		return name;
	}//usePreparedStatement
	
}//class

# Login을 수행할 클래스

import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.sql.SQLException;

import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import javax.swing.border.TitledBorder;

@SuppressWarnings("serial")
public class TestLogin extends JFrame implements ActionListener {

	private JTextField jtfId;
	private JPasswordField jpfPass;
	private JLabel jlblOutput;
	
	public TestLogin() { 
		super("로그인");
		jtfId=new JTextField();
		jpfPass=new JPasswordField();
		jlblOutput=new JLabel("출력");
		
		jtfId.setBorder(new TitledBorder("아이디"));
		jpfPass.setBorder(new TitledBorder("비밀번호"));
		jlblOutput.setBorder(new TitledBorder("결과"));
		
		jpfPass.addActionListener(this);
		
		setLayout(new GridLayout(3,1));
		
		add(jtfId);
		add(jpfPass);
		add(jlblOutput);
	
		// 종료
		addWindowListener(new WindowAdapter() {

			@Override
			public void windowClosing(WindowEvent e) {
				dispose();
			}//windowClosing

			@Override
			public void windowClosed(WindowEvent e) {
				System.exit(JFrame.ABORT);
			}//windowClosed
		});
		setBounds(100, 100, 250, 300);
		setVisible(true);
	}//TestLogin
	
	public void usePreparedStatement() {
		LoginDAO ld = new LoginDAO();
		
		//아이디와 비밀번호를 받자
		LoginVO lv = new LoginVO(jtfId.getText(), new String(jpfPass.getPassword()));
		
		try {
			String name = ld.usePreparedStatement(lv);
			
			if("".equals(name)) {//이름이 조회되지 않음
				JOptionPane.showMessageDialog(this, "아이디나 비밀번호를 확인해주세요.");
				return;
			}//end if
			
			jlblOutput.setText(name + "님 반갑습니다.");
			
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(this, "죄송합니다.");
			e.printStackTrace();
		}//end catch
		
	}//usePreparedStatement
	
	@Override
	public void actionPerformed(ActionEvent e) {
		usePreparedStatement(); // SQLInjection 공격불가
	}//actionPerformed

	public static void main(String[] args) {
		new TestLogin();
	}//main

}//class

# 출력 결과 #

(기존 DB 데이터의 ID와 비밀번호는 각각 WOO/4321)

> 로그인 성공시 화면

> 로그인 실패시 화면

 Singleton Pattern 

- JVM에서 하나의 인스턴스를 생성하고 사용하는 개발 패턴 (Design pattern)

- 메모리를 절감할 수 있음

- 참조하는 속도가 빠름

- Framework에서 주로 객체를 생성하고 사용하는 방식

- 아래 작성법이 class안에 들어가있으면 singleton 패턴인 클래스 라고 함

- Ex) Calendar Class의 getInstance()

- 작성법)

# 예시 #

- 싱글톤 패턴의 클래스

/**
 * 싱글톤 패턴으로 만드는 클래스 : 객체가 하나로만 사용되는 클래스
 * @author user
 */
public class Singleton {

	private static Singleton single;
	private Singleton() { // 접근지정자 private으로 클래스 외부에서 객체화 불가
		
	}// Singleton

	/**
	 * 생성된 객체를 얻기 위한 method
	 * @return
	 */
	public static Singleton getInstance() {
		// 객체를 하나로 생성 관리할 수 있는 코드
		if (single == null) {
			single = new Singleton();
		}//end if
		
		return single;
	}// getInstance

}// class

- 싱글톤 클래스 사용

public class UseSingleton {

	public static void main(String[] args) {
		//생성자가 private이기 때문에 클래스 외부에서 객체화 될 수 없음
//		Singleton single = new Singleton(); // 생성자가 보이지 않아서 생성 불가! getInstance 사용해야함
		
		//getInstance method를 통해서만 객체를얻음
		Singleton single = Singleton.getInstance();
		Singleton single2 = Singleton.getInstance();
		Singleton single3 = Singleton.getInstance();
		
		//몇개를 생성하든 객체는 하나이므로 주소는 똑같당
		System.out.println(single);
		System.out.println(single2);
		System.out.println(single3);
		
		//일반 class는 객체 생성 횟수만큼 heap 주소가 할당됨!
		UseStatement us = new UseStatement();
		UseStatement us2 = new UseStatement();
		UseStatement us3 = new UseStatement();
		
		System.out.println(us);
		System.out.println(us2);
		System.out.println(us3);
		
	}//main

}//class

# 출력 결과 #

> 객체를 하나만 생성해서 사용할 때 !! 

DB 연동 작업 시 한 객체만 DB에 접근/조작할 수 있도록 할 수 있음

 1. SQLException 

- Connection Class에서 쿼리문 객체를 생성하면 SQLException 발생

- 클래스에서 쿼리문 실행 method 작성 시 연결을 반드시 끊기 위해 try~finally로 작성

     Ex)  Connection con = null; Statement stmt = null;

             try { connection 연결 ~ 쿼리문 생성 객체 얻기~ 쿼리문 수행 후 결과얻기~ }

             finally { conncetion 및 쿼리문 객체 연결 끊기}

-  SQLException은 getErrorCode() 존재 : DB에서 발생한 error code를 확인할 수 있음

     > 반환형 int  

        1 : PK 제약

        1438 : 정수 컬럼의 설정된 길이보다 큰 값이 입력된 경우

        12899 : 문자열 컬럼의 설정된 길이보다 큰 값이 입력된 경우

 2.SQLInjection 

- Web에서 DBMS의 정보를 탈취하기 위해 가장 많이 발생하는 공격

- 프로그램 외부에서 쿼리문을 예상하여, 나머지 쿼리문을 작성하여 입력하는 방법

- 방어 방법) PreparedStatement를 사용 / injection block code 작성

- Statement 사용시 발생 / PreparedStatement 사용하면 발생 X

   Ex) login창에서 id와 password를 입력받아서 인증을 진행하는 경우

   > Statement 사용시, JTextArea로 사용자로부터 값을 입력받을 때 ' or 1=1 -- 로그인 창에서 id에 id대신 이것을 입력하면

       항상 참이 되어 첫번째 사람 정보로 로그인 됨

   > SQLInjection 발생

   > 따라서, Injection 방어 코드를 아래와 같이 만들어서 쿼리 실행문에 적용해야 함

- PreparedStatement를 사용하면 방어 코드 필요 없음!!

# 방어코드 작성 예시 #

	/**
	 * SQLInjection 방어하기<br>
	 * 입려값에 공백, 주석, ', 쿼리문이 입력되면 해당 데이터를 삭제함
	 * @return
	 */
	public String blockInjection(String value) {
		//' or 1=1 --  : 로그인 창에서 id에 id대신 이것을 입력하면 항상 참이 되어 첫번째 사람 정보로 로그인 됨 
		//>> SQLInjection 발생!
		return value.replaceAll(" ", "").replaceAll("--", "").replaceAll("'", ""); 
        // --대신 -해도 됨
		
	}//blockInjection

# 방어코드 적용 예시 #

 // 쿼리문 생성 객체 작성시
			StringBuilder selectName = new StringBuilder();
			selectName
			.append("	select	name ")
			.append("	from 	test_login	")
			.append("	where	id='").append(blockInjection(loginVO.getId())).append("' and pass='")
			.append(loginVO.getPass()).append("'");

이렇게 적용하면 Statement 도 SQL Injection 방어 가능 !_!

 ResultSet 

- select의 조회결과를 자바에서 얻기 위해 cursor의 제어권을 저장한 객체

- 자바에서는 ResultSet을 써서 cursor가 위치한 행(record)의 컬럼값을 얻음 

-  !! 하는 일!!

1) cursor를 이동시키는 일

 * cursor : 조회 결과를 얻기 위해 움직이는 pointer

      > DB에서 테이블이 조회되면 inline view가 생성되는데, inline view 앞에 cursor가 존재함

      >  cursor 이용하는 경우에는 previous() 쓰지 말자. 부하가 엄청 크다. 쿼리로 해결하자

  - 커서가 있는 위치에서 다음 레코드가 존재하는지? : rs.next()

     > 커서가 있는 위치에서 다음 레코드가 존재하면 true가 반환되고 커서의 위치가 아래로 이동함

     > 무조건 처음에 next()를 먼저 수행해야 커서가 컬럼의 값이 존재하는 레코드의 첫 행으로 내려갈 수 있음!

2) Oracle의 데이터형을 Java에 저장하기 위해 형변환을 수행

  - 오라클의 데이터형을 자바로 저장하기 위해 자바의 데이터형으로 변환하여 얻는 일

   -  get method의 () 안에는 컬럼명 or 인덱스를 넣어서 값을 가져온다 

       > 인덱스는 1번부터 시작! cursor가 0번

      > 인덱스를 사용하면 가독성이 떨어지므로 되도록 컬럼명을 사용하여 코딩하자

   - Java API에서 ResultSet의 method 참고하여 골라 사용하자

# 오늘의 코딩 #

- Statement를 사용해보자!

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.sun.net.httpserver.Authenticator.Result;

/**
 * Statement 사용한 DBMS 연동
 * @author user
 */
public class UseStatement {

	/**
	 * CP_DEPT 테이블의 모든 레코드 조회
	 * @throws SQLException
	 */
	public void selectAllCpDept() throws SQLException {
		
		//1. 드라이버 로딩
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}//end catch
		
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String id = "scott";
		String pass = "tiger";
		
		Connection con = null; // DB연동 유지, transaction, 쿼리문 생성객체 얻기
		Statement stmt = null; // 쿼리문 실행
		ResultSet rs = null ; // 조회(Cursor의 제어권), 데이터형 변환
				
		try {
		//2. Connection 얻기
			con = DriverManager.getConnection(url, id, pass);
		//3. 쿼리문 생성객체 얻기
			stmt=con.createStatement();
		//4. 쿼리문 수행 후 결과 얻기
			String selectCpDept = "select deptno,dname,loc from cp_dept";
			rs = stmt.executeQuery(selectCpDept); 
            // DB조회 결과로 인라인뷰가 만들어지고 커서 제어권 가져옴
			
			int deptno = 0;
			String dname = "";
			String loc = "";
			// 조회결과에서 커서의 첫 위치는 컬럼명이 있는 곳이므로 무조건 한번은 내려야 값이있는 행에 위치할 수 있음
			while(rs.next()) { // 포인터가 존재하는 위치 아래에 레코드가 존재하는지?
				//컬럼명으로 얻기 => 가독성이 높음
				deptno = rs.getInt("deptno");
				dname = rs.getString("dname");
		    	loc = rs.getString("loc");
		
				System.out.println(deptno + " / " + dname + " / " + loc);
			}//end while
			
		} finally {
		//5. 연결 끊기
			if(rs != null) {rs.close();}
			if(stmt != null) {stmt.close();}
			if(con != null) {con.close();}
		}//end finally
		
	}//selectAllCpDept
	
	public static void main(String[] args) {
		UseStatement us = new UseStatement();

		try {
			us.selectAllCpDept();
		} catch (SQLException e) {
			e.printStackTrace();
		}//end catch
		
	}// main

}// class

# 출력 결과 #

무사히 출력완료!

 JDBC 작업 순서 

*하기 순서에 입각하여 코딩해야 함*

*()은 항목 당 사용하는 Class/Interface명

1) 드라이버로딩 (Class)

2) Connection 얻기 (Connection, DriverManager)

3) 쿼리문 생성객체 얻기 (Statement, PreparedStatement, CallableStatement)

4) 쿼리문 수행 후 결과 얻기 (Select인 경우 ResultSet)

5) 연결 끊기

** 상세 과정 ** 

1. 드라이버 로딩

- DB Vendor사에서 제공  -> Oracle에서는 ojdbc8.jar

- new를 사용하지 않고 클래스를 JVM에 instance로 생성할 수 있는 클래스

- Class라는 Class (java.lang package)

- ClassNotFoundException 예외가 발생

- 문법) Class.forName(“드라이버클래스”); 

             Class.forName(“oracle.jdbc.OracleDriver”) // 클래스파일은 대소문자 구분 필수!!

2. Connection 얻기

- Connection Interface는 autocommit이 기본 설정이므로, java는 쿼리 실행 후 자동 commit이 됨

- 로딩된 드라이버를 관리하는 클래스 : java.sql package의 DriverManager Class

- 로딩된 드라이버를 사용하여 Connection URL(DB URL), id, password를 입력하고 DB와 연결된 connection을 얻음

- SQLException 예외 발생

- 문법) String url = “jdbc:oracle:thin:@DBServer위치:DB port:SID”; // "jdbc:oracle:thin:@localhost:1521:orcl";

             String id = “아이디”; // scott

             String pass = “비밀번호”; // tiger

            Connection con = DriverManager.getConnection(url, id, pass);

① url

  - 연결할 DB의 포트 번호와 DB명 넣어야 함

  - @은 ip주소 표시하는 것, localhost(127.0.01.)는 loopback

  - oracle은 1521가 기본 포트고, SID는 컴퓨터마다 유일한 하나의 값을 가짐!

 ② ip/pw

  - 설정된 url에 id와 pw를 가지고 DB와 연동을 기다림

③ getConnection

  - DB는 port를 열고 접속을 기다림 (오라클 port 번호 : 1521 / name : orcl)

      > DB가 접속을 허가하면 인증 정보를 getConnection이 받는다

3. 쿼리문 생성객체 얻기

- 쿼리문 생성 객체 종류 : statement, preparedstatement, callablestatement

     > Statement, PreparedStatement  : 쿼리 실행 

     > CallableStatement : procedure 호출

- java.sql.Connection Interface로부터 얻음

• Statement 
  - 얻기 : Statement stmt = con.createStatement();  
  - 객체는 쿼리문을 실행할 때마다 반복적으로 계속 생성하여 실행하는 객체 
       >  쿼리문 실행 : excuteXXX(String sql)
  - Statement 객체는 실행할 쿼리문을 알지 못함
  - 쿼리문이 반복적으로 실행될 때 효율이 떨어짐
  - 쿼리문에 값을 넣어 생성하기 때문에 쿼리문의 복잡도가 높음
  - PreparedStatement / CallableStatement의 부모
  - SQLInjection 발생 : injecton block code 작성 필요
• PreparedStatement
  - 얻기 : PreparedStatement pstmt = con.prepareStatement(String sql); 
       >  쿼리문 실행 : excute()
  - 객체는 쿼리문을 한 번만 생성하고 값만 넣어서 실행하는 객체
  - PreparedStatement 객체는 실행할 쿼리문을 알고 있음
  - 쿼리문에 bind변수를 설정하고 값을 나중에 입력함
        >  bind 변수 : 쿼리문 안에 ? 로 정의하는 변수,  추후에 값이 반드시 들어가야 함, PreparedStatment에서 많이 사용됨
  - 쿼리문을 한 번만 생성하고 값을 변경하여 실행하기 때문에 쿼리문이 반복적으로 실행되는 환경에서 효율이 좋음
       > Java는 대용량 서비스를 많이 제공하기 때문에 반복 쿼리가 많은 편이라 PreparedStatement가 자주 사용됨!
  - 쿼리문과 입력되는 값을 따로 작성하기 때문에 쿼리문의 복잡도가 낮아짐
  - Statement Interface의 하위 Interface이나, 부모의 method는 잘 쓰지 않음
  - SQLInjection 발생하지 않음 : injecton block code 불필요
• CallableStatement
  - 얻기 : CallableStatement cstmt = con.prepareCall(String sql);
                > 여기서의 sql은 호출할 procedure명
  - procedure를 호출하는 일 (직접 실행 - 쿼리문 없이 실행하는 것) // 쿼리는 간접 실행
  - PreparedStatement Interface의 하위 Interface
  - bind 변수로 입력하는 값을 할당할 수 있음
  - registerOutParameter() method를 사용하여 procedure의 OutParameter를 처리해야 함
  (OutParameter  : java method의 반환형같은 것! OurParameter 여러 개 설정 가능)

4. 쿼리문 수행 후 결과 얻기

- 쿼리를 작성하여 실행

- 쿼리문 분류에 따라 사용하는 실행 method 가 다르므로 주의

   (어떤 excute를 써도 실행은 되나, 올바른 결과가 아님)

• DB를 변경하는 쿼리문
  create, drop, truncate, grant, revoke 
    - 처리 됐는지 안됐는지로 판단하므로, 반환형이 boolean인 Statement Interface의 execute() 사용
    -  stmt.excute()
  insert, update, delete
    - 몇 행이 추가/변경/삭제 되었는가가 중요하므로, 반환형이 int인 Statement Interface의 excuteUpdate() 사용
    - 단, insert는 subquery가 아니라면 성공 아님 예외라서 return값을 안 받아도 됨
            update와 delete : 0건 적용 (조건 잘못 넣었을때) or 적용완료 or 실패임
    - stmt.excuteUpdate() 
  commit, rollback
    - commit과 rollback은 Connection Interface에 method가 따로 존재하므로 그걸 씀
       > commit(), rollback()
• DB를 변경하지 않는 쿼리문
  select
    - 결과가 조회되어야 함! 반환형이 ResultSet인 Statment Interface의 excuteQuery() 사용
    - ResultSet = stmt.excuteQuery()     //  ResultSet은 다음편에!

5. bind 변수에 값 설정 (PreparedStatment의 경우 해당)

- bind 변수는 쿼리문에 ?표로 기술하는 변수

- 장점) SQL문과 값이 분리된다

- PreparedStatement의 method 활용 (parameter index => bind index를 말함)

- bind 변수는 차례로 1번의 index부터 시작하며, setXXX()로 bind 변수에 값을 설정함

    > 숫자 : pstmt.setInt (bind변수의 인덱스, 값);

    > 문자열 : pstmt.setString (bind변수인덱스, 값);

- 주의)

 ① bind 변수에는 ‘ 를 사용하지 않음

 ② Java는 like 의 % 와 함께 사용되면 바인드 변수를 인식하지 못함

       해결방법) where 컬럼명 like ‘?%’ (X)

                        => %를 일반 문자열로 변경하고 ?와 붙여줌(‘와 || 사용)

                        => where 컬럼명 like ?||’%’ (O)

 ③ bind 변수는 값이 들어가는 부분에만 사용할 수 있음 & 테이블명/컬럼명을 직접 사용할 수 없음

      해결방법) 변수로 갖다 붙이자! "select * from ?" // 안됨=> "select * from " + 테이블명

       > 단,  테이블명이 값으로 들어갈 때에는 bind변수를 사용할 수 있음 (다른 변수에 할당해서 사용은 가능)

6. 연결 끊기 (반드시!!)

- rs.close(); // select query의 경우 생성되는 ResultSet 객체가 있으면 이거 먼저 끊음

- stmt.close(); // Statement 객체 먼저 끊고

- con.close();  // 이후 Connection 끊기