Creating High-Performance Database Applications with Java Triggers and the Oracle Database

By Bruce Hopkins, first published November 2009, updated February 2010

As application developers, we're always challenged with new problems to solve for our customers and stakeholders. Whether you're creating desktop, enterprise, or SOA applications, you will often face the need to persist data to some sort of datastore. Although there are other options, most developers choose to use a relational database to persist data, as well as to store application state.

SQL is a great language for selecting, inserting, and updating data in the database, but it's not well suited for handling things when a particular part of your application changes state, and you need to do something about it. What do I mean by that? Let's take a look at some common, everyday examples of state changes that should be familiar to everyone.

I'm referring to any changes in state within an application, such as:

  1. For an HR application, when an employee is terminated from a company, all the user accounts need to be disabled.

  2. For a vehicle dealership application, after 3 years of ownership, your new vehicle's warranty expires.

  3. For a health coverage application, when your dependent turns 25, then he/she is no longer covered by your health insurance.

  4. For a credit account application, when you miss a payment on your credit card, then the interest rate changes from normal to default status

So, in a typical three tier application, as shown in Figure 1 below, most developers will move all the logic to the 2nd tier (note that for a desktop app, the 1st and 2nd tiers are combined) to monitor state changes, and to act accordingly.

Figure 1. A Typical Three-Tier Architecture with Java in Tier 2
 

So, for Java developers, this means that you will need to spin up a thread that will periodically poll the database to determine if any changes in state occur. As with any polling application the question immediate arises, 'How often should I poll?' If you poll the database too frequently, then you're consuming precious resources (such as CPU cycles, heap space, and a db connection) that could be used by the rest of the application. However, if you don't poll your database often enough, then you may have unwanted side-effects in your application because it's not responsive enough. For instance, if you're using a database to maintain the status of employment for employees in a corporation, then polling the database every 24 hours will result in the possibility of a terminated employee having access to the corporate resource for up to an entire day.

Therefore, the purpose of this article is to show developers how to completely eliminate the necessity for polling databases for state changes. I want to show you how to create Java triggers in the Oracle database that handle state changes by themselves.

Wait, There's a JVM Inside the Oracle Database?

Yes, there's a JVM inside the Oracle database. Yes, it's been there for years — in fact, since the days of Oracle 8i. Yes, it's available for application developers to use in their own applications -- and yes, you can achieve a performance improvement by utilizing it. How much of an improvement? Some tests have shown that JDBC operations utilizing the internal OracleJVM compared to an external JVM can increase performance by 600%, which is quite impressive. Figure 2, located below shows a modified 3-tier architecture using the OracleJVM.

Figure 2. A Typical Three-Tier Architecture with Java in Tier 2 and 3
 

Once you think about it, it's easy to see how the OracleJVM is able to boast such incredible performance numbers:

  • The OracleJVM exists on the same machine as the Oracle database. This means that the overhead of negotiating a TCP connection between two machines is eliminated.

  • The OracleJVM exists in the same address space of the SQL engine; therefore it does not need to perform IPC (inter-process communication) or RPC (remote process communication) to connect the Java code to the database.

  • The JDBC code within the OracleJVM reuses the existing connection to the database that's already present, so the overhead of creating a new connection the database is eliminated.

Now if you're an enterprise developer, does this mean that you can completely dispose of your application server and deploy all your code in the OracleJVM? Absolutely not. You're still going to need (at least) one machine that is capable of handling a large number of HTTP requests from your web clients. This machine should have a pool of connections to the database, and should be able to reuse those connections to handle various operations on behalf of the end-users. However, this does mean that if you have any internal tasks that are contained within the database itself, then it's a likely candidate to be encapsulated within the database's JVM — such as internal state changes! The following table maps OracleJVM versions with Java SE compatibility.

Table 1. OracleJVM to Java SE Compatibility
OracleJVM Version
Java SE Compatibility
Oracle 11g
JDK 1.5 (aka Java SE 5)
Oracle 10g
JDK 1.4
Oracle XE
No JVM Available
Oracle 9i
JDK 1.3
Oracle 8i
JDK 1.2
 

Creating and Deploying Your First Application on the OracleJVM

Now before we go through the steps involved on how to create a pure-Java trigger in the Oracle database, we're going to take on a simpler task. Let's see how to create and load a simple Java class in the database and execute a method from the command line. Listing 1, shown below is the source code for firstclass.java.sql

Listing 1. Source code for firstclass.java.sql 
create or replace java source named FirstClass as

        public class FirstClass{

                public static String greeting(String name){

                        return "Hello " + name + "!";
                }

        }

/

create or replace function firstclass_greeting (name varchar2) return varchar as

        language java name 'FirstClass.greeting(java.lang.String) return java.lang.String';

/

As you can see, the first create statement encloses the pure-Java source code for a class named FirstClass (the first create statement is terminated by the "/"). When this statement is executed, then your Java class will be loaded in the OracleJVM. The second create statement associates the FirstClass.greeting() method with a callable function name of firstclass_greeting(). The second create statement is not necessary to load, compile, or initialize your Java class, but it provides a PL/SQL wrapper around your Java class so that entities in the SQL engine can call it. One more thing to notice about Listing 1: our PL/SQL wrapper is creating a function because of the fact that we're returning something (in this case, a String) after execution. Later on, you should notice that I'll create a procedure when I want to wrap a Java void method.

Now when you load firstclass.java.sql into SQLPlus and execute it, then you should see the following results, as shown in Listing 2.

