Oracle developer Java Stored procedures

oracle| Stored Procedures

Oracle developer Java Stored procedures
Simplifying database operations with Java stored procedures

Author: Kuassi Mensah

Leverage Java stored procedures to communicate SQL, XML, Java, EE, and Web services.

Stored procedures (stored procedure) allow the persistence logic running in the database layer to be effectively detached from the business logic running in the middle tier. This separation can reduce the complexity of the entire application and provide its reusability, security, performance, and scalability.

However, a major impediment to the widespread adoption of stored procedures is the use of various proprietary, database-dependent implementation languages by different database vendors. Using a java-based stored procedure can solve this problem. Oracle has implemented the ANSI standard, which prescribes the ability to invoke a static Java method as a procedure or function from SQL. This implementation is simply referred to as a "Java stored procedure."

In this article, you will learn how java-based stored procedures can help simplify business logic, improve performance, and extend the functionality of your database. This article describes how Oracle enables Java-based stored procedures within a database. It also describes how Java stored procedures Access data, and how to create basic Java stored procedures.

Choose Pl/sql or Java

When considering Oracle stored procedures, you may think of Pl/sql. However, starting with Oracle8i, Oracle has supported Java in the database, providing an open and portable approach that differs from Pl/sql for stored procedures. I can hear "$64 000 questions": "How do I make a choice between Pl/sql and Java?" Should I forget all the pl/sql related knowledge I have learned and become a novice in Java world? "

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


The logical use of Pl/sql for database centers that require seamless integration with SQL completes access to database objects, types, and attributes.


For reasons unrelated to the database, Java can be chosen as an open language to replace Pl/sql, as well as to integrate and communicate the fields of SQL, XML, Java EE, and Web services.
ORALCEJVM enables Java to run in the database

Starting with Oracle8i version 1 (Oralce8.1.5), Oracle provides tightly integrated Java virtual machines (JVMs), and the JVM supports the ORALCE database session structure. Any database session can start a virtual, dedicated JVM when the first Java code is called, and subsequent users can use this existing Java-enabled conversation period. In fact, all sessions share the same JVM code and maintain a "static only" private state, while garbage is collected in a single conversation space, providing the same period of conversation isolation and data integrity capabilities for each of the Java dialogs. Here, there is no need for a separate Java support process for data integrity. This dialog-based architecture provides a smaller memory footprint and enables ORACLEJVM to have the same linear SMP scalability as an Oracle database.

To create a Java stored procedure

A few steps are required to convert a Java method to a Java stored procedure, including: Loading Java classes into a database with the Loadjava utility, publishing Java methods by invoking the specification (call spec), mapping Java methods, parameter types, and return types to their corresponding portions of SQL. The following sections explain how to complete these steps.

I'm going to use a simple Hello class that has a method Hello.world () that returns the string "Hello World":


public class Hello {public static String world () {return ' Hello World ';}}
Loadjava Utility

Loadjava is a utility that loads Java source files, Java class files, and Java resource files, which can be used to validate bytecode and to lay out Java classes and jar files in a database. It can be invoked either from 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

Starting with Oracle9i version 2, Loadjava allows a Java class to be published as a stored procedure by creating the corresponding call specs from the method contained in the class being processed. Oracle provides oracle9i JDeveloper for developing, testing, debugging, and laying out Java stored procedures.

The Resolver Spec

The JDK jvm finds and resolves a class reference in a directory that is listed in Classpath. Because Oracle database classes exist in the database schema, ORACLEJVM uses the database parser (resolver) to find and resolve class references through the schemas listed in resolver spec. Unlike classpath (Classpath can be applied to all classes), Resover spec is applied according to the circumstances of each class. The default parser first searches for the class in the schema of the load class, and then searches for the public synonyms (the common synonyms).


Loadjava-resolve <myclass>
You may need to specify a different parser, or you can force parsing when using Loadjava to determine any problems that may occur at a later time when you decorate.


Loadjava-resolve-resolver "((* SCOTT) (foo/bar/* others) (* public))"
Call spec and stored procedure calls

In order to invoke Java methods from SQL (as well as from Pl/sql and JDBC), you must first publish a public static method, using the call spec, for the parameters used by the SQL-defined method and the type of SQL returned.

