8/4 JDBC(Select, Insert, Update, procedure, function)

OraSelect2.java

package ch18;

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

public class OraSelect2 {

	public static void main(String[] args) throws SQLException {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String sql = "Select * From emp";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		System.out.println("사원명단");
		System.out.println("사원코드\t 사원명\t 업무\t\t 급여\t 일자");
		System.out.println("==================================================");
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "scott", "tiger");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				do {
					int empno = rs.getInt(1);
					String ename = rs.getString(2);
					String job = rs.getString(3);
					int sal = rs.getInt("sal");
					//이 date는 sql을 쓰지 않으므로 util으로 import함.
					Date date = rs.getDate("hiredate");
					//길이 tab 간격을 맞추기 위해 if문 작성한 것임.
					if (job.length() > 7)
						// %TF는 년월일
						System.out.printf("%d\t%s\t%s\t%d\t%TF\n", empno, ename, job, sal, date);
					else System.out.printf("%d\t%s\t%s\t\t%d\t%TF\n", empno, ename, job, sal, date);
				} while (rs.next());
				//while문 하나씩 실행될 때 마다 한 줄씩 실행되는 것임.
				//맨 마지막 줄이 되면 false로 나와서 do while문을 빠져나오게 됨.
			}
			else {
				System.out.println("data No");
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}

	}

}

출력 결과

 

* https://owasp.org/www-project-top-ten/ : Top 10 Web Application Security Risks를 알려줌.

보안이 중요하기 때문에 PreparedStatement 을 사용하는 것을 권장한다.

 

 

OraPrepare.java

package ch18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class OraPrepare {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("입력할 Oracle 부서코드?"); 	String deptno = sc.nextLine();
		System.out.println("입력할 Oracle 부서명?"); 	String dname = sc.nextLine();
		System.out.println("입력할 Oracle 근무지?"); 	String loc = sc.nextLine();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		String driver 	= "oracle.jdbc.driver.OracleDriver";
		String url 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String sql  	= "Insert Into dept values(?, ?, ?)";
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "scott", "tiger");
			//지금까지는 execute문(실행문)에 sql을 넣었지만
			//지금은 문장을 준비해놓고 바로 sql을 넣음(prepareStatement). 실행문에 넣지 않음. 
			//이 prepareStatement가 보안과 가독성이 좋음.
			pstmt = conn.prepareStatement(sql);
			//1번 순서에 deptno를 넣고, 2번 순서에 dname을 넣고, 3번 순서에 loc을 넣는다는 뜻
			//이렇게 하면 내가 넣은 sql문과 파라미터가 섞여 들어가기 때문에 해커가 데이터를 알아보기 어렵다.
			pstmt.setString(1, deptno);
			pstmt.setString(2, dname);
			pstmt.setString(3, loc);
			//result는 작업에 성공한 갯수
			int result = pstmt.executeUpdate();
			if (result > 0)
				System.out.println("OraPrepare 입력 성공");
			else
				System.out.println("OrePrepare 입력 실패");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		}
		
	}

}

 

출력 결과

dept 테이블에 데이터가 추가되었다.

 

OraUpdate.java

package ch18;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.util.Scanner;

public class OraUpdate {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("수정할 부서를 입력하세요"); 	String deptno = sc.nextLine();
		System.out.println("부서명?"); 				String dname = sc.nextLine();
		System.out.println("근무지?"); 				String loc = sc.nextLine();
		String sql = String.format("Update Dept set dname = '%s', "
				+ "loc = '%s' where deptno = %s", dname, loc, deptno);
		
		String driver 	= "oracle.jdbc.driver.OracleDriver";
		String url 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		Connection conn = null;
		Statement stmt = null;
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "scott","tiger");
			stmt = conn.createStatement();
			int result = stmt.executeUpdate(sql);
			if (result>0)
				System.out.println("수정 성공");
			else
				System.out.println("수정 실패");
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally {
			if (stmt != null) stmt.close();
			if(conn !=null) stmt.close();
		
	}
}

}

출력 결과

 

기존 테이블 데이터

변경 후 데이터

 

 

PROCEDURE Dept_Insert.sql

--Procedure : 오라클 서버 안에 만드는 함수
--3개의 파라미터를 받아서 insert하는 기능
--CREATE OR REPLACE : Dept_Insert 프로시저가 없으면 만들고 있으면 교체하라. 이렇게 해야 프로시저 수정이 용이해짐
CREATE OR REPLACE PROCEDURE Dept_Insert
--파라미터를 3개 넣음
--%type : dept.deptno의 타입을 따라가겠다 라는 뜻. 유지보수가 쉬워짐.
(vdeptno IN dept.deptno%TYPE, 
vdname IN dept.dname%TYPE, 
vloc dept.loc%TYPE)
IS
--변수명을 지정해준다면 begin문 실행됨.
BEGIN
    INSERT INTO dept VALUES(vdeptno, vdname, vloc);
    COMMIT;
END;

실행 결과

가 나오고 scott > 프로시저에 생성된 프로시저 Dept_Insert가 있다.

 

Dept_Insert우클릭 > 실행 > 매개변수 입력값에 값 입력

dept 테이블 확인해보면 입력한 데이터가 추가돼있음.

 

 

OraProc1.java

package ch18;

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

public class OraProc1 {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("입력할 Oracle 부서코드?"); 	String deptno = sc.nextLine();
		System.out.println("입력할 Oracle 부서명?"); 	String dname = sc.nextLine();
		System.out.println("입력할 Oracle 근무지?"); 	String loc = sc.nextLine();
		
		Connection 	conn=null;
		//PROCEDURE 호출객체
		//CallableStatement도 preparedStatement와 비슷한 구조를 가지기 때문에 보안이 좋음.
		//사실은 CallableStatement가 preparedStatement의 자식임.
		CallableStatement cs = null;
		String driver 	= "oracle.jdbc.driver.OracleDriver";
		String url 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		//프로시저명을 ?에 써 줌.
		String sql = "{call dept_Insert(?, ?, ?)}";
		
		try {
			Class.forName(driver); 	//없어도 될 수 있음, 연결되어 있다면
			conn = DriverManager.getConnection(url, "scott", "tiger");
			//DB에 저장된 프로시저를 호출하기 위한 것. 
			cs = conn.prepareCall(sql);
			cs.setString(1, deptno);
			cs.setString(2, dname);
			cs.setString(3, loc);
			int result = cs.executeUpdate();
			if (result > 0)
				System.out.println("Oracle CallableStatement 입력 성공");
			else
				System.out.println("Oracle CallableStatement 입력 실패");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if(cs != null) cs.close();
			if(conn != null) conn.close();
		}
		cs.close();

	}

}

출력 결과

dept 테이블을 확인해보면 입력한 데이터가 들어가있다.

 

 

PROCEDURE Emp_Info2.sql

--보통 이름 지정할 때 파라미터는 p, 변수는 v로 주어줌.
--in 한개넣으면 out 두개 알 수 있는 것
CREATE OR REPLACE PROCEDURE Emp_Info2
--프로시저는 여러개 받을 수 있다. 리턴값이 여러개일 수 있다.
-- emp.empno은 emp테이블의 empno라는 뜻
(p_empno IN emp.empno%TYPE, 
p_ename OUT emp.ename%TYPE, 
p_sal OUT emp.sal%TYPE)
IS
    --is와 begin 사이에 일반변수 하나 넣음
    -- %TYPE 데이터형 변수 선언
    v_empno emp.empno%TYPE;
BEGIN
--데이터베이스 콘솔창으로 결과를 보기 위해 dmbs_output.enable을 설정한 것임
    DBMS_OUTPUT.ENABLE;
    --%TYPE 데이터형 변수 사용
    SELECT empno, ename, sal
    INTO v_empno, p_ename, p_sal
    FROM emp
    WHERE empno = p_empno;
    --결과값 출력
    -- v_empno : 입력값
    -- || 는 문자열을 합치는 역할
    -- chr(10) || chr(13) : 아스키코드값을 가져오고 싶을 때 chr을 쓴다.
    -- chr(10진수)를 넣으면 아스키코드로 받아줌. *chr(10)은 LF, chr(13)은 CR -> 줄이 바뀌는 것임.
    -- LF는 다음줄로, CR는 제일 처음 칸으로
    -- DBMS_OUTPUT.PUT 에 _LINE 붙이면 다음줄로(Println과 같음)
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || v_empno || chr(10) || chr(13) || '줄바뀜');
    DBMS_OUTPUT.PUT_LINE('사원이름 : ' || p_ename);
    DBMS_OUTPUT.PUT_LINE('사원급여 : ' || p_sal);
END;

파라미터 변수는 파라미터를 넣어줄 때 쓰는 변수이다

자바코드에서 예를 들면

이렇게 파라미터를 넘겨줄 때 쓰는 변수가 파라미터 변수이고,

이것이 일반 변수이다.

 

sql에서는

in, out은 파라미터를 넘겨주므로 파라미터 변수이고

IS와 BEGIN문 사이에 넣는 것은 일반 변수이다.

 

