JDBC Project Description

Java Database Connectivity (JDBC™) Overview

The JDBC API is a collection of Java classes and interfaces that enables you to access, manipulate, and persist data to a data source. Most of the time the data source is a relational database like Oracle, Informix, or MySQL. (There are many others!) However, the data source could very well be a spreadsheet, a flat file, or some other type of tabular data.

JDBC™ Packages

The JDBC API comes in two packages: java.sql and javax.sql. The java.sql package, referred to as the JDBC core API, provides the bulk of the JDBC functionality while the javax.sql package, referred to as the JDBC Optional Package API, provides server-side JDBC support. The JDBC programming examples in this chapter will utilize a subset of the interfaces and classes found in the java.sql package.

JDBC™ Specification

JDBC is also a specification. Most of what the API provides is in the form of interfaces. Java platform vendors like Sun or Apple must provide implementation classes to fulfill the contract specified by these interfaces. Database vendors are also responsible for providing suitable JDBC driver classes that allow Java programs to connect to their databases via JDBC.

Using JDBC — A Macro View

Using JDBC to connect and interact with a database is like solving a puzzle that has the following pieces:

• A database that’s been properly configured (i.e., tables, relationships, access rights, etc.)

• A suitable database JDBC driver class (provided by the database vendor)

• Knowledge of relational database design (if you are accessing a relational database)

• Knowledge of the database schema you are going to access

• Knowledge of SQL

• A Java program that uses the JDBC API to connect to the database

Properly Configured Database

If you own the database then you will have to set things up yourself. This means you must get smart on how to install and configure your particular vendor’s database product.

WARNING:   Not all things potentially possible with JDBC are supported by all database products. For instance, JDBC provides a way to execute stored procedures. If your database product does not support stored procedures then you will not be able to use this aspect of JDBC.

JDBC Driver Class

The database vendor will supply the JDBC driver. However, you must track down the driver class(es) or jar file and make sure it is installed on your machine and that the classpath is properly set to find it. If you can’t find this information in your database’s documentation chances are you can find it easily on the internet.

Knowledge of Relational Database Design

If you are planning to use JDBC to access a relational database then you must possess, at the very least, a rudimentary understanding of relational database concepts. You must understand the concepts of tables, columns, and rows. You must understand the concepts of primary keys and foreign keys and how they are used to establish relationships between tables.

Knowledge of the Database Schema

As I said above, there’s no need to be an expert in relational database design theory — you just need to know a little something about it so you can read and understand the database schema against which you will write your JDBC code.

knowledge of SQL

Your JDBC code will include Structured Query Language (SQL) statements. You must know the difference between Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements. You must get comfortable writing select, insert, update, and delete statements.

The Java JDBC Program

Lastly, all this knowledge comes together in the form of a Java program that utilizes the JDBC API to connect to and manipulate data in a relational database. If your program is relatively simple, like the primary JDBC program presented in this chapter, then you will survive well by arming yourself with a basic understanding of all these subject areas. However, increasingly complex projects demand a corresponding increase in competence in each area.

General Steps Required To Employ JDBC

JDBC is used in a Java program to establish a connection to a database for the purposes of accessing, manipulating, or storing data. The following general steps describe the process of using JDBC to connect to a database and execute SQL statements:

• Step 1: Load the database diver using the Class.forName() method

• Step 2: Use the DriverManager class’s getConnection() method to create a Connection object

• Step 3: Use the Connection object to create a Statement or PreparedStatement object as required

• Step 4: Use the Statement or PreparedStatement object to submit an SQL statement to the database

• Step 5: If applicable, use the ResultSet object obtained from step 4 to process the retrieved data

Each of these steps can be executed at different locations within your program at different times. For instance, you usually have to load the JDBC driver class just once. In complex applications this may be already done for you as part of the application’s environment initialization. You generally only need to establish the connection to the database once as well and keep it open as long as you need access to the database. Statements, including PreparedStatements, might need to be created and closed many times during the course of application execution.

Moving Forward

As you have by now concluded, learning to use JDBC dictates that you must learn a few new tricks as well. If you are a complete novice in the areas of relational database concepts and SQL then I recommend you procure a few reference books on those topics and study up. I have listed several good ones at the end of this chapter. I will attempt to explain, as simply as I can, what you need to know about these topics, so you can move forward with the remaining chapter material.

Quick Review

JDBC™ is an API that lets you access data sources from Java programs. The JDBC API comes in two packages: the java.sql package contains the JDBC core API and the javax.sql package contains the JDBC Optional Package API. The JDBC API consists mostly of interfaces which must be implemented by Java platform vendors. Database vendors must also supply JDBC driver classes to facilitate JDBC connectivity to their database products.