Database Reference
In-Depth Information
Connection conn = DriverManager.getConnection("jdbc:default:connection");
stmt = conn.createStatement();
We use our instance of
Connection
to create a
Statement
by calling
conn.createStatement()
. Note
that these are the standard Java classes used in Java to talk to any vendor's SQL database, including SQL
Server and DB2. Yet, because we requested (or received by default) the
OracleDriver
to give us the
Connection
, we have a
Connection
that is specific to Oracle database.
Each database vendor must implement these standard interfaces (Connection and Statement) to
enable our Java code to talk with their databases using vendor-specific drivers. The Oracle-specific
drivers and classes are what we get by including
ojdbc6.jar
in our
CLASSPATH
. There are several additional
types of
Statements
and
Connections
that we will use—the variations being specific to Oracle database.
ResultSet
Once you have your
Statement
object, you can execute Oracle commands, like the one that we execute in
the example, Listing 4-1 and repeated in the following. When we execute a query by calling
stmt.executeQuery()
, we expect to get data back. Data from a query is returned in a
ResultSet
object (
rs
in our example). By calling the
next()
method of the
ResultSet
, we bring the values from the next (first)
row into the
ResultSet
object so we can request each value (each column in the row specified in the
query) with a “getter” method, like the call to
rs.getString()
in our example code.
ResultSet rs = stmt.executeQuery( "select sysdate from dual" );
if( rs.next() ) {
timeString = rs.getString(1);
}
An interesting thing about the
ResultSet
“getter” methods is that, for some types that are returned,
a translation can be done automatically. For example, in our code, we are requesting a
Date
type (value),
because in our query we
SELECT
ed (asked for) the value of
SYSDATE
which is the time and date of the
Oracle database. Yet in our call to the
ResultSet
we asked to
getString()
, so the
ResultSet
does a
translation from
Date
type to
String
type.
Picture the data being returned in the
ResultSet
as a spreadsheet with rows of data entries, and for
each row there are multiple columns of associated values. When you call
ResultSet.next()
, you are
getting the equivalent of the next row into the current
ResultSet
values space. Once there, you can get
the value from each column using the column number. In our example, we get the value from the first
column with the statement:
rs.getString(1);
Uniquely, the index for a
ResultSet
is 1-based; that is, the first column value is at index 1. This is
different from everything else in Java (that I can think of). Java arrays and
Collections
are typically 0-
based, and the first element is at index 0. Also, character places in a
String
start with 0.
We will almost always want to see if there is any data being returned in the
ResultSet
before we try
to use it. For that reason, we put a
ResultSet.getString()
call in the
if
condition statement that tests
whether there is a next row. Alternatively, we could just say:
rs.next();
rs.getString(1);
However, if we did this and there were no “next,” then the
ResultSet
values space would be
null
,
and when we call
rs.getString(1)
, we'd generate an
Exception
.
