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 members such as Connection, Statement, and ResultSet are 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.javapackage 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)); } } |
EAN: 2147483647
Pages: 285