Java JDBC操作Oracle的代码示例

package demo;

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

import oracle.jdbc.OracleCallableStatement;

import org.junit.Test;

public class Demo {
 @Test
 public void testCursor() throws Exception{
  Class.forName("oracle.jdbc.OracleDriver");
  
  String url ="jdbc:oracle:thin:@localhost:1521:orcl";
  String user = "scott";
  String password  = "tiger";
  //调用包下的存储过程
  String sql = "{call mypackage.queryEmp(?,?)}";
  
  Connection conn = DriverManager.getConnection(url, user, password); 
  CallableStatement call = conn.prepareCall(sql);
  
  //参数赋值
  call.setInt(1, 10);
  call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
  //执行
  call.execute();
  
  //取出结果
  //需要将call转换成OracleCallableStatement
  ResultSet rs = ((OracleCallableStatement)call).getCursor(2);
  while(rs.next()){
   System.out.println(rs.getObject(1) + "  " + rs.getObject(2));
  }
  
  rs.close();
  call.close();
  conn.close();
 }
 
 
 
 @Test
 public void testQueryEmp2() throws Exception{
  //注册驱动
  Class.forName("oracle.jdbc.OracleDriver");
  
  String url ="jdbc:oracle:thin:@localhost:1521:orcl";
  String user = "scott";
  String password  = "tiger";
  String sql = "{?=call queryEmp2(?,?,?)}";
  
  Connection conn = DriverManager.getConnection(url, user, password); 
  CallableStatement call = conn.prepareCall(sql);
  
  //参数赋值
  call.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
  call.setInt(2, 7839);
  call.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
  call.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
  //执行
  call.execute();
  
  //取结果
  double annalIncome = call.getDouble(1);
  String name = call.getString(3);
  double sal = call.getDouble(4);
  //输出
  System.out.println(name+"  年收入:" + annalIncome + " 月薪:" + sal);
  
  call.close();
  conn.close();
  
 }
 
 @Test
 public void testQueryEmp1() throws Exception{
  //注册驱动
  Class.forName("oracle.jdbc.OracleDriver");
  
  String url ="jdbc:oracle:thin:@localhost:1521:orcl";
  String user = "scott";
  String password  = "tiger";
  String sql = "{call queryEmp1(?,?,?)}";
  
  Connection conn = DriverManager.getConnection(url, user, password);
  CallableStatement call = conn.prepareCall(sql);
  
  //付参数值
  call.setInt(1, 7839);
  //对于out参数
  call.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
  call.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
  
  //执行
  call.execute();
  
  //取结果
  String name = call.getString(2);
  double sal = call.getDouble(3);
  
  System.out.println(name+" " +sal);
  
  //关闭资源
  call.close();
  conn.close();
 }
}