Table Of ContentTable of Contents
Java Database Programming Bible
by John O'Donahue ISBN:0764549243
John Wiley & Sons © 2002 (702 pages)
Packed with lucid explanations and lots of real-world examples, this comprehensive guide gives you
everything you need to master Java database programming techniques.
Companion Web Site
Table of Contents
Java Database Programming Bible
Preface
Part I - Introduction to Databases, SQL, and JDBC
Chapter 1 - Relational Databases
Chapter 2 - Designing a Database
Chapter 3 - SQL Basics
Y
Chapter 4 - Introduction to JDBC
L
Part II - Using JDBC and SQL in a Two-Tier Client/Server Application
F
Chapter 5 - Creating a Table withJDBC and SQL
M
Chapter 6 - Inserting, Updating,and Deleting Data
A
Chapter 7 - Retrieving Data withSQL Queries
E
Chapter 8 - Organizing Search Results and Using Indexes
Chapter 9 - Joins and Compound Queries T
Chapter 10 - Building a Client/Server Application
Part III - A Three-Tier Web Site with JDBC
Chapter 11 - Building a Membership Web Site
Chapter 12 - Using JDBC DataSources with Servlets and Java Server Pages
Chapter 13 - Using PreparedStatements and CallableStatements
Chapter 14 - Using Blobs and Clobs to Manage Images and Documents
Chapter 15 - Using JSPs, XSL, and Scrollable ResultSets to Display Data
Chapter 16 - Using the JavaMail API with JDBC
Part IV - Using Databases, JDBC,and XML
Chapter 17 - The XML Document Object Model and JDBC
Chapter 18 - Using Rowsets to Display Data
Chapter 19 - Accessing XML Documents Using SQL
Part V - EJBs, Databases, and Persistence
Chapter 20 - Enterprise JavaBeans
Chapter 21 - Bean-Managed Persistence
i
Team-Fly®
Table of Contents
Chapter 22 - Container-Managed Persistence
Chapter 23 - Java Data Objects and Transparent Persistence
Part VI - Database Administration
Chapter 24 - User Management and Database Security
Chapter 25 - Tuning for Performance
Appendix A - A Brief Guide to SQL Syntax
Appendix B - Installing Apache and Tomcat
Index
List of Figures
List of Tables
List of Listings
List of Sidebars
ii
Preface
Preface
Welcome to Java Database Programming Bible. This book is for readers who are already
familiar with Java, and who want to know more about working with databases. The JDBC
Application Programming Interface has made database programming an important
aspect of Java development, particularly where Web applications are concerned.
The ease with which Java enables you to develop database applications is one of the
main reasons for Java's success as a server-side development language. Database
programming is perhaps the key element in developing server-side applications, as it
enables such diverse applications as auction sites, XML-based Web services,
shipment-tracking systems, and search engines.
What this Book Aims to Do
The aims of this book are to give you a good understanding of what a relational database
is, how to design a relational database, how to create and query a relational database
using SQL, and how to write database-centric applications in Java. There are many
books that cover individual aspects of the aforementioned topics, such as SQL or JDBC.
The intention of this book is to provide a single source of information and application
examples covering the entire subject of relational databases.
When I first started to develop database-driven applications in Java, I was working with a
database administrator who was responsible for the database side of the project. This is a
fairly common approach to managing larger database-driven applications, since it places
responsibility for the database in the hands of a database expert and allows the Java
programmer to concentrate on his or her own area of expertise. The disadvantages of this
approach only became apparent when some of my code proved to be unacceptably slow
because of database design considerations that failed to take into account the needs of
the business logic.
Working on subsequent smaller projects enabled me to manage my own databases and
develop an understanding of how to design databases that work with the business logic. I
also learned about the tradeoffs involved in using indexes and the importance of
normalization in designing a database. Perhaps the most important thing I learned was
that, thanks to the design of the JDBC API and the universality of the SQL language,
much of what you learn from working with one database-management system is directly
applicable to another.
Although this book aims to give you a good overall understanding of Java database
programming and, in particular, to cover the JDBC API thoroughly, it is impossible to
cover either all of the different JDBC drivers currently available or all the variations of the
-1-
Preface
SQL language in a book of this nature. The examples in this book were developed using a
number of different JDBC drivers and RDBMS systems; Part II of the book addresses the
ease with which you can use the same code with different drivers and different
database-management systems.
You will find, as you work with a variety of different Relational Database Management
Systems, that the SQL standards are really just guidelines. SQL has as many different
dialects as there are relational database management systems. So although the
examples in this book should work with only minor changes on virtually any RDBMS, you
would be well advised to read a copy of the documentation for your own
database-management system.
Who Should Read this Book
This book is aimed at all levels of programmers, including those with no prior database
experience. However, you should already have some experience with Java basics and
Swing, so no attempt has been made to explain this book's examples at that level. The
server-side applications are introduced with a brief discussion of servlets and Java Server
Pages, supported by the information in Appendix B on downloading and installing the
Apache HTTP server and the Tomcat servlet and JSP eengine. If you are looking for a
beginner-level Java book, consider Java 2 Enterprise Edition Bible (ISBN 0-7645-0882-2)
by Justin Couch and Daniel H. Steinberg. For the beginning- to-intermediate-level
programmer, Java Database Programming Bible introduces all the various technologies
available to you as a J2EE programmer. If you have never used J2EE before, this book
will show you where to start and the order in which to approach your learning.
For the more advanced-level programmer, this book serves as a guide to expanding your
horizons to include the more concentrated areas of programming. Use this book as a
guide to exploring more possibilities within the area that you have already been working
on or to find new ways to address a problem. Finally, you can use this book to learn about
new areas that you may have not heard of before. Because of the breadth of J2EE, it is
always possible that new topics exist that you haven't heard of. Even after six-plus years
of Java programming experience, I am constantly finding new items popping up that I
want to learn about.
How to Use this Book
This book is divided into a number of parts. Each part covers a different aspect of the
technology, while the chapters focus on individual elements. The examples in the various
chapters are intended to provide a set of practical applications that you can modify to suit
your own needs.
-2-
Preface
The depth of coverage of each aspect of the technology is sufficient for you to be able to
understand and apply Java database programming in most of the situations you will
encounter. However, this book assumes that you are comfortable downloading and
working with the Javadocs to ferret out the details of an API. Unlike some books, Java
Database Programming Bible does not reproduce the Javadocs within its covers.
This book's approach is to present the different aspects of the technology in the context of
a set of real-world examples, many of which may be useful as they are, although some
may form the foundation of your own applications. For example, the book presents JDBC
core API in the context of a simple Swing application for the desktop, while the extension
API is covered in a series of server-side Web applications.
Since I have never read a programming book from cover to cover, I don't expect you to,
either. Individual chapters and even examples within chapters are intended to stand by
themselves. This necessarily means that there is a certain amount of repetition of key
concepts, with cross-references to other parts of the book that provide more detail.
If you don't have much of an understanding of database technology, I do recommend that
you read Part I, which introduces the basic concepts. If you know something about the
JDBC core API, but you are not familiar with the extension API, you might want to read
just the JDBC chapter in Part I to see how it all fits together.
This book is made up of six parts that can be summarized as follows.
Part I: Introduction to Databases, SQL, and JDBC
The introductory chapters discuss what a relational database is and how to create and
work with one. This part is concerned mainly with the big picture, presenting overviews of
the technology in such a way that you can see how the parts fit together. This part
contains an overview of the SQL language, as well as an explanation of JDBC as a
whole.
Part II: Using JDBC and SQL in a Two-Tier Client/Server Application
Part II presents the JDBC core API and SQL in the context of a series of desktop
applications. These applications are combined in the final chapter of this part to form a
Swing GUI that can be used as a control panel for any database system. A key concept
presented in this part of the book is the way that JDBC can be used with any RDBMS
system by simply plugging in the appropriate drivers.
Part III: A Three-Tier Web Site with JDBC
One of the most common Java database applications is the creation of dynamic Web
sites using servlets, JSPs, and databases. This part discusses the JDBC extension API in
the context of developing a Web application. It also talks about using JDBC and SQL to
-3-
Preface
insert large objects such as images into a database, and retrieving them for display on a
Web page.
Part IV: Using Databases, JDBC, and XML
Another big application area for Java and database technologies is the use of XML. This
part introduces XML and the Document Object Model, and it presents different ways to
work with Java, databases, and XML. This part also discusses the design of a simple
JDBC driver and a SQL engine to create and query XML documents.
Part V: EJBs, Databases, and Persistence
Applications using Enterprise Java Beans are another significant area where Java and
databases come together. This part introduces EJBs and persistence, and it compares
bean-managed persistence with container-managed persistence.
Part VI: Database Administration
The final major topics we discuss are often overlooked in books about database
programming: database administration, and tuning. This oversight might be
understandable if all databases had a dedicated administrator, but in practice it frequently
falls to the Java developer to handle this task, particularly where smaller systems are
involved.
Appendixes
The appendixes are a comparison of some major SQL dialects and a guide to installing
Apache and Tomcat.
Companion Web Site
Be sure to visit the companion Web site, where you can download all of the code listings
and program examples covered in the chapters. The URL for the website is:
http://www.wiley.com/extras.
Conventions Used in this Book
This book uses special fonts to highlight code listings and commands and other terms
used in code. For example:
This is what a code listing looks like.
In regular text, monospace font is used to indicate items that would normally appear in
code.
-4-
Preface
This book also uses the following icons to highlight important points:
Note The Note icon provides extra information to which you need to pay
special attention.
Tip The Tip icon shows a special way of performing a particular task.
Caution The Caution icon alerts you to take care when performing certain
tasks and procedures.
Cross-Reference The Cross-Reference icon refers you to another part of the book or
another source for more information on a topic.
Acknowledgments
Writing a book is both challenging and rewarding. Sometimes, it can also be very
frustrating. However, like any other project, it is the people you work with who make it an
enjoyable experience. I would like to thank Grace Buechlein for her patience and
encouragement, and my co-authors, Kunal Mittal, who also acted as the technical editor,
and Andrew Yang, the EJB guru, for their contributions.
-5-
Chapter 1:Relational Databases
Chapter 1: Relational Databases
In This Chapter
The purpose of this chapter is to lay the groundwork for the rest of the book by explaining
the underlying concepts of Relational Database Management Systems. Understanding
these concepts is the key to successful Java database programming. In my experience,
just understanding how to handle the Java side of the problem is not enough. It is
important to understand how relational databases work and to have a reasonable
command of Structured Query Language (SQL) before you can do any serious Java
database programming.
Understanding Relational Database Management Systems
A database is a structured collection of meaningful information stored over a period of
time in machine-readable form for subsequent retrieval. This definition is fairly
intuitive and says nothing about structure or methodology. By this definition, any file
or collection of files can be considered a database. However, to be useful in practical
terms, a database must form part of a system that provides for the management of
the data it contains. Seen from this perspective, a database must be more than a
mere collection of files. It must be a complete system.
A practical database management system combines the physical storage of data with
the capability to manage and interact with the data. Such a system must support the
following tasks:
§ Creation and management of a logical data structure
§ Data entry and retrieval
§ Manipulation of the data in a logical and consistent manner
§ Storage of data reliably over a significant period of time
Prior to the development of modern relational databases, a number of different
approaches were tried. In many cases, these were simple, proprietary data-storage
systems designed around a specific application. However, large corporations, notably
IBM, were marketing more general solutions.
The Relational Model
The big step forward in database technology was the development of the relational
database model. The relational database derives from work done in the late 1960s by
E.F. Codd, a mathematician at IBM. His model is based on the mathematics of set
theory and predicate logic. In fact, the term relational has its roots in the mathematical
-6-
Chapter 1:Relational Databases
terminology of Codd's paper entitled "A relational model of data for large shared data
banks," which was published in Communications of the ACM, Vol. 13, No. 6, June
1970, pp. 377-387. In this paper, Codd uses the terms relation, attribute, and tuple
where more common programming usage refers to table, column, and row,
respectively.
The importance of Codd's ideas is such that the term "database" generally refers to a
relational database. Similarly, in common usage, a Database Management System,
or DBMS, generally means a Relational Database Management System. The terms
are used interchangeably in this chapter, and throughout the book.
Codd's model covers the three primary requirements of a relational database:
structure, integrity, and data manipulation. The fundamentals of the relational model
are as follows:
§ A relational database consists of a number of unordered tables.
§ The structure of these tables is independent of the physical storage medium used to store the
data.
§ The contents of the tables can be manipulated using nonprocedural operations that return tables.
The implementation of Codd's relational model means that a user does not need to
understand the physical structure of the data in order to access and manage data in
the database. Rather than accessing data by referring to files or using pointers, the
user accesses data through a common tabular architecture. The relational model
maintains a clear distinction between the logical views of the data presented to the
user and the physical structure of the data stored in the system.
Codd based his model on a simple tabular structure, though his term for a table was a
relation. Each table is made up of one or more rows (or tuples). Each row contains a
number of fields, corresponding to the columns or attributes of the table.
Throughout the rest of this book, the more common programming terms are used:
table, column, and row. Generally, only database theorists use Codd's original
terminology; in that context, you are most likely to see references to relations,
attributes, and tuples.
The tabular structure Codd defines is simple and relatively easy for the user to
understand. It is also sufficiently general to be capable of representing most types of
data in virtually any kind of structure. An additional advantage of a tabular structure is
that tables are amenable to manipulation by a clearly defined set of mathematical
operations that generate results that are also in the form of tables. These
mathematical operations lend themselves readily to implementation in a high-level
language. In fact, Codd's rules require that a high level language be incorporated in
the RDBMS for just this purpose. That language has evolved into the Structured
Query Language, SQL, discussed in subsequent chapters.
-7-
Chapter 1:Relational Databases
The use of a high-level language to manipulate the data at the logical level is an
important feature, providing a level of abstraction which lets the user insert or retrieve
data from the tables based on attributes of the data rather than its physical structure.
For example, rather than requiring the user to retrieve a number stored in a certain
location on disk, the use of a high-level query language allows the user to request the
checking balance of a particular customer's account by account number or customer
name.
A further advantage of this approach is that, while the user defines his or her requests
in logical terms, the database management system (DBMS) can implement them in a
highly optimized manner with respect to the physical implementation of the storage
system. By decoupling the logical operations from the physical operations, the DBMS
can achieve a combination of user friendliness and efficiency that would not
otherwise be possible.
Codd's Rules
When Codd initially presented his paper, the meaning of the relational model he
described was not widely understood. To clarify his ideas, Codd published his famous
Fidelity Rules, which are summarized in Table 1-1. In theory, a RDBMS must conform
to these rules. As it turns out, some of these rules are extremely difficult to implement
in practice, so no existing RDBMS complies fully.
Table 1-1: Codd's Rules
Rule Name Description
0 Foundation Rule A RDBMS must use its relational facilities exclusively to
manage the database.
1 Information Rule All data in a relational database must be explicitly
represented at the logical level as values in tables and in
no other way.
2 Guaranteed Access Rule Every data element must be logically accessible through
the use of a combination of its primary key name,
primary key value, table name, and column name.
3 Systematic Nulls Rule The RDBMS is required to support a representation of
missing and inapplicable information that is systematic,
distinct from all regular values, and independent of data
type.
4 Dynamic Catalog Rule The database description or catalog must also be stored
at the logical level as tabular values. The relational
language must be able to act on the database design in
the same manner in which it acts on data stored in the
-8-
