在Java程序中调用PL/SQL程序包代码

问题描述

我正在尝试在 Java 程序中调用使用 PL/SQL 包定义的过程.

I am trying to call a procedure defined with a PL/SQL package in a Java program.

我知道人们可以使用Jdbc中的 connection.prepareCall 来调用存储过程.但是，关于如何在包中调用过程的信息很少.

I am aware one can call stored procedures using connection.prepareCall in Jdbc. But there is very little information out there on how to call a procedure within a package.

我正处于开发阶段，我仍在考虑使用什么数据库框架.只是想知道将JDBC用于PLSQL有什么优缺点?对于此用例，还有更好的JDBC替代方法吗?

I am at a stage in development where i am still considering what db framework to use. Just wondering what are the pros and cons of using JDBC for PLSQL ? For this usecase are there better alternatives to JDBC ?

推荐答案

遵循以下简单步骤:

public static final String SOME_NAME = "{call   schema_name.org_name_pkg.return_something(?,?)}"; // Change the schema name,packagename,and procedure name.

// Simple JDBC Connection Pooling
// Here I am passing param companyId which is IN param to stored procedure which will return me some value.

Connection conn = null;
CallableStatement stmt = null;
ResultSet rset = null;

try {
        conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
        stmt = conn.prepareCall(SOME_NAME);//We have declared this at the very top
        stmt.setString(1, companyid);//Passing CompanyID here
        stmt.registerOutParameter(2, OracleTypes.CURSOR);//Refcursor selects the row based upon query results provided in Package.
        stmt.execute();
        rset = (ResultSet) stmt.getObject(2);

        while (rset.next()) {
            String orgId=rset.getString("RPT_ORG_ID");
            // When using refcursor easy to get the value just by using Column name
            String orgName=rset.getString("RPT_ORG_NAME");
            // Some Logic based what do you want to do with the data returned back from query
} catch (Exception e) {
        LOGGER.error("Error extracting ", e);
} finally {
        DBUtils.cleanUp(conn, stmt, rset);
}

// Clean and close you connection

这篇关于在Java程序中调用PL/SQL程序包代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！