准备一个oracle 的JDBC jar 包:ojdbc14_11g.jar
首先找到你的 oracle 安装位置,例如:
1.创建一个JDBC数据库连接工具类:
package com.test.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private static String user = "数据库连接名";
private static String password = "数据库连接密码";
//注册数据库驱动
static{
try {
Class.forName(driver);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* 获取数据库连接
* @return
*/
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 释放数据库连接资源
* @param conn
* @param st
* @param rs
*/
public static void release(Connection conn,Statement st,ResultSet rs){
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if (st!=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
2.调用 存储过程:
package com.test.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import org.junit.Test;
import oracle.jdbc.internal.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import com.hwb.db.JDBCUtils;
/**
* 调用存储过程 一个输入参数,多个 输出参数
* @author Administrator
*
*/
public class ProcedureTest {
/**
* create or replace procedure selectUserById(uid in number,
uName out VARCHAR2,
uAge out number,
uSex out char)
*/
@Test
public void testProcedure(){
String sql = "{call selectUserById(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
//得到一个数据库连接
conn = JDBCUtils.getConnection();
//通过连接创建出statement
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1, 2); // (第几个问号,要赋的值)
//对out参数,声明
call.registerOutParameter(2, OracleTypes.VARCHAR); //(第几个问号,声明的类型)
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.CHAR);
//执行调用
call.execute();
//取出结果
String userName = call.getString(2);
int userAge = call.getInt(3);
String userSex = call.getString(4);
System.out.println("用户姓名:"+userName+"\n\t年龄:"+userAge+"\n\t性别:"+userSex);
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭连接,释放资源
JDBCUtils.release(conn, call, null);
}
}
}
3.调用存储方法:
package com.test.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import oracle.jdbc.internal.OracleTypes;
import org.junit.Test;
import com.hwb.db.JDBCUtils;
/**
* 调用存储函数,一个输入参数,一个输出参数
* @author Administrator
*
*/
public class FunctionTest {
/**
* create or replace function selectAge(eno in number)
return number
*/
@Test
public void testFunction(){
//{?= call <procedure-name>[<arg1>,<arg2>...]}
String sql = "{call selectAge(?)}";
Connection conn = null;
CallableStatement call = null;
try {
//得到数据库连接
conn = JDBCUtils.getConnection();
//通过数据库连接创建statement
call = conn.prepareCall(sql);
//对于输出参数,声明
call.registerOutParameter(1, OracleTypes.NUMBER);
//对于输入参数,赋值
call.setInt(2, 3);
//执行调用
call.execute();
//获取返回的结果
int age = call.getInt(1);
System.out.println("该用户年龄:"+age);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}
4.调用存储过程,一个输入参数,返回一个查询结果集合
package com.hwb.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import org.junit.Test;
import oracle.jdbc.internal.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import com.hwb.db.JDBCUtils;
/**
* 存储过程 一个输入参数,返回一个查询结果集合
* @author Administrator
*
*/
public class CursorTest {
/**
* create or replace package Mypackage as
procedure queryUserList(uid in number,userList out usercursor);
end mypackage;
*/
@Test
public void testCursor(){
String sql = "{call Mypackage.queryUserList(?,?) }";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
//得到数据库连接
conn = JDBCUtils.getConnection();
//通过数据库连接创建statement
call = conn.prepareCall(sql);
//对于输入参数,赋值
call.setInt(1, 1);
//对于输出参数,声明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行调用
call.execute();
//将CallableStatement 强转成 OracleCallableStatement 用来获取光标类型Cursor,并得到结果ResultSet
rs = ((OracleCallableStatement)call).getCursor(2);
//遍历 ResultSet
while (rs.next()) {
//根据类型和列名取值
int id = rs.getInt("user_id"); //括号内为 列名
String user_name = rs.getString("user_name");
int age = rs.getInt("user_age");
String sex = rs.getString("user_sex");
System.out.println("查询到的用户信息:\n\tid:"+id+"\n\t姓名:"+user_name
+"\n\t年龄:"+age+"\n\t性别:"+sex);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}
}