[Day5] JSP/JDBC 5 - transaction 처리, callable statement, reflection

[Day5] JSP/JDBC 5 [11/1]

1. transaction 트랜잭션 처리

-- [ 트랜잭션 처리 ]
-- 예) 계좌 이체 =  A   UPDATE , B  UPDATE
--                    논리적인 이 두개의 작업은 하나로 묶어셔 모두 성공/ 모두 실패.
CREATE OR REPLACE PROCEDURE UP_계좌이체
(
       p계좌정보1
     , p계좌정보2
     , p이체금액
)
IS
BEGIN
   UPDATE 테이블명
   SET 잔고 = 잔고 - p이체금액
   WHERE p계좌정보1;
   
   UPDATE 테이블명
   SET 잔고 = 잔고 + p이체금액
   WHERE p계좌정보2;
   
   COMMIT;
EXCEPTION
    WHEN THEN
        ROLLBACK;
END;

[트랜잭션 실패]

Connection conn = null;
conn = DBconn.getConnection();
PreparedStatement psmt = null;
int rowCount = 0;

String sql = "INSERT INTO dept VALUES(?,?,?)";

// 1. dept테이블 부서 추가 O
try{
	pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1,50);
	pstmt.setString(2,"QC");
	pstmt.setString(3,"SEOUL");
	rowCount = pstmt.executeUpdate();
	if(rowCount ==1) System.out.println("> 50/QC/SEOUL 부서 추가 성공!!!");
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	try {
    	pstmt.close();
    } catch (SQLException e){
    	e.printStackTrace();
    }
}

// 2. dept테이블 부서 추가 X
try{
	pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1,100);
	pstmt.setString(2,"XXX");
	pstmt.setString(3,"YYY");
	rowCount = pstmt.executeUpdate();
	if(rowCount ==1) System.out.println("> 50/QC/SEOUL 부서 추가 성공!!!");
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	try {
    	pstmt.close();
    } catch (SQLException e){
    	e.printStackTrace();
    }
}

[트랜잭션 성공]

conn.setAutoCommit(false); // 자동커밋 off

conn.commit(); // 오류가 안나면 커밋

conn.rollback(); // 롤백

Connection conn = null;
conn = DBconn.getConnection();
PreparedStatement psmt = null;
int rowCount = 0;

String sql = "INSERT INTO dept VALUES(?,?,?)";

try{
	conn.setAutoCommit(false); // 자동커밋 off
	// 1번 O
	pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1,50);
	pstmt.setString(2,"QC");
	pstmt.setString(3,"SEOUL");
	rowCount = pstmt.executeUpdate();
	if(rowCount ==1) System.out.println("> 50/QC/SEOUL 부서 추가 성공!!!");
    // 2번 X
    pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1,100);
	pstmt.setString(2,"XXX");
	pstmt.setString(3,"YYY");
	rowCount = pstmt.executeUpdate();
	if(rowCount ==1) System.out.println("> 50/QC/SEOUL 부서 추가 성공!!!");
    
    conn.commit(); // 오류가 안나면 커밋
} catch (SQLException e) {
	e.printStackTrace();
    try{
    	conn.rollback(); // 롤백
    } catch (SQLException e1) {
    	e1.printStackTrace();
    }
} finally {
	try {
    	pstmt.close();
    } catch (SQLException e){
    	e.printStackTrace();
    }
}

2. CallableStatement (cstmt) = null

- 저장 프로시저 사용하는 stmt

String sql = "{ call 저장프로시저명(파라미터...)}";

* 프로시저 테스트

EXEC UP_INSERTEMP (pempno => 9999, pname => 'kim');

* java

psvm() {
	// 1. up_insertemp 저장 프로시저 하나 생성
    int empno = 9999;
    String ename = "ADMIN";
    String mgr = 1234;
    int deptno = 10;
    Date hiredate = new Date(1991, 2, 23);
    
    Connection conn = null;
    CollableStatement cstmt = null;
    int rowCount = 0;
    String sql = "{ call UP_INSERTEMP(pempno => ?, pename => ?, pdeptno => ?, phiredate => ? )}";;
    
    conn = DBConn.getConnection();
    
    try{
		cstmt = conn.prepareCall(sql);
        cstmt.setInt(1,empno);
        cstmt.setString(2, ename);
        cstmt.setInt(3,mgr);
        cstmt.setInt(4,deptno);
        cstmt.setDate(5,hiredate);
		rowCount = cstmt.executeUpdate();
        
		if(rowCount ==1) System.out.println("사원 추가 성공!!!");
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try{
        	cstmt.close();
        } catch (SQLException e){
        	e.printStackTrace();
        }
   	 }
}

