在Java程序中调用Java存储过程 [英] Java stored procedure calling in java program

问题描述

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

What I should do is to create a Java stored procedure and later call it from Java program.

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

I am having problems executing the JAVA program - JDBCPiemers (below) with 1 parameter (I need to get out the String Nosaukums). Error: "Invalid column name".

在ORACLE中调用该过程可以正常工作.

Calling out the procedure in ORACLE works fine.

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

This is my stored procedure which I have loaded in database.

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创建了以下软件包.

I have created a following package with 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程序.但是我无法使它工作.我收到错误消息:无效的列索引"

This is my Java program in which I call out the procedure. However I cannot get it working. I am getting error: "Invalid column index"

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."); }}

推荐答案

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

To return a value you need it to be a function, not a procedure:

  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.当然，所有这些未经测试...

Note that the out parameter is now index 1 as it's the return from the function, so the value you pass in is now index 2. All untested of course...

这篇关于在Java程序中调用Java存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！