This section will be a brief introduction to JDBC, addressing the basics of JDBC, issues,
caveats, and so forth. For more detailed information, visit the JDBC website (http://java.sun.com/products/jdbc/), which has many good resources and
will always provide the most up to date information. Also, the API documentation included with
your JDK has detailed information on specific classes, methods, and fields. Look for the
java.sql package.
JDBC has classes to represent most of the basic pieces of a program’s interaction with
SQL. The classes are: Connection, Statement, ResultSet, Blob, and Clob, and they all map
directly to some concept in SQL. JDBC also has helper classes, such as ResultSetMetaData and DatabaseMetaData, that represent
meta-information. These are useful for when you’d like to get information about the
capabilities of the database. They are also useful for getting the types of results returned by
a query, either for debugging, or because you don’t know about the data you are dealing
with.
PostgreSQL’s JDBC interface also provides classes to map to PostgreSQL’s non-standard
extensions to JDBC’s SQL support. These non-standard extensions include: Fastpath, geometric types, native large objects, and a class that aids
serialization of Java objects into the database.
Example 12-2 used a Connection object, representing
a physical connection to the database. You can use this Connection object
to create Statement objects. Statement objects are
JDBC’s way of getting SQL statements to the database.
There are three main types of Statement objects: the base class
Statement, the PreparedStatement, and the CallableStatement.
To create a Statement object, use the createStatement method as shown in Example 12-3:
Example 12-3 creates a Statement object
named s, from the Connection object c. You can now use this Statement object to execute queries and
updates on the database.
There are two main methods in the Statement class that are important.
The first is executeQuery. This method takes one argument, the SQL
statement to be executed, and returns an object of type ResultSet, which is
discussed later. This method is used for executing queries which will return a set of data
back, for instance, a SELECT statement. The ResultSet
object returned represents the data resulting from the query.
Example 12-4 retrieves some data from the booktown database:
Example 12-4. A simple JDBC select
Statement s = null;
try {
s = c.createStatement();
} catch (SQLException se) {
System.out.println("We got an exception while creating a statement:" +
"that probably means we're no longer connected.");
se.printStackTrace();
System.exit(1);
}
ResultSet rs = null;
try {
rs = s.executeQuery("SELECT * FROM books");
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
}
int index = 0;
try {
while (rs.next()) {
System.out.println("Here's the result of row " + index++ + ":");
System.out.println(rs.getString(1));
}
} catch (SQLException se) {
System.out.println("We got an exception while getting a result:this " +
"shouldn't happen: we've done something really bad.");
se.printStackTrace();
System.exit(1);
}Example 12-4 creates a Statement object, and then uses
that Statement object’s executeQuery method to execute
the query SELECT * FROM books. You get back a ResultSet,
and use that ResultSet to print out some of the information you got
back.
The ResultSet object is our primary interface for fetching information
from the database. It has two main features. It can step through the set of rows returned, and
it can return the value for a specific column in that row. It works in a similar fashion to a
standard Java Enumeration: it starts before the first element, and you use the next method to step through the rest of the elements.
next returns true if the ResultSet was able to step
to the next results; that is to say, there are results to be read. The while loop in Example 12-4 will print out the first column
of each of the rows returned. If no rows were returned, next will return
false initially, representing this fact, and therefore nothing will be printed.
ResultSet can return values of all sorts of different types; Example 12-4 treats the first column as if it were a String. Fortunately, all standard SQL data types can be represented as String, so regardless of the type of the first column, you will be able to fetch
the value of the first column and print it out. There are many other methods available on
ResultSet, including methods for fetching all of the various SQL data
types and converting them to native Java types. Consult the API documentation on ResultSet for more information.
The other important method is executeUpdate. This method, again, takes
one argument, which is the SQL statement to be executed. The difference between executeQuery and executeUpdate is that executeUpdate is for executing statements that change data in the database. For
example, use executeUpdate to execute a CREATE an
INSERT or an UPDATE statement. executeUpdate returns an int, and the value of that int corresponds to the number of records that were modified.
Example 12-5 uses the executeUpdate method to
insert a new row into the books table.
Example 12-5. A simple JDBC insert
Statement s = null;
try {
s = c.createStatement();
} catch (SQLException se) {
System.out.println("We got an exception while creating a statement:" +
"that probably means we're no longer connected.");
se.printStackTrace();
System.exit(1);
}
int m = 0;
try {
m = s.executeUpdate("INSERT INTO books VALUES " +
"(41472, 'Practical PostgreSQL', 1212, 4)");
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
}
System.out.println("Successfully modified " + m + " rows.\n");As
mentioned earlier, besides the basic Statement object, there are two
additional types of statements available in JDBC: PreparedStatements and
CallableStatements. These two types are described later in this
section.
In addition to these statements, this section also describes the use of the ResultSetMetaData and DatabaseMetaData objects. You can use
these last two objects to interrogate JDBC for information about a given set of query results,
or for information about your database. The ability to get such information at run-time
enables you to dynamically execute any SQL statement, even one that is unknown when you write
your program.
Callable statements are implemented by the CallableStatement object.
A CallableStatement is a way to execute stored procedures in a
JDBC-compatible database. The best reference for this is Sun’s Javasoft web site
(http://java.sun.com/products/jdbc/), because callable
statements represent a changing and evolving standard, and their application will depend
greatly on your version of Java, and JDBC.
A PreparedStatement, in contrast to a CallableStatement, is used
for SQL statements that are executed multiple times with different values. For instance, you
might want to insert several values into a table, one after another. The advantage of the
PreparedStatement is that it is pre-compiled, reducing the overhead of
parsing SQL statements on every execution. Example 12-6 is an
example of how a PreparedStatement might be used.
Example 12-6. A JDBC prepared statement
PreparedStatement ps = null;
try {
ps = c.prepareStatement("INSERT INTO authors VALUES (?, ?, ?)");
ps.setInt(1, 495);
ps.setString(2, "Light-Williams");
ps.setString(3, "Corwin");
} catch (SQLException se) {
System.out.println("We got an exception while preparing a statement:" +
"Probably bad SQL.");
se.printStackTrace();
System.exit(1);
}
try {
ps.executeUpdate();
} catch (SQLException se) {
System.out.println("We got an exception while executing an update:" +
"possibly bad SQL, or check the connection.");
se.printStackTrace();
System.exit(1);
}You can see that Example 12-6 prepares a statement in a
similar fashion as before, except it uses a question mark (?) character in
place of each value that you want to supply. Use the appropriate PreparedStatement set method (e.g., setInt, setString) to set each value. The specific set method that you use for a column
depends on the data type of the column.
The PreparedStatement approach is useful because it avoids manual
conversion of Java types to SQL types. For instance, the you do not have to worry about
quoting or escaping when going to a text type.
Notice that the first parameter passed to a set method indicates the specific placeholder parameter (the question marks) that you are setting. A value of 1 corresponds to the first question mark, a value of 2 corresponds to the second, and so on.
The other strength of the PreparedStatement is that you can use it
over and over again with new parameter values, rather than having to create a new Statement
object for each
new set of parameters. This approach is obviously more efficient, as only one object is
created.
Use the set methods each time to specify new parameter values.
You can interrogate JDBC for detailed information about a query’s result set using a
ResultSetMetaData object. ResultSetMetaData is a class
that is used to find information about the ResultSet returned from a
executeQuery call. It contains information about the number of columns,
the types of data they contain, the names of the columns, and so on.
Two of the most common methods in the ResultSetMetaData are getColumnName and getColumnTypeName. These retrieve the name
of a column, and the name of its associated data type, respectively, each in the form of a
String.
Note
The getColumnType method is not the same as the
getColumnTypeName. getColumnType returns an int corresponding to a data type’s internal JDBC identification code, whereas
getColumnTypeName returns the name as a String.
Example 12-7 is an example of using the ResultSetMetaData to get the name and data type of the first column in a ResultSet called rs. This code could logically follow the
acquisition of the ResultSet named rs in Example 12-4.
Overall, the PreparedStatement mechanism is considerably more robust
than the Statement class.
Example 12-7. JDBC ResultSetMetaData
ResultSetMetaData rsmd = null;
try {
rsmd = rs.getMetaData();
} catch (SQLException se) {
System.out.println("We got an exception while getting the metadata:" +
"check the connection.");
se.printStackTrace();
System.exit(1);
}
String columnName = null,
columnType = null;
try {
columnName = rsmd.getColumnName(1);
columnType = rsmd.getColumnTypeName(1);
} catch (SQLException se) {
System.out.println("We got an exception while getting the column name:" +
"check the connection.");
se.printStackTrace();
System.exit(1);
}
System.out.print("The name of the first column is: '");
System.out.print(columnName);
System.out.println("'");
System.out.print("The data type of the first column is: ");
System.out.println(columnType);There are many other useful methods in the ResultSetMetaData class,
all of which are well documented in the JDK API documentation.
Finally, DatabaseMetaData is a class that can be used to fetch
information about the database you are using. Use it to answer questions such as:
What kind of catalogs are in the database?
What brand of database am I working with?
What username am I?
Example 12-8 uses DatabaseMetaData to
query the JDBC driver for the username used to establish the connection, and the database
URL.
Example 12-8. JDBC DatabaseMetaData
DatabaseMetaData dbmd = null;
try {
dbmd = c.getMetaData();
} catch (SQLException se) {
System.out.println("We got an exception while getting the metadata:" +
" check the connection.");
se.printStackTrace();
System.exit(1);
}
String username = null;
try {
username = dbmd.getUserName();
} catch (SQLException se) {
System.out.println("We got an exception while getting the username:" +
"check the connection.");
se.printStackTrace();
System.exit(1);
}
String url = null;
try {
url = dbmd.getURL();
} catch (SQLException se) {
System.out.println("We got an exception while getting the URL:" +
"check the connection.");
se.printStackTrace();
System.exit(1);
}
System.out.println("You are connected to '" + url +
"' with user name '" + username + "'");Once again, the best source for the most current information about DatabaseMetaData’s many other methods is in the JDK API documentation.