Download
cs276 advanced oracle using java n.
Skip this Video
Loading SlideShow in 5 Seconds..
CS276 Advanced Oracle Using Java PowerPoint Presentation
Download Presentation
CS276 Advanced Oracle Using Java

CS276 Advanced Oracle Using Java

216 Views Download Presentation
Download Presentation

CS276 Advanced Oracle Using Java

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. CS276 Advanced Oracle Using Java Java Stored Procedures and Triggers Chapter 10

  2. Objectives • Outline the Architecture of the Oracle JVM • Will learn how to write , deploy , and run Java stored programs in the database. • Will learn how to use both the command-line tools and JDeveloper to deploy Java stored programs

  3. The Oracle JVM Architecture jvm_examples.sql (SQL*Plus script)- need to run if you want to follow along with the examples and programs examples in this chapter. This script adds a number of database items to the store_user schema that will be used to illustrate the use of the Oracle JVM.

  4. The Oracle JVM Architecture • The Oracle JVM is a complete Java Virtual Machine that conforms to the Java 2 Standard Edition (J2SE). • The Oracle JVM is intended to run Java stored programs in the same process and memory space as the database. • The Oracle JVM shares the same memory heaps and data structures as those used in the database.

  5. The Oracle JVM Architecture • The executable code of a Java stored program is also automatically cached in memory and may be shared among multiple database users. This reduces the overall memory requirements for a given application. • Performance boost - Oracle delivers the standard Java class libraries and the JDBC drivers precompiled as native binary code.

  6. The Oracle JVM Architecture NOTE • You can also precompile your own classes using the ncomp command-line tool, described in Appendix B; • Oracle jVM is designed to run back-end programs. It cannot be used to run graphical programs like applets directly because it doesn't come with any graphical Java components.

  7. The Oracle JVM Architecture NOTE • You can run Java 2 Enterprise Edition J2EE components using Oracle9iAS Containers for J2EE (OC4J). • OC4J allows you to run components like Enterprise JavaBeans (EJB), servlets, and JavaServer Pages JSP). • You can then use JDBC within these components to access the database. • Generally, J2EEcomponents are deployed in the middle tier of your system.

  8. The Oracle JVM Architecture The Oracle JVM consists of the following major software subsystems that make up its high-level architecture: • RDBMS library manager • RDBMS memory manager • Compiler • Bytecode interpreter and runtime system • Class loader • Verifier • Garbage Collector

  9. The Oracle JVM Architecture • RDBMS library manager Loads Java source, class, and resource files into the database as a result of running the loadjava command-line tool. As you'll see shortly, the loadjava tool is used to load Java files into the database. • RDBMS memory manager Allocates memory as Java programs are run. • Compiler Compiles the Java source files into Java classes (these classes contain bytecodes that the Oracle JVM is able to run). .

  10. The Oracle JVM Architecture • Bytecode interpreter and runtime system The bytecode interpreter reads the bytecodes from the Java classes, which are then run by the runtime system. • Class loader As Java stored programs are run, the runtime system makes requests to the class loader to load the required Java classes. The class loader then finds and loads the Java classes into the Oracle jVM.

  11. The Oracle JVM Architecture • Verifier Checks the Java classes to ensure that they don't attempt to alter program flow or violate Java's access restrictions. This protects the Oracle JVM from "spoofed" Java classes and ensures the security of the system. • Garbage collector Removes Java objects from memory when they are no longer needed. This memory cleanup is performed on a regular basis, which means that your Java objects may not be removed as soon as they are closed: they will stay in memory until the garbage collector runs.

  12. Features of Java Stored Programs Standalone Java applications we implemented so far have used the JDBC Thin driver to access the database. Java stored programs use either the JDBC server-side internal driver or the server-side Thin driver to communicate with a database. The server-side internal driver is used to communicate with the local database, this is the same database that the Java stored program is deployed to. The server-side Thin driver may be used to communicate with a remote database.

  13. Features of Java Stored Programs Unique features of Java stored programs • The JDBC server-side internal driver uses a built-in default connection to the local database. • This means that a Java stored program doesn't create a new connection to the local database because a connection is already provided through the server-side internal driver. see "Using the Default Database Connection."

  14. Features of Java Stored Programs Unique features of Java stored programs • The database connection through the server-side internal driver is permanent, and you cannot close the connection from a Java stored program. • If you close the Connection object used by a Java stored program using the close () method, you don't actually close the default database connection, but you will need to call getConnection () again prior to accessing the database.

  15. Features of Java Stored Programs Unique features of Java stored programs ( continue) • The server-side internal driver doesn't allow auto-commit functionality. • This means you have to perform a commit or rollback in your Java stored program; you should perform this commit (or rollback) outside of your Java stored program. By not having the Java stored program do the commit, you are free to perform the commit (or rollback) as part of the transaction to which a call to the Java stored program forms a part. You'll see an example of this later.

  16. Features of Java Stored Programs unique features of Java stored programs ( continue) • The Java class for your Java stored program doesn't need a main () method. • The output from calls to the Java System.out.println () method that would normally display output on the screen is sent to a database trace file by default. • This is because the Oracle JVM doesn't have direct access to the screen. • Fortunately, you can route such output to the screen of the client computer.

  17. Using the Default Database Connection Even though a default connection to the local database is provided through the server-side internal driver, your Java methods must still call the getConnection () method, but you don't specify a database user name and password. The Java method will use either the database privileges of the user who invokes the Java stored program, or the privileges of the user who creates the Java stored program.

  18. Features of Java Store Procedure: Using the Default Database Connection To use the default database connection, call the getConnection () method and store the returned Connection object Connection myConnection = DriverManager.getConnection( "jdbc :default: connection" ); Subsequent JDBC statements in your Java method can then use this Connection object to access the database.

  19. Features of Java Store Procedure: Output The database trace files contain log messages produced by the Oracle database software as it runs. These trace files are located in a directory specified in the database's init.ora file This file is normally only read and normally only modified by the DBA. init.ora file is located in a subdirectory of the admin directory where the Oracle database software is installed. For example: E: \Oracle\admin\ORCL\pfile, where ORCL is SID

  20. Features of Java Store Procedure: Output user_dump_dest – parameter that sets the directory for the trace files Example: user_dump_dest = E: \Oracle\admin\ORCL\udump max_dump_file_size - the maximum length of a trace file in bytes using the parameter; max_dump_file_size = 10240 • This means that the maximum size for each trace file is 10,240 bytes. When the file reaches this length, it is closed and a new one is opened.

  21. Features of Java Store Procedure: Output SQL *Plus command SHOW PARAMETER, followed by the parameter name - get the settings for user_dump_dest and max_dump_file_size . You must connect to the database as the system user (which has a default password of “tiger") to do this. • SQL> SHOW PARAMETER user_dump_dest NAME TYPE VALUE user_dump_dest string E:\Oracle\admin\ORCL\udump SQL> SHOW PARAMETER max_dump_file_size NAME TYPE VALUE max_dump_file_size string 10240

  22. Features of Java Store Procedure: Output • If a Java stored program calls the System.out.println () method, by default its output will be sent to the current database trace file. • If you are running your Java stored program through SQL *Plus, you can set up a buffer using the PL/SQL procedure dbms_java.set_output() . This buffer can accept output from your Java stored program's calls to System.out.println(), and you can display the contents of the buffer using SQL *Plus.

  23. Features of Java Store Procedure: Output NOTE • You can also use the PL/SQL dbms­output.enable () procedure, which creates a buffer that accepts output from calls to the PL/SQL dbms_output.put_line () procedure, which may also be used to display output.

  24. Features of Java Store Procedure: Output • The dbms_java.set_output () procedure (and the dbms_output.enable() procedure) accepts an integer parameter that specifies the size of the buffer in bytes. For example, let's say you wanted a buffer that is 2,000 bytes in size, then you would use the following PUSQL call to set it up: • The following JDBC statement does the same thing: callableStatement myCallableStatement = myConnection.prepareCall( "{call dbms_java.set_ouput (2000)}" ) ;

  25. Features of Java Store Procedure: Output Once you have buffer, all output form calls to System.out.println() will be sent to the buffer instead of the trace file. In SQL* Plus SET SERVEROUTPUT ON – should be on. When Java Stored Procedure is finished, the output will be displayed on the screen.

  26. A Worked Example JvmExample1 .java It illustrates how to use the default database connection, set up output buffers, and perform other database operations. The file JvmExample1.java defines a class named JvmExample1 that contains five methods (four procedures and one function).

  27. A Worked Example These five methods are as follows: • displayMessagelnTraceFile() • Calls System.out.println() • to show that, by default, output goes to the current database trace file. • displayMessageOnScreen() Calls dbms_java. set_output() • to set up a buffer and then calls System.out.println() • to show that output has been routed to the buffer. • It also calls dbms_output. enable() and dbms_output.put_line() • to show that output from PL/SQL can also be routed to the buffer. • The buffer contents are subsequently displayed on the screen.

  28. A Worked Example • displayProduct() • Displays the column values for a row in the products table. • The id column for that row is specified by a parameter. • It then sets up a buffer using dbms_java.set_output(), • Retrieves the column values for the specified row, • Finally, writes the column values to the buffer.

  29. A Worked Example • addProduct() • Adds a row to the products table, with the values for the type_id, name, description, and price columns being passed as parameters. • The value for the new row's id column is generated by first retrieving the highest id value from the products table using the SQL MAX () function, and then adding 1 to that number. • The procedure then adds the new row using an INSERT statement. • This procedure intentionally does not perform a commit; this way, you are free to perform the commit (or rollback) as part of a transaction of which a call to the Java stored program forms a part.

  30. A Worked Example • countProducts() Counts the number of rows in the products table using the SQL COUNT () function and then returns the result.

  31. A Worked Example To Develop and Compile Java classes use: • JDeveloper • Text editor and compile the Java source file using the javac command line tool. Before you can run these methods using the Oracle JVM, you must perform the following three steps: 1. Compile the Java source file to produce a class file using either the command-line tool or JDeveloper. 2. Load the class file into the database using either the loadjava command-line tool or JDeveloper. 3. Publish the Java methods using PL/SQL

  32. A Worked Example • Publish the Java methods using PL/SQL, involves creating a procedure or function (known as a call specification or simply a call spec) for each method in your Java class that you want to make available database NOTE The call spec is also sometimes referred to as a wrapper because it "wraps around" your Java method.

  33. A Worked Example Steps to compile, load, and publish the five Java methods defined in the JvmExample1 .java source file using the command-line tools. Step 1:Compile the Java Source File javac JvmExample1.java This compiles JvmExample1.java and produces a class file named JvmExample1.class.

  34. A Worked Example Step 2: Load the Class File into the Database The following example loads JvmExample1.class into the store_user schema: loadjava -user store_user/store_password JvmExample1.class As you can see, the database user name and password are specified using the -user option. This creates a Java class object for the JvmExample1 class in the store user schema.

  35. A Worked Example NOTE A Java class object is a structure used by the database to represent the Java class and is not to be confused with a Java object, which is an instance of the Java class. By default, loadjava uses the JDBC OCI driver to communicate with the database when loading Java classes.

  36. A Worked Example Checking That the Java Class was Loaded Successfully into the Database Perform the following query using SQL *Plus (after connecting to the database as the same user you used to load the Java class): SELECT object_name, object_type FROM user_objects WHERE object_type = 'JAVA CLASS';

  37. A Worked Example The user_objects view contains a list of the objects owned by that user, a using the clause WHERE object_type = 'JAVA CLASS , will show the Java class objects owned by that user.

  38. A Worked Example The output of the query run using SQL* Plus OBJECT_NAME -----------------­ OBJECT TYPE -----------------­ JvmExample1 JAVA CLASS This shows that the JvmExample1 class was loaded successfully into the database schema.

  39. A Worked Example • Loading Packaged Java Classes • Java classes can also be part of a Java package which allows you to group your classes together, much like you can group PL/SQL procedures and functions together into packages. • For example • MyClass that is part of a Java package named MyPackage • This give fully qualified name of • MyPackage. MyClass for that class. • If you load that into the database • the dots (.) that separate the package and class name are replace by forward slashes (/) in the resulting database Java class name.

  40. A Worked Example • Loading Packaged Java Classes • For example, Mypackage . MyClass would become MyPackage/MyClass when loaded into the database. • If the package and class name in your Java source file exceeds 30 characters contains characters that can't be converted to a character in the character set by the database, then a short name for the class is automatically produced by the database to identify the Java class in the schema.

  41. A Worked Example • LoadingMultipleJavaClasses • Load more than one class together using loadjava • if you had two class files MyClass1.class and MyClass2.class, the following command would identify those two class files using a wildcard asterisk character (*) in place of the number: loadjava –user_store_user/store_password MyClass*.class

  42. A Worked Example • You could also create a Java Archive JAR file and then load that JAR file using loadjava. • A JAR file may contain many class files. • To create a JAR file use the jar command-line tool. The following example shows the of the jar tool to create a JAR file that contains the MyClass1.class MyClass2.class files: jar –cf MyJarFi1e.jar MyC1ass*.c1ass • -c option specifies that the class files are to be compressed, • -f option allows you to specify the file name for the JAR file. In this example, the JAR is named MyJarFile. jar.

  43. A Worked Example load the JAR file into the database use loadjava, for example: loadjava -user store_user/store-password MyJarFi1e.jar

  44. A Worked Example Loading Java Classes into a Remote Database • The previous examples assumed you were loading class files into a database running on the same machine that loadjava is running on. If your database is running on a remote machine accessible through Oracle Net, you have to specify the location of that database en running loadjava. The database location may be specified using a database URL (Chapter 3). • The following is an example of a database URL: • remotehost: 1521 :ORCL. • The machine is identified as remotehost, with an Oracle Net listener assumed to be waiting for connection requests on port 1521 for the database with a System Identifier (SID) of ORCL.

  45. A Worked Example Loading Java Classes into a Remote Database ( continue) specifying JDBC driver is used by loadjava when communicating with the database: -thin for the Thin driver, -oci for the OCI driver (the OCI driver is the default one used). loadjava –user store_user/store_password@remotehost:1521:0RCL JvmExample1.class -thin

  46. A Worked Example Loading Java Classes into a Remote Database ( continue) NOTE You can use the machine name localhost in a database URL to identify your local machine. To replace a Java class load the class again using loadjava.

  47. A Worked Example Dropping Java Classes from the Database • dropjava command-line tool - remove Java classes from the database dropjava -user store_user/store_password JvmExample1.class • You can also drop Java classes from a remote database, for example: dropjava -user store_user/store_password@remotehost:1521:0RCL JvmExample1.class -thin

  48. A Worked Example Dropping Java Classes from the Database ( continue) • To verify that the Java class has indeed been dropped from the database repeat the query previously used to verify that the class was loaded. • If you loaded a JAR file, you should specify that JAR file when using dropjava. • The following command drops classes loaded from a JAR file named MyJarFile.jar: dropjava -user store_user/store_password MyJarFile.jar

  49. A Worked Example • Step 3: Publish the Java Methods Using PL/SQL • publish Java methods by creating procedures and functions using PL/SQL. these PL/SQL procedures and functions are referred to as call specifications or call specs, and you use these call specs to invoke your Java methods. They are also sometimes referred to as wrappers. • You can group your call specs into PL/SQL packages. This allows you to group the call specs for your Java class methods together in one place, and it can make maintenance easier. I'll show you how to group call specs into PL/SQL packages later in this chapter.

  50. A Worked Example Step 3: Publish the Java Methods Using PUSQL( continue) • Use the PL/SQL CREATE PROCEDURE statement to create a call spec for a Java procedure in your class • Use the PL/SQL CREATE FUNCTION statement to create a call spec for a Java function. • The syntax for creating a call spec using a CREATE PROCEDURE or CREATE FUNCTION statement is as follows: