java calls oracle stored procedure

As a widely used programming language, Java plays an important role in database development. As one of the most popular relational database management systems today, Oracle is widely used in enterprise applications due to its high scalability and powerful performance. Stored procedures are a very common technology when using Oracle databases. This article will introduce how Java calls Oracle stored procedures.

1. Overview of Oracle stored procedures

A stored procedure is a set of precompiled SQL statements that can be reused and are generally stored in the database. Stored procedures can receive parameters, generate output, return results, etc., and have great advantages in execution efficiency. In Oracle database, stored procedures are usually written in PL/SQL language.

2. Java calls Oracle stored procedures

There are two ways to call Oracle stored procedures in Java: using JDBC or using the Hibernate framework.

1. Using JDBC

Using JDBC to call Oracle stored procedures generally requires the following steps:

(1) Open the database connection

Pass Use the Class.forName() method and the DriverManager.getConnection() method to create a database connection.

(2) Create a CallableStatement object

In JDBC, call the stored procedure through CallableStatement. CallableStatement objects can be created using the Connection.prepareCall() method.

(3) Set parameters

Set the input parameters and output parameters of the stored procedure through the setXXX() method of the CallableStatement object.

(4) Execute the stored procedure

Execute the stored procedure through the CallableStatement.execute() method.

(5) Release resources

After execution is completed, resources must be closed, including CallableStatement objects and database connections.

The following is a code example that uses JDBC to call Oracle stored procedures.

// 首先加载Oracle驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

//创建数据库连接
Connection conn = DriverManager.getConnection(url, username, password);

//设置输入参数和输出参数
CallableStatement cstmt = conn.prepareCall("{call procedure_name(?,?,?)}");
cstmt.setString(1, input_param1);
cstmt.setString(2, input_param2);
cstmt.registerOutParameter(3, Types.VARCHAR);

//执行存储过程
cstmt.execute();

//获取输出参数
String output_param = cstmt.getString(3);

//释放资源
cstmt.close();
conn.close();

2. Using the Hibernate framework

The Hibernate framework is an open source object/relational mapping (ORM) framework that provides a way to map Java objects into relational databases Methods. When using the Hibernate framework, you usually need to use stored procedures and custom SQL statements to query the database. In Hibernate, you can use the @NamedStoredProcedureQuery annotation to call stored procedures.

The following is a code example that uses Hibernate to call Oracle stored procedures.

//定义存储过程
@NamedStoredProcedureQuery(
   name = "PROCEDURE_NAME",
   procedureName = "procedure_name",
   parameters = {
      @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "input1"),
      @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "input2"),
      @StoredProcedureParameter(mode = ParameterMode.OUT, type = String.class, name = "output")
   }
)

//调用存储过程
StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("PROCEDURE_NAME");
query.setParameter("input1", input1);
query.setParameter("input2", input2);
query.execute();

//获取结果
String result = (String) query.getOutputParameterValue("output");

3. Summary

Java calling Oracle stored procedures is a very common requirement in enterprise-level development, which can be achieved by using JDBC or Hibernate framework. When using JDBC, you need to perform steps such as database connection, creating CallableStatement objects, setting parameters, executing stored procedures, and releasing resources, which is relatively troublesome. When using the Hibernate framework, you can use the @NamedStoredProcedureQuery annotation to achieve this. No matter which method is used, you need to pay attention to the type and order of setting parameters and the correct stored procedure name.

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