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

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.

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.

So, with this tutorial, please try to get practise.