Communicate SQL, XML, Java, J2EE, and Web services using Java stored procedures.
Stored Procedure Allows the operational logic running in the database layer with business logic running in the intermediate layer to leave. This separation can reduce the complexity of the entire application and provide its reuse, security, performance, and scalability.
However, a major obstacle to hindering storage procedures is a variety of proprietary and relying on database implementations of different database vendors. Use Java-based stored procedures to solve this problem. Oracle has implemented ANSI standards, which specifies the ability to call static Java methods as process or functions from SQL. This implementation is simply referred to as "Java Store".
In this article, you will understand how Java-based stored procedures help simplify business logic, improve its performance, and expand the functionality of the database. This article describes how Oracle enables Java-based stored procedures within the database. The Java stored procedure will also be described, and how to create a basic Java stored procedure.
Choose PL / SQL or Java
When considering the Oracle stored procedure, you may think of PL / SQL. However, from Oracle8i, Oracle has supported Java in the database, providing a stored procedure to the open and portable ways of PL / SQL. I can hear "$ 64 000 issues": "How do I make a choice between PL / SQL and JAVA? Do I have to forget all PL / SQL related knowledge that has been learned, and become a new hand of Java World?"
Both languages apply to database programming, all have their own advantages and weaknesses. When deciding which language to choose, you can refer to the general rules obtained by the experience:
For database centers that require seamless integration with SQL, logic uses PL / SQL to complete access to database objects, types, and features. When considering the irrelevant to the database, Java can be selected as an open language to replace PL / SQL, and also to integrate and communicate all fields such as SQL, XML, J2EE, and Web services.
OralceJVM makes Java can run in the database
From ORACLE8I version 1 (Oralce8.1.5), Oracle provides a closely set Java Virtual Machine (JVM), JVM supports Oralce's database session structure. Any database dialog period can initiate a virtual on-specific JVM when calling the first Java code, and subsequent users can use this already existing session period that supports Java. In fact, all sessions share the same JVM code and keep "only static" private status, and waste is collected in a single conversation space, providing the same dialog isolation and data integrity of each Java to SQL operation. ability. Here, there is no need for a separate Java support for data integrity. This structure-based structure provides a smaller memory usage, and makes OracleJVM have the same linear SMP scalability as the Oracle database.
Create a Java stored procedure
Several steps to convert Java methods to Java stored procedures, including: Loading Java classes into the database with the loadjava utility, using the call specification (CALL SPEC) to publish the Java method, parameter type, and return type to the Type The corresponding part of its SQL. The following sections explain how to complete these steps.
I will use a simple Hello class, it has a method hello.World (), returns a 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 verify the bytecode and place the Java class and JAR file into the database. It can be called both by command line, or by the loadjava () method included in the DBMS_JAVA class. In order to load our Hello.class example, enter:
Loadjava -user scott / tiger hello.class
Starting from Oracle9i Version 2, LoadJava allows the Java class to store the Java class as a stored procedure by creating the corresponding CALL SPECS to create a corresponding CALL SPECS for methods in the processed class. Oracle provides Oracle9i JDeveloper for the development, test, debugging, and arranged Java stored procedures.
The Resolver Spec
JDK-based JVM looks out in the directory listed in the ClassPath and parses it. Because the Oracle database class exists in database mode, OracleJVM uses the database parser (Resolver) to find and resolve class references through the mode in Resolver SPEC. Unlike ClassPath (ClassPath can be applied to all classes), ResOver SPEC is applied according to the cases per class. The default parser first searches classes in the load class, and then searches in public synons (Public Synonyms).
Loadjava -Resolve
You may need to specify a different parser, or you can force the parsing when using LoadJava, making it possible to determine any problems that may occur during the future.
Loadjava-Reesolve-ResoLver ((* Scott) (Foo / Bar / * Others)
(* Public)) "
Call Spec and Storage Procedure Call
In order to call the Java method from SQL (and from PL / SQL and JDBC), a public static method must first be released through the Call SPEC, which is the parameters employed by the SQL definition method and the returned SQL type.
In our example, we will use SQL * Plus to connect to the database and define a top Call Spec for Hello.World ():
SQL> Connect Scott / Tiger
SQL> Create or Replace Function HelloWorld Return
VARCHAR2 As Language Java Name 'Hello.World () Return
Java.lang.string ';
/
Function created.
You can call the Java stored procedure like this:
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 items: SQL DML statements (INSERT, UPDATE, DELETE, SELECT, CALL, EXPLAIN, LOCK TABLE, PL / SQL block, subroutine, package, Database trigger. The beauty of Call Spec is that the stored procedure can be converted from PL / SQL to Java, and vice versa, this is transparent to the requester.
Call SPEC is abstracted in the implementation language (PL / SQL or Java), thus allowing it to share business logic between original applications and new Java / J2EE-based applications. However, when you call the Java class where you reside from the Java client, you may not want to pass through the PL / SQL wrapper (Wrapper). In the later version, the Oracle program provides a mechanism that allows the developer to slightly Call Spec. Advanced Data Access Control
The Java stored procedure can be used to control and limit access to Oracle data, which is only allowed to be executed by stored procedures, while stored procedures execute within their caller, and cannot access tables. For example, you can disable you update data within a specific time, or enable the manager to have the right to query the salary data, and not update, or record all access and inform a security mechanism.
The original application and the data logic sharing between J2EE applications
Because the original application is called by the J2EE application through the Call SPEC, the J2EE and the non-J2EE application can share the same data logic. Because of the Call SPEC, you don't have to consider what is implemented (whether PL / SQL or Java), this data logic can be shared.
Automatically generate primary keywords for BMP entity beans
When BMP is applied to the EJB entity bean, a bean instance can be determined by the automatically generated primary keyword associated with the newly inserted data, which is the return value of EJBCREATE (). This value in ejbceater () can be retrieved in a database operation using a stored procedure inserted into the corresponding data, and retrieves or calculates the primary key. As another method, the RETURN_GENERATED_KEYS feature of JDBC3.0 can also be utilized, and the data is inserted into the data with a SQL statement and retrieves the corresponding keyword (or RowID). However, the stored procedure method is more portable between the various JDBC drivers and the database.
This mode can be achieved by using the following three steps:
Create a Java stored procedure, define a common static Java method inSertaccount () in the public Genpk class. This method will insert data, calculate the unique keyword (by issuing a serial number) and return the calculated keyword as the primary key. 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 []) ';
/
In EJBCREATE () internal calling process Public Accountpk Ejbcreate (String OwnerName, Int Balance) Throws CreateException
{
Try {
Callablestatement call = conn.preparecall {
"{CALL INSERTACCOUNT (?,?,?)}"};
Return New Accountpk (Accountid);
}
}
Custom keyword finder for CMP entity bean
Finder Methods is used to retrieve existing EJB entity bean instances. The main keyword finder enables you to retrieve the unique EJB instance. For the CMP entity bean, the EJB container automatically generates the main keyword finder FindbyPrimaryKey () method according to the declaration description. However, in some cases, more control may be needed, for example, a special lookup, such as FindByStoredProckey (). In these cases, you can use the Java stored procedure and object relationship framework (such as Oracle9i Application Server [Oracle9ias] Toplink) to implement custom primary keyword finder methods. After defining the EJB lookup as the Redirect or NAMED lookup, Toplink generates a SQL query for retrieving the Bean instance. Data-driven EJB call
In the data drive architecture, business logic calls can be triggered as a result of database operations such as insertion, update, or deletions. The Java stored procedure implemented for this data logic can be declared as a database trigger to call the EJB running in the intermediate layer J2EE application server. EJB calls can be implemented using J2EE1.3 compatible servers through Interoperable Inter-ORB Protocol (IIOP) standard remote method call (RMI), or by vendor-specific transport protocols (such as ORACLE9IAS / OC4J ORMI) Or through the T3 of Bea WebLogic is implemented with RMI. Each application server provider has its own optimized protocol while providing IIOP-based RMI to provide interoperability. Oracle9ias also supports IIOP-based RMI calls and ORMI protocol-based RMI calls.
Data-driven message transfer
Oracle9i Database embedded Advanced Queuing (AQ, Advanced Queuing), which is an integrated, stable, reliable, secure, scalable and transaction-proof message queuing framework. Oracle provides AQ features for Java developers through standard Java Messaging System, JMS) API. The Java stored procedure can call AQ operations through the JMS interface, so that fast, in the session period, scalable, data-driven message transmission.
The Java stored procedure can use JMS to call AQ operations. This mode can be achieved by the following four steps:
Create and start JMS Queue (for this, you can embed the following operations within the SQL script): Execute DBMS_AQADM.CREATE_QUE_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 (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 "'");
..}
}
Create Call Spec: Create or Replace Procedure JMSProc (T1 In Varchar)
As Language Java Name 'JMssample.main (java.lang.string [])';
/
Call the stored procedure: Call JMSProc ('Hello');
Database assisted Web release (buffer failure)
One common problem that each application structure must face is to cache database information reliably to improve the performance of the entire system. JCache is an upcoming standard specification (JSR 107), which can solve this problem. It illustrates a method of cache a Java object to caches, including object creation, sharing access, spooling, failure, and respective JVM consistency. It can be used to cache data that is the most frequently read in JSP, such as product catalog and price list. Using JCache, most queries will speed up due to cached data (internal tests indicate that the reaction time is approximately 15 times).
In order to track all changes in the original data, the cached data is refreshed, and the Java stored procedure is attached to a table as a trigger. Any changes to this table automatically calls the stored procedure, which calls up a defined JSP to fail the JCAChe object, which maps its status to the database table. In the event of failure, keep back with the query will force the cache to update according to the data of the database.
Extended the functionality of the database
One thing to run Java code directly in the database is to implement new features, just simply load code or library, and use Call Spec to make SQL, PL / SQL, Java, J2EE and non-Java API enters Point (public static method). Oracle9i database users can easily extend database functions. Oracle uses this capability to get new applications and kits, such as XML Developer Kits (XDKs).
Communicate SQL, PL / SQL, Java, J2EE, .NET and XML
Oracle XDK is written in Java and uses its public method as a Java stored procedure, thereby expanding the XML programmable ability of the database. SQL, PL / SQL, JAVA, J2EE, and non-Java (.NET) business logic can access XML analyzer, XSLT processor, XPath Engine, and XML SQL Utility (XSU). XML analyzers can be accessed through XMLParser and XMLDOM packages. XSU is a Java utility that can generate an XML document by SQL query results or JDBC ResultSet and write data in the XML document into database tables or views. With XSU, XML output can be output as text, DOM tree or DTS. The XSU can be used for PL / SQL via DBMS_XMLQuery and DBMS_XMLSAVE packets.
in conclusion
Oracle Database Integration of Java VM can create portable, powerful and database-independent data logic and persistence logic. Separation of business logic running in the intermediate layer and the separation between the data logic running on the database layer improves the application's scalability, flexibility, and maintainability.
