Database Access with JDBC
Storing and retrieving database information is one of the most common operations performed by servlets. In this chapter, we will examine the Java Database Connectivity (JDBC) API and demonstrate database connectivity from within a servlet. In addition, we will discuss some advanced database concepts, including prepared statements, transactions, stored procedures, and connection pooling. Finally, a sample JDBC servlet using the MySQL database will be presented.
This chapter provides a brief overview of a topic that could easily consume an entire book. In fact, many books currently focus completely on JDBC programming. Specifically, the following topics are covered in this chapter:
JDBC architecture
Accessing a database
Advanced database concepts
Database connection pooling
Introduction to the MySQL database
JDBC Architecture
JDBC is an API specification developed by Sun Microsystems that defines a uniform interface for accessing various relational databases. JDBC is a core part of the Java platform and is included in the standard JDK distribution.
The primary function of the JDBC API is to provide a means for the developer to issue SQL statements and process the results in a consistent, database-independent manner. JDBC provides rich, object-oriented access to databases by defining classes and interfaces that represent objects such as:
Database connections
SQL statements
Result sets
Database metadata
Prepared statements
Binary Large Objects (BLOBs)
Character Large Objects (CLOBs)
Callable statements
Database drivers
Driver manager
The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. The location of the driver manager with respect to the JDBC drivers and the servlet is shown in Figure 16.1.
){kind=link}
Figure 16.1 Layers of the JDBC Architecture
A JDBC driver translates standard JDBC calls into a network or database protocol or into a database library API call that facilitates communication with the database. This translation layer provides JDBC applications with database independence. If the back-end database changes, only the JDBC driver need be replaced with few code modifications required. There are four distinct types of JDBC drivers.
Type 1 JDBC-ODBC Bridge. Type 1 drivers act as a "bridge" between JDBC and another database connectivity mechanism such as ODBC. The JDBC- ODBC bridge provides JDBC access using most standard ODBC drivers. This driver is included in the Java 2 SDK within the sun.jdbc.odbc package. Finally, the JDBC-ODBC bridge requires that the native ODBC libraries, drivers, and required support files be installed and configured on each client employing a Type 1 driver. This requirement may present a serious limitation for many applications. (See Figure 16.2.)
){kind=link}
Figure 16.2 Type 1 JDBC Architecture
Type 2 Java to Native API. Type 2 drivers use the Java Native Interface (JNI) to make calls to a local database library API. Type 2 drivers are usually faster than Type 1 drivers. Like Type 1 drivers, Type 2 drivers require native database client libraries to be installed and configured on the client machine. (See Figure 16.3.)
){kind=link}
Figure 16.3 Type 2 JDBC Architecture
Type 3 Java to Network Protocol. Type 3 drivers are pure Java drivers that use a proprietary network protocol to communicate with JDBC middleware on the server. The middleware then translates the network protocol to database-specific function calls. Type 3 drivers are the most flexible JDBC solution because they do not require native database libraries on the client and can connect to many different databases on the back end. Type 3 drivers can be deployed over the Internet without client installation. (See Figure 16.4.)
){kind=link}
Figure 16.4 Type 3 JDBC Architecture
Type 4 Java to Database Protocol. Type 4 drivers are pure Java drivers that implement a proprietary database protocol (like Oracle's SQL*Net) to communicate directly with the database. Like Type 3 drivers, they do not require native database libraries and can be deployed over the Internet without client installation. One drawback to Type 4 drivers is that they are database specific. Unlike Type 3 drivers, if your back-end database changes, you may save to purchase and deploy a new Type 4 driver (some Type 4 drivers are available free of charge from the database manufacturer). However, because Type drivers communicate directly with the database engine rather than through middleware or a native library, they are usually the fastest JDBC drivers available. (See Figure 16.5.)
){kind=link}
Figure 16.5 Type 4 JDBC Architecture
So, you may be asking yourself, "Which is the right type of driver for your application?" Well, that depends on the requirements of your particular project. If you do not have the opportunity or inclination to install and configure software on each client, you can rule out Type 1 and Type 2 drivers.
However, if the cost of Type 3 or Type 4 drivers is prohibitive, Type 1 and type 2 drivers may become more attractive because they are usually available free of charge. Price aside, the debate will often boil down to whether to use Type 3 or Type 4 driver for a particular application. In this case, you may need to weigh the benefits of flexibility and interoperability against performance. Type 3 drivers offer your application the ability to transparently access different types of databases, while Type 4 drivers usually exhibit better performance and, like Type 1 and Type 2 drivers, may be available free if charge from the database manufacturer. For more information, visit the following URL for a list of available JDBC drivers: