Database Reference
In-Depth Information
class. The constructor with no arguments creates a
Date
with the current date and time. Frequently with
Oracle database, we will be using instances of
java.sql.Date
(notice the package
java.sql
instead of
java.util
), which does not have such a constructor; however, we can construct a
java.sql.Date
with the
long (milliseconds) coming from the
java.util.Date.getTime()
method. We could also use the same
statement used by
java.util.Date()
constructor to instantiate a
java.sql.Date
object, like this:
java.sql.Date( System.currentTimeMillis() );
I have one observation about the syntax we use in our code. The parentheses around our instantiation of a
( new java.util.Date() )
allow us immediate access to it as an object, to its
getTime()
method, like
this:
stmt.setDate( 6,
new Date( ( new java.util.Date() ).getTime())
);
Okay, so why doesn't
java.sql.Date
have a
Date()
constructor, didn't it inherit one by extending
java.util.Date
? Oh, excellent question! The short answer is that constructors are not considered
members of a class, so they are not inherited by the subclass. You can, however, get access to the parent
constructors by a call to the
super()
method as the first line in any subclass constructor. There can be
multiple
super()
methods with differing signatures, one for each constructor in the parent class.
We will be coming back to the subject of
java.sql
and
java.util
Dates
again in Chapter 9. We need a
standard practice for exchanging dates, and I will propose one there.
Selecting a Single Row from EMPLOYEES
Perhaps we don't want to select the entire table, but only records that meet a certain criteria. We can
provide criteria to be made part of the
WHERE
clause in our query. In this procedure,
p_select_employee_by_id_sens
, Listing 7-25, we are providing a tenth parameter, the
EMPLOYEE_ID
.
Listing 7-25.
Select by ID, Sensitive Data from Employees, from
p_select_employee_by_id_sens
stmt = ( OracleCallableStatement )conn.prepareCall(
"CALL hr.hr_sec_pkg.p_select_employee_by_id_sens(?,?,?,?,?,?,?,?,?,?)" );
...
stmt.setInt( 10, 300 )
; // Employee ID 300
stmt.executeUpdate();
The remainder of the procedure call is identical to our previous query examples, except that we only
expect one or zero records to be returned (the
EMPLOYEE_ID
is a
UNIQUE
key for this table.) So we can
handle the
ResultSet
in an
if( rs.next() )
block instead of
while( rs.next() )
.
Selecting EMPLOYEES Data by Last Name: Try SQL Injection
We can also query all records meeting some other criteria that is perhaps not
UNIQUE
. One example that
we will show queries
EMPLOYEES
by
LAST_NAME
using the
p_select_employee_by_ln_sens
procedure, in
partial Listing 7-26. There are two entries with the
LAST_NAME
“King”. So this would return two rows.
Listing 7-26.
Select by Last Name, Sensitive Data from Employees, from
p_select_employee_by_ln_sens
stmt = ( OracleCallableStatement )conn.prepareCall(