실행 결과

scott > 프로시저 > Emp_Info2가 생성되었다.

Emp_Info2 > 우클릭 > 실행 > 입력값에 1000 넣어보면

출력 결과

 

OraProc2.java

package ch18;

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

public class OraProc2 {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner (System.in);
		System.out.println("정보가 궁금한 사람 사번");
		int empno = sc.nextInt();
		String driver 	= "oracle.jdbc.driver.OracleDriver";
		String url 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		//sql 쓸 때 {}을 빼도 실행은 되지만 원래는 넣어야 한다. 
		//select조회와 다르기때문에 result 받는 것 아님.
		String sql = "{call emp_Info2(?, ?, ?)}"; //프로시저 emp_Info2를 받음
		Connection conn = null;
		CallableStatement cs =null;
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url, "scott", "tiger");
			cs = conn.prepareCall(sql);
			//프로시저에서 받아들여서 sql에 쓰고 싶으면 in, 자바에게 던져주고 싶으면 out
			//첫 번째 인덱스를 입력받음
			cs.setInt(1, empno);
			// registerOutParameter : 파라미터를 안에서 꺼내와서 register(기억장치) 속에 저장한다.
			//out을 하였으면 registerOutParameter을 선언해주어야 한다.
			cs.registerOutParameter(2, java.sql.Types.VARCHAR); 	//varchar(문자열) 타입으로 레지스터 안에 저장돼 있는 상태
			cs.registerOutParameter(3, java.sql.Types.INTEGER);		//Integer(숫자형) 타입으로 레지스터 안에 저장돼 있는 상태
			cs.executeQuery();
			String ename = cs.getString(2); 	//String으로 받았으므로 String으로 넣는다.
			int sal		 = cs.getInt(3); 		//int로 받았으므로 int로 넣는다.
			System.out.println("사번 : " + empno);
			System.out.println("이름 : " + ename);
			System.out.println("급여 : " + sal);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (cs 	!= null) cs.close();
			if (conn != null) conn.close();
		}
	}
}

 

출력 결과

 

 

FUNCTION func_sal.sql

CREATE OR REPLACE FUNCTION func_sal
    --사원번호를 받으면
    (p_empno IN Number)
-- function은 리턴값이 하나이다.
RETURN NUMBER
IS
    vsal emp.sal%type;
BEGIN
    UPDATE emp SET sal=sal*1.1
    WHERE empno=p_empno;
    COMMIT;
    --select만 하면 조회, select into하면 조회한 것을 저장
    SELECT sal INTO vsal
    FROM emp
    --급여 올린게 반영이 됐는지 조회함.
    WHERE empno=p_empno;
    --function은 리턴값이 있어야 함. 프로시저는 리턴값이 따로 없음.
    RETURN vsal;
END;

실행 결과

scott > 함수 > FUNC_SAL이 저장돼있다.

 

FUNC_SAL 우클릭 > 값 입력 후 확인(실행)

 

실행 전 데이터

실행 후 데이터

 

 

OraProc3.java

package ch18;

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

public class OraProc3 {

	public static void main(String[] args) throws SQLException {
		Scanner sc = new Scanner(System.in);
		System.out.println("수정할 Oracle 사원코드?"); String empno = sc.nextLine();
		
		Connection conn = null;
		CallableStatement cs = null;
		String driver 	= "oracle.jdbc.driver.OracleDriver";
		String url 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		
		//Procedure Call 할 때의 Format
		//첫 번째 ? 에는 저장할 급여를 넣음
		//두 번째 ? 에는 사번을 넣음
		String sql = "{? = call func_sal(?)}";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "scott", "tiger");
			cs = conn.prepareCall(sql);
			cs.setString(2, empno); 	//변경 사번
			//값을 더블형으로 넣음. 더블형으로 넣으면 소수점이 들어감.
			//emp테이블에서 속성을 확인해보면 크기가 7로 돼있기 때문에 int로 받아도 상관은 없음.
			cs.registerOutParameter(1, java.sql.Types.DOUBLE); 	//변경 급여
			cs.executeQuery();
			Double sal = cs.getDouble(1);
			
			if (sal > 0) {
				System.out.println("Oracle CallableStatement 수정 성공");
				System.out.println("변경사번 : " + empno);
				System.out.println("변경급여 : " + sal);
			}
			else System.out.println("Oracle CallableStatement 수정 실패");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (cs != null) cs.close();
			if (conn != null) conn.close();
		}
		sc.close();
	}
}

출력 결과

 

출력 결과 (변경 전)

출력 결과 (변경 후)