Listing 2. The Results of Executing firstclass.java.sql 
SQL> @firstclass.java.sql

Java created.

Function created.

So now we have our class loaded in the OracleJVM, and we also have a function that maps to our class's static method. So let's call that function and see what happens:

Listing 3. The Results of Calling the firstclass_greeting() function 
SQL> select firstclass_greeting('Bruce') from dual;

FIRSTCLASS_GREETING('BRUCE')
--------------------------------------------------------------------------------
Hello Bruce!

Great, so at this point, we have successfully tested that we can load Java classes in the OracleJVM, create a PL/SQL wrapper around Java methods, and call the wrapper from within the database. Now, let's write a pure Java trigger that actually solves a real business problem.

Creating the Book Reorder Trigger

So let's examine a classic use case that should be familiar with most people: book sales via an online bookstore. One of the problems that any retailer faces (whether online or brick & mortar) is ensuring that items for sale are always kept in stock. Figure 3 depicts a simple data model for a bookstore's database.

Figure 3. A Simple Data Model for a Bookstore Database
 

In order to create the tables required for this example, as well as to insert some sample data in the database, just execute the SQL script contained in Listing 4.

Listing 4. The Source Code for create_bookstore_tables.sql
drop table books
/
drop table publisher_supply_orders
/

create table books(
        book_id                 number  primary key,
        publisher_id    number,
        page_count              number,
        author_name             varchar2(50),
        book_title              varchar2(50),
        description             varchar2(500),
        status                  varchar2(10),
        inventory_qty   number
)
/

insert into books values(100, 200, 234, 'Bruce Hopkins', 'Bluetooth for Java', 'great book', 'IN STOCK', 10);
insert into books values(101, 200, 401, 'Sam Jones', 'Living on the East Coast', 'worth every penny', 'IN STOCK', 50);
insert into books values(102, 250, 278, 'Max Jason', 'The South of France', 'a best-seller', 'IN STOCK', 20);

create table publisher_supply_orders(
        book_id                 number,
        publisher_id    number,
        order_quantity  number
)
/

So, as you can imagine, the business logic for our internal trigger is very simple. Whenever the books table is updated, check to see if the books.inventory_qty field is less than a particular threshold. If the books.inventory_qty is less than the threshold (which I set to be 5 books), then reorder the book by inserting a new row in the publisher_supply_orders table. At a later time, a batch process can read all the entries in the publisher_supply_orders and place the actual orders with the publishers at the one time. This way, you can aggregate the orders with the individual publishers, which is a lot more efficient compared to submitting the orders directly to the publishers for each title one at a time. The code located in Listing 5, located below creates the Java class, the PL/SQL wrapper, as well as the trigger that the database will use to call your Java class's static method.

Listing 5. The Source Code for ReorderTrigger.java.sql 
create or replace java source named "ReorderTrigger" as
 
        import java.sql.*;
        import oracle.jdbc.driver.*;
 
        public class ReorderTrigger {

                public static int REORDER_THRESHOLD = 5;
                public static int REORDER_QTY = 25;
 
                public static void reorderBooks(Integer bookID, Integer publisherID, Integer inventoryQty) {

                        if(inventoryQty.intValue() < REORDER_THRESHOLD){
                        
                                try     {
                                Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
                                PreparedStatement prep = conn.prepareStatement("insert into publisher_supply_orders values(?,?,?)");
 

                                prep.setInt(1, bookID);
                                prep.setInt(2, publisherID);
                                prep.setInt(3, REORDER_QTY);
 
 
                                prep.executeUpdate();
                                prep.close();
                                conn.close();

                                } catch (Exception e){

                                }
                        }

                }
        }
/

create or replace procedure procedure_reorderbooks(bookID number, publisherID number, inventoryQty number) as

        language java name 'ReorderTrigger.reorderBooks(java.lang.Integer, java.lang.Integer)';

/

create or replace trigger trigger_reorderbooks
before update on books
for each row
begin
    procedure_reorderbooks(:new.book_id, :new.publisher_id, :new.inventory_qty);
end;
/

As you can see in Listing 5, when your Java code executes from within the OracleJVM you still use all the JDBC classes and paradigms that you would use as if your code executed outside of the database. You should notice that the connection String that allows you to utilize the internal JDBC driver in the OracleJVM is " jdbc:default:connection:". Additionally, you should notice that we're creating a procedure as our PL/SQL wrapper for our Java method since we're not returning a result from the method call. After you execute the ReorderTrigger.java.sql file, you should see the results shown in Listing 6, below.

Listing 6. The Results of Executing ReorderTrigger.java.sql 
SQL> @ReorderTrigger.java.sql

Java created.

Procedure created.

Trigger created.

So now that we have all the components in place for the trigger to execute, let's update the books table so that the inventory_qty is less than the threshold of 5 books. Figure 4 shows one of the rows of sample data in the books table to be modified under the threshold, and Figure 5 shows the new row that was inserted in the publisher_supply_orders table automatically by our trigger.

Figure 4. The inventory_qty Field in the Books Table was Modified Under the Threshold
 
Figure 5. The publisher_supply_orders Table Shows a New Row from the Trigger
 

Conclusion

Whew! We've covered a lot of material today. The Oracle database is a powerful development platform that not only allows developers to store and query relational data, but it also includes a JVM, which enables developers to create powerful server-side applications. Trust me, we've only scratched the surface here... Stay tuned for more.

Rate This Article
Comments
Terms of Use