EX 02_03 파일 리팩토링

1) 저장 프로시저 만들고

2) 자바 파일 생성

- 저장 프로시저 (삭제)

부서 번호 입력 받아서 부서 삭제

CREATE OR REPLACE PROCEDURE UP_DELETEDEPT
( 
	pdeptno dept.deptno%type
)
IS
BEGIN
	DELETE FROM  dept  
	WHERE deptno = pdeptno;
	COMMIT;
EXCEPTION
WHEN OTHERS THEN
	RAISE_APPLICATION_ERROR(-20011, '> DEPT DELETE X...');
END;

EXEC UP_DELETEDEPT(50);

INSERT INTO dept VALUES ( 50, 'QC', 'SEOUL');
COMMIT;

- 자바 파일 생성

private static void deleteDept() {
	System.out.print("> 삭제할 부서번호(deptno) 입력 ? ");
	int pdeptno = scanner.nextInt();      
	String sql = "{ CALL UP_DELETEDEPT( ? ) }";
	try {
		cstmt = conn.prepareCall(sql);
		cstmt.setInt(1, pdeptno);
		int rowCount =  cstmt.executeUpdate();  
		if( rowCount == 1 ) {
			System.out.println("> 1개 부서 삭제 완료!!!");
		}
	} catch (SQLException e) {          
		e.printStackTrace();
	} finally {
		try {
			cstmt.close();
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	} 
}

update

- 특정 부서 번호에 dname과 loc 수정

- 저장 프로시저 파라미터 디폴트값 설정방법 두 가지

pdname dept.dname%type := null
ploc dept.loc%type DEFAULT NULL

CREATE OR REPLACE PROCEDURE UP_UPDATEDEPT
(
	pdeptno dept.deptno%type
    , pdname dept.dname%type := null
    , ploc dept.loc%type DEFAULT NULL
)
IS
BEGIN
	UPDATE dept
    SET dnmae = NVL(pdname, dname), loc = NVL(ploc, loc)
    WHERE deptno = pdeptno;
    
    COMMIT;
EXCEPTION
	WHEN OTHERS THEN
    	RAISE_APPLICATION_ERROR(-20013, '>DEPT INSERT X...');
END;

// 부서 수정
private static void updateDept() {
	System.out.print("> 수정할 부서번호(deptno) 입력 ? ");
	int pdeptno = scanner.nextInt();
	System.out.print("> 수정할 부서명, 지역명 입력 ? ");
	String pdname = scanner.next();
	String ploc = scanner.next();
		
	String sql = { CALL UP_UPDATEDEPT(pdeptno => ?, pdname => ? ploc => ?) };
	
	// System.out.println( sql );
	
	// 1.  2.   
	
	// 3. UPDATE 처리
	try {
		cstmt = conn.prepareCall(sql);
        cstmt.setInt(1,pdeptno);
	    cstmt.setString(2,pdname);
        cstmt.setString(3,ploc);
        int rowCount =  cstmt.executeUpdate(); 
	    if( rowCount == 1 ) {
	    	System.out.println("> 1개 부서 수정 완료!!!");
	    }
	} catch (SQLException e) {	 
		e.printStackTrace();
	} finally {
		try {
			stmt.close();
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	}	
	// 4.
}

select

프로시저

CREATE OR REPLACE PROCEDURE UP_SELECTDEPT
(
	pcursor OUT SYS_REFCURSOR
)
IS
	vsql VARCHAR2(1000); -- 동적쿼리
BEGIN
	vsql := 'SELECT * '
	vsql := vsql || ' FROM dept ';
	
    -- OPEN FOR 문
    OPEN pcursor FOR vsql;
    
    COMMIT;
EXCEPTION
	WHEN OTHERS THEN
    	RAISE_APPLICATION_ERROR(-20013, '>DEPT INSERT X...');
END;

자바

refcursor -> rs (result set) 으로 바로 받아오지 않음 !!

커서를 cstmt에 등록하고 getObject로 받아옴!

// 출력용 매개변수 OUT 등록
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.executeQuery(); 
		
// 출력용 매개변수로부터 가져옴
rs = (ResultSet)cstmt.getObject(1);

private static void selectAllDept() {

	String sql { CALL UP_SELECTDEPT((?) };
	
	ArrayList<DeptDTO> deptList = null;
		
	try {
		cstmt = conn.prepareCall(sql);
        // 출력용 매개변수 OUT 등록
        cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cstmt.executeQuery(); 
		
        // 출력용 매개변수로부터 가져옴
        rs = (ResultSet)cstmt.getObject(1);
        
		if( rs.next() ) {
			deptList =  new ArrayList<DeptDTO>();
			do {					
				DeptDTO dto = new DeptDTO(  rs.getInt("deptno") , rs.getString("dname"),  rs.getString("loc"));					
				deptList.add(dto);					
			} while (rs.next());
		} // if		
		printDept( deptList );
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			cstmt.close();
            rs.close();
			DBConn.close();  프로그램 종료할 때 그 때 한 번만 닫기.
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	} // try
}

id_check 예시

프로시저

create or replace PROCEDURE up_idCheck
(
    pempno  IN emp.empno%type
    ,pempnoCheck OUT NUMBER --  사용가능 0,   사용불가능 1
)
IS
BEGIN
   SELECT COUNT(*) INTO pempnoCheck
   FROM emp
   WHERE empno = pempno;
-- EXCEPTION
END;

자바

psvm{
	Syso.("중복체크할 아이디 입력");
    Scanner scanner = new Scanner(System.in);
    int pempno = scanner.nextInt();
    
    Connection conn = null;
    CallableStatement cstmt = null;
    String sql = " {CALL up_idCheck(?,?) }";
    
    conn = DBConn.getConnection();
    try {
		cstmt = conn.prepareCall(sql);
        cstmt.setInt(1,pempno); // IN
        cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
        cstmt.executeQuery(); 
        
        int idCheck = (int)cstmt.getObject(2);
        
        if(idCheck == 0) {
        	System.out.println("사용가능한 아이디");
        } else {
        	System.out.println("사용중인 아이디");
        }
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			cstmt.close();
			DBConn.close();  프로그램 종료할 때 그 때 한 번만 닫기.
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	} // try
    
}

-sql-

CREATE OR REPLACE PROCEDURE UP_SEARCHDEPT
( 
     psearchCondition NUMBER  -- 1,2,3
   , psearchword VARCHAR2   --  검색어
   , pcursor  OUT SYS_REFCURSOR
)
IS
   vsql VARCHAR2(1000);  -- 동적 쿼리 변수 선언
BEGIN
    vsql := 'SELECT *  '; 
    vsql :=  vsql || ' FROM dept  '; 
    
    IF psearchCondition = 1 THEN
       vsql :=  vsql || ' WHERE REGEXP_LIKE( dname, :psearchword , ''i'' )'; 
    ELSIF psearchCondition = 2 THEN 
        vsql :=  vsql || ' WHERE REGEXP_LIKE( loc, :psearchword, ''i'' )'; 
    ELSIF psearchCondition = 3 THEN
       vsql :=  vsql || ' WHERE REGEXP_LIKE( dname, :psearchword, ''i'' ) OR  REGEXP_LIKE( loc, :psearchword, ''i'' )'; 
    END IF;
    
    vsql :=  vsql || ' ORDER BY deptno ASC ';
      
    
    -- OPEN~FOR문
    IF psearchCondition = 1 THEN
        OPEN pcursor FOR vsql  
                     USING psearchword; 
    ELSE
     OPEN pcursor FOR vsql 
         -- INTO 
         USING psearchword, psearchword; 
    END IF;
   
    COMMIT;
EXCEPTION
   WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20014, '> DEPT SEARCH  X...');
END;

-java-

private static void searchDept() {
		// 검색조건 선택(입력)    1) 부서명  2) 지역명  3) 부서명 + 지역명
		// 검색어 
	 
		System.out.println("[ 검색 조건 ]   "); 
		System.out.println("1) 부서명  ");
		System.out.println("2) 지역명  ");
		System.out.println("3) 부서명 + 지역명  ");
	 
		System.out.print("> 검색조건 선택 ? ");     // 1,  2, 3
		int searchCondition = scanner.nextInt();
		
		System.out.print("> 검색어 입력 ? ");   
		String searchWord = scanner.next();
		
		String sql = "{CALL UP_SEARCHDEPT(?,?,?)}";
		
        // selectAllDept 메서드 복사 붙여넣기
		ArrayList<DeptDTO> deptList = null;
		
		try {
			cstmt = conn.prepareCall(sql);
			cstmt.setInt(1, searchCondition);
			cstmt.setString(2, searchWord);
			cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
			cstmt.executeQuery(); 
            
            rs = (ResultSet)cstmt.getObject(3);
			if( rs.next() ) {
            	deptList =  new ArrayList<DeptDTO>();
            	do {					
            		DeptDTO dto = new DeptDTO(  rs.getInt("deptno") , rs.getString("dname"),  rs.getString("loc"));					
            		deptList.add(dto);					
            	} while (rs.next());
			} // if
			
			printDept( deptList );
		} catch (SQLException e) { 
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				rs.close();
			} catch (SQLException e) { 
				e.printStackTrace();
			}
		} // try
	}
}

