لطفا منتظر باشید ...
18.1 Accessing a Database
Example 18-1 shows
a program that connects to a database and then loops, prompting the
user for a SQL statement, sending that statement to the database, and
displaying the results. It demonstrates the four most important
techniques for JDBC programming: registering a database driver, using
the DriverManager class to obtain a
Connection object that represents a database
connection, sending a SQL statement to the database using the
Statement object, and retrieving the results of a
query with a ResultSet object. Before we look at
the specifics of the ExecuteSQL program,
let's examine these basic techniques.One
of the interesting things about the java.sql
package is that its most important memberssuch as
Connection, Statement, and
ResultSetare interfaces instead of classes.
The whole point of JDBC is to hide the specifics of accessing
particular kinds of database systems, and these interfaces make that
possible. A JDBC driver is a set of classes that implement the
interfaces for a particular database system; different database
systems require different drivers. As an application programmer, you
don't have to worry about the implementation of
these underlying classes. All you have to worry about is writing code
that uses the methods defined by the various interfaces.The DriverManager
class is responsible for keeping track of all the JDBC drivers that
are available on a system. So the first task of a JDBC program is to
register an appropriate driver for the type of database being used.
By convention, JDBC driver classes register themselves with the
DriverManager when they are first loaded, so, in
practice, all you have to do is load the driver class, allowing it to
register itself. The Class.forName( ) method
provides one easy way of doing this. This method takes a
String argument that specifies a class name, so
it's simple to pass the driver name to the program
on the command line, instead of hardcoding the driver class into your
program. Note that this step simply loads a driver and registers it
with the DriverManager; it
doesn't specify that the program actually use that
driver. If a program needs to use multiple databases, it can load
multiple driver classes in this step. The driver selection step comes
next, when the program actually connects to a database.After the required driver is loaded
(and has registered itself), a JDBC program can connect to the
database by calling DriverManager.getConnection(
). You specify the database to connect to with a
jdbc: URL. This URL has this general syntax:
jdbc:subprotocol://host:port/databasename
The subprotocol of the URL identifies the
particular database system that is being used. The
DriverManager class uses that part of the URL to
select an appropriate JDBC driver from the list of drivers that have
been registered. If the DriverManager
can't find a JDBC driver for the database, it throws
a SQLException.
I
used the MySQL database while developing the examples in this
chapter, so I had to use a URL like the following to connect to my
database:
jdbc:mysql://dbserver.mydomain.com:1234/mydb
This URL specifies that JDBC should connect to the database named
"mydb" stored on a MySQL database
server running on the host dbserver.mydomain.com
and listening for connections on port 1234.
If
you are running the database server on the same host your Java
program is running on, you can omit the host name portion of the URL.
If your database server is listening on its default port (which it
usually does), you can omit the port number. For example:
jdbc:mysql:///mydb
Here's another jdbc: URL that
works for a local PosgreSQL server:
jdbc:postgresql:///mydb
DriverManager.getConnection(
) returns an object that implements the
Connection interface. This object represents the
connection to the database; you use it to interact with the database.
The createStatement( ) method of the
Connection object creates an object that
implements the Statement interface, which is what
you use to send SQL queries and updates to the database. The
executeQuery( ) and executeUpdate(
) methods of the Statement object send
queries and updates, respectively, while the general-purpose
execute( ) method sends a statement that can be
either a query or an update.After you send a query to the
database, use the getResultSet( ) method of
Statement to retrieve an object that implements
the ResultSet interface. This object represents
the values returned by the SQL query; it is organized into columns
and rows like a table. A ResultSet offers its data
one row at a time; you use next( ) to move from
the current row to the next row. ResultSet
provides numerous
getX(
) methods that allow you to retrieve the data from each
column of the current row as a number of different types.The JDBC API was updated in Java 1.2
to JDBC 2.0. In JDBC 2.0, result sets can be configured to be
scrollable, which means that in addition to the next(
) method, you can also use the previous(
) method to move to the previous row, first(
) and last( ) to move to the first and
last rows, respectively, and absolute( ) and
relative( ) to move to an arbitrary row specified
with an absolute or relative row number. If your database server
supports scrollable result sets, and if you're using
a JDBC 2.0-compliant driver, you can specify scrollable result sets
when you create your Statement object. To do this,
use the two-argument version of the createStatement(
) method, with code like this:
Statement s = connection.
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
There are other possible values for the two arguments to
createStatement( ), but a discussion of them is
beyond the scope of this chapter. See Java Enterprise in a
Nutshell for further information.Now that you understand the basic
techniques used in a JDBC program, let's go on to
Example 18-1. The ExecuteSQL
program uses all the techniques just discussed to connect to a
database, execute SQL statements, and display the results. The
program parses its command-line arguments to determine the class name
of the JDBC driver, the URL of the database, and other parameters
necessary to connect to the database. For example, you might invoke
the ExecuteSQL program and enter a simple query
like this (note that this long Java command line has been broken in
two here):
% java je3.sql.ExecuteSQL -d com.mysql.jdbc.
Driver -u java -p nut jdbc:mysql:///apidb
sql> SELECT * FROM package WHERE name LIKE '%.rmi%'
+----+--------------------------------+
| id | name |
+----+--------------------------------+
| 14 | java.rmi |
| 15 | java.rmi.dgc |
| 16 | java.rmi.registry |
| 17 | java.rmi.server |
+----+--------------------------------+
sql> quit
Note that this example uses a database table that contains Java
package names. If you don't have an existing
database with tables to experiment with, you may want to skip ahead
to the MakeAPIDB program of Example 18-3 and create some tables to play with.Notice
that ExecuteSQL uses the execute(
) method of its Statement object to
execute SQL statements. Since the user can enter any kind of SQL
statement, you have to use this general-purpose method. If
execute( ) returns true, the
SQL statement was a query, so the program retrieves the
ResultSet and displays the results of the query.
Otherwise, the statement was an update, so the program simply outputs
information about how many rows in the database were affected by the
update.The
printResultsTable( ) method handles displaying the
results of a query. This method gets a
ResultSetMetaData object to find out some
information about the data returned by the query, so it can format
the results appropriately.There are two other important
JDBC programming techniques to note in Example 18-1.
The first is the handling of SQLException
exceptions that are thrown. The SQLException
object supports the standard exception message with
getMessage( ), but it may also contain an
additional message sent by the database server. You obtain this
message by calling the getSQLState( ) method of
the exception object.The second technique is the
handling of warnings. The SQLWarning class is a
subclass of SQLException, but warnings, unlike
exceptions, are not thrown. When a SQL command is executed, any
warnings reported by the server are stored in a linked list of
SQLWarning objects. You obtain the first
SQLWarning object in this list by calling the
getWarnings( ) method of the
Connection object. If there are any additional
SQLWarning objects, you get the next one by
calling the getNextWarning( ) method of the
current SQLWarning object. In Example 18-1, these warnings are displayed using a
finally clause, so that they appear both when an
exception is thrown and when execution completes normally.
Example 18-1. ExecuteSQL.java
package je3.sql;
import java.sql.*;
import java.io.*;
/**
* A general-purpose SQL interpreter program.
**/
public class ExecuteSQL {
public static void main(String[ ] args) {
Connection conn = null; // Our JDBC connection to the database server
try {
String driver = null, url = null, user = ", password = ";
// Parse all the command-line arguments
for(int n = 0; n < args.length; n++) {
if (args[n].equals("-d")) driver = args[++n];
else if (args[n].equals("-u")) user = args[++n];
else if (args[n].equals("-p")) password = args[++n];
else if (url == null) url = args[n];
else throw new IllegalArgumentException("Unknown argument.");
}
// The only required argument is the database URL.
if (url == null)
throw new IllegalArgumentException("No database specified");
// If the user specified the classname for the DB driver, load
// that class dynamically. This gives the driver the opportunity
// to register itself with the DriverManager.
if (driver != null) Class.forName(driver);
// Now open a connection to the specified database, using the
// user-specified username and password, if any. The driver
// manager will try all of the DB drivers it knows about to try to
// parse the URL and connect to the DB server.
conn = DriverManager.getConnection(url, user, password);
// Now create the statement object we'll use to talk to the DB
Statement s = conn.createStatement( );
// Get a stream to read from the console
BufferedReader in =
new BufferedReader(new InputStreamReader(System.in));
// Loop forever, reading the user's queries and executing them
while(true) {
System.out.print("sql> "); // prompt the user
System.out.flush( ); // make the prompt appear now.
String sql = in.readLine( ); // get a line of input from user
// Quit when the user types "quit".
if ((sql == null) || sql.equals("quit")) break;
// Ignore blank lines
if (sql.length( ) == 0) continue;
// Now, execute the user's line of SQL and display results.
try {
// We don't know if this is a query or some kind of
// update, so we use execute( ) instead of executeQuery( )
// or executeUpdate( ). If the return value is true, it was
// a query, else an update.
boolean status = s.execute(sql);
// Some complex SQL queries can return more than one set
// of results, so loop until there are no more results
do {
if (status) { // it was a query and returns a ResultSet
ResultSet rs = s.getResultSet( ); // Get results
printResultsTable(rs, System.out); // Display them
}
else {
// If the SQL command that was executed was some
// kind of update rather than a query, then it
// doesn't return a ResultSet. Instead, we just
// print the number of rows that were affected.
int numUpdates = s.getUpdateCount( );
System.out.println("Ok. " + numUpdates +
" rows affected.");
}
// Now go see if there are even more results, and
// continue the results display loop if there are.
status = s.getMoreResults( );
} while(status || s.getUpdateCount( ) != -1);
}
// If a SQLException is thrown, display an error message.
// Note that SQLExceptions can have a general message and a
// DB-specific message returned by getSQLState( )
catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage( )+ ":" +
e.getSQLState( ));
}
// Each time through this loop, check to see if there were any
// warnings. Note that there can be a whole chain of warnings.
finally { // print out any warnings that occurred
SQLWarning w;
for(w=conn.getWarnings( ); w != null; w=w.getNextWarning( ))
System.err.println("WARNING: " + w.getMessage( ) +
":" + w.getSQLState( ));
}
}
}
// Handle exceptions that occur during argument parsing, database
// connection setup, etc. For SQLExceptions, print the details.
catch (Exception e) {
System.err.println(e);
if (e instanceof SQLException)
System.err.println("SQL State: " +
((SQLException)e).getSQLState( ));
System.err.println("Usage: java ExecuteSQL [-d <driver>] " +
"[-u <user>] [-p <password>] <database URL>");
}
// Be sure to always close the database connection when we exit,
// whether we exit because the user types 'quit' or because of an
// exception thrown while setting things up. Closing this connection
// also implicitly closes any open statements and result sets
// associated with it.
finally {
try { conn.close( ); } catch (Exception e) { }
}
}
/**
* This method attempts to output the contents of a ResultSet in a
* textual table. It relies on the ResultSetMetaData class, but a fair
* bit of the code is simple string manipulation.
**/
static void printResultsTable(ResultSet rs, OutputStream output)
throws SQLException
{
// Set up the output stream
PrintWriter out = new PrintWriter(output);
// Get some "meta data" (column names, etc.) about the results
ResultSetMetaData metadata = rs.getMetaData( );
// Variables to hold important data about the table to be displayed
int numcols = metadata.getColumnCount( ); // how many columns
String[ ] labels = new String[numcols]; // the column labels
int[ ] colwidths = new int[numcols]; // the width of each
int[ ] colpos = new int[numcols]; // start position of each
int linewidth; // total width of table
// Figure out how wide the columns are, where each one begins,
// how wide each row of the table will be, etc.
linewidth = 1; // for the initial '|'.
for(int i = 0; i < numcols; i++) { // for each column
colpos[i] = linewidth; // save its position
labels[i] = metadata.getColumnLabel(i+1); // get its label
// Get the column width. If the db doesn't report one, guess
// 30 characters. Then check the length of the label, and use
// it if it is larger than the column width
int size = metadata.getColumnDisplaySize(i+1);
if (size == -1) size = 30; // Some drivers return -1...
if (size > 500) size = 30; // Don't allow unreasonable sizes
int labelsize = labels[i].length( );
if (labelsize > size) size = labelsize;
colwidths[i] = size + 1; // save the column size
linewidth += colwidths[i] + 2; // increment total size
}
// Create a horizontal divider line we use in the table.
// Also create a blank line that is the initial value of each
// line of the table
StringBuffer divider = new StringBuffer(linewidth);
StringBuffer blankline = new StringBuffer(linewidth);
for(int i = 0; i < linewidth; i++) {
divider.insert(i, '-');
blankline.insert(i, " ");
}
// Put special marks in the divider line at the column positions
for(int i=0; i<numcols; i++) divider.setCharAt(colpos[i]-1,'+');
divider.setCharAt(linewidth-1, '+');
// Begin the table output with a divider line
out.println(divider);
// The next line of the table contains the column labels.
// Begin with a blank line, and put the column names and column
// divider characters "|" into it. overwrite( ) is defined below.
StringBuffer line = new StringBuffer(blankline.toString( ));
line.setCharAt(0, '|');
for(int i = 0; i < numcols; i++) {
int pos = colpos[i] + 1 + (colwidths[i]-labels[i].length( ))/2;
overwrite(line, pos, labels[i]);
overwrite(line, colpos[i] + colwidths[i], " |");
}
// Then output the line of column labels and another divider
out.println(line);
out.println(divider);
// Now, output the table data. Loop through the ResultSet, using
// the next( ) method to get the rows one at a time. Obtain the
// value of each column with getObject( ), and output it, much as
// we did for the column labels above.
while(rs.next( )) {
line = new StringBuffer(blankline.toString( ));
line.setCharAt(0, '|');
for(int i = 0; i < numcols; i++) {
Object value = rs.getObject(i+1);
if (value != null)
overwrite(line, colpos[i] + 1, value.toString( ).trim( ));
overwrite(line, colpos[i] + colwidths[i], " |");
}
out.println(line);
}
// Finally, end the table with one last divider line.
out.println(divider);
out.flush( );
}
/** This utility method is used when printing the table of results */
static void overwrite(StringBuffer b, int pos, String s) {
int slen = s.length( ); // string length
int blen = b.length( ); // buffer length
if (pos+slen > blen) slen = blen-pos; // does it fit?
for(int i = 0; i < slen; i++) // copy string into buffer
b.setCharAt(pos+i, s.charAt(i));
}
}
