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();

 }

}