Java调用Oracle存储过程详解
程序员文章站
2024-03-06 10:44:49
java调用oracle存储过程详解
步骤:
1、编写oracle存储过程
2、编写数据库获取连接工具类
3、编写简单应用调用存储过程
实现:
1、oracle...
java调用oracle存储过程详解
步骤:
1、编写oracle存储过程
2、编写数据库获取连接工具类
3、编写简单应用调用存储过程
实现:
1、oracle存储过程:
/*测试表*/ create table test( id varchar2(32), name varchar2(32) ); /*存储过程 插入数据*/ create or replace procedure insert_procedure( para1 in varchar2, para2 in varchar2 ) as begin insert into test (id, name) values (para1, para2); end insert_procedure; /*存储过程 返回结果集*/ create or replace procedure select_procedure( para_id in varchar2, name out sys_refcursor /* 这个sys_refcursor类型在sys.standard包中 */ ) as begin open name for select * from test where id = para_id; end;
2、jdbc工具类
import java.sql.connection; import java.sql.drivermanager; import java.sql.resultset; import java.sql.sqlexception; import java.sql.statement; public class dbutil { public static final string driver = "oracle.jdbc.driver.oracledriver"; public static final string url = "jdbc:oracle:thin:@localhost:1521/orcl"; public static final string username = "pfm"; public static final string password = "pfm"; /** * 通过静态代码块 注册数据库驱动 */ static { try { class.forname(driver); } catch (classnotfoundexception e) { e.printstacktrace(); } } /** * 获得connection * * @return */ public static connection getconnection() { connection conn = null; try { conn = drivermanager.getconnection(url, username, password); } catch (sqlexception e) { e.printstacktrace(); } return conn; } /** * 获得statement * * @return */ public static statement getstatement() { statement st = null; try { st = getconnection().createstatement(); } catch (sqlexception e) { e.printstacktrace(); } return st; } /** * 关闭resultset * * @param rs */ public static void closeresultset(resultset rs) { if (rs != null) { try { rs.close(); } catch (sqlexception e) { e.printstacktrace(); } } } /** * 关闭statement * * @param st */ public static void closestatement(statement st) { if (st != null) { try { st.close(); } catch (sqlexception e) { e.printstacktrace(); } } } /** * 关闭connection * * @param conn */ public static void closeconnection(connection conn) { if (conn != null) { try { conn.close(); } catch (sqlexception e) { e.printstacktrace(); } } } /** * 关闭全部 * * @param rs * @param sta * @param conn */ public static void closeall(resultset rs, statement sta, connection conn) { closeresultset(rs); closestatement(sta); closeconnection(conn); } }
3、调用存储过程:
import java.sql.callablestatement; import java.sql.connection; import java.sql.preparedstatement; import java.sql.resultset; import java.sql.sqlexception; import oracle.jdbc.driver.oracletypes; /** * 测试调用存储过程 * */ public class storedtest { public static void main(string[] args) { insert_call(); //select_call(); } /** * 执行存储过程 插入数据 */ public static void insert_call() { connection conn = dbutil.getconnection(); preparedstatement pst = null; callablestatement proc = null; // 创建执行存储过程的对象 try { proc = conn.preparecall("{ call insert_procedure(?,?) }"); proc.setstring(1, "1"); // 设置第一个输入参数 proc.setstring(2, "hello call"); // 设置第一个输入参数 proc.execute();// 执行 } catch (sqlexception e) { e.printstacktrace(); } finally { try { // 关闭io流 proc.close(); dbutil.closeall(null, pst, conn); } catch (exception e) { e.printstacktrace(); } } } /** * 执行存储过程 查询数据 */ public static void select_call() { connection conn = dbutil.getconnection(); callablestatement stmt; try { stmt = conn.preparecall("{ call select_procedure(?, ?) }"); // 用此调用方法不能实现多行语法 stmt.setstring(1, "1"); stmt.registeroutparameter(2, oracletypes.cursor); stmt.execute(); resultset rs = (resultset) stmt.getobject(2); while (rs.next()) { system.out.println(rs.getstring("name")); } } catch (sqlexception e) { e.printstacktrace(); } finally { dbutil.closeconnection(conn); } } }
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!