Java Programming with Oracle JDBC

Java Programming with Oracle JDBC


Choose Expedited Shipping at checkout for guaranteed delivery by Monday, July 22


JDBC is the key Java technology for relational database access. Oracle is arguably the most widely used relational database platform in the world. In this book, Donald Bales brings these two technologies together, and shows you how to leverage the full power of Oracle's implementation of JDBC.

You begin by learning the all-important mysteries of establishing database connections. This can be one of the most frustrating areas for programmers new to JDBC, and Donald covers it well with detailed information and examples showing how to make database connections from applications, applets, Servlets, and even from Java programs running within the database itself.

Next comes thorough coverage of JDBC's relational SQL features. You'll learn how to issue SQL statements and get results back from the database, how to read and write data from large, streaming data types such as BLOBs, CLOBs, and BFILEs, and you'll learn how to interface with Oracle's other built-in programming language, PL/SQL.

If you're taking advantage of the Oracle's relatively new ability to create object tables and column objects based on user-defined datatypes, you'll be pleased with Don's thorough treatment of this subject. Don shows you how to use JPublisher and JDBC to work seamlessly with Oracle database objects from within Java programs. You'll also learn how to access nested tables and arrays using JDBC.

Donald concludes the book with a discussion of transaction management, locking, concurrency, and performance—topics that every professional JDBC programmer must be familiar with. If you write Java programs to run against an Oracle database, this book is a must-have.

Product Details

ISBN-13: 9780596000882
Publisher: O'Reilly Media, Incorporated
Publication date: 12/28/2001
Pages: 498
Product dimensions: 7.00(w) x 9.19(h) x 1.14(d)

About the Author

Donald Bales is a Computer Applications Consultant specializing in the analysis, design, and programming of distributed systems; systems integration; and data warehousing. Don has over sixteen years experience with Oracle as both a developer and a database administrator, and six years experiance with Java. He is currently working on the migration of medical and industrial hygiene systems to a web environment for a major Oil company. When he is not developing applications, Donald can often be found working with horses, playing the piano, or playing the bagpipes. Donald has had several careers, and has at various times been a mechanic, a general contractor, Mr. Mom, a developer, and currently a consultant. He has a bachelor of science degree in Business from Elmhurst College in Elmhurst, Illinois. Don currently resides in Downers Grove, Illinois with his wife Diane and his daughter Kristyn. He can be contacted by email at

Read an Excerpt

Chapter 19: Performance

Performance is usually considered an issue at the end of a development cycle when it should really be considered from the start. Often, a task called "performance tuning" is done after the coding is complete, and the end user of a program complains about how long it takes the program to complete a particular task. The net result of waiting until the end of the development cycle to consider performance includes the expense of the additional time required to recode a program to improve its performance. It's my opinion that performance is something that is best considered at the start of a project.

When it comes to performance issues concerning JDBC programming there are two major factors to consider. The first is the performance of the database structure and the SQL statements used against it. The second is the relative efficiency of the different ways you can use the JDBC interfaces to manipulate a database.

In terms of the database's efficiency, you can use the EXPLAIN PLAN facility to explain how the database's optimizer plans to execute your SQL statements. Armed with this knowledge, you may determine that additional indexes are needed, or that you require an alternative means of selecting the data you desire.

On the other hand, when it comes to using JDBC, you need to know ahead of time the relative strengths and weaknesses of using auto-commit, SQL92 syntax, and a Statement versus a PreparedStatement versus a CallableStatement object. In this chapter, we'll examine the relative performance of various JDBC objects using example programs that report the amount of time it takes to accomplish a given task. We'll first look at auto-commit. Next, we'll look at the impact of the SQL92 syntax parser. Then we'll start a series of comparisons of the Statement object versus the PreparedStatement object versus the CallableStatement object. At the same time we'll also examine the performance of the OCI versus the Thin driver in each situation to see if, as Oracle's claims, there is a significant enough performance gain with the OCI driver that you should use it instead of the Thin driver. For the most part, our discussions will be based on timing data for 1,000 inserts into the test performance table TESTXXXPERF. There are separate programs for performing these 1,000 inserts using the OCI driver and the Thin driver.

The performance test programs themselves are very simple and are available online with the rest of the examples in this book. However, for brevity, I'll not show the code for the examples in this chapter. I'll only talk about them. Although the actual timing values change from system to system, their relative values, or ratios from one system to another, remain consistent. The timings used in this chapter were gathered using Windows 2000. Using objective data from these programs allows us to come to factual conclusions on which factors improve performance, rather than relying on hearsay.

I'm sure you'll be surprised at the reality of performance for these objects, and I hope you'll use this knowledge to your advantage. Let's get started with a look at the testing framework used in this chapter.

