What Is JDBC Programming?

This chapter explains JDBC programming by using a set of questions and answers. Java and JDBC are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. According to Sun Microsystems,JDBC is not an acronym and does not stand for Java Database Connectivity (but the fact of the matter is that most Java engineers believe that JDBC stands for Java DataBase Connectivity).

JDBC is a platform-independent interface between relational databases and Java. In today’s Java world, JDBC is a standard API for accessing enterprise data in relational databases(such as Oracle, MySQL, Sybase, PostgreSQL, and DB2) using SQL(Structured Query Language). In this chapter, we will examine the basic aspects of JDBC, and save the details about JDBC metadata for upcoming chapters. Data and metadata(data about data/information) are at the heart of most business applications, and JDBC deals with data and metadata stored and manipulated in relational database systems(RDBMSs). Note that each RDBMS has a lot of metadata, and JDBC maps some of those metadata in a uniform and consistent fashion by its API.

This book takes an examples-based approach to describing the metadata features available in JDBC(such as getting a list of tables or views, or getting a signature of a stored procedure). Whether you are a new or an experienced database or JDBC developer, you should find the examples and accompanying text a valuable and accessible knowledge base for creating your own database solutions. Using JDBC’s database metadata, you can generate GUI/web-based applications. Also, you can develop web entry forms based on metadata.

In this book, we use some basic Java/JDBC utility classes(such as the DatabaseUtil class), which are available for download from the Source Code section of the Apress website. The DatabaseUtil class provides methods for closing JDBC objects(such as Connection, ResultSet, Statement, and PreparedStatement). The reason for using the DatabaseUtil class is to make the code compact and more readable(for example, closing a ResultSet object by DatabaseUtil takes one line of code versus a couple of lines without using DatabaseUtil).

VeryBasicConnectionManager is a very simple class that provides Connection objects for Oracle and MySQL by using getConnection(dbVendor). In real production applications, the VeryBasicConnectionManager class is not an acceptable solution and should be replaced by a connection pool manager . We use these classes to demonstrate JDBC concepts for different vendors such as Oracle and MySQL. Connection pooling is a technique used for reusing and sharing Connection objects among requesting clients.

The remaining chapters in this book will deal with JDBC metadata and nothing but JDBC metadata.

JDBC is a set of programming APIs that allows easy connection to a wide range of databases(especially relational databases) through Java programs. In this book, we will be using JDBC 2.0 and 3.0 versions(JDBC 4.0 is just a specification and has not been implemented extensively yet.) In Java 2 Platform Standard Edition (J2SE) 5.0 (which supports JDBC 3.0), the JDBC API is defined by two packages:

java.sql provides the API for accessing and processing data stored in a data source(usually a relational database) using the Java programming language. This package provides the foundation and most commonly used objects(such as Connection, ResultSet, Statement, and PreparedStatement). Also, this package provides classes and interfaces to get both database and result set metadata from the database server. This package has a set of classes and interfaces (such as DatabaseMetaData and ResultSetMetaData) that deal with database metadata, which will be one of the focuses of this book.

 javax.sql provides the API for server-side data source access. According to the Java Development Kit(JDK) documentation, “This package supplements the java.sql package and, as of the version 1.4 release, is included in the JDK. It remains an essential part of the Java 2 SDK, Enterprise Edition (J2EE).” This package provides services for J2EE(such as DataSource and RowSets). Also, the package has a set of classes and interfaces(such as RowSetMetaData) that deal with row set metadata. In this book we focus on the metadata components of this package.

 In a nutshell, JDBC is a database-independent API for accessing a relational database. You pass SQL to Java methods in the JDBC classes(the packages java.sql and javax.sql) and get back JDBC objects(such as ResultSet, DatabaseMetaData, and ResultSetMetaData) that represent the results of your query. JDBC is designed so simply that most database programmers need learn only a few methods to accomplish most of what they need to do.

Figure shows how a database application(such as a Java application/applet/servlet) uses JDBC to interact with one or more databases.

Java database application using JDBC

Figure presents the basic outline of the JDBC architecture. JDBC’s DriverManager class provides the basic service for managing a set of JDBC drivers. The DriverManager loads JDBC drivers in memory, and can also be used to create java.sql.Connection objects to data sources(such as Oracle and MySQL). 

Note that you can have more than one driver and therefore more than one database.

Figure illustrates how a Java application uses JDBC to interact with one or more relational databases(such as Oracle and MySQL) without knowing about the underlying JDBC driver implementations. Figure illustrates the core JDBC classes and interfaces that interact with Java and JDBC applications. This figure also shows the basic relationships of the DatabaseMetaData and ResultSetMetaData interfaces with other JDBC objects.

Using JDBC database metadata

The following are core JDBC classes, interfaces, and exceptions in the java.sql package:

• DriverManager: This class loads JDBC drivers in memory. It is a “factory” class and can also be used to create java.sql.Connection objects to data sources(such as Oracle, MySQL, etc.).
• Connection: This interface represents a connection with a data source. The Connection object is used for creating Statement, PreparedStatement, and CallableStatement objects.
• DatabaseMetaData: This interface provides detailed information about the database as a whole. The Connection object is used for creating Database MetaData objects.
• Statement: This interface represents a static SQL statement. It can be used to retrieve ResultSet objects.
• PreparedStatement: This interface extends Statement and represents a precompiled SQL statement. It can be used to retrieve ResultSet objects.
• CallableStatement: This interface represents a database stored procedure. It can execute stored procedures in a database server.
• ResultSet: This interface represents a database result set generated by using SQL’s SELECT statement. Statement, PreparedStatement, CallableStatement, and other JDBC objects can create ResultSet objects.
• ResultSetMetaData: This interface provides information about the types and properties of the columns in a ResultSet object.
• SQLException: This class is an exception class that provides information on a database access error or other errors.