Java程序辅导

CaSIT CSE5200 Distributed Database Systems 1
Semester 2, 2006, Tutorial 4 (Version 1.0, dated: 8/7/2006)
1 Java Review Questions
1. Go to the website java.sun.com/docs/books/tutorials and try the java tutorials.
2. Go to the website otn.oracle.com register and find the Oracle 10g Java Stored Procedures
documentation.
2 Java Stored Procedure Exercises
The objective of this exercise is to become familiar with Java and Oracle Java Stored Procedures
If you want to write SQL to access the Oracle database. NOTE: The server-side internal JDBC
driver runs within a default session and default transaction context. So, you are already “connected”
to the database, and all your SQL operations are part of the default transaction. You need not register
the driver because it comes pre-registered. To get a Connection object, simply execute the following
statement:
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
Create or Reuse the Java Classes
Use your favorite Java IDE to create classes, or simply reuse existing classes that meet your needs.
Oracle’s Java facilities support many Java development tools and client-side programmatic
interfaces. For example, the JServer JVM accepts programs developed in popular Java IDEs such as
Symantec’s Visual Cafe, Oracle’s JDeveloper, and Borland’s JBuilder.
In the example below, you create the public class Oscar. It has a single method named quote(),
which returns a quotation from Oscar Wilde.
Step 1. Create and test a connection in the lab
The following instructions are for the lab. (To perform this exercise at home you must have installed
Oracle and JDeveloper.)
1. open Jdeveloper (/jdev/bin/jdev.exe)
2. right click on connections and open new database connection.
3. Click on New
Connection Name - CON1_CSE5200A
Username s1234567 - your student number
Password student - your password
Host Name - LLAMA.ITS.MONASH.EDU.AU
SID CSE5200A
Note : use a different SID name for local databases (at home) and
not the name of the databases at Monash as it gets confusing.
Port 1521
Test click here
Communication between jdeveloper and oracle has now been established.
CaSIT CSE5200 Distributed Database Systems 2
Step 2. Creating the Java Stored Procedure
1. File>New
2. General > workspace
Directory Name: c:\temp\workspace1
Workspace Name: oscar
Click on Add a New Empty Project and click on OK
3. New Project window will come.
Directory Name: c:\temp\workspace1\project1
Project Name: oscarprj
click OK.
That will create a new work space with an empty project.
4. Right click on oscarprj.jpr > new java class
5. Follow the instructions and rename the class to Oscar
A Oscar.java class is created, and a new window opens.
6. Double click on Oscar.java in the top right hand pane and replace the
following
public class Oscar extends Object {
/**
* Constructor
*/
public Oscar() {
}
}
with
public class Oscar {
// return a quotation from Oscar Wilde
public static String quote() {
return "I can resist everything except temptation.";
}
}
7. Go into File > Save all
Step 3: Compile Oscar.java
1. Go to menu option Project > Make oscarprj.jpr
- you should get a message Compiler: Sucessful, with no errors
Step 4: Load and Resolve the Java Classes
1. Go into File > New > General > Deployment Profile
Select Type of Deployment as Load Java and Java Stored Procedures
Click OK and save in Location C:\temp\workspace1\project1
Tick oscarprj.jpr and oscar.java in Source Files and OK.
(this creates storedProc1.deploy)
CaSIT CSE5200 Distributed Database Systems 3
2. Right click on storedProc1.deploy > Add Stored Procedure
Select quote() and OK.
Step 5: Publish the Java Classes
1. Right click storedProc1.deploy > Deploy to > CON1_5200A
You should see
---- Stored procedure deployment finished. ----
Step 6: Call the Stored Procedures Perform the following.
You can call Java stored procedures from SQL DML statements, PL/SQL blocks, and PL/SQL
subprograms. Using the SQL CALL statement, you can also call them from the top level (from
SQL*Plus, for example) and from database triggers. Be careful there is more than one version of
SQL*PLUS on some lab machines if one version doesnt work look for the version under the Oracle
10g menus.
In the following example, you declare a SQLPlus host variable theQuote, call the function quote()
and print the result (the printed result is also shown below).
SQL> VARIABLE theQuote VARCHAR2(50);
SQL> CALL quote() INTO :theQuote;
SQL> PRINT theQuote;
THEQUOTE
--------------------------------------------------
I can resist everything except temptation.
Step 7: If Necessary, Debug the Stored Procedures Your Java stored procedures execute remotely
on a server, which typically resides on a separate machine. However, the JDK debugger (jdb) cannot
debug remote Java programs, so JServer provides a way to debug them. The class DebugProxy
makes remote Java programs appear to be local. It lets any debugger that supports the
sun.tools.debug.Agent protocol connect to a program as if the program were local. The proxy
forwards requests to the server and returns results to the debugger. For detailed instructions, see the
Oracle10g Java Developer’s Guide.
Note: Delete all connections created during your tute work before you logout. You can do this by
right clicking on connection > database > connection-name.