1、Java连接oracle的jar包
ojdbc14-10.2.0.1.0.jar
2、数据库连接字符串
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.106.128:1521:orcl";
String user = "scott";
String password = "orcl";
说明:url连接的是虚拟机主机的ip地址
Oracle的连接初始化:
private Connection conn;
private CallableStatement cs;
private ResultSet rs;
@Before
public void init() throws Exception{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.106.128:1521:orcl";
String user = "scott";
String password = "orcl";
Class.forName(driver);
// 获取连接对象
conn = DriverManager.getConnection(url,user,password);
}
@After
public void destroy() throws Exception{
if (rs != null){
rs.close();
}
if (cs != null){
cs.close();
}
if (conn != null){
conn.close();
}
}
测试:OracleJDBC的连接:
/**
* OracleJDBC连接
* @throws Exception
*/
@Test
public void testOracleConnection() throws Exception{
// 创建CallableStatement对象
cs = conn.prepareCall("select * from emp");
// 执行查询结果
rs = cs.executeQuery();
while(rs.next()){
System.out.println(rs.getObject(1)+":"+rs.getString("ename"));
}
};
运行:
3、实现过程的调用
(1)调用无返回值的存储过程
/**
* 如何调用无返回值的存储过程
* pro_add_sal(eno in number)
* @throws Exception
*/
@Test
public void testOracleCallProcedure() throws Exception{
// 创建CallableStatement对象
cs = conn.prepareCall("{call pro_add_sal(?)}");
cs.setInt(1, 7788);
cs.executeQuery();
};
(2)调用有返回值的存储过程
/**
* 调用有返回值的存储过程
* pro_emp_totalsal(eno number,totalsal out number)
* @throws Exception
*/
@Test
public void testOracleCallProcedureOutNumber() throws Exception{
// 创建CallableStatement对象
cs = conn.prepareCall("{call pro_emp_totalsal(?,?)}");
cs.setInt(1, 7788);
// 注册一个输出参数
cs.registerOutParameter(2, OracleTypes.NUMBER);
cs.executeQuery();
System.out.println("7788的年金是"+cs.getObject(2));
};
测试结果:
(3)调用有返回值是游标(cursor)类型的存储过程
/**
* 调用有返回值是cursor类型的存储过程
* pro_emplist(dno number,emplist out sys_refcursor)
* @throws Exception
*/
@Test
public void testOracleCallProcedureOutCursor() throws Exception{
// 创建CallableStatement对象
cs = conn.prepareCall("{call pro_emplist(?,?)}");
cs.setInt(1, 10);
// 注册一个输出参数
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.executeQuery();
// 需要强转OracleCallableStatement
rs = ((OracleCallableStatement)cs).getCursor(2);
while(rs.next()){
System.out.println(rs.getObject(1)+":"+rs.getString("ename"));
}
};
测试结果:
4、实现函数的调用
(1)调用有返回值的类型的存储函数,返回值类型为number
/**
* 调用有返回值的类型的存储函数
* fun_emp_totalsal(eno number) return number
* @throws Exception
*/
@Test
public void testOracleCallFunctionReturnNumber() throws Exception{
// 创建CallableStatement对象
cs = conn.prepareCall("{? = call fun_emp_totalsal(?)}");
// 注册一个输出参数
cs.registerOutParameter(1, OracleTypes.NUMBER);
cs.setInt(2, 7788);
cs.executeQuery();
System.out.println("7788的年金是"+cs.getObject(1));
};
测试结果:
(2)
调用有返回值类型的存储函数,返回值类型为varchar
/**
* 调用有返回值的类型的存储函数,返回类型是varchar
* fun_emp_dname(dno number) return dept.dname%type
* @throws Exception
*/
@Test
public void testOracleCallFunctionReturnVarchar() throws Exception{
// 创建CallableStatement对象
cs = conn.prepareCall("{? = call fun_emp_dname(?)}");
// 注册一个输出参数
cs.registerOutParameter(1, OracleTypes.VARCHAR);
cs.setInt(2, 10);
cs.executeQuery();
System.out.println("部门名称是:"+cs.getObject(1));
};
测试结果: