This chapter defines the purpose of the Java Database Connectivity API and illustrates the basic JDBC architecture. The four types of JDBC drivers are also described. In addition, the process of connecting to a database is discussed in detail and many advanced database concepts are presented. These advanced concepts include a discussion regarding prepared statements, stored procedures, transactions, and connection pooling. Finally, the MySQL database is introduced in order to provide the reader with a simple (and free) way to test the techniques presented in this chapter.
JDBC is an API specification developed by Sun Microsystems that defines a uniform interface for accessing different relational databases. The primary function of the JDBC API is to allow the developer to issue SQL statements and process the results in a consistent, database-independent manner.
The JDBC API uses a driver manager and database-specific drivers to pro-vide 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 con-current drivers connected to multiple heterogeneous databases.
A JDBC driver translates standard JDBC calls into a network protocol or client API call that facilitates communication with the database. This transla-tion provides JDBC applications with database independence.
The basic process of connecting to a database via JDBC goes like this: regis-ter the JDBC driver, establish a database connection, execute an SQL state-ment, process the results, close the database connection.
A prepared statement is an SQL statement that is precompiled by the data-base. Through precompilation, prepared statements improve the perfor-mance of SQL commands that are executed multiple times (given that the database supports prepared statements).
A transaction is a set of SQL statements that are grouped such that all state-ments are guaranteed to be executed or the entire operation will fail. If all statements execute successfully, the results are committed to the database; otherwise, all changes are rolled back.
A stored procedure is an SQL operation that is stored on the database server. Stored procedures are usually written in an SQL dialect that has been expanded to include conditional statements, looping constructs, and other procedural programming features.
Metadata is defined as information (or data) about data. JDBC provides spe-cific information about a database or a result set via metadata objects.
Database connection pooling is the process of establishing a set, or pool, of database connections before they are actually needed.