추가문제

1. 부서번호를 입력받아서 해당 부서원들의 정보를 출력하는 코딩을 하세요.

1) CallableStatement 사용
2) UP_SELECTDEPTEMP 저장 프로시저 생성 후 사용.
3) printEmp( List list) 메서드 사용해서 출력.

-sql-

CREATE OR REPLACE PROCEDURE UP_SELECTDEPTEMP
(
    pdpateno dept.deptno%type
    , pcursor OUT SYS_REFCURSOR
)
IS
	vsql VARCHAR2(1000);
BEGIN
	vsql := 'SELECT * '
	vsql := vsql || ' FROM  emp  ';
	vsql := vsql || ' WHERE deptno =: pdeptno   ';
	
    -- OPEN FOR 문
    OPEN pcursor FOR vsql
    -- INTO
    USING pdeptno
    
EXCEPTION
	WHEN OTHERS THEN
    	RAISE_APPLICATION_ERROR(-20013, '>EMP SELECT X...');
END;

-java-

public static void main(String[] args) {
    Connection conn = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    ArrayList<EmpDTO> list = null;
    String sql = "{CALL UP_SELECTDEPTEMP(?,?)}";
    
    Scanner scanner = new Scanner(System.in);
    int deptno = 0;
    System.out.println("부서 번호를 입력하세요");
    pdeptno = scanner.nextInt();
    
    conn = DBConn.getConnection();
    
    try {
		cstmt = conn.prepareCall(sql);
        cstmt.setInt(1, pdeptno);
        cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cstmt.executeQuery(); 
		 
        rs = (ResultSet)cstmt.getObject(2);
        
        printEmp(list);
        
		if( rs.next() ) {
			list =  new ArrayList<EmpDTO>();
			do {					
				EmpDTO dto = new DeptDTO();
                dto.setEmpno(rs.getInt("empno"));
                dto.setEname(rs.getString("ename"));
                dto.setHiredate(rs.getDate("hiredate"));
                dto.setDeptno(rs.getInt("deptno"));
                
				list.add(dto);					
			} while (rs.next());
		} // if		
		printEmp( list );
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			cstmt.close();
            rs.close();
			DBConn.close();  
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	} // try
}

