在Java程序中调用Java存储过程

我应该做的是创建一个Java存储过程,然后再从Java程序中调用它。

我在执行JAVA程序时遇到问题-带有1个参数的JDBCPiemers(如下)(我需要弄清楚String Nosaukums)。错误:“无效的列名”。

调用ORACLE中的过程可以正常工作。

这是我已存储在数据库中的存储过程。

import java.sql.*;

import java.io.*;

public class BeerBeer {

public static void getBreweryInfo (int Raz_ID)

throws SQLException

{ String sql =

"SELECT Nosaukums FROM Alus_razotaji WHERE ID = ?";//vaicajums

try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Izveidojam savienojumu ar datubāzi

PreparedStatement apstmt = conn.prepareStatement(sql);

apstmt.setInt(1, Raz_ID);

ResultSet rset = apstmt.executeQuery();// SQL vaicājuma izpildīšana

rset.close();

apstmt.close(); //Savienojuma aizvēršana

}

catch (SQLException e) {System.err.println(e.getMessage()); //Kļūdu izvadīsana

}

}

}

我用oracle创建了以下程序包。

CREATE OR REPLACE PACKAGE BeerBeer AS

PROCEDURE getBreweryInfo(Raz_ID number);

END BeerBeer;

CREATE OR REPLACE PACKAGE BODY BeerBeer AS

PROCEDURE getBreweryInfo(Raz_ID number) AS Language Java

NAME 'BeerBeer.getBreweryInfo(int)';

END BeerBeer;

这是我在其中调用该过程的Java程序。但是我无法使它工作。我收到错误消息:“无效的列索引”

package client;

import java.sql.*;

public class JDBCPiemers {

// Norādam URL draiverim un datu bāzei

static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";

static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";

// Izveidojam norādi uz datu bāzes lietotājvārdu un paroli

static final String USER = "SYSTEM";

static final String PASS = "asdasd";

private String sql;

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Connection conn = null;

CallableStatement stmt = null;

try {

//Reģistrējam JDBC draiveri

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("Izveidojam savienojumu ar datubazi ...");

conn = DriverManager.getConnection(DB_URL, USER, PASS);

// Vaicājuma izveide

System.out.println("Komandas veidosana...");

String SQL = "{CALL BeerBeer.getBreweryInfo (?)}";

stmt = conn.prepareCall(SQL);

int Raz_ID = 4;

stmt.setInt(1, Raz_ID);

stmt.registerOutParameter(2, java.sql.Types.VARCHAR);

System.out.println("Izpildam JAVA glabajamo proceduru ...");

// Vaicājuma izpilde

stmt.execute();

//Izgustam Alus_razotaja nosaukumu ar getXXX metodi.

String Razotajs = stmt.getString(2);

System.out.println("Izvadam rezultatus ...");

System.out.println("Identifikatoram NR. " +Raz_ID + " atbilst Alus_Razotajs ar nosaukumu '" + Razotajs + "'" );

stmt.close();

conn.close(); }

catch(SQLException se) {

//Kludu apstrade JDBC

se.printStackTrace(); }

catch(Exception e) {

//Kludu apstrade klasei Class.forName

e.printStackTrace(); }

finally {

// Bloks, lai aizvērtu resursus

try {if(stmt!=null)

stmt.close(); }

catch(SQLException se2) {}

try {if(conn!=null)

conn.close(); }

catch(SQLException se) {se.printStackTrace(); }

//finally bloka beigas

} // try

System.out.println("Darbu beidzam."); }}

回答:

要返回一个值,您需要它是一个函数,而不是一个过程:

  public static String getBreweryInfo (int Raz_ID) 

throws SQLException

{

String sql =

"SELECT Nosaukums FROM Alus_razotaji WHERE ID = ?";//vaicajums

try {

Connection conn = DriverManager.getConnection("jdbc:default:connection:");

PreparedStatement apstmt = conn.prepareStatement(sql);

apstmt.setInt(1, Raz_ID);

ResultSet rset = apstmt.executeQuery();// SQL vaicājuma izpildīšana

if (rset.next()) {

return rset.getString(1);

}

}

catch (SQLException e) {

System.err.println(e.getMessage()); //Kļūdu izvadīsana

}

finally {

rset.close();

apstmt.close(); //Savienojuma aizvēršana

}

}

然后您的包裹变成:

CREATE OR REPLACE PACKAGE BODY BeerBeer AS

FUNCTION getBreweryInfo(Raz_ID number) RETURN varchar2 AS Language Java

NAME 'BeerBeer.getBreweryInfo(int) return java.lang.String';

END BeerBeer;

您将其称为:

        String SQL = "{CALL ? = BeerBeer.getBreweryInfo (?)}";

stmt = conn.prepareCall(SQL);

int Raz_ID = 4;

stmt.registerOutParameter(1, java.sql.Types.VARCHAR);

stmt.setInt(2, Raz_ID);

System.out.println("Izpildam JAVA glabajamo proceduru ...");

// Vaicājuma izpilde

stmt.execute();

//Izgustam Alus_razotaja nosaukumu ar getXXX metodi.

String Razotajs = stmt.getString(1);

请注意,out参数现在是索引1,因为它是从函数返回的值,因此您传入的值现在是索引2。当然,所有这些未经测试…

以上是 在Java程序中调用Java存储过程 的全部内容, 来源链接: utcz.com/qa/414932.html

回到顶部