In our example, we will use Sql*plus to connect to the database and define a top-level call Spec for Hello.world ():


Sql> Connect Scott/tiger sql> Create or Replace function HelloWorld return VARCHAR2 as language Java name ' Hello.wor LD () return java.lang.String '; /Function created.
You can invoke a 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 invoked from the call spec: SQL DML statements (INSERT, UPDATE, DELETE, SELECT, call, EXPLAIN plan, LOCK table, and merge), pl/sql block , subroutines, packages, and database triggers. The beauty of call spec is that stored procedure implementations can be converted from pl/sql to Java, and vice versa, which is transparent to the requestor.

Call Spec abstracts the calling interface from the implementation language (Pl/sql or Java), making it possible to share business logic between an existing application and a new, JAVA/J2EE-based application. However, you may not want to pass the Pl/sql wrapper (wrapper) When invoking Java classes that reside in a database from a Java client program. In future releases, the Oracle plan provides 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 only users to manage data through stored procedures, while stored procedures execute within the permissions of their callers and cannot access the table itself. For example, you can disable updating of data for a specific period of time, or make managers have only the right to inquire about payroll data, not to update them, or to record all visits and notify a security authority.

Logical sharing of data between legacy applications and Java EE applications

Because both the original application and the Java EE application invoke the stored procedure through call spec, the Java EE and non-Java applications can share the same data logic. Because of the call Spec, the data logic can be shared without considering what implementation language is used (whether Pl/sql or Java).

Automatically generate primary keywords for BMP entity beans

When BMP is applied to an EJB entity bean, a bean instance can be uniquely determined by an automatically generated primary key associated with the newly inserted data, which is the return value of Ejbcreate (). You can use a stored procedure that inserts the corresponding data to retrieve the value from Ejbceater () in a database operation and retrieve or compute the primary key. Alternatively, you can use the Return_generated_keys attribute of JDBC3.0 to insert the data in an SQL statement and retrieve the corresponding keyword (or rowid). However, stored procedure methods are more portable between the versions of each JDBC drive and the database.

You can implement this pattern in the following three steps:


Create a Java stored procedure that defines a public static Java method Insertaccount () in the public GENPK class. This method inserts the data, evaluates the unique keyword (by issuing a serial number), and returns the computed key as the primary keyword.


Define Call Spec


CREATE OR REPLACE PROCEDURE insertaccount (owner in varchar, bal in number, newid out number) as LANGUAGE JAVA NAME ' GENPK. Insertaccount (java.lang.String []) '; /


Calling a stored procedure within ejbcreate ()


Public ACCOUNTPK ejbcreate (String ownername, int balance) throws CreateException {try {callablestatement call = Conn.pre parecall{"{Call Insertaccount (?,?,?)}"}; return new ACCOUNTPK (AccountId); } }
Customizing the Primary keyword finder for CMP entity beans

The Finder method (a lookup methods) is used to retrieve an existing instance of an EJB entity bean. The Primary keyword Finder enables you to retrieve uniquely identified EJB instances. The CMP Entity BEAN,EJB container automatically generates the Primary keyword Finder Findbyprimarykey () method based on the declaration description. However, in some cases, more control may be required, such as a special finder, such as Findbystoredprockey (). In these cases, you can use the Java stored procedure and the Object Relational framework (such as the oracle9i application server [Oracle9iAS] toplink) to implement the custom primary keyword Finder method. After the EJB Finder is defined as a redirect or named Finder, TopLink generates an SQL query to retrieve the bean instance.

Data-driven EJB calls

In a data-driven architecture, business logic calls can be triggered as a result of database operations such as inserts, updates, or deletes. Java stored procedures that implement this data logic can be declared as database triggers to invoke EJBS running on the middle-tier Java application server. The invocation of an EJB can be implemented using both a j2ee1.3-compliant server and a interoperable Inter-ORB Protocol (IIOP) standard remote method call (remote methods Invocation,rmi). It can also be implemented using RMI via the vendor-specific transport protocol (such as oracle9ias/oc4j Ormi, or the T3 of Bea WebLogic). Each application server provider has its own optimized protocols while providing an IIOP RMI to provide interoperability. Oracle9iAS simultaneously supports RMI calls based on IIOP and RMI calls based on the Ormi protocol.

