Oracle数据库连接、存储过程及调用
1. 定义一个存储过程
create or replace procedure getuser(eid in number, na out varchar, em out varchar)
as
begin
select username, email into na, em from T_USER where userid = eid;
dbms_output.put_line(na);
end;
2. 应用程序连接数据库
package utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 连接oracle数据库工具类
* @author 宾桀锋
* @date 2017年8月13日
*/
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.2.152:1521:orcl";
private static String username= "binjf";
private static String password = "123456";
//注册数据库的驱动
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
//获取数据库连接
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//释放数据库的资源
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;
}
}
}
}
3. 调用存储过程
package oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import oracle.jdbc.internal.OracleTypes;
import org.junit.Test;
import utils.JDBCUtils;
/**
* 测试存储过程类
* @author 宾桀锋
* @date 2017年8月13日
*/
public class TestProcedure {
@Test
public void testProcedure(){
String sql = "{call getuser(?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try{
//得到一个连接
conn = JDBCUtils.getConnection();
//通过连接创建出statement
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1, 5);
//对于out参数,申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.VARCHAR);
//执行调用
call.execute();
//取出结果
String username = call.getString(2);
String email = call.getString(3);
System.out.println("用户名:" + username + " 邮箱:" + email);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
}