Java Reference
In-Depth Information
JDBC
Packages
java.sql JDBC 2.1 Core API
javax.sql JDBC 2.0 Optional package
Use:
J2SE (1.0; restructured to JDBC 2.1 in 1.2)
Overview
Databases are everywhere; it's hard to imagine a big enterprise application without some kind of persistence. To
access the data in those databases from Java, you can use Java Database Connectivity (JDBC). JDBC is a generic
SQL database access framework that provides a uniform interface on top of a variety of different database
connectivity modules. JDBC provides a way to manipulate the data in a database independent from any particular
DBMS.
One of the challenges with this kind of framework is that each database can have its own SQL version, with
minor but important differences. The framework had to be flexible as well as simple. This results in an API with
only a few interfaces, and only a few methods in each interface. The consequence is that JDBC is reasonably easy
of use.
To communicate with the database, you need a driver that understands and speaks the databases' protocol. You
can get this drive with the database from the vendor, or from some third party. The driver contains
implementations for the interfaces specific for this protocol.
Every driver has a class that implements the
Driver
interface. When the class is loaded, it creates an instance of
itself and registers with the
DriverManager
. The
DriverManager
keeps a list of drivers it can use. When a
connection is requested the
DriverManager
tries to locate a suitable driver.
DriverManager
checks its list of
drivers and starts with the first driver specified at creation time (reading from the
jdbc.drivers
property) and
continues until a suitable driver has been located. Drivers that were loaded during execution are added to the end
of the list, so they are tried as well, but later. After a suitable driver is found, the
getConnection
method returns
a
Connection
instance. The
Connection
object represents the session with the database. When a client calls the
createStatement
method on a Connection object, the
Connection
object creates a
Statement
objects for
executing SQL queries on the database. Other types of Statements for more specialized purposes are available, as
well.
The
Statement
object is the object that receives a SQL statement as a String from the client and executes the
query on the database to change or retrieve information. Depending on the type of query, either executeUpdate or
exceuteQuery is called. The
Statement
returns a ResultSet when information is requested (SELECT).
The
ResultSet
object is a representation of a table of data that encapsulates the result of the executed SELECT.
Every time you call the
next
method on the result set, to iterate through the data table, the cursor is set to the next
line in the results. When the cursor is moved to the next line, you can retrieve the values in the columns of that
specific line. To read other lines, you call the
next
method multiple times.
Databases tend to grow fairly large so the results could be big, as well. To prevent memory problems from occurring, the
ResultSet
fetches only a limited number of rows in batches. When the end of the current batch is reached, the
ResultSet
requests a new batch from the database. This is transparent for the user.Typical use could b
e as shown in
Example 7.2
:
Example 7.2
Obtaining results from a database
Connection con = DriverManager.getConnection("some url");
Statement stmt = con.createStatement();
String query = "SELECT * FROM students WHERE " +
" iq GREATER THAN 140 AND sociallife='non-existent'";
ResultSet nerds = stmt.executeQuery(query);
while (nerds.next()) {
String name = nerds.getString(1);
int iq = nerds.getInt(2);
//read entries from the resultset and process them
}
