DB, JAVA 연동

Day33. 230615

 

update_sal_fc 사용자함수 실행>

package db;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
 
//scott계정의 update_sal_fc 사용자함수 실행하는 클래스
/*라이브러리 등록 - 해당프로젝트 Build Path -> add External Library -> ojdbc6.jar
1. JDBC Driver등록
2. 연결 Connection얻기
3. 객체준비
4. 쿼리실행
5. 자원반납
*/
public class FuntionEx {
 
    public static void main(String[] args) {
        //1. JDBC Driver등록
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("JDBC Driver등록");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //2. 연결 Connection얻기
        String url ="jdbc:oracle:thin:@172.30.1.86:1521/xe";
        String user = "scott";
        String password = "tiger";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("연결 Connection얻기-성공");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //3. 객체준비
        //String sql = "{? = call 함수명(?,..,?)}";
        String sql = "{? = call UPDATE_SAL_FC(?,?)}";
        CallableStatement stmt = null;
        try {
            stmt = conn.prepareCall(sql);
            //4. 쿼리실행 //exec UPDATE_SAL_PROC(7369,300);
            stmt.registerOutParameter(1, Types.NUMERIC);
            stmt.setInt(2,7369);
            stmt.setInt(3,600);
            stmt.execute();
            int result = stmt.getInt(1);
            System.out.println("result="+result);
 
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5. 자원반납
            try {
                if(stmt!=null) {stmt.close();}
                if(conn!=null) {conn.close();}
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        
    }
 
}

package db;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
//scott계정의 UPDATE_SAL_PROC 프로시저 실행하는 클래스
/*라이브러리 등록 - 해당프로젝트 Build Path -> add External Library -> ojdbc6.jar
1. JDBC Driver등록
2. 연결 Connection얻기
3. 객체준비
4. 쿼리실행
5. 자원반납
*/
public class ProcecedureEx {
 
    public static void main(String[] args) {
        //1. JDBC Driver등록
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("JDBC Driver등록");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //2. 연결 Connection얻기
        String url ="jdbc:oracle:thin:@172.30.1.86:1521/xe";
        String user = "scott";
        String password = "tiger";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("연결 Connection얻기-성공");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //3. 객체준비
        String sql = "{call UPDATE_SAL_PROC(?,?)}";
        CallableStatement stmt = null;
        try {
            stmt = conn.prepareCall(sql);
            //4. 쿼리실행 //exec UPDATE_SAL_PROC(7369,300);
            stmt.setInt(1,7369);
            stmt.setInt(2,300);
        /*    boolean result = stmt.execute();
            if(result) {
                System.out.println("실행성공"); //콘솔출력
            }else {
                System.out.println("실패"); //콘솔출력
            }
        */    
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5. 자원반납
            try {
                if(stmt!=null) {stmt.close();}
                if(conn!=null) {conn.close();}
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        
    }
 
}

-> 실행 됐는데 "실패"가 출력됨...수정해보기...