Java Stored Procedures in a LIMS Environment
![]()
LIMSfinder > Picture1″ height=70 alt=”w3markets > LIMSfinder > Picture1″ hspace=0 src=”http://w3markets.smugmug.com/photos/27112514-M.jpg” width=310 border=0>
Java Stored Procedures in a LIMS Environment
By Rob Sullivan, Senior Consultant<span lang=EN style="FONT-FAMILY: Verdana; mso-ansi-language: EN; mso-bidi-font-family: Arial]
In this article, I will review the benefits of using Java Stored Procedures in a LIMS environment. When Oracle 8i was released (yes, the rigors of validation dictate that many modern LIMS systems still use Oracle 8), one of its significant new features it offered over previous versions was the ability to embed and execute Java in the database. This gave the LIMS developer opportunities to write object-oriented code that provided both reusability and performance increases.
Overview of Java Stored Procedures
Java Stored Procedures allow for server-side programming in addition to the standard Oracle database language, PL/SQL. While PL/SQL can only operate inside the database, Java code can be created for file-system applications but then also uploaded to the database to operate along side PL/SQL programs. Using JDBC, the Java code can access and manipulate the database; and using special “wrapper” functions, PL/SQL code can directly call Java Stored Procedures.
Benefits
One of the obvious benefits of Java is the code reusability it provides. Generic Java classes can be created and used by many different systems. In a LIMS system, a Java class can be created to perform basic utility functions – for example, a diagnostics system that provides error messaging and outputs the logical flow of your code. This class can then be used by the client applications and GUIs you create and at the same time can be loaded into the database and accessed by all of your database code.
In addition to reusability, Java Stored Procedures can provide significant performance increases over PL/SQL code. PL/SQL is rather slow when performing mathematical computations and string manipulations. So when trying to achieve performance increases, Java Stored Procedures are well suited for numerically or computationally intensive tasks.
Case Study
As an example of the performance benefits of Java Stored Procedures, we will look at a J&R Consulting client whose system was entirely written in PL/SQL. Though the system met all of the User and Functional requirements of the LIMS, the system was unacceptably slow for all but the smallest tasks. Executions of the system on moderate or large datasets would take anywhere from 15 seconds up to 30 minutes or more to complete. And, the end-users’ client sessions were tied up during the processing, leading to a poor user experience. The consultants at J&R ported the computationally intensive portion of the PL/SQL code to Java, allowing the remaining PL/SQL code to concentrate on what it is best at – database access. This produced a system that worked as follows:
- Query data from the database (PL/SQL)
- Manipulate strings of data and perform mathematical computations (Java)
- Write computed values to the database (PL/SQL)
The final system provided dramatic performance increases. Executions that previously took 15 seconds now complete immediately and the 30+ minute executions now complete in about 10 seconds. Needless to say, the users are much happier with the new system.
Deploying Java Stored Procedures
If you are convinced that Java Stored Procedures can benefit your LIMS system, the following provides a brief overview of how to deploy Java Stored Procedures:
- Write and compile the Java code using your favorite IDE (or using your favorite text editor and the command line)
- Use the Oracle-provided loadjava application to load the compiled Java bytecodes into the database
- Create a special PL/SQL wrapper package to publish the Java Stored Procedure (this makes the Java Stored Procedure appear as a normal PL/SQL package to the rest of the code in the database)
The next time you are designing a LIMS system or sub-system, consider how Java Stored Procedures can benefit the overall system.
About the author: Rob Sullivan is a Senior Consultant at J&R Consulting, Inc, a consulting firm specializing in LIMS systems, and excels in technical development and implementation. Regular articles on LIMS software and contact information can be found at http://www.jandrconsult.com .