A Testing Framework

For the most part, the test programs in this chapter report the timings for inserting data into a table. I picked an INSERT statement because it eliminates the performance gain of the database block buffers that may skew timings for an UPDATE, DELETE, or SELECT statement.

The test table used in the example programs in this chapter is a simple relational table. I wanted it to have a NUMBER, a small VARCHAR2, a large VARCHAR2, and a DATE column. Table TESTXXXPERF is defined as:

create table TestXXXPerf (
id          number,
code        varchar2(30),
descr       varchar2(80),
insert_user varchar2(30),
insert_date date )
tablespace users pctfree 20
storage( initial 1 M next 1 M pctincrease 0 );
alter table TestXXXPerf
add constraint TestXXXPerf_Pk
primary key ( id )
using index
tablespace users pctfree 20
storage( initial 1 M next 1 M pctincrease 0 );

The initial extent size used for the table makes it unlikely that the database will need to take the time to allocate another extent during the execution of one of the test programs. Therefore, extent allocation will not impact the timings. Given this background, you should have a context to understand what is done in each section by each test program.


By default, JDBC's auto-commit feature is on, which means that each SQL statement is committed as it is executed. If more than one SQL statement is executed by your program, then a small performance increase can be achieved by turning off auto-commit.

Let's take a look at some numbers. Table 19-1 shows the average time, in milliseconds, needed to insert 1,000 rows into the TESTXXXPERF table using a Statement object. The timings represent the average from three runs of the program. Both drivers experience approximately a one-second loss as overhead for committing between each SQL statement. When you divide that one second by 1,000 inserts, you can see that turning off auto-commit saves approximately 0.001 seconds (1 millisecond) per SQL statement. While that's not interesting enough to write home about, it does demonstrate how auto-commit can impact performance.

Table 19-1: Auto-commit timings (in milliseconds)










Clearly, it's more important to turn off auto-commit for managing multistep transactions than for gaining performance. But on a heavily loaded system where many users are committing transactions, the amount of time it takes to perform commits can become quite significant. So my recommendation is to turn off auto-commit and manage your transactions manually. The rest of the tests in this chapter are performed with auto-commit turned off....

SQL92 Token Parsing

Like auto-commit, SQL92 escape syntax token parsing is on by default. In case you don't recall, SQL92 token parsing allows you to embed SQL92 escape syntax in your SQL statements (see "Oracle and SQL92 Escape Syntax" in Chapter 9). These standards-based snippets of syntax are parsed by a JDBC driver transforming the SQL statement into its native syntax for the target database. SQL92 escape syntax allows you to make your code more portable--but does this portability come with a cost in terms of performance?

Table 19-2 shows the number of milliseconds needed to insert 1,000 rows into the TESTXXXPERF table. Timings are shown with the SQL92 escape syntax parser on and off for both the OCI and Thin drivers. As before, these timings represent the result of three program runs averaged together.

Table 19-2: SQL92 token parser timings (in milliseconds)

SQL92 parser









Notice from Table 19-2 that with the OCI driver we lose 177 milliseconds when escape syntax parsing is turned off, and we lose only 37 milliseconds when the parser is turned off with the Thin driver. These results are the opposite of what you might intuitively expect. It appears that both drivers have been optimized for SQL92 parsing, so you should leave it on for best performance.

Now that you know you never have to worry about turning the SQL92 parser off, let's move on to something that has some potential for providing a substantial performance improvement.

Statement Versus PreparedStatement

There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.

When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we'll look at this performance issue with respect to both the OCI driver and the Thin driver.

The OCI Driver

Table 19-3 shows the timings in milliseconds for 1 insert and 1,000 inserts in the TESTXXXPERF table. The inserts are done first using a Statement object and then a PreparedStatement object. If you look at the results for 1,000 inserts, you may think that a prepared statement performs better. After all, at 1,000 inserts, the PreparedStatement object is almost twice as fast as the Statement object, but if you examine Figure 19-1, you'll see a different story.

Table 19-3: OCI driver timings (in milliseconds)










Figure 19-1 is a graph of the timings needed to insert varying numbers of rows using both a Statement object and a PreparedStatement object. The number of inserts begins at 1 and climbs in intervals of 10 up to a maximum of 150 inserts. For this graph and for those that follow, the lines themselves are polynomial trend lines with a factor of 2. I chose polynomial lines instead of straight trend lines so you can better see a change in the performance as the number of inserts increases. I chose a factor of 2 so the lines have only one curve in them. The important thing to notice about the graph is that it's not until about 65 inserts that the PreparedStatement object outperforms the Statement object. 65 inserts! Clearly, the Statement object is more efficient under typical use when using the OCI driver.

