java 调用存储过程

java 调用存储过程

 

创建存储过程

create or replace procedure sp_inout 
    (i_id in int , o_name out varchar2 , o_job out varchar2)
as
begin
    select ename,job
        into o_name,o_job
    from emptest where empno = i_id;
end;

 

java代码

package star;

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

public class JdbcConn {

	public static void main(String[] args) {
		conn();
	}
	
	public static void conn(){
		String driver = "oracle.jdbc.driver.OracleDriver";
		String user = "SCOTT";
		String password = "tiger";
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			
			//下面是一般的oracle查询
			String sql = "select * from emptest where ename = ?";
			/*ps = conn.prepareStatement(sql);
			ps.setString(1, "SCOTT");
			rs = ps.executeQuery();
			
			while(rs.next()){
				System.out.println(rs.getString(2)+"  的工作是  "+rs.getString(3));
			}*/
			
			//下面是java调用存储过程
			CallableStatement callableStatement = conn.prepareCall("{call sp_inout(?,?,?)}");
			callableStatement.setInt(1, 7788);
			callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
			callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
			
			callableStatement.execute();
			
			String name = callableStatement.getString(2);
			String job = callableStatement.getString(3);
			
			System.out.println(name + "  " +job);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}