Oracle and Java

There are numerous good reasons to choose Oracle over its competitors in the RDBMS market. These include, in no particular order:

• Oracle has the best support for Java and has had that support for the longest time though other vendors, particularly IBM and Sybase, are moving quickly to catch up.

• With regard to the market share, Oracle is the market leader. However, as of 2002, IBM is not far behind. Although Oracle is far and away the leader on Unix, IBM makes up the difference to a large extent in the mainframe market.

• Of the top three databases IBM DB2 and Microsoft SQL Server being the other two Oracle is the most platform-independent.

These are good reasons for choosing Oracle over its competitors. But to an extent, it doesn't really matter because this is also a book about Java database programming, and the mantra of Java programmers is: "Write once, run anywhere."

Portability is one of the biggest selling points for Java. In a perfect world, it wouldn't make any difference which database we used, as long as it has support for Java. But there are imperfections in the current database world.

• Java support is not complete in any database not even Oracle.

• Even where two databases implement a particular Java feature or technology, they are not always fully compatible.

• Relational databases do not implement relational standards in compatible ways.

• Sometimes we need to rely on a relational database's proprietary features.

To the extent possible, this book will stick to platform- and database-independent code and techniques. It will clearly be identified where this is not possible. Recommendations will be provided where it is appropriate. It should not be difficult to carry over what you learn here to other databases.

Overview of Oracle's Java Features

There are many ways that we can use Java together with Oracle:

• Use Java and SQLJ (with embedded SQL statements) outside the database.

• Use Java and Java database connectivity (JDBC) outside the database.

• Use Java inside the database as stored procedures.

• Use a middleware layer to manage the connection between Java and the database.

• Use a mapping layer to provide more-or-less transparent persistence for Java transparent meaning that Java does not need to manage persistence explicitly.

As we will see, these are not mutually exclusive options.

SQLJ

SQLJ is the easiest way to perform database access from Java. It is an ANSI standard way of embedding static SQL statements in Java code. A precompiler is used to translate the SQLJ + Java code into a pure Java source file. In the case of Oracle's SQLJ implementation, at least, the underlying connectivity is actually achieved with JDBC, and it is possible to mix SQLJ code with JDBC.

JDBC: Java Database Connectivity

The fundamental technology linking Java and databases is Java database connectivity (JDBC; Figure 1-3). The JDBC specification defines a way to access any form of tabular data from Java from text files to spreadsheets to databases. Java provides a set of interfaces in the core language, the java.sql package. The database vendor typically provides an actual implementation in the form of a JDBC driver. After loading the driver in our code, we can connect to the database, send SQL statements to the database, and retrieve results.

Figure 1-3. Java and the database.

Java Stored Procedures

Java methods can be loaded and run in the database in three different ways:

• Procedures can be called from interfaces such as SQLJ or JDBC, or interactively from SQL*Plus, using the CALL statement. Procedures can optionally have input and output parameters.

• Triggers are set up to run automatically when data is inserted, deleted, or updated. These are typically used to maintain a dependent column, write to a log, or enforce complicated constraints.

• Functions are similar to procedures but are required to return a single value. They can be used just like built-in SQL functions in SQL statements, such as SELECT, DELETE, INSERT, and UPDATE.

Procedures, triggers, and functions differ in the way they are loaded, published, and called, but they are more alike than different. They are all included in the term stored procedure.

There are numerous advantages to running a program on the database server, rather than on the client machines. If a program is data-intensive, running it on the server will significantly reduce the amount of data traffic over the network. This will benefit the client, but it may also benefit other users, too, even though additional processing takes place on the shared server. A procedure can be set up to provide limited access to data.

Stored procedures were traditionally written in Oracle's native procedural language, PL/SQL. (Actually, PL/SQL is still worth considering for use in stored procedures where performance is more important than portability.) We'll encounter a bit of this legacy later, when we find that we must publish the interface of our Java method as a mapping to PL/SQL.

J2EE, Application Servers, and JDO

In addition to using a database for persistence, networked Java applications often need additional or enhanced services, such as messaging, naming and directory services, Web services, connection pooling, and transaction services.

J2EE and Application Servers

In its J2EE specification (Java 2, Enterprise Edition), Sun defines a specific set of these features, including a technology, EJB (Enterprise JavaBeans), that can manage persistence automatically for applications. A product that implements J2EE services is called an application server. The most popular application servers are BEA WebLogic and IBM WebSphere but Oracle also has one that is largely compatible with the others and easy to use.

In order for a product to claim J2EE compliance, it must be able to provide the full set of J2EE functionality specified by Sun. But the single compelling use that many people find for an application server is as an EJB container. Because the container can manage persistence automatically, the developers of the application can largely or entirely avoid SQL and many of the details of database programming.

Java Data Objects

In addition to J2EE, Sun has published a specification for Java Data Objects (JDO) an API for Java persistence. JDO provides a framework for mapping Java objects to a database of any kind and, therefore, isn't an object-relational API, per se. But naturally, implementations for relational databases, by definition, provide object-relational mapping. JDO provides nearly perfect transparent persistence. At the time of this writing just six months since the release of the specification there are at least nine commercial implementations already available and several open source projects underway.