JDBC accomplishes Java/database separation with a
driver manager.
Simply by changing driver managers, a Java application can change the
kind of database (Apache, MySQL, Oracle...) with which it communicates:
- Manages connections to a particular database system
- Translates JDBC statements to the specific commands of the
database system
- Implements the interfaces and extends the classes in the
java.sql package:
- Connection
- DriverManager
- Statement
- PreparedStatement
- ResultSet
The
Statement interface has methods that take a string
representing an SQL
command and executes it against the database:
- execute: for table creation (CREATE)
- executeUpdate: for table updating (INSERT,
UPDATE, and DELETE)
- executeQuery: for table query (SELECT)
Suppose we want to retrieve a list of vehicle identification numbers
(vins) from the
Cars database under Java program control.
If the instance field
carsSource is of
type
DataSource and has been suitably injected to access
the
Cars database (see
Data
Sources), then we can do:
String query = "SELECT vin FROM car";
Connection carsConnection = carsSource.getConnection();
Statement statement = carsConnection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
// use the result here
carsConnection.close();
A
ResultSet object is returned by the
executeQuery
method.
Result sets have iterators attached to them that allow you to create,
for example, a
java.util.List.
Suppose that
columnName names a column in the result set
of
VARCHAR type:
List<String> vinList = new ArrayList<String>();
while (resultSet.next()) {
vinList.add(resultSet.getString(columnName));
}
The list can then be used to populate a JSF input selection component:
<h:selectOneListbox value="#{ownerQuery.vin}">
<f:selectItems value="#{database.vinList}"/>
</h:selectOneListbox>
Note that the result set might have column values that are not strings.
In that case you would use the appropriate
ResultSet
method:
getInt,
getDouble,
getBlob, etc.
Opening and closing database connections using data sources
involves connection pools.
Each JSF cycle that uses a database must open and close a connection; you
can't hold onto a connection across multiple JSF cycles.
If there are
SQLExceptions during database processing and they aren't
properly caught, impatient users can repeatedly make doomed requests that
cause database connection leaks.
You can make sure that a connection is properly closed by wrapping the call
to
close in a
finally block.
The call to
close can itself throw a
(checked)
SQLException which must either be caught or declared.
Recommendation: make each method that deals with the database have the
following form:
someDBMethod() throws SQLException {
String query = ... ;
Connection carsConnection = carsSource.getConnection();
try {
// make the query and process result
} finally {
carsConnection.close();
}
}
A simple SQL query such as our first example:
SELECT vin FROM car
is easy enough to include in Java code, but most queries are not so
simple. Consider:
SELECT
car.vin as "VIN",
manufacturer."name" AS "Manufacturer",
model."name" AS "Model"
FROM car, manufacturer, model
WHERE
car.vin = 0123456789abcdefg
AND car.model = model.id
AND model.manufacturer = manufacturer.id
It is better to separate query strings from Java code by storing them
in files, as we show in this section.
The
Cars web application has a folder under source packages
called
sql.
- It includes files with a .sql suffix that contain queries used
in JDBC calls from Java code, and
- An SQL.java source class that creates strings from the
contents of any .sql file:
Instead of coding query strings directly in Java, use the static
method
getSQL in the
SQL class.
SQL.getSQL takes a string argument that should name a file that
contains an SQL command:
- The file must have a .sql suffix
- The file must be in the same folder as SQL.java
- The argument to SQL.getSQL should not include
the .sql suffix
Suppose the query
SELECT vin FROM car
is contained in
vin-query.sql. Then our statement example from
before should have:
String query = SQL.getSQL("vin-query");
...
ResultSet resultSet = statement.executeQuery(query);
You are encouraged to use the the
SQL.java class in your own
web apps.
Since
SQL.getSQL is dealing with files, it could possibly throw
an
IOException, which can be handled like this:
someDBMethod() throws SQLException, IOException {
String query = SQL.getSQL(...) ;
Connection carsConnection = carsSource.getConnection();
try {
// make the query and process result
} finally {
carsConnection.close();
}
}
The complete contents of an SQL query often cannot be known before web
app run time.
This section shows how to handle this with
prepared statements.
In the
Cars application the user chooses a vin from
a selection list.
Suppose the chosen vin is
0123456789abcdefg. Then to get the
vehicle's owner we would need a file, say
owner-query.sql, that
contains:
SELECT person."name" FROM person, ownership, car
WHERE
ownership.owner = person.id
AND ownership.car = car.id
AND car.vin = 0123456789abcdefg
But the vin cannot be known at the time this file is created.
A
PreparedStatement object represents a "parameterized" SQL
statement.
That is, the statement can have occurrences of "
?" that are
intended to be replaced by values at run time:
SELECT person."name" FROM person, ownership, car
WHERE
ownership.owner = person.id
AND ownership.car = car.id
AND car.vin = ?
- You get a PreparedStatement object through the
prepareStatement method in the Connection class
- prepareStatement takes a (parameterized) SQL query string as
argument (compare to createStatement which takes no
arguments)
- You put in a value for the "?" in a prepared statement
using set<type> methods, where <type>
stands for the type of the value
- With a prepared statement, executeQuery takes no parameters (the
statement already knows about the SQL string)
Suppose
owner-query.sql contains the parameterized statement
just shown, and
vin is a string containing a specific vin:
String query = SQL.getSQL("owner-query");
Connection carsConnection = carsSource.getConnection();
try {
PreparedStatement statement =
carsConnection.prepareStatement(query);
statement.setString(1, vin);
ResultSet resultSet = statement.executeQuery();
// use the result here
} finally {
carsConnection.close();
}
Recall that the JSF
h:dataTable tag can take as a value the
result returned by an SQL query of a database.
This section re-presents the relevant part of Chapter 6.