private static void printEmp(List<EmpDTO> list) {
	Iterator<EmpDTO> ir = list.Iterator();
    while(ir.hashNext()){
		EmpDTO dto = ir.next();
        System.out.println(dto);
    }
}

2. ID(empno), PWD(ename) 을 입력받아서

    로그인 성공
    로그인 실패 - ID가 존재하지 않습니다.
    로그인 실패 - ID는 존재하지만 PWD가 일치하지 않습니다.
    라고  출력하는 코딩을 하세요.
    1) CallableStatement 사용
    2) 오라클 수업 때 생성한 UP_LOGON 저장 프로시저 사용.

-sql-

CREATE OR REPLACE PROCEDURE up_logon
(
    pempno    emp.empno%type
    , pename  emp.ename%type
    , plogonCheck OUT  NUMBER  -- 성공 0  ID X -1            1
)
IS
   vename emp.ename%type;
BEGIN
    SELECT COUNT(*)  INTO plogonCheck -- 1,  0
    FROM emp
    WHERE empno = pempno;
    
    IF plogonCheck = 1  THEN  -- ID 존재한다.
        SELECT ename INTO vename
        FROM emp
        WHERE empno = pempno;        
        IF  vename = pename THEN
            plogonCheck := 0;
        ELSE
            plogonCheck := 1;
        END IF;        
    ELSE 
       plogonCheck := -1;
    END IF;
-- EXCEPTION
END;

-java-

