Creating and Calling a Stored Procedure

// The following uses opengaussjdbc.jar as an example.
// There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation).
// You need to change the values of $ip, $port, and database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Types;

public class DBTest {

    // Create a database connection.
  public static Connection GetConnection(String username, String passwd) {
    String driver = "com.huawei.opengauss.jdbc.Driver";
    String sourceURL = "jdbc:opengauss://$ip:$port/database";
    Connection conn = null;
    try {
      // Load the database driver.
      Class.forName(driver);
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }

    try {
    // Create a database connection.
      conn = DriverManager.getConnection(sourceURL, username, passwd);
      System.out.println("Connection succeed!");
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }

    return conn;
  };


  // Create a stored procedure.
  public static void CreateCallable(Connection conn) {
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      // Create a stored procedure and return the sum of the three input values.
      stmt.execute("create or replace procedure testproc \n" +
          "(\n" +
          "    psv_in1 in integer,\n" +
          "    psv_in2 in integer,\n" +
          "    psv_inout inout integer\n" +
          ")\n" +
          "as\n" +
          "begin\n" +
          "    psv_inout := psv_in1 + psv_in2 + psv_inout;\n" +
          "end;\n" +
          "/");
    } catch (SQLException e) {
      throw new RuntimeException(e);
    } finally {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          throw new RuntimeException(e);
        }
      }
    }
  }

  // Call the stored procedure.
  public static void ExecCallableSQL(Connection conn) {
    CallableStatement cstmt = null;
    try {
      cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
      cstmt.setInt(2, 50); 
      cstmt.setInt(1, 20);
      cstmt.setInt(3, 90);
      cstmt.registerOutParameter(4, Types.INTEGER);  // Register an OUT parameter of the integer type.
      cstmt.execute();
      int out = cstmt.getInt(4);  // Obtain the OUT parameter.
      System.out.println("The CallableStatment TESTPROC returns:"+out);
      cstmt.close();
    } catch (SQLException e) {
      if (cstmt != null) {
        try {
          cstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }
  

  /**
   * Main process. Call static methods one by one.
   * @param args
  */
  public static void main(String[] args) {
    // Create a database connection.
    String userName = System.getenv("EXAMPLE_USERNAME_ENV");
    String password = System.getenv("EXAMPLE_PASSWORD_ENV");
    Connection conn = GetConnection(userName, password);

    // Create a stored procedure.
    CreateCallable(conn);

    // Call the stored procedure.
    ExecCallableSQL(conn);

    // Close the database connection.
    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

  }

}