Simplify database operations using Java stored procedures

Use Java stored procedures to communicate with SQL, XML, Java, J2EE, and Web services.

Stored procedures allow effective separation of 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 a 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

Starting from Oracle8i version 1 (oralce8.1.5), Oracle provides a tightly integrated Java Virtual Machine (JVM) that 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 releasing a Java method using call spec, 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. Unlike classpath (classpath can be applied to all classes), resover spec is applied based on each type of situation. The default parser first searches for classes in the loading mode, and then searches for classes in public synonyms.

 loadjava -resolve <myclass>

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 from PL/SQL and JDBC), you must first publish the public static method through call spec, it defines the parameters used by the SQL method and the returned SQL type.

In our example, we will connect to the database using SQL * Plus and define a top-level call spec for hello. World:

SQL> connect scott/tigerSQL> create or replace function helloworld returnVARCHAR2 as language java name 'Hello.world () returnjava.lang.String'; /Function created.

You can call the Java stored procedure as follows:

SQL> variable myString varchar2[20];SQL> call helloworld() into :myString;Call completed.SQL> print myString;MYSTRING---------------------Hello world

Java stored procedures can be called from the following through their call SPEC: SQL DML statements (insert, update, delete, select, call, explain Plan, lock table, and merge), PL/SQL blocks, subprograms, packages, and database triggers. The beauty of call spec is that the implementation of the stored procedure can be converted from PL/SQL to Java, and vice versa, which is transparent to the requester.

Call spec abstracts the call interface from the implementation Language (PL/SQL or Java), so that it can share the business logic between the original application and the new Java/J2EE-based application. However, when calling a Java class that resides in the database from a Java client program, you may not want to use the PL/SQL wrapper ). In later versions, Oracle plans to provide a mechanism that allows developers to skip call spec.

Advanced Data Access Control

Java stored procedures can be used to control and restrict access to Oracle Data by allowing users to manage data only through stored procedures, while stored procedures are executed within the permissions of their callers, but cannot access the table itself. For example, you can prohibit data updates within a specific period of time, or make managers have the right to query only the wage data, but cannot update the data, or record all accesses and notify a security organization.

Data logic sharing between original applications and J2EE applications

Because both the original application and J2EE application call the stored procedure through call spec, J2EE and non-J2EE applications can share the same data logic. With the call spec, you do not need to consider the implementation language used (whether PL/SQL or Java). This data logic can be shared.

Automatically generate primary keywords for BMP object beans

When applying Bmp to an EJB object bean, a bean instance can be uniquely identified by the automatically generated primary key word associated with the newly inserted data. It is the return value of ejbcreate. You can use a stored procedure to insert the corresponding data to retrieve the value in ejbceater () in a database operation and retrieve or compute the primary key word. As another method, you can also use the return_generated_keys feature of jdbc3.0 to insert the data with an SQL statement and retrieve the corresponding keywords (or rowid ). However, stored procedure methods are more portable between each JDBC driver version and the database.

You can use the following three steps to implement this mode:

1. Create a Java stored procedure and define a public static Java method insertaccount () in the public genpk class (). This method inserts data, computes unique keywords (by issuing a serial number), and returns the calculated keywords as the primary keywords.

2. Define call spec

   CREATE OR REPLACE PROCEDURE insertAccount(owner INvarchar, bal IN number, newid OUT number)AS LANGUAGE JAVA NAME 'GenPK.insertAccount(java.lang.String [])';/

3. Call the stored procedure in ejbcreate ()

   Public AccountPK ejbCreate(String ownerName, int balance) throws CreateException{   try {     CallableStatement call = conn.prepareCall{      "{call insertAccount(?, ?, ?)}"};           return new AccountPK(accountID);   }}

Customizes the primary keyword finder for CMP object beans

The Finder method is used to retrieve an existing EJB Entity Bean instance. The primary keyword finder allows you to retrieve the EJB instance with the unique identifier. For CMP object beans, the EJB container automatically generates the primary keyword finder findbyprimarykey () method based on the declaration description. However, in some cases, more control may be required, for example, a dedicated finder, such as findbystoredprockey (). In these cases, you can combine Java stored procedures and object relationship frameworks (such as Oracle9i Application Server [Oracle9iAS] toplink) to implement a custom primary keyword finder method. After defining the EJB finder as a redirect or named finder, toplink generates an SQL query for retrieving bean instances.

Data-driven EJB call

