Stored Procedures written in SQL still achieve the maximum performance boost, but Java Stored Procedures support a richer programming language, third-party libraries and an easy migration path from client-side code to server-side extension.
A Java method used as a Stored Procedure must have these features to be successfully bound to the procedure:
|For each parameter of the stored procedure there must be a parameter |
in the Java method of the corresponding type as listed in this table:
|SQL Type||IN Parameter||INOUT or OUT Parameter|
|Argument #N |
(if the stored procedure
specifies the RETURNS
When the stored procedure parameter is an INOUT or OUT parameter, the Java parameter is defined as an array that at runtime will be assigned to an array of one element only. This way, if the Java extension code reassigns the element of the array to a different object, the caller of the method can access the modified value.
If the procedure has the
TABLE clause, the corresponding Java method must have a final
java.sql.ResultSet argument that can be used to populate the return table. The user can also replace the object with a new ResultSet obtained by running a query.
Step 1 Create Java Class
Step 2 Compile the File
Compile the file created in Step 1 and package it into a JAR archive.
Step 3 Create javaclass in NuoSQL
To load the javaclass into memory, run the following command in NuoSQL (authenticated as a user with the DBA role), where
myfirstjavaproc.jar is the JAR file compiled in Step 2.
For a full description of syntax and parameters, see CREATE JAVACLASS.
Step 4 Create a NuoSQL Stored Procedure
For a full description of syntax and parameters, see CREATE PROCEDURE.
Step 5 Invoke the Java Stored Procedure
For a full description of syntax and parameters, see EXECUTE.
Step 6 Unload the Java Class
When the jar file isn't needed anymore, you can unload it unload by running the following NuoSQL command:
See DROP JAVACLASS.
In general, a Java stored procedure could perform any operation that Java allows. In practice though, because the Java code is being hosted by the TE process, a few operations are prohibited via a custom SecurityManager. Thee types of operations do not work with Java Stored Procedures:
- Loading native code
- Killing the current process
- Launching new processes
- Defining classes dynamically or creating new class loaders
- Accessing files from the file system
- Modifying system properties
- Accessing the printer or the clipboard
- Creating server sockets
See API Restrictions for important information information about differences between the NuoDB JDBC Driver API and the Embedded Java API used by Java Stored Procedures and Triggers.