CN103092587A - Java stored procedure database simplifying operation method - Google Patents

Abstract

According to a Java stored procedure database simplifying operation method, Java stored procedure can be used to communicate SQL (Structured Query Language), XML (Extensive Makeup Language), Java, J2EE (Java 2 Platform Enterprise Edition) and Web services. The stored procedure allows separation of persistence logic operated in database tier from business logic operated on middle tier, so as to reduce complexity of whole application program and provide reusability, security, performance and scalability. However, one major barrier which prevents wide application of stored procedure is that different database vendors use a variety of proprietary implementation languages depending on database. The problem can be solved by the stored procedure based on Java. Oracle realizes ANSI (American National Standards Intuition) standard which formulates to use static Java method in SQL as procedure or function invoking ability, the realization of ANSI is simply named Java stored procedure.

Description

A kind of Java storing process reduced data storehouse method of operating

Technical field

The present invention relates to a kind of Java storing process reduced data storehouse method of operating, this technology is mainly to utilize JAVA and database store process to realize.

Background technology

How storing process based on Java helps to simplify business logic, improve its performance, and the function of extending database.How Oracle enables the storing process based on Java in database.The Java storing process is visit data how, and how to create basic Java storing process.

Select PL/SQL or Java when considering the Oracle storing process, may expect PL/SQL.Oracle has supported Java in database, thereby provides for storing process the open and transplantable method that is different from PL/SQL.

It is various proprietary and depend on the implementation language of database that a major obstacle that hinders storing process extensively to adopt is that disparate databases manufacturer uses.

Summary of the invention

Based on the problems referred to above, hereby invent a kind of Java storing process reduced data storehouse method of operating; For requiring with SQL carries out seamless integrated database hub logic to use PL/SQL, thus the access of database object, type and characteristic.

When considering with the independence of database, can select Java to replace PL/SQL as open language, simultaneously also for integrated and link up the every field such as SQL, XML, J2EE and Web service.Any database session can start when a Java code call one virtual special-purpose JVM, follow-up user can use the session phase of this support Java that has existed.In fact, all sessions are shared same JVM code and are kept the privately owned state of " only static ", and rubbish is collected in space of single session, thereby provide session isolation and the data integrity sexuality identical with the SQL operation for each Java session.Here, need to not carry out for data integrity the process that independent Java supports.This structure based on session provides less memory usage, and makes OracleJVM have the linear SMP scalability the same with oracle database.Establishment Java storing process will be converted to the Java method Java storing process needs several steps, comprise: with the loadjava utility routine, java class is loaded in database, standard (Call Spec) issue Java method is called in utilization, Java method, parameter type and return type is mapped to the corresponding part of its SQL.

Concrete methods of realizing

