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. Select 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 the "$ 64 000 problem": "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?" Language is applicable 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 allows Java to run from Oracle8i Version 1 (ORACE8.1.5) in the database, and 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. Creating a Java stored procedure To convert the Java method to the Java stored procedure, you need a few steps, including: Loading the Java class into the database with the loadjava utility, using the Call Spec) to release the Java method, will join the Java method, parameter type, and The return type is mapped to 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 Utilities LoadJava is a utility that loads Java source files, Java 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 is found in the directory listed in the ClassPath in the directory of the ClassPath and parses it based on JDK. 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 To call Java methods from SQL (and from PL / SQL and JDBC), you must first publish a common static method through the Call SPEC, which uses 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: SQL> Connect Scott / Tiger for Hello.World ()
SQL> Create or Replace Function HelloWorld Return
VARCHAR2 As Language Java Name 'Hello.World () Return
Java.lang.string ';
/
Function created.
You can call Java stored procedures like this: SQL> Variable mystring varcha2 [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 Java stored procedures can be used to control and limit access to Oracle data, which only allows users to execute them within their caller's permissions, without accessing table itself. 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 J2EE application data logic share because the original application is called with the J2EE application through the Call SPEC, so J2EE and non-J2EE applications 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 generating a primary key for BMP entity bean When applying BMP to an EJB entity bean, a bean instance can be determined by automatically generated primary keyword associated with newly inserted data, it is an EJBCREATE () return value. 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 implemented in the following three steps: Create a Java stored procedure, define a public static Java method inSertCount () 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);
}
}
For the CMP Entity Bean Custom Keyword Finder Finder Method (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 calls In the data drive architecture, business logical 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 messages Transfer Oracle9i database embedded in Advanced Queuing (AQ, Advanced Queuing), which is an integrated, stable, reliable, secure, scalable and transactional 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 implemented in the following four steps: Create and start JMS Queue (for this, you can embed the following operations): 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');
