oracle stored procedure call java

With the continuous deepening of enterprise informatization, the use of databases is becoming more and more widespread, and the application of stored procedures is becoming more and more common. Oracle database has done an excellent job in implementing and optimizing stored procedures, but sometimes it is necessary to call Java programs in stored procedures to meet some specific needs. This article will analyze and explain the implementation principles, methods and cases of stored procedure calling Java.

1. Implementation principle of stored procedure calling Java

A stored procedure is a precompiled database object. It can be regarded as a script written at the database level and can implement complex business logic. . Stored procedures can call other stored procedures or functions, but they cannot directly call Java programs. When you need to call a Java program in a stored procedure, you need to use the extended language mechanism of the database to achieve this by creating a Java stored procedure.

Java stored procedure is essentially a Java program that connects to the Oracle database through JDBC (Java Database Connectivity) to interact with the database. Creating a Java stored procedure in an oracle database requires the following steps:

1. Create a Java class

You need to write a class in Java to access and operate the database. This class must implement the specific interface of the oracle database, which is "oracle.jdbc.OracleCallableStatement".

2. Compile Java classes into Java bytecode files

Compile the written Java classes into Java bytecode files (.class files). You can use the Javac compiler or other Java IDEs.

3. Store Java bytecode files in the oracle database

Use the "loadjava" tool that comes with the JDK to upload the Java bytecode files to the oracle database. This tool can be used in Use it in the command line window as follows:

loadjava -user username/password@database -verbose classfile

where classfile is the full path of the Java bytecode file.

4. Create a Java stored procedure in the oracle database

To create a Java stored procedure in the oracle database, you can use tools such as PL/SQL or SQL Developer. The syntax for creating a Java stored procedure is as follows:

CREATE OR REPLACE PROCEDURE procedure_name
AS LANGUAGE JAVA
NAME 'Java_class_name.method_name()';

Among them, procedure_name is the stored procedure Name; Java_class_name is the name of the Java class; method_name is the method name in the Java class.

2. Methods of calling Java from stored procedures

1. Use JDBC connection

In Java stored procedures, you can use JDBC to connect to the oracle database to interact with the database . In Java, you can use the following code to get the JDBC connection:

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin :@localhost:1521:ORACLE", "username", "password");

Among them, "oracle.jdbc.driver.OracleDriver" is the JDBC driver of the oracle database, which needs to be loaded in the code program; "jdbc:oracle:thin:@localhost:1521:ORACLE" is the URL of the database connection; "username" and "password" are the username and password of the database.

2. Call Java methods

In Java stored procedures, you can directly call methods in Java classes. You can use the following code to call methods in a Java class:

String result = Java_class_name.method_name();

Where "Java_class_name" is the name of the Java class, "method_name" is the Java class The method name in , "result" is the return value of the method.

3. Passing parameters in stored procedures

In Java stored procedures, parameters can be passed to Java methods through the parameters of the stored procedure. You can use the following code to get the parameters of the stored procedure:

CallableStatement cs = null;
cs = (OracleCallableStatement)conn.prepareCall("{call procedure_name(?, ?, ?)}");
cs.registerOutParameter(1, OracleTypes.VARCHAR);
cs.setString(2, param1);
cs.setString(3, param2);
cs.executeUpdate();
String result = cs.getString(1);

Among them, "cs" is the stored procedure object, "procedure_name" is the name of the stored procedure, "param1" and "param2" are the parameters of the stored procedure, and "result" is the return value of the method.

3. Case of stored procedure calling Java

The following is a case of using Java stored procedure to implement a simple query of user information.

1. Create a Java class

Create a class in Java to implement the method of querying user information based on user ID. The code is as follows:

public class UserInfo {

public static String getUserInfo(String userid) {
    String result = "";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORACLE", "username", "password");
        String sql = "SELECT * FROM userinfo WHERE userid = ?";
        ps = conn.prepareStatement(sql.toString());
        ps.setString(1, userid);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            result += rs.getString("userid") + "\t";
            result += rs.getString("username") + "\t";
            result += rs.getString("age") + "\t";
            result += rs.getString("sex") + "\n";
        }
        rs.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return result;
}

}

2. Compile Java class

Compile the written Java class into Java bytecode document.

3. Store Java bytecode files in the oracle database

Use the "loadjava" tool to upload the Java bytecode files to the oracle database.

4. Create a Java stored procedure

Create a Java stored procedure in the oracle database to implement the function of calling Java methods to query user information. The code is as follows:

CREATE OR REPLACE PROCEDURE getUserInfo(userid IN VARCHAR2, result OUT VARCHAR2)
AS LANGUAGE JAVA
NAME 'UserInfo.getUserInfo(java.lang.String) return java.lang.String ';

5. Call Java stored procedures

Use tools such as PL/SQL or SQL Developer to call Java stored procedures to implement the function of querying user information. The code is as follows:

DECLARE

result VARCHAR2(10000);

BEGIN

getUserInfo('001', result);
dbms_output.put_line(result);

END;

The above code will output the user information with user ID "001".

Summary

Stored procedures are a very important function in the database, which can implement complex business logic and improve the efficiency of the database. When you need to call a Java program in a stored procedure, you can do so by creating a Java stored procedure. This article introduces the implementation principles, methods and cases of stored procedure calling Java, and hopes to be helpful to everyone.

The above is the detailed content of oracle stored procedure call java. For more information, please follow other related articles on the PHP Chinese website!