Java Database Programming Bible- P4

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

86
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Java Database Programming Bible- P4: Welcome to Java Database Programming Bible. This book is for readers who are already familiar with Java, and who want to know more about working with databases. The JDBC Application Programming Interface has made database programming an important aspect of Java development, particularly where Web applications are concerned.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Java Database Programming Bible- P4

Chapter 4:Introduction to JDBC 2. Set a new value for each column in the row by using the appropriate update method. 3. Call the method insertRow() to insert the new row into the result set and, simultaneously, into the database. Listing 4-9 demonstrates the use of the UpdatableResultSet to insert a new row into a database. Listing 4-9: Using UpdatableResultSet to insert a new row Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection ("jdbc:odbc:Contacts"); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.moveToInsertRow(); rs.updateInt("Contact_ID", 150); rs.updateString("First_Name", "Nigel"); rs.updateString("Last_Name", "Thornebury"); Y FL rs.insertRow(); AM If you insert a row without supplying a value for every column in the row, the default value for the column will be used if there is one. Otherwise, if the column accepts SQL NULL values, a NULL will be inserted. Failing either of those, a SQLException TE will be thrown. You will also get a SQLException if a required table column is missing in the ResultSet you use to insert the row, so the query used to get the ResultSet object should generally select all columns, though you will probably want to use a WHERE clause to limit the number of rows returned by your SELECT statement. Caution If you move the cursor from the insert row before calling the method insertRow(), you will lose all of the values you have added to the insert row. To move the cursor from the insert row back to the result set, you can use any of the methods that put the cursor on a specific row: first, last, beforeFirst, afterLast, and absolute. You can also use the methods previous and relative because the result set maintains a record of the current row while accessing the insert row. In addition, you can use a special method: moveToCurrentRow(), which can be called only when the cursor is on the insert row. This method moves the cursor from the insert row back to the row that was previously the current row. -149 - Team-Fly® Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC Deleting a Row Deleting a row in an UpdatableResultSet is very simple. All you have to do is move the cursor to the row you want to delete and call the method deleteRow(). The example in the following code snippet shows how to delete the third row in a result set by getting the ResultSet object, moving the cursor to the third row, and using the deleteRow() method: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection ("jdbc:odbc:Contacts"); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.absolute(3); rs.deleteRow(); Caution Be aware that different JDBC drivers handle deletions in different ways. Some remove a deleted row so that it is no longer visible in a result set, and others insert a blank row where the deleted row used to be. When you make a change to a ResultSet, the change may not necessarily be visible. The next section explains the reasons. Seeing Changes in ResultSets Changes made to a ResultSet are not necessarily visible, either to the ResultSet itself or to other open transactions. In this context, the terms visible and not visible have the following meanings: § An update is visible if the updated value can be retrieved by calling the appropriate getter method after making an update. § An update is not visible if the getter method still returns the initial column value. Similarly, an inserted row is visible if it appears in the ResultSet after calling insertRow(). Deletions are visible if deleted rows are either removed from the result set or if deleted rows leave a hole in the result set. There are a number of factors affecting the visibility of changes, including the following: § JDBC driver implementation § Transaction isolation level in effect § Result-set type -150- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC An application can determine if the changes a result set makes are visible to the result set itself by calling these DatabaseMetaData methods: § ownUpdatesAreVisible(int ResultSet.TYPE_XXX) § ownDeletesAreVisible(int ResultSet.TYPE_XXX) § ownInsertsAreVisible(int ResultSet.TYPE_XXX) The DatabaseMetaData interface also provides the following methods that allow an application to determine whether a JDBC driver can detect changes for a particular result-set type: § insertsAreDetected(ResultSet.TYPE_XXX) § deletesAreDetected(ResultSet.TYPE_XXX) § updatesAreDetected(ResultSet.TYPE_XXX) If these methods return true, the following methods can be used to detect changes to a ResultSet: § wasInserted() § wasDeleted() § wasUpdated() Remember that if you modify data in a ResultSet object, the change will always be visible if you close the ResultSet and reopen it by executing the same query again after the changes have been made. Another way to get the most recent data is to use the method refreshRow(), which gets the latest values for a row straight from the database. This is done by positioning the cursor to the desired row and calling refreshRow(), as shown here: rs.absolute(3); rs.refreshRow(); Note The result set should be TYPE_SCROLL_SENSITIVE; if you use the method refreshRow() with a ResultSet object that is TYPE_SCROLL_INSENSITIVE, refreshRow() does nothing. Another way to get data from a database is to use a RowSet object. RowSets add JavaBeans support to the functionality of the ResultSet, as explained in the next section. RowSets A RowSet is an object that contains a set of rows from a result set or some other source of tabular data, like a fi le or spreadsheet. RowSet is an extension of ResultSet, -151 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC with the added feature that it adds JavaBeans support to the JDBC API. Similarly, the RowSetMetaData interface extends the ResultSetMetaData interface. Being JavaBeans, RowSets follow the JavaBeans model for setting and getting properties and for event notification, so they are easy to combine with other components in an application. RowSets make it easy to send tabular data over a network. They can also be used as a wrapper, providing scrollable result sets or updatable result sets when the underlying JDBC driver does not support them. There are two main types of RowSets: connected and disconnected. § A connected RowSet, like a ResultSet, maintains a connection to a data source for as long as the RowSet is in use. § A disconnected RowSet gets a connection to a data source to load data or to propagate changes back to the data source, but most of the time it does not have a connection open. While it is disconnected, a RowSet does not need a JDBC driver or the full JDBC API, so its footprint is very small. Because it is not continually connected to its data source, a disconnected RowSet stores its data in memory. It maintains MetaData about the columns it contains and information about its internal state. It also includes methods for making connections, executing commands, and reading and writing data to and from the data source. Implementations of RowSets include the following: § JDBCRowSet — A connected RowSet that serves mainly as a thin wrapper around a ResultSet object to make a JDBC driver look like a JavaBeans component § CachedRowSet — A disconnected RowSet that caches its data in memory § WebRowSet — A connected RowSet that uses the HTTP protocol internally to talk to a Java servlet that provides data access Creating a Rowset and Setting Properties Since RowSets are JavaBeans, they contain setter and getter methods for retrieving and setting properties. These methods include the following: § setCommand — The SQL command to be executed § setConcurrency — Read only or updatable § setType — Scrollable or foward only § setDataSourceName — Used with DataSource access -152 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC § setUrl — used with DriverManager access § setUsername § setPassword § setTransactionIsolation You need only set those properties that are needed for your particular use of a RowSet. The following lines of code make the CachedRowSet object crset scrollable and updatable. CachedRowSet crset = new CachedRowSet(); crset.setType(ResultSet.TYPE_SCROLL_INSENSITIVE); crset.setConcurrency(ResultSet.CONCUR_UPDATABLE); crset.setCommand("SELECT * FROM Customers"); crset.setDataSourceName("jdbc/customers"); crset.setUsername("myName"); crset.setPassword("myPwd"); crset.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); crset.addRowSetListener(listener); If the DriverManager were being used to make a connection, you would set the properties for a JDBC URL, a user name, and a password. The preferred means of getting a connection is to use a DataSource object with the owner's user name and the owner's password. Now that the CachedRowSet has been created and initialized, all that remains is to call the execute() method; the RowSet uses the information in its properties to make a connection and execute the query. The data in the RowSet can then be accessed and updated. Rowset Events A RowSetEvent is generated when something important happens in a RowSet, such as a change in a column value. Being JavaBeans, RowSets can use the Java event model to notify listeners when the RowSet is changed. These are the RowSetListener methods: § rowChanged (Called when the RowSet is changed) § rowSetChanged(Called when a RowSet is inserted, updated, or deleted) § cursorMoved (Called when a RowSet's cursor is moved))) -153 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC In addition to obtaining the data stored in the database, it is frequently ver useful to be able to obtain data about the database and its contents. This capability is supported by the MetaData objects discussed in the next section. MetaData MetaData is information about the database or its contents made available by the JDBC API. These are the main types of MetaData accessible from JDBC: § DatabaseMetaData § ResultSetMetaData § ParameterMetaData DatabaseMetaData The DatabaseMetaData interface provides information about the underlying database as a whole. The interface defines over 150 different methods providing the following types of information about the database: § General information about the data source § Data-source limits § Levels of transaction support § Feature support § Information about the SQL objects that the source contains Many of the DatabaseMetaData methods return information in ResultSets, allowing you to use ResultSet methods such as getString and getInt to retrieve this information. If a given form of MetaData is not available, these methods should throw a SQLException. Some of the DatabaseMetaData methods take arguments that are String patterns conforming to the normal wild-card rules for SQL Strings. For pattern String arguments, "%" means match any substring of zero or more characters, and "_" means match any one character. If a search pattern argument is set to null, that argument's criteria will be ignored in the search. If a driver does not support a MetaData method, a SQLException will normally be thrown. In the case of methods that return a ResultSet, either a ResultSet (which may be empty) is returned or a SQLException is thrown. A DatabaseMetaData object is created using the Connection.getMetaData() method. It can then be used to get information about the database, as in the following example, which gets the names of the tables in the database: -154 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC Connection con = DriverManager.getConnection ("jdbc:odbc:Customers"); DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.getTables(null,null,"%",new String[]{"TABLE"}); General information about the underlying database is accessible from the DatabaseMetaData interface by using methods such as these: § getURL() § getUserName() § getDatabaseProductName() § getSQLKeywords() § nullsAreSortedHigh() and nullsAreSortedLow() Useful methods for retrieving information about supported functionality include the following: § supportsBatchUpdates() § supportsStoredProcedures() § supportsFullOuterJoins() § supportsPositionedDelete() These methods are provided to determine limits the database imposes: § getMaxRowSize() § getMaxStatementLength() § getMaxConnections() § getMaxColumnsInTable() Useful methods for retrieving information about SQL objects and their attributes include the following: § getSchemas() § getCatalogs() § getTables() § getPrimaryKeys() § getProcedures() The transaction-support capabilities of the database management system can be queried using these methods: § supportsMultipleTransactions() § getDefaultTransactionIsolation() § supportsSavePoints() Note Many of the DatabaseMetaData methods have been added or modified in JDBC 2.0 and JDBC 3.0, so if your driver is not JDBC 2.0 or JDBC 3.0 -155- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC compliant, a SQLException may be thrown. ResultSetMetaData Information about the columns in a ResultSet is available by calling the getMetaData() method. The ResultSetMetaData object returned gives the number, types, and properties of its ResultSet object's columns. Some of the methods available to access ResultSetMetaData are as follows: § getColumnCount() — Returns the number of columns in the ResultSet § getColumnDisplaySize(int column)— Returns the column's normal max width in chars § getColumnLabel(int column) — Returns the column title for use in printouts and displays § getColumnName(int column) — Returns the column name § getColumnType(int column) — Returns the column's SQL data-type index § getColumnTypeName(int column)— Returns the name of the column's SQL data type § getPrecision(int column)— Returns the number of decimal digits in the column § getScale(int column) — Returns the number of digits to right of the decimal point § getTableName(int column) — Returns the table name § isAutoIncrement(int column) — Returns true if the column is automatically numbered § isCurrency(int column) — Returns true if the column value is a currency § isNullable(int column)— Returns true if the column value can be set to NULL Listing 4-10 illustrates the use of the ResultSetMetaData methods getColumnCount and getColumnLabel in an example where the column names and column count are unknown. Listing 4-10: Using ResultSetMetaData public void printResultSet(String query){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection ("jdbc:odbc:Inventory"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); ResultSetMetaData md = rs.getMetaData(); int nColumns = md.getColumnCount(); for(int i=1;i
Chapter 4:Introduction to JDBC System.out.print(rs.getString(i)+((i==nColumns)?"\n":"\t")); } } } catch(ClassNotFoundException e){ e.printStackTrace(); } catch(SQLException e){ e.printStackTrace(); } } Notice in particular the use of the getColumnLabel method. This method returns the preferred display name for the column, defaulting to the column name if no specific label is assigned. ParameterMetaData The PreparedStatement method getMetaData() retrieves a ResultSetMetaData object containing a description of the columns that will be returned when the PreparedStatement is executed. Here's an example: PreparedStatement ps = con.PrepareStatement("SELECT * FROM CUSTOMERS"); ResultSetMetaData md = ps.getMetaData(); int cols = md.getColumnCount(); The method getParameterMetaData() returns a ParameterMetaData object containing descriptions of the IN and OUT parameters the PreparedStatement uses, as shown here: PreparedStatement ps = con.PrepareStatement("SELECT * FROM CUSTOMERS"); ParameterMetaData pd = ps.getParameterMetaData(); int pType = pd.getParameterType(1); Note Support for ParameterMetaData is provided as part of the JDBC 3.0 API, and requires JDK 1.4 JDBC Mapping of SQL Data Types The JDBC Core API provides automatic type conversion between SQL data types and Java data types. Table 4-5 summarizes these conversions. Table 4-5: Standard Mapping from SQL Types to Java SQL type Java Type Description CHAR String Fixed-length character string. For a CHAR type -157 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC Table 4-5: Standard Mapping from SQL Types to Java SQL type Java Type Description of length n, the DBMS invariably assigns n characters of storage, padding unused space. VARCHAR String Variable-length character string. For a VARCHAR of length n, the DBMS assigns up to n charcters of storage, as required. LONGVARCHAR String Variable-length character string. JDBC allows retrieval of a LONGVARCHAR as a Java input stream. NUMERIC java.math.BigDecimal Arbitrary-precision signed decimal numbers. Can be retrieved using either BigDecimal or String. DECIMAL java.math.BigDecimal Arbitrary-precision signed decimal numbers. Can be retrieved using either BigDecimal or String. BIT boolean Yes / No value TINYINT byte 8 bit integer values SMALLINT short 16 bit integer values INTEGER int 32 bit integer values BIGINT long 64 bit integer values REAL float Floating point number, mapped to float FLOAT double Floating point number, mapped to double DOUBLE double Floating point number, mapped to double BINARY byte[] Retrieve as byte array. VARBINARY byte[] Retrieve as byte array. LONGVARBINARY byte[] Retrieve as byte array. JDBC allows retrieval of a LONGVARCHAR as a Java input stream. DATE java.sql.Date Thin wrapper around java.util.Date TIME java.sql.Time Thin wrapper around java.util.Date TIMESTAMP java.sql.Timestamp Composite of a java.util.Date and a separate nanosecond value Cross-Reference In addition to the data types supported by the JDBC Core API, JDBC 2.0 and JDBC 3.0 have introduced support for other data -158- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC types. These are discussed in the next few paragraphs. Some databases allow for certain columns to be given automatically generated key values. In this case, an insert statement is not responsible for supplying a value for the column. The database generates a unique value for the column and inserts the value. This is often used for generating unique primary keys. A problem with this approach is that it may be difficult to get the value after the insert is executed. The JDBC 3.0 specification defines a more functional Statement interface that provides access to these values after an insert. Assume a table called USERS with three columns. The FIRST_NAME column and LAST_NAME column are varchars. The USER_ID column is auto-generated and should contain a unique identifier for each user in the table. Here's an example: Statement stmt = conn.createStatement(); String SQLInsert = "INSERT INTO Users (First_Name, Last_Name) "+ "VALUES('Robert', 'Conners')"); stmt.executeUpdate(SQLInsert); ResultSet rs = stmt.getGeneratedKeys(); Y FL SQL3 Data Types AM The JDBC 2.0 Extension API adds support for the new data types commonly referred to as SQL3 types. The JDBC 3.0 Extension API extends this support. These new data types support the two following major new features: TE § Very large data objects § Object relational data types The SQL3 data types are being adopted in the next version of the ANSI/ISO SQL standard. The JDBC API extensions provide interfaces that represent the mapping of these SQL3 data types into the Java programming language. With these new interfaces, you can work with SQL3 data types the same way you do other data types. Object Relational Databases Object relational databases are simply an extension to normal relational database management systems supporting the use of an object-oriented-design approach to the database world. For example, in a normal RDBMS, you might create a table of names and addresses, containing these columns: -159 - Team-Fly® Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC First_Name VARCHAR (20) MI CHAR(1) Last_Name VARCHAR(30) Street VARCHAR(50) City VARCHAR(30) State CHAR(2) Zip CHAR(10) In another application, you might create a second table of names and addresses, perhaps this time with different field sizes or even additional fields. From a design viewpoint, the ability to define a class or structure can be used across the board is very attractive. An object relational database provides the necessary tools to support this approach with User Defined Data Types (UDTs). Using SQL3 Data Types The new SQL3 data types that the JDBC 2.0 Extension supports include the following: § BLOB (Binary Large Object), which can store very large amounts of data as raw bytes § CLOB (Character Large Object), which can store very large amounts of character data § ARRAY, which can store an array as a column value § User Defined Types § Structured, object relational types § The DISTINCT type The following list provides the JDBC 2.0 interfaces that map SQL3 types. We discuss them in more detail later in this chapter. § A Blob instance maps an SQL BLOB value. § A Clob instance maps an SQL CLOB value. § An Array instance maps an SQL ARRAY value. § A Struct instance maps an SQL structured type value. § A Ref instance maps an SQL REF value. SQL3 data types are retrieved, stored, and updated in the same way as other data types, using the methods shown in Table 4-6. -160- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC Table 4-6: SQL3 Data Type Reference Methods SQL3 type get set update BLOB getBlob seBlob updateBlob CLOB getClob setClob updateClob ARRAY getArray setArray updateArray Structured type getObject setObject updateObject REF ( structured type) getObject setObject updateObject Note At the time of this writing, the update methods are scheduled for future release. Until then, you can use the method updateObject, which works just as well. Here's an example of accessing one of these new data types. The following code fragment retrieves a CLOB value, Notes, from a patient's medical records. ResultSet rs = stmt.executeQuery( "SELECT Notes FROM Patients WHERE SSN = 123-45-6789"); rs.next(); Clob notes = rs.getClob("Notes"); Because a SQL BLOB, CLOB, or ARRAY object may be very large, an instance of any of these types is actually a SQL locator or logical pointer to the object in the database that the instance represents. JDBC provides the tools to manipulate them without having to bring all of their data from the database server to your client machine. This feature can make performance significantly faster. If you want to bring the data of a BLOB or CLOB value to the client, you can use the following methods in the Blob and Clob interfaces provided for this purpose: § getAsciiStream()(Gets the CLOB value designated by this Clob object as a stream of ASCII bytes) § getCharacterStream() (Gets the Clob contents as a Unicode stream) § getSubString(long pos, int length)(Returns a copy of the specified substring in the CLOB value designated by this Clob object) § length()(Returns the number of characters in the CLOB value designated by this Clob object) § position(Clob searchstr, long start) (Determines the character position at which the specified Clob object searchstr appears in this Clob object) § position(String searchstr, long start) (Determines the character position at which the specified substring searchstr appears in the CLOB) -161 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC Both Blob and Clob objects provide methods for materializing the object's value on the client, for getting the length of the object, and for performing searches within the object's value. The JDBC 3.0 API Extensions add methods to alter the values of BLOBS and CLOBS directly, using these methods: § Blob.setBytes() § Clob.setString() A JDBC Array object materializes the SQL ARRAY it represents as either a result set or a Java array. For example, after retrieving the SQL ARRAY value in the column Meds as a java.sql.Array object, the following code fragment materializes the ARRAY value on the client. It then iterates through Medications, the Java array that contains the elements of the SQL ARRAY value. ResultSet rs = stmt.executeQuery( "SELECT MEDS FROM Patients WHERE SSN = 123-45-6789"); while (rs.next()) { Array Medications = rs.getArray("MEDS"); String[] meds = (String[])Medications.getArray(); for (int i = 0; i < meds.length; i++) { . . . // code to display medications } } The ResultSet method getArray returns the value stored in the column MEDS of the current row as the java.sql.Array object Medications, as shown here: Array Medications = rs.getArray("MEDS"); The variable Medications contains a locator, which means that it is a logical pointer to the SQL ARRAY on the server; it does not contain the elements of the ARRAY itself. In the following line, getArray is the Array.getArray method, returning a Java Object that is cast to an array of String objects before being assigned to the variable meds. String[] meds = (String[])Medications.getArray(); Thus, the Array.getArray method materializes the SQL ARRAY elements on the client as an array of String objects we can iterate through and display. Creating User Defined Data Types -162 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC SQL allows the user to create user defined data types or UDTs with the CREATE TYPE statement. There are two main kinds of data type which the user can create: § The structured data type § The DISTINCT type Creating a structured data type The following SQL statement creates the new data type ADDRESS and registers it with the database as a data type, so it is available for use as the data type for a table column or as an attribute of a structured type: CREATE TYPE ADDRESS ( STREET VARCHAR(40), APT_NO INTEGER, CITY VARCHAR(40), STATE CHAR(2), ZIP CHAR(5) ); In this definition, the new type ADDRESS has five attributes, which are equivalent to fields in a Java class. The attribute STREET is a VARCHAR(40); the attribute APT_NO is an INTEGER; the attribute CITY is a VARCHAR(40); the attribute STATE is a CHAR(2); and the attribute ZIP is a CHAR(5). Creating a DISTINCT type A DISTINCT type can be thought of as a structured type with only one attribute. DISTINCT types are always based on another data type, which must be a predefined type; they cannot be based on another UDT. DISTINCT types are retrieved or set using the appropriate method for the underlying type. For example, a Social Security Number (SSN), which is never going to be used for arithmetic operations, and may be a good candidate for special handling, can be created using the command. Here's an example: CREATE TYPE SSN AS CHAR(9); This is the equivalent SQL Server command: EXEC sp_addtype SSN, 'VARCHAR(9)' Now that User Defined Data Types for Address and Social Security Number have been created, they can be used to define a new UDT, as shown here: -163 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC CREATE TYPE EMPLOYEE ( EMP_ID INTEGER, LAST_NAME VARCHAR(40), FIRST_NAME VARCHAR(40), RESIDENCE ADDRESS, SOCIAL SSN ); This definition can be created in a JDBC application by opening a connection and creating a Statement in the normal way, then executing the following code to send the definition of the structured type EMPLOYEE to the database. String createEmployee = "CREATE TYPE EMPLOYEE ("+ "EMP_ID INTEGER,"+ "LAST_NAME VARCHAR(40),"+ "FIRST_NAME VARCHAR(40),"+ "RESIDENCE ADDRESS,"+ "SOCIAL SSN);"; stmt.executeUpdate(createEmployee); On occasion, your code may generate errors. Java handles these errors by throwing SQLExceptions, as discussed in the next section. Exceptions and Logging There are several types of exceptions which can be thrown during data base access. The most common is the SQLException. SQLException The SQLException class extends java.lang.Exception to provide information on database-access errors. Each SQLException provides the following information: § The Java exception message String, available using the getMessage() method § The SQLState String, which follows the XOPEN SQLState conventions, available using the getSQLState() method § A vendor-specific, integer-error code, available using the getErrorCode() method. Normally, this isthe actual error code that the underlying database returns. SQLException also lets you get the next exception, which can be used to provide additional error information. SQLWarning -164 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC The SQLWarning class extends SQLException to provide information on database-access warnings. Warnings are silently chained to the object whose method causes the warning to be reported and are returned by the getWarnings() method of that class. In addition to the inherited methods of SQLException, SQLWarning provides methods to get the next SQLWarning for additional information or to add a warning to the chain. BatchUpdateException A BatchUpdateException provides information about problems arising during batch updates. BatchUpdateException extends SQLException, adding an array of update counts similar to the array returned by the executeBatch method. You can retrieve this array by using the getUpdateCounts() method as follows: int [] updateCounts = b.getUpdateCounts(); Since the update counts are in the same order as the commands, you can tell which commands in the batch have executed successfully. Logging In all but the simplest applications, it is worth incorporating some degree of error and event logging. The most basic form of logging, of course, is the use of System.err and System.out to report exceptions and significant events. In a practical application, simply dumping exception messages to the system console is generally inadequate. It is preferable to use dedicated logging files or perhaps even a database to manage event logs and error logs. It is easy to implement a file-based error and event-logging system by simply redirecting the basic System.err stream and by defining a PrintWriter for use by the Exception class for dumping a StackTrace. Listing 4-11 extends the example of Listing 4-1 to demonstrate two different ways to log exceptions to an error-logging file: § Define a PrintWriter for use with the printStackTrace() method. § Redirect System.err to a logging file by using System.setErr(). Listing 4-11: Logging errors to a file package java_databases.ch04; import java.io.*; -165- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC import java.sql.*; import java.util.*; public class Logging{ public static void main(String args[]){ PrintWriter errLog = null; PrintStream stderr = null; try{ FileOutputStream errors = new FileOutputStream ("StdErr.txt", true); stderr = new PrintStream (errors); errLog = new PrintWriter(errors,true); } catch (Exception e){ System.out.println ("Redirection error: Unable to open SystemErr.txt"); } System.setErr ( stderr ); int qty; float cost; String name; String desc; String query = "SELECT Name,Description,Qty,Cost,Sell_Price FROM Stock"; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection ("jdbc:odbc:Inventory"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { name = rs.getString("Name"); desc = rs.getString("Description"); qty = rs.getInt("Qty"); cost = rs.getFloat("Cost"); System.out.println(name+", "+desc+"\t: "+qty+"\t@ $"+cost); } } catch(ClassNotFoundException e){ e.printStackTrace(errLog); } catch(SQLException e){ System.err.println((new GregorianCalendar()).getTime()); System.err.println("Thread: "+Thread.currentThread()); -166 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC System.err.println("ErrorCode: "+e.getErrorCode()); System.err.println("SQLState: "+e.getSQLState()); System.err.println("Message: "+e.getMessage()); System.err.println("NextException: "+e.getNextException()); e.printStackTrace(errLog); System.err.println(); } try{ stderr.close (); } catch (Exception e){ System.out.println("Redirection error: Unable to close SystemErr.txt"); } } } A practical point worth noting is that the example saves current time and the current thread as part of the logged-error information. Caution Remember to open your error-logging file for append. Otherwise, you see only the last error. Also, it is a good idea to set autoFlush = true as shown, so that errors are written to the file immediately. This query is used in Listing 4-11: String query = "SELECT Name,Description,Qty,Cost,Sell_Price FROM Stock"; This query attempts to SELECT a nonexistent column, so a SQL Exception is thrown, resulting in logging the following error messages to the error log: Sun Dec 30 14:43:44 EST 2001 Thread: Thread[main,5,main] ErrorCode: -3010 SQLState: 07001 Message: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. NextException: null java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494) at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:314) at -167 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 4:Introduction to JDBC sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:229) at java_databases.ch04.Logging.main(Logging.java:30) Summary Part I is an introduction to database management systems, SQL, and JDBC, providing a theoretical overview of the topics as a basis for the more detailed explanations in subsequent chapters. This chapter provides an overview of the use of the JDBC API. In this chapter, you learn about the building blocks of a JDBC-based application: § Using the DriverManager and different types of JDBC drivers § Using JDBC DataSources for dimple, pooled, and distributed connections § Using connections § Using Statements, PreparedStatements and CallableStatements § Using transactions, isolation levels and SavePoints § Handling batch updates § Using ResultSets and Rowsets § Using MetaData § JDBC Mapping of SQL Data Types § Exceptions and loggin -168 - Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.