private static void id_Check() {
	Connection conn = null;
	CallableStatement cstmt = null;
    String sql { CALL UP_LOGON((?, ?, ?) };
    
    Scanner scanner = new Scanner(System.in);
	
    System.out.print("ID, PW 입력");
    
	int pempno = scanner.nextInt();      
	String pename = scanner.next();      
	
    conn = DBConn.getConnection();
	
	try {
		cstmt = conn.prepareCall(sql);
		cstmt.setInt(1, pempno);
		cstmt.setString(2, pename);
		cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
		cstmt.executeQuery(); 
		
		int idCheck = (int)cstmt.getObject(3);
        
		if(idCheck == 0) {
        		System.out.println("로그인 성공");
        	} else if(idCheck == -1) {
        		System.out.println("로그인 실패, 비밀번호 오류");
        	} else if(idCheck == 1){
			System.out.println("로그인 실패, 아이디 없음");
		}
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {
			cstmt.close();
			DBConn.close();  
		} catch (SQLException e) { 
			e.printStackTrace();
		}
	} 
}

Reflection

- ResultSet 결과물에 대한 정보 추출해서 사용할 수 있는 기술

public static void main(String[]args) {
      String sql = "SELECT table_name"
               + "FROM tabs"
               + "ORDER BY table_name ASC";
  
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      String tableName = null;
      
      conn = DBConn.getConnection();
	
      try {
      	pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        
        int no = 1;
		while(rs.next()) {
        	tableName = rs.getString(1);
            System.out.printf("%d. %s\n", no++, tableName);
        } // while
        // SCOTT이 소유한 모든 테이블 출력
        
        
      } catch (SQLException e){
      	e.printStackTrace();
      } finally {
      	try {
        	pstmt.close();
        } catch (SQLException e){
        	e.printStackTrace();
        }
      }
      System.out.print(">테이블명 입력");
      Scanner scanner = new Scanner(System.in);
      tableName = scanner.next();
      
      sql = "SELECT *"
      	    +" FROM + tableName";
            
      try {
      	pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        
        // getMetaData() : rs객체로부터 컬럼정보(수,자료형, 컬럼명) 얻어오는 메서드
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        System.out.println(">칼럼갯수:" + columnCount);
        
        String columnName = rsmd.getColumnName(1);  // 첫번째 칼럼명
        System.out.println(">칼럼갯수:" + columnCount);
        
        int columnType = rsmd.getColumnType(1);
        System.out.println(">칼럼타입:" + columnType);
        
        String columnTypeName = rsmd.getColumnTypeName(1);
        System.out.println(">칼럼타입:" + columnTypeName);
        
        int p = rsmd.getPrecision(1);
        System.out.println(">정밀도:" +p);
        
        int s = rsmd.getScale(1);
        System.out.println(">스케일:" +s);
        
        for(int i=1; i<columnCount; i++) {
        	String columnName = rsmd.getCountName(i);
            System.out.printf("%s\t", columnName);
        }
        // NUMBER      2(getInt)    Scale != 0 (실수) (getDouble)
        // VARCHAR2   12(
        // DATE       93
        
        for(int i=1; i<columnCount; i++) {
        	int columnType = rsmd.getColumnType(i);
            int scale = rsmd.getScale(i);
            
            if(columnType == 2 && scale == 0) {
              System.out.printf("%d\t", rs.getInt(i));
            } else if(columnType ==2 && scale != 0) {
              System.out.printf("%2f\t", rs.getDouble(i));
            } else if(columnType == 93) {
              System.out.printf("%tf\t", rs.getDate(i));
            } else if(columnType ==12) {
              System.out.printf("%s\t", rs.getString(i));
        }
      } catch (SQLException e){
      	e.printStackTrace();
      } finally {
      	try {
        	pstmt.close();
        } catch (SQLException e){
        	e.printStackTrace();
        }
      }
      DBConn.close();
      System.out.println("END");

저작자표시

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

[Day2] JSP/Servlet 2 - db연결 테이블 반환, 서블릿, request객체 (0)	2022.12.06
[Day1] JSP/Servlet 1 - 개요 (0)	2022.12.06
[Day4] JSP/JDBC 4 - Board (0)	2022.11.01
[Day3] JSP/JDBC 3 - 사용자 변경, 여러출력방식, linkedhashmap사용 DTO 저장, MVC, JUnit (0)	2022.10.31
[Day2] JSP/JDBC 2 - 동적쿼리, PreparedStatement (0)	2022.10.31

[Day5] JSP/JDBC 5 - transaction 처리, callable statement, reflection

[Day5] JSP/JDBC 5 [11/1]

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day5] JSP/JDBC 5 [11/1]

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바