Oracle® Database Lite Developer's Guide for Java
10g (10.0.0) Part No. B13811-01 |
|
![]() Previous |
![]() Next |
This appendix demonstrates how to create a Java stored procedure and trigger. Topics include:
In this tutorial, you create a Java class EMAIL
, load the class into Oracle Database Lite, publish its method to SQL, and create a trigger for the method. The EMAIL
class appears in the source file EMAIL.java, and is available in the Java examples directory at the following location.
&fmv132;\Mobile\Sdk\Samples\JDBC
EMAIL
has a method named assignEMailAddress
, which generates an email address for an employee based on the first letter of the employee's first name and up to seven letters of the last name. If the address is already assigned, the method attempts to find a unique email address using combinations of letters in the first and last name.
After creating the class, you load it into Oracle Database Lite using MSQL. For this example you use the SQL statement CREATE JAVA. Alternatively, you can use the loadjava
utility to load the class into Oracle Database Lite. After loading the class, you publish the assignEMailAddress
method to SQL.
Finally, you create a trigger that fires the assignEMailAddress
method whenever a row is inserted into T_EMP, the table that contains the employee information.
As arguments, assignEMailAddress
takes a JDBC connection object, the employee's identification number, first name, middle initial, and last name. Oracle Database Lite supplies the JDBC connection object argument. You do not need to provide a value for the connection object when you execute the method. assignEMailAddress
uses the JDBC connection object to ensure that the generated e-mail address is unique.
Start MSQL and connect to the default Oracle Database Lite. Since the Java application in this tutorial prints to standard output, use the DOS version of MSQL. From a DOS prompt, type:
msql system/mgr@jdbc:polite:polite
The SQL prompt should appear.
To create a table, type:
CREATE TABLE T_EMP(ENO INT PRIMARY KEY, FNAME VARCHAR(20), MI CHAR, LNAME VARCHAR(20), EMAIL VARCHAR(8));
Create and compile the Java class EMAIL
in the file EMAIL.java in C:\tmp. EMAIL.java implements the assignEMailAddress
method. The code sample given below lists the contents of this file. You can copy this file from the following location.
&fmv133;\Mobile\Sdk\Samples\JDBC
import java.sql.*; public class EMAIL { public static void assignEMailAddress(Connection conn, int eno, String fname,String lname) throws Exception { Statement stmt = null; ResultSet retset = null; String emailAddr; int i,j,fnLen, lnLen, rowCount; /* create a statement */ try { stmt = conn.createStatement(); } catch (SQLException e) { System.out.println("conn.createStatement failed: " + e.getMessage() + "\n"); System.exit(0); } /* check fname and lname */ fnLen = fname.length(); if(fnLen > 8) fnLen = 8; if (fnLen == 0) throw new Exception("First name is required"); lnLen = lname.length(); if(lnLen > 8) lnLen = 8; if (lnLen == 0) throw new Exception("Last name is required"); for (i=1; i <= fnLen; i++) { /* generate an e-mail address */ j = (8-i) > lnLen? lnLen:8-i; emailAddr = new String(fname.substring(0,i).toLowerCase()+ lname.substring(0,j).toLowerCase()); /* check if this e-mail address is unique */ try { retset = stmt.executeQuery( "SELECT * FROM T_EMP WHERE email = '"+ emailAddr+"'"); if(!retset.next()) { /* e-mail address is unique; * so update the email column */ retset.close(); rowCount = stmt.executeUpdate( "UPDATE T_EMP SET EMAIL = '" + emailAddr + "' WHERE ENO = " + eno); if(rowCount == 0) throw new Exception("Employee "+fname+ " " + lname + " does not exist"); else return; } } catch (SQLException e) { while(e != null) { System.out.println(e.getMessage()); e = e.getNextException(); } } } /* Can't find a unique name */ emailAddr = new String(fname.substring(0,1).toLowerCase() + lname.substring(0,1).toLowerCase() + eno); rowCount = stmt.executeUpdate( "UPDATE T_EMP SET EMAIL = '" + emailAddr + "' WHERE ENO = " + eno); if(rowCount == 0) throw new Exception("Employee "+fname+ " " + lname + " does not exist"); else return; } }
To load the EMAIL
class file into Oracle Database Lite, type:
CREATE JAVA CLASS USING BFILE ('c:\tmp', 'EMAIL.class');
If you want to make changes to the class after loading it, you need to:
Drop the class from the database, using dropjava
or DROP JAVA CLASS
Commit your work
Exit MSQL
Restart MSQL
You make the stored procedure callable from SQL by creating a call specification (call spec) for it. Since assignEMailAddress
does not return a value, use the CREATE PROCEDURE command, as follows:
CREATE OR REPLACE PROCEDURE ASSIGN_EMAIL(E_NO INT, F_NAME VARCHAR2, L_NAME VARCHAR2) AS LANGUAGE JAVA NAME 'EMAIL.assignEMailAddress(java.sql.Connection, int, java.lang.String, java.lang.String)';
Insert a row into T_EMP:
INSERT INTO T_EMP VALUES(100,'John','E','Smith',null);
To make ASSIGN_EMAIL execute whenever a row is inserted into T_EMP, create an AFTER INSERT trigger for it. Create the trigger as follows:
CREATE TRIGGER EMP_TRIGG AFTER INSERT ON T_EMP FOR EACH ROW ASSIGN_EMAIL(eno,fname,lname);
A trigger named EMP_TRIGG fires every time a row is inserted into T_EMP. The actual arguments for the procedure are the values of the columns eno
, fname
, and lname
.
You do not need to specify a connection
argument.