In the data-driven architecture, business logic calls can be triggered as the results of database operations (such as insertion, update, or deletion. The Java stored procedure that implements the data logic can be declared as a database trigger to call the EJB running on the middle layer J2EE application server. EJB calls can be implemented through interoperable inter-ORB Protocol (IIOP) Standard Remote Method Invocation (RMI) on j2ee1.3 compatible servers, you can also use RMI through the seller's specific transmission protocol (such as ormi of Oracle9iAS/Solaris, or T3 of BEA WebLogic. Each application server provider provides IIOP-based RMI to provide interoperability while having its own optimized protocol. Oracle9iAS supports both IIOP-based RMI calls and ormi-based RMI calls.

Data-driven message transmission

The Oracle9i database is embedded with Advanced Queuing (aq, Advanced Queuing). It is an integrated, stable, reliable, secure, scalable, and transactional message queuing framework. Oracle provides AQ functions for Java developers through standard Java messaging system (JMS) APIs. Java stored procedures can call AQ operations through the JMS interface to implement fast, scalable, and data-driven message transmission during the session period.

Java stored procedures can use JMS to call AQ operations. You can use the following four steps to implement this mode:

1. Create and start the JMS Queue (to do this, you can embed the following operations into the SQL script ):

   execute dbms_aqadm.create_queue_table(queue_table =>'queue1', queue_payload_type =>'SYS.AQ$_JMS_TEXT_MESSAGE', comment => 'a test queue', multiple_consumers => false, compatible => '8.1.0');execute dbms_aqadm.create_queue( queue_name  => 'queue1', queue_table => 'queue1' );execute dbms_aqadm.start_queue(queue_name => 'queue1');

2. Create a Java stored procedure (the Code excerpt is as follows ):

   public static void runTest(String msgBody){ try    {  // get database connection     ora_drv = new OracleDriver();     db_conn = ora_drv.defaultConnection();  // setup sender (cf online code sample)    ..  // create message     s_msg = s_session.createTextMessage(msgBody);  // send message     sender.send(s_msg);     s_session.commit();  // receive message     r_msg = (TextMessage) receiver.receive();     r_session.commit();  // output message text      String body = r_msg.getText();     System.out.println("message was '"+body+"'");  ..}} 

3. Create call SPEC:

   create or replace procedure jmsproc (t1 IN VARCHAR) as language java name 'jmsSample.main (java.lang.String[])';/

4. Call the stored procedure:

   call jmsproc('hello');

Database-assisted Web Publishing (cache failure)

A common problem that application structures must face is that database information is reliably cached to improve the performance of the entire system. Jcache is an upcoming Standard Specification (JSR 107) that can solve this problem. It illustrates a method for temporarily caching Java objects in the memory, including object creation, shared access, spooling, failure, and JVM consistency. It can be used to cache the most frequently read data in JSP, such as the product catalog and price list. Using jcache, the response time of most queries will be accelerated due to cached data (internal tests show that the response time is about 15 times faster ).

To track all changes to the original data and refresh the cached data, the Java stored procedure is attached to a table as a trigger. Any changes to this table will automatically call the stored procedure. The latter calls up a defined JSP to invalidate the jcache object and maps its status to the database table. When it fails, the query that follows it will force the cache to update based on the database data.

Extended database functions

One of the advantages of running Java code directly in a database is that to implement new functions, you only need to simply load the code or library, call spec is used to create entry points for SQL, PL/SQL, Java, J2EE, and non-Java APIs (public static methods ). Oracle9i database users can easily expand database functions. Oracle uses this capability to obtain new applications and toolkit, such as XML developer kits (xdks ).

Communicate SQL, PL/SQL, Java, J2EE,. net, and XML

Oracle xdk is written in Java and its public methods can be used as Java stored procedures, thus extending the XML programming capability of the database. SQL, PL/SQL, Java, J2EE, and non-Java (. NET) business logic can all access XML analyzer, XSLT processor, XPath engine, and xml SQL utility (xsu ).

XML analyzer can be accessed through xmlparser and xmldom packages. Xsu is a Java utility that generates XML documents by SQL query results or JDBC resultset and writes data in XML documents to database tables or views. Xsu and XML output can be output as text, DOM tree, or DTs. Xsu can be used for PL/SQL through the dbms_xmlquery and dbms_xmlsave packages.

Conclusion

Oracle Database and Java VM can be integrated to create portable, powerful, and database-independent data logic and persistence logic ). The separation of business logic running on the middle layer and data logic running on the database layer improves the scalability, flexibility, and maintainability of applications.