Data-driven Message delivery

The Oracle9i database is embedded in the Advanced Queuing (AQ, advanced queuing), an integrated, stable, reliable, secure, extensible, and transactional Message Queuing framework. Oracle provides AQ functionality to Java developers through the standard Java messaging System (Java Messaging SYSTEM,JMS) APIs. Java stored procedures can invoke AQ operations through the JMS interface to enable fast, session-time, scalable, data-driven messaging.

Java stored procedures can invoke AQ operations using JMS. You can implement this pattern in the following 4 steps:


Create and start the JMS Queue (for this, you can embed some of the following operations within 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 ');


Create a Java stored procedure (code excerpt below):


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 is" "+body+" ");} }


Create call Spec:


Create or Replace procedure Jmsproc (T1 in VARCHAR) as language Java name ' Jmssample.main (java.lang.string[]) '; /


To invoke a stored procedure:


Call Jmsproc (' Hello ');
Database-assisted Web publishing (buffering failure)

A common problem that must be faced by each application structure is the reliable caching of database information to improve the performance of the entire system. Jcache is an upcoming standard specification (JSR 107) that solves this problem. It describes a method for temporarily caching Java objects in memory, including object creation, shared access, spooling (spooling), expiration, and consistency of the JVM. It can be used to cache the most frequently read data in a JSP, such as a product catalog and a price list. With Jcache, the response time for most queries is accelerated by cached data (internal tests show that the reaction time is about 15 times times faster).

To keep track of all changes in the original data and to refresh the cached data, the Java stored procedure is appended to a table as a trigger. Any change in this table will automatically invoke the stored procedure, which then brings up a defined JSP to invalidate the Jcache object, which maps its state to the database table. When it fails, the query that follows immediately forces the buffer to update against the data in the database. The following steps
Read more about Java stored procedures
This digest is available from the white paper "Releasing the energy of the Java stored Procedures (unleash the power of Java Stored Procedures)", which can be found in the following locations:
otn.oracle.com/tech/java/java_db/pdf/
Ow_30820_java_stored_proc_paper. Pdf

New Pl/sql features in the 2nd edition of Oracle9i Database
otn.oracle.com/tech/pl_sql/pdf/
Paper_30720_doc.pdf

Resolver Spec
otn.oracle.com/docs/products/oracle9i/
Doc_library/release2/java.920/a96659.pdf

ORACLEJVM and Java 2 security
otn.oracle.com/docs/products/oracle9i/
Doc_library/release2/java.920/a96656.pdf

Download code
Code examples in the exercise book:
otn.oracle.com/sample_code/tech/
Java/jsp/oracle9ijspsamples.html

Understanding the stored procedures as Web services
Otn.oracle.com/tech/webservices



Extending the functionality of a database

One of the beauty of running Java code directly in a database is to implement new functionality by simply loading code or libraries and using call spec to make access points (public static methods) that can be used for SQL, Pl/sql, Java, EE, and non-Java APIs. Oracle9i database users can easily extend database functionality. Oracle uses this capability to get new applications and toolkits, such as XML Developer kits (XDKs).

Communication SQL, Pl/sql, Java, EE,. NET and XML

Oracle XDK is written in Java, and its public methods can be used as Java stored procedures to extend the XML programmability of the database. SQL, Pl/sql, Java, EE, and non-Java (. NET) business logic have access to XML parsers, XSLT processors, XPath engines, and XML SQL Utility (XSU).

XML parsers can be accessed through Xmlparser and XMLDOM packages. xSU is a Java utility that generates XML documents from SQL query results or JDBC resultset and writes data from an XML document to a database table or view. Use xsu,xml output to output as text, Dom tree, or DTS. The XSU can be used for pl/sql through Dbms_xmlquery and dbms_xmlsave packages.

Conclusion

Integration of Oracle Databases with Java VMS creates portable, powerful and database-independent logic and persistence logic (persistence logic). The separation between the business logic running in the middle tier and the logic of the data running at the database level improves the scalability, flexibility, and maintainability of the application.