How to use Java stored procedures to communicate SQL statements in Oracle databases

Today, we will introduce how Oracle databases use Java stored procedures to communicate with SQL, XML, and Java, J2EE, and Web services. If you are interested in the actual operations, the following articles will provide you with relevant knowledge.

Stored procedure) allows you to effectively separate the persistence logic running on the database layer from the business logic running on the middle layer. This separation reduces the complexity of the entire application and provides reusability, security, performance, and scalability.

However, a major obstacle to the widespread adoption of stored procedures is that different database vendors use proprietary database implementation languages. Java-based stored procedures can solve this problem. Oracle has implemented ANSI standards that define the ability to call static Java methods as processes or functions from SQL. This implementation is simply called "Java stored procedure ".

In this article, you will learn how Java-based stored procedures help simplify business logic, improve its performance, and expand database functions. This article describes how to enable Java-based stored procedures in an Oracle database. We will also introduce how Java stored procedures access data and how to create basic Java stored procedures.

Select PL/SQL or Java

When considering Oracle stored procedures, you may think of PL/SQL. However, Oracle has supported Java in the database since Oracle8i, which provides an open and portable Method for stored procedures different from PL/SQL. I can hear "$64 000 question": "How do I select between PL/SQL and Java? Should I forget all the PL/SQL knowledge I have learned and become a beginner in the Java World? "

Both languages are applicable to database programming and have their own advantages and weaknesses. When deciding which language to choose, you can refer to the following general rules based on experience:

For database centers that require seamless integration with SQL, the logic uses PL/SQL to access database objects, types, and features.

For the sake of database independence, you can use Java as an open language to replace PL/SQL, and also to integrate and communicate SQL, XML, J2EE, Web services, and other fields.

OralceJVM enables Java to run in the database

Since Oracle8i version 1Oralce8. 1.5), Oracle provides a tightly integrated Java Virtual Machine (JVM). JVM supports the Oralce database session structure. During any database conversation period, a virtual dedicated JVM can be started when the first Java code is called. Later users can use this existing Java-supported session period.

In fact, all sessions share the same JVM code and maintain a "static" private State, while garbage is collected within a single dialog period, this provides the same isolation and data integrity capabilities as SQL operations for various Java dialogs. Here, you do not need to perform a separate Java support process for data integrity. This dialog-based structure provides a small memory usage and enables OracleJVM to have the same linear SMP scalability as Oracle databases.

Create a Java stored procedure

Several steps are required to convert a Java method to a Java stored procedure, including loading a Java class into a database using the loadjava utility and calling the Call Spec standard, map the Java method, parameter type, and return type to the corresponding part of its SQL. The following describes how to complete these steps.

I will use a simple Hello class, which has a method Hello. world () and returns the string "Hello world ":

 
 

  
  
public class Hello { public static String world () { return "Hello world"; } }  
 
 

Loadjava Utility

Loadjava is a utility for loading Java source files, Java class files, and Java resource files. It can be used to verify bytecode and arrange Java classes and JAR files in the database. It can be called either through the command line or through the loadjava () method contained in the DBMS_JAVA class. To load our Hello. class Example, enter:

 
 

  
  
loadjava -user scott/tiger Hello.class 
 
 

From Oracle9i version 2, loadjava allows you to automatically publish a Java class as a stored procedure by creating the corresponding Call Specs for methods contained in the processed class. Oracle provides Oracle9i JDeveloper for developing, testing, debugging, and arranging Java stored procedures.

 
 

  
  
The Resolver Spec 
 
 

JDK-based JVM searches for class references in the directory listed in CLASSPATH and parses them. Because Oracle database classes exist in the Database mode, OracleJVM uses the database parser resolver to find and parse class references in the mode listed in Resolver Spec.

Different from CLASSPATH, CLASSPATH can be applied to all classes), and Resover Spec can be applied based on each category. The default parser first searches for classes in the loading mode and then searches for classes in the public synonym public synonyms.

 
 

  
  
loadjava -resolve  
 
 

You may need to specify different Resolvers or force the parsing when using loadjava, so as to determine any problems that may occur in future running.

 
 

  
  
loadjava -resolve -resolver "((* SCOTT) (foo/bar/* OTHERS) (* PUBLIC))"  
 
 

Call Spec and stored procedure Call

To Call the Java method from SQL and PL/SQL and JDBC), you must first release the public static method through Call Spec, it defines the parameters used by the SQL method and the returned SQL type.