The Thin Driver

If you examine Table 19-4 (which shows the same timings as for Table 19-3, but for the Thin driver) and Figure 19-2 (which shows the data incrementally), you'll see that the Thin driver follows the same behavior as the OCI driver. However, since the Statement object starts out performing better than the PreparedStatement object, it takes about 125 inserts for the PreparedStatement to outperform Statement....

Table of Contents

Why I Wrote This Book;
This Book’s Intended Audience;
Structure of This Book;
Conventions Used in This Book;
Software and Versions;
Comments and Questions;
Chapter 1: Introduction to JDBC;
1.1 The JDBC API;
1.2 Clients;
1.3 Using SQL;
Chapter 2: Application Database Connections;
2.1 JDBC Drivers;
2.2 Installation;
2.3 Connecting to a Database;
2.4 Handling Exceptions;
Chapter 3: Applet Database Connections;
3.1 Oracle Drivers and JDK Versions;
3.2 It’s an Applet’s Life;
3.3 Packaging Your Applet;
3.4 Getting Around the Sandbox;
3.5 Establishing a Connection Through a Firewall;
3.6 Guidelines for Choosing a Workaround;
Chapter 4: Servlet Database Connections;
4.1 Oracle Driver Selection;
4.2 Servlet Connection Strategies;
4.3 Guidelines for Choosing a Connection Strategy;
Chapter 5: Internal Database Connections;
5.1 Server-Side Driver Types;
5.2 Using the Server-Side Internal Driver;
5.3 Using the Server-Side Thin Driver;
5.4 JServer Program Support;
Chapter 6: Oracle Advanced Security;
6.1 Authentication;
6.2 Data Encryption;
6.3 Data Integrity;
6.4 A Data Encryption and Integrity Example;
6.5 Secure Sockets Layer;
Chapter 7: JNDI and Connection Pooling;
7.1 DataSources;
7.2 Oracle’s Connection Cache;
Relational SQL;
Chapter 8: A Relational SQL Example;
8.1 Relational Database Analysis;
8.2 Refining the Analysis;
8.3 Relational Database Design;
Chapter 9: Statements;
9.1 Creating a Statement Object;
9.2 The execute( ) Method;
9.3 The executeUpdate( ) Method;
9.4 The executeQuery( ) Method;
9.5 OracleStatement Implements Statement;
Chapter 10: Result Sets;
10.1 Basic Cursor Positioning;
10.2 Data Types;
10.3 Accessor Methods;
10.4 Scrollable, Updateable Result Sets;
10.5 ResultSet Is an OracleResultSet;
Chapter 11: Prepared Statements;
11.1 A Prepared Statement Versus a Statement;
11.2 Formulating SQL Statements;
11.3 Batching;
11.4 PreparedStatement Is an OraclePreparedStatement;
Chapter 12: Streaming Data Types;
12.1 BLOBs;
12.2 CLOBs;
12.3 BFILEs;
12.4 LONG RAWs;
12.5 LONGs;
Chapter 13: Callable Statements;
13.1 Understanding Stored Procedures;
13.2 Calling Stored Procedures;
13.3 CallableStatement Is an OracleCallableStatement;
Object-Relational SQL;
Chapter 14: An Object-Relational SQL Example;
14.1 From Relational Tables to Object Views;
14.2 Object Tables;
Chapter 15: Weakly Typed Object SQL;
15.1 Accessing Objects as Relational Tables;
15.2 Structs;
15.3 Arrays;
15.4 Refs;
15.5 Calling Object Methods;
15.6 Putting It All Together;
15.7 Oracle’s Implementations;
Chapter 16: Strongly Typed Object SQL;
16.1 JPublisher;
16.2 The SQLData Interface;
16.3 Oracle’s CustomDatum Interface;
Chapter 17: Transactions;
17.1 Manual Transactions;
17.2 Transaction Scope;
17.3 Implicit Locking and Visibility;
17.4 Isolation Levels;
17.5 Distributed Transactions;
Chapter 18: Detection and Locking;
18.1 Oracle’s Locking Mechanisms;
18.2 Detection;
18.3 Data Integrity Solutions;
Chapter 19: Performance;
19.1 A Testing Framework;
19.2 Auto-Commit;
19.3 SQL92 Token Parsing;
19.4 Statement Versus PreparedStatement;
19.5 Batching;
19.6 Predefined SELECT Statements;
19.7 CallableStatements;
19.8 OCI Versus Thin Drivers;
Chapter 20: Troubleshooting;
20.1 The “Gotchas”;
20.2 Unsupported Features;
20.3 Debugging;
20.4 Net8 Tracing;
20.5 Wait for the Cure;

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews