5. JDBC - CallableStatement(프로시져호출), 동적커서

 

1. CallableStatement

 

- PreparedStatement를 상속받은 인터페이스로 저장프로시져를 호출하는 기능을 갖음.

 

 

2. 테스트

 

1) 프로시져 생성.

 

PROCEDURE MEMPROC

CREATE OR REPLACE PROCEDURE MEMPROC
(
  ID MEMBERS.ID%TYPE,
  PWD VARCHAR2,
  EMAIL VARCHAR2,
  PHONE VARCHAR2
)
IS

BEGIN

  INSERT INTO MEMBERS
  VALUES(ID,PWD,EMAIL,PHONE,SYSDATE);
  
  COMMIT;

END ;

/

 

 

EX> DB접속 CLASS

 

DBConnection.java

package test.db;

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

 

public class DBConnection {
    public static Connection getCon() throws SQLException{
        Connection con=null;
        try{
            Class.forName("oracle.jdbc.OracleDriver");
            String url="jdbc:oracle:thin:@localhost:1521:XE";
            con= DriverManager.getConnection(url,"scott","tiger");
            return con;
        }catch(ClassNotFoundException ce){
            System.out.println(ce.getMessage());
            return null;
        }
    }
}

 

 EX> 프로시져 호출하기.

Test01.java

package test01.jdbc;

 

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import test.db.DBConnection;

 

class MyJdbc01{
    public  MyJdbc01()  {
        Connection con=null;
        CallableStatement  cstmt=null;
       
        try{
            con=DBConnection.getCon();
            System.out.println("db접속성공!");

            //String  sql="{call  프로시져명(파라미터,...)}";
            String  sql="{call  memproc(?,?,?,?)}";
            cstmt=con.prepareCall(sql);

            //?에  대응되는  파라미터값들  넣기
            cstmt.setString(1,"yb");
            cstmt.setString(2,"8765");
            cstmt.setString(3,"yb@naver.com");
            cstmt.setString(4,"010-111-1111");

            //프로시져호출하기
            cstmt.execute();
            System.out.println("프로시져가  성공적으로  호출되었어요!");
        }catch(SQLException se){   
            System.out.println(se.getMessage());   
        }finally{
            try{
                if(cstmt!=null)  cstmt.close();   
                if(con!=null)  con.close();
            }catch(SQLException  se){
                System.out.println(se.getMessage());
            }
        }
    }
}
public class Test01{
    public static void  main(String[]  args)  {   
        new MyJdbc01();   
    }
}

 

 

 

 

 

 

3. 동적 커서

 

- 리졀트셋 커서를 앞뒤로 자유롭게 이동. 기본적으로 커서는 앞으로만 이동할수 있고 읽기전용으로 설정되어 있음.

 

- 설정방법

 

Statement stmt= con.createStatement(

ResultSet.TYPE_SCROLL_INSENSITIVE,//커서이동방법

ResultSet.CONCUR_UPDATABLE //수정가능한 모드

);

 

EX> Test02.java

package test01.jdbc;

 

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import test.db.DBConnection;

 

class MyJdbc02{
    public MyJdbc02() {

 

        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;

 

        try{
            con=DBConnection.getCon();
            System.out.println("db접속성공!");
            //Statement createStatement(int resultSetType,
            //int resultSetConcurrency) throws SQLException
       
            stmt=con.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,//커서앞뒤로이동가능
                ResultSet.CONCUR_UPDATABLE //수정가능한 모드
            );
       
            String sql="select id,pwd,email,phone,regdate " +
              "from members "+
              "order by regdate desc";
            rs=stmt.executeQuery(sql);
            System.out.println("<< 앞에서부터 출력 >>");
            while(rs.next()){
                //문자열을 전체 10자리로 출력.출력방향은 왼쪽기준(왼쪽정렬)
                System.out.printf("%-10s",rs.getString(1));
                System.out.printf("%-10s",rs.getString(2));
                System.out.printf("%-15s",rs.getString(3));
                System.out.printf("%-15s",rs.getString(4));
                System.out.printf("%-15s\n",rs.getString(5));
            }
            System.out.println("<< 뒤에서부터 출력 >>");
            while(rs.previous()){
                //문자열을 전체 10자리로 출력.출력방향은 왼쪽기준(왼쪽정렬)
                System.out.printf("%-10s",rs.getString(1));
                System.out.printf("%-10s",rs.getString(2));
                System.out.printf("%-15s",rs.getString(3));
                System.out.printf("%-15s",rs.getString(4));
                System.out.printf("%-15s\n",rs.getString(5));
            }
            rs.absolute(1);//맨첫번째 행으로 이동
            rs.deleteRow();//현재행을 삭제
            System.out.println("첫번째 데이터가 삭제되었어요!!!");
        }catch(SQLException se){
            System.out.println(se.getMessage());
        }finally{
            try{
                if(rs!=null) rs.close();
                if(stmt!=null) stmt.close();
                if(con!=null) con.close();
            }catch(SQLException se){}
        }
    }
}

public class Test02 {
    public static void main(String[] args) {
        new MyJdbc02();
    }
}