To use a simple Hello class, it has a method Hello.world (), return string " Hello world ": public class Hello{public static String world () { return﹠amp; Quot; Hello world "; The Loadjava utility routine, Loadjava is the utility routine that loads Java source file, java class file and Java resource file, it can be used for verifying bytecode, and java class and jar file are arranged in database.It both can call by order line, also can be by being contained in the loadjava () method call in DBMS JAVA class.In order to load our Hello.class example, input: loadjava-user scott/tiger Hello.class is from the Oracle9i version 2, and loadjava allows by coming automatically java class to be issued as storing process for the corresponding Call Specs of method establishment that is included in processed class.Oracle is for exploitation, test, debugging and arrange that the Java storing process provides Oracle9i JDeveloper.Search class reference in the catalogue of JVM in listing in CLASSPATH of The Resolver Spec based on JDK, and it is resolved.Because the oracle database class is present in database schema, so OracleJVM utilizes database resolver (resolver) to search and resolve class reference by the pattern of listing in Resolver Spec.Different from CLASSPATH (CLASSPATH can be applied to all classes), Resover Spec uses according to the situation of every class.At first default resolver searches class in the pattern of loading classes, then search in public synonym (public synonyms).Loadjava-resolve＜myclass〉you may need to specify different resolvers, also can force resolve when using loadjava, thus any problem that occurs when determining to move afterwards when arranging.Loadjava-resolve-resolver " ((* SCOTT) be (* PUBLIC) (foo/bar/*OTHERS)) " Call Spec and call stored procedure are for calling Java method from SQL (and calling from PL/SQ1 and JDBC), must be at first by the public static method of Call Spec issue, it is the parameter of SQL define method employing and the SQL type of returning.In our example, we will utilize SQL*Plus to be connected to database, and be top Call Spec of Hello.world () definition:

SQL＞connect?scott/tiger?SQL＞create?or?replace?function?helloworld?return?VARCHAR2?as?language?java?name’Hello.world()return?java.lang.String’；/Function?created.

Can call the Java storing process as following:

SQL＞variable myString varchar2[20]; SQL＞call helloworld () into:myString; Call completed.SQL＞print myString; MYSTRING---------------------Hello world Java storing process can call from the following by its Call Spec: SQL DML statement (INSERT, UPDATE, DELETE, SELECT, CALL, EXPLAIN PLAN, LOCK TABLE and MERGE), PL/SQL piece, subroutine, routine package and database trigger.The beautiful part of Call Spec is that the storing process realization can be converted to Java from PL/SQL, and vice versa, and this point is transparent for the requestor.Call Spec takes out in (PL/SQL or Java) from implementation language and calls the interface, thereby enables in original application program and new share business logic between based on the application program of Java/J2EE.But when being invoked at the resident java class of database from the Java CLIENT PROGRAM, you may not wish by PL/SQL wrapper (wrapper).In version afterwards, the Oracle plan provides a kind of mechanism, and it can make the developer skip over Call Spec.High-level data access control Java storing process can be used for controlling and limit the access to the Oracle data, and its method is only to allow the user to pass through the storing process management data, and storing process is carried out in the authority of its caller, and can not prevent asking by his-and-hers watches itself.

For BMP entity B ean generates primary key automatically when EJB entity bean is used BMP, a bean example can be only definite by the primary key that is associated with data new insertion automatic generation, and it is the rreturn value of ejbCreate ().Can utilize a storing process that inserts corresponding data this value retrieve ejbCeater () in a database manipulation in, and retrieval or calculate primary key.As another kind of method, also can utilize the RETURN_GENERATED_KEYS characteristic of JDBC3.0, insert these data and retrieve corresponding key word (or ROWID) with a SQL statement.But the storing process method has more portability between each JDBC driver versions and database.Can realize this pattern with following three steps;＞

Create a Java storing process, a definition public static Java method insertAccount () in public GenPk class.The method with data inserting, calculate only key word (by sending a sequence number), and return to the key word that calculates as primary key.Definition 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 Stored Procedure Public AccountPKejbCreate (String ownerName in ejbCreate (), int balance) throws CreateException{try{CallableStatement call=conn.prepareCall{ " callinsertAccount (?;?, ?) " }; Return new AccountPK (accountID);

For CMP entity B ean customization primary key finger finger method (Finder methods) is used for retrieving already present EJB entity bean example.The primary key finger makes you can retrieve the EJB example of only sign.For CMP entity bean, Enterprise Java Bean container is described according to statement, automatically generates primary key finger findByPrimaryKey () method.But, in some cases, may need more control, for example may need special finger, as findByStoredProcKey ().In these cases, you can be combined with Java storing process and object relationship framework (as Oracle9i application server [Oracle9iAS] TopLink) and realize the primary key finger method that customizes.After the EJB finger is defined as REDIRECT or NAMED finger, TopLink will generate a SQL query and be used for retrieval bean example.The EJB of data-driven is invoked in data-driven architecture, and business logic is called the result that can be used as database manipulation (as inserting, upgrade or deletion) and triggered.The Java storing process of realizing this mathematical logic can be declared as database trigger, in order to call the EJB that runs on middle layer J2EE application server.The server that both can adopt the J2EE1.3 compatibility that calls of EJB passes through Interoperable Inter-ORBProtocol (IIOP) standard remote method invocation (RMI) (remote methodinvocation, RMI) realize, also can pass through the specific host-host protocol of dealer (as the ORMI of Oracle9iAS/Oc4J, perhaps the T3 by BEA WebLogic) realizes with RMI.Each application server provider is at the RMI that provides based on IIOP, when interoperability to be provided, and the agreement that has it oneself to optimize.Oracle9iAS supports simultaneously the RMI based on IIO P to call and calls based on the RMI of ORMI agreement.

The message of data-driven transmits the Oracle9i database and has embedded Advanced Queuing (AQ, senior queuing), it be a kind of integrated, stable, reliable, safe, can expand and the message queueing framework of issued transaction formula.Oracle provides the AQ function by Java message transfer service (Java Messaging System, the JMS) API of standard for Java developer.The Java storing process can be by JMS interface interchange AQ operation, thus can realize fast, within the session phase, message extendible, data-driven transmits.The Java storing process can utilize JMS to call the AQ operation.Can realize this pattern with following 4 steps:

Create and start JMS Queue (for this reason, can be with in following certain operations embedded SQL script): execute dbms_aqadm.create_queue_table (queue_table=＞' queuel ', queue_payload_type=＞' SYS.AQ $ _ JMS_TEXT_MESSAGE ', comment=﹠amp; Gt; ' a test queue ', multiple_consumers=＞false, compatible=＞' 8.1.0 '); Execute dbms_aqadm.create_queue (queue_name=＞' queuel ', queue_table=＞' queue1 '); Execute dbms_aqadm.start_queue (queue_name=＞' queue1 '); Establishment Java storing process (code is taken passages 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 (); //outputmessage 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[]) '; / Calling Stored Procedure: call jmsproc (' hello '); The common issue with that auxiliary Web issue (buffer invalidation) each application structure of database must be faced is if reliably database information is carried out buffer memory, to improve the performance of whole system.JCACHE is a kind of standard criterion (JSR 107) that is about to announcement, and it can address this problem.It has illustrated a kind ofly carries out the method for buffer memory to the Java object temporarily in internal memory, comprise establishment, share and access, automatic spool (spooling), the inefficacy of object, consistance of each JVM etc.It can be used in buffer memory JSP. and directly a pleasant place of operation Java code just is to realize new function in database, only need loading code or storehouse simply, and utilize Call Spec to make the inlet point (public static method) that can be used for SQL, PL/SQL, Java, J2EE and non-Java API.The Oracle9i database user is the growth data library facility at an easy rate.Oracle oneself utilizes this ability to obtain new application program and kit, as XMLDeveloper Kits (XDKs).Link up SQL, PL/SQL, Java, J2EE .NET and XMLOracle XDK and write with Java, and its public method be can be used as the Java storing process, thereby expanded the XML programmability of database.SQL, PL/SQL, Java, J2EE and non-Java (.NET) business logic can both be accessed XM L analyzer, xslt processor, XPath engine and XML SQL Utility (XSU).The XML analyzer can conduct interviews by xmlparser and xmldom bag.XSU is a kind of Java utility routine, and it can generate XML document by SQL query result or JDBC ResultSet, and with in data write into Databasce table or view in XML document.Utilize XSU, XML output can be output as text, Dom tree or DTS.By dbms_xmlquery and dbms_xmls ave bag, XSU namely can be used for PL/SQL.

Claims (2)

1. Java storing process reduced data storehouse method of operating: the method is mainly to utilize storage process between JAVA and the Oracle method that simplifies the operation.

2. according to claim 1 Java storing process reduced data storehouse method of operating, the method are mainly for requiring with SQL carries out seamless integrated database hub logic to use PL/SQL, thus the access of database object, type and characteristic.