36.9. Review Questions

 
[Page 1135 ( continued )]

33.4. Scrollable and Updateable Result Set

The result sets used in the preceding examples are read sequentially. A result set maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next () method moves the cursor forward to the next row. This is known as sequential forward reading . It is the only way of processing the rows in a result set that is supported by JDBC 1.

With JDBC 2, you can scroll the rows both forward and backward and move the cursor to a desired location using the first , last , next , previous , absolute , or relative method. Additionally, you can insert, delete, or update a row in the result set and have the changes automatically reflected in the database.

To obtain a scrollable or updateable result set, you must first create a statement with an appropriate type and concurrency mode. For a static statement, use

 Statement statement = connection.createStatement (   int   resultSetType,   int   resultSetConcurrency); 

For a prepared statement, use

 PreparedStatement statement = connection.prepareStatement (String sql,   int   resultSetType,   int   resultSetConcurrency); 

The possible values of resultSetType are the constants defined in the ResultSet :

  • TYPE_FORWARD_ONLY : The result set is accessed forward sequentially.

  • TYPE_SCROLL_INSENSITIVE : The result set is scrollable , but not sensitive to changes in the database.

  • TYPE_SCROLL_SENSITIVE : The result set is scrollable and sensitive to changes made by others. Use this type if you want the result set to be scrollable and updateable.

The possible values of resultSetConcurrency are the constants defined in the ResultSet :

  • CONCUR_READ_ONLY : The result set cannot be used to update the database.

  • CONCUR_UPDATEABLE : The result set can be used to update the database.


    [Page 1136]

For example, if you want the result set to be scrollable and updateable , you can create a statement, as follows :

 Statement statement = connection.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATEABLE) 

You use the executeQuery method in a Statement object to execute an SQL query that returns a result set, as follows:

 ResultSet resultSet = statement.executeQuery(query); 

You can now use the methods first() , next() , previous() , and last() to move the cursor to the first row, next row, previous row, and last row. The absolute(int row) method moves the cursor to the specified row; and the get Xxx (int columnIndex) or get Xxx (String columnName) method is used to retrieve the value of a specified field at the current row. The methods insertRow() , deleteRow() , and updateRow() can also be used to insert, delete, and update the current row. Before applying insertRow or updateRow , you need to use the method updateXxx(int columnIndex, Xxx value) or update(String columnName, Xxx value) to write a new value to the field at the current row. The cancelRowUpdates() method cancels the updates made to a row. The close() method closes the result set and releases its resource. The boolean wasNull() method indicates whether the last column read had a value of SQL NULL.

For example, the following code updates the phone number of the second row in the ResultSet to "912921111" :

 resultSet.absolute(   2   );  // moves the cursor to the 2nd row  resultSet.updateString(   "phone"   ,   "912921111"   );  // updates the column  resultSet.updateRow();  // updates the row in the data source  

An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. This special row is called the insert row . To insert a row, first invoke the moveToInsertRow() method to move the cursor to the insert row, then update the columns using the updateXxx method, and finally insert the row using the insertRow() method. For example, the following code inserts a new row with lastName " Yao " and firstName " An ":

 resultSet.moveToInsertRow();  // Move cursor to the insert row  resultSet.updateString(   "lastName"   ,   "Yao"   );  // Update the lastName  resultSet.updateString(   "firstName"   ,   "An"   );  // Update the firstName  resultSet.insertRow();  // Insert the row  resultSet.moveToCurrentRow();  // Move the cursor to the current row  

Now let us turn our attention to developing a useful utility that displays all the rows of a database table in a JTable and uses a scrollable and updateable result set to navigate the table and modify its contents.

As shown in Figure 33.3, you enter or select a JDBC driver and database, enter a username and a password, and specify a table name to connect the database and display the table contents in the JTable . You can then use the buttons First , Next , Prior , and Last to move the cursor to the first row, next row, previous row, and last row in the table, and use the buttons Insert , Delete , and Update to modify the table contents. When you click the Insert button, a dialog box is displayed to receive input, as shown in Figure 33.4.

Figure 33.3. The program enables you to navigate and modify the table.
(This item is displayed on page 1137 in the print version)

Figure 33.4. The Insert a New Record dialog box lets the user enter a new record.
(This item is displayed on page 1137 in the print version)


The status bar at the bottom of the window shows the current row in the result set and the JTable . The cursor in the result set and the row in the JTable are synchronized. You can move the cursor by using the navigation buttons or by selecting a row in the JTable .

Create three classes: TestTableEditor (Listing 33.3), TableEditor (Listing 33.4), and NewRecordDialog (Listing 33.5). TestTableEditor is the main class that enables the user to enter the database connection information and a table name. Once the database is connected, the table contents are displayed in an instance of TableEditor . The TableEditor class can be used to browse a table and modify a table. An instance of NewRecordDialog is displayed to let you enter a new record when you click the Insert button in TableEditor .


[Page 1137]
Listing 33.3. TestTableEditor.java
(This item is displayed on pages 1137 - 1138 in the print version)
 1   import   javax.swing.*; 2   import   java.awt.*; 3   import   java.awt.event.*; 4   import   java.sql.*; 5 6   public class   TestTableEditor   extends   JApplet { 7   private   JComboBox jcboDriver =   new   JComboBox(   new   String[] { 8   "com.mysql.jdbc.Driver"   ,   "oracle.jdbc.driver.OracleDriver"   , 9   "sun.jdbc.odbc.JdbcOdbcDriver"   }); 10   private   JComboBox jcboURL =   new   JComboBox(   new   String[] { 11   "jdbc:mysql://localhost/test"   ,   "jdbc:odbc:exampleMDBDataSource"   , 12   "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl"   }); 13 14   private   JButton jbtConnect = 15   new   JButton(   "Connect to DB & Get Table"   ); 16   private   JTextField jtfUserName =   new   JTextField(); 17   private   JPasswordField jpfPassword =   new   JPasswordField(); 18   private   JTextField jtfTableName =   new   JTextField(); 19   private   TableEditor tableEditor1 =   new   TableEditor(); 20   private   JLabel jlblStatus =   new   JLabel(); 21 22  /** Creates new form TestTableEditor */  23   public   TestTableEditor() { 24 JPanel jPane1 =   new   JPanel(); 25 jPane1.setLayout(   new   GridLayout(   5   ,     )); 26 jPane1.add(jcboDriver); 27 jPane1.add(jcboURL); 28 jPane1.add(jtfUserName); 29 jPane1.add(jpfPassword); 30 jPane1.add(jtfTableName); 31 

[Page 1138]
 32 JPanel jPanel2 =   new   JPanel(); 33 jPanel2.setLayout(   new   GridLayout(   5   ,     )); 34 jPanel2.add(   new   JLabel(   "JDBC Driver"   )); 35 jPanel2.add(   new   JLabel(   "Database URL"   )); 36 jPanel2.add(   new   JLabel(   "Username"   )); 37 jPanel2.add(   new   JLabel(   "Password"   )); 38 jPanel2.add(   new   JLabel(   "Table Name"   )); 39 40 JPanel jPane3 =   new   JPanel(); 41 jPane3.setLayout(   new   BorderLayout()); 42 jPane3.add(jbtConnect, BorderLayout.SOUTH); 43 jPane3.add(jPanel2, BorderLayout.WEST); 44 jPane3.add(jPane1, BorderLayout.CENTER); 45 tableEditor1.setPreferredSize(   new   Dimension(   400   ,   200   )); 46 47 add(   new   JSplitPane(JSplitPane.HORIZONTAL_SPLIT, 48 jPane3, tableEditor1), BorderLayout.CENTER); 49 add(jlblStatus, BorderLayout.SOUTH); 50 51 jbtConnect.addActionListener(   new   ActionListener() { 52   public void   actionPerformed(ActionEvent evt) { 53   try   { 54  // Connect to the database  55 Connection connection = getConnection(); 56 tableEditor1.setConnectionAndTable(connection, 57 jtfTableName.getText().trim()); 58 } 59   catch   (Exception ex) { 60 jlblStatus.setText(ex.toString()); 61 } 62 } 63 }); 64 } 65 66  /** Connect to a database */  67   private   Connection getConnection()   throws   Exception { 68  // Load the JDBC driver  69 System.out.println((String)jcboDriver.getSelectedItem()); 70  Class.forName(((String)jcboDriver.getSelectedItem()).trim());  71 System.out.println(   "Driver loaded"   ); 72 73  // Establish a connection  74  Connection connection = DriverManager.getConnection  75  (((String)jcboURL.getSelectedItem()).trim(),  76  jtfUserName.getText().trim(),  77    new   String(jpfPassword.getPassword()));  78 jlblStatus.setText(   "Database connected"   ); 79 80   return   connection; 81 } 82 } 

Listing 33.4. TableEditor.java
(This item is displayed on pages 1138 - 1143 in the print version)
 1   import   java.util.*; 2   import   java.sql.*; 3   import   javax.swing.table.*; 4   import   javax.swing.event.*; 5   import   javax.swing.*; 6   import   java.awt.*; 

[Page 1139]
 7   import   java.awt.event.*; 8 9   public class   TableEditor   extends   JPanel { 10  // Dialog box for inserting a new record  11    private   NewRecordDialog newRecordDialog =   new   NewRecordDialog();  12 13  // JDBC Connection  14   private   Connection connection; 15 16  // Table name  17   private   String tableName; 18 19  // JDBC Statement  20   private   Statement statement; 21 22  // Result set for the table  23   private   ResultSet resultSet; 24 25  // Table model  26   private   DefaultTableModel tableModel =   new   DefaultTableModel(); 27 28  // Table selection model  29   private   DefaultListSelectionModel listSelectionModel = 30   new   DefaultListSelectionModel(); 31 32  // New row vector  33   private   Vector rowVectors =   new   Vector(); 34 35  // columnHeaderVector to hold column names  36   private   Vector columnHeaderVector =   new   Vector(); 37 38  // Column count  39   private int   columnCount; 40 41   private   JButton jbtFirst =   new   JButton(   "First"   ); 42   private   JButton jbtNext =   new   JButton(   "Next"   ); 43   private   JButton jbtPrior =   new   JButton(   "Prior"   ); 44   private   JButton jbtLast =   new   JButton(   "Last"   ); 45   private   JButton jbtInsert =   new   JButton(   "Insert"   ); 46   private   JButton jbtDelete =   new   JButton(   "Delete"   ); 47   private   JButton jbtUpdate =   new   JButton(   "Update"   ); 48 49   private   JLabel jlblStatus =   new   JLabel(); 50   private   JTable jTable1 =   new   JTable(); 51 52  /** Creates new form TableEditor */  53   public   TableEditor() { 54  jTable1.setModel(tableModel);  55  jTable1.setSelectionModel(listSelectionModel);  56 57 JPanel jPanel1 =   new   JPanel(); 58 setLayout(   new   BorderLayout()); 59 jPanel1.add(jbtFirst); 60 jPanel1.add(jbtNext); 61 jPanel1.add(jbtPrior); 62 jPanel1.add(jbtLast); 63 jPanel1.add(jbtInsert); 64 jPanel1.add(jbtDelete); 65 jPanel1.add(jbtUpdate); 66 67 add(jPanel1, BorderLayout.NORTH); 

[Page 1140]
 68 add(   new   JScrollPane(jTable1), BorderLayout.CENTER); 69 add(jlblStatus, BorderLayout.SOUTH); 70 71 jbtFirst.addActionListener(   new   ActionListener() { 72   public void   actionPerformed(ActionEvent evt) { 73  moveCursor(   "first"   );  74 } 75 }); 76 jbtNext.addActionListener(   new   ActionListener() { 77   public void   actionPerformed(ActionEvent evt) { 78  moveCursor(   "next"   );  79 } 80 }); 81 jbtPrior.addActionListener(   new   ActionListener() { 82   public void   actionPerformed(ActionEvent evt) { 83  moveCursor(   "previous"   );  84 } 85 }); 86 jbtLast.addActionListener(   new   ActionListener() { 87   public void   actionPerformed(ActionEvent evt) { 88  moveCursor(   "last"   );  89 } 90 }); 91 jbtInsert.addActionListener(   new   ActionListener() { 92   public void   actionPerformed(ActionEvent evt) { 93  insert();  94 } 95 }); 96 jbtDelete.addActionListener(   new   ActionListener() { 97   public void   actionPerformed(ActionEvent evt) { 98  delete();  99 } 100 }); 101 jbtUpdate.addActionListener(   new   ActionListener() { 102   public void   actionPerformed(ActionEvent evt) { 103  update();  104 } 105 }); 106 listSelectionModel.addListSelectionListener( 107   new   ListSelectionListener() { 108   public void   valueChanged(ListSelectionEvent e) { 109 listSelectionModel_valueChanged(e); 110 } 111 }); 112 } 113 114   private    void   delete()  { 115   try   { 116  // Delete the record from the database  117  resultSet.deleteRow();  118  refreshResultSet();  119 120  // Remove the row in the table  121  tableModel.removeRow(  122  listSelectionModel.getLeadSelectionIndex());  123 } 124   catch   (Exception ex) { 125 jlblStatus.setText(ex.toString()); 126 } 127 } 

[Page 1141]
 128 129   private      void   insert()  { 130  // Display the dialog box  131 newRecordDialog.displayTable(columnHeaderVector); 132 Vector newRecord = newRecordDialog.getNewRecord(); 133 134   if   (newRecord ==   null   )   return   ; 135 136  // Insert the record to the Swing table  137 tableModel.addRow(newRecord); 138 139  // Insert the record to the database table  140   try   { 141   for   (   int   i =   1   ; i <= columnCount; i++) { 142  resultSet.updateObject(i, newRecord.elementAt(i -   1   ));  143 } 144 145  resultSet.insertRow();  146  refreshResultSet();  147 } 148   catch   (Exception ex) { 149 jlblStatus.setText(ex.toString()); 150 } 151 } 152 153  /** Set cursor in the table and set the row number in the status */  154   private void   setTableCursor()   throws   Exception { 155   int   row = resultSet.getRow(); 156 listSelectionModel.setSelectionInterval(row -   1   , row -   1   ); 157 jlblStatus.setText(   "Current row number: "   + row); 158 } 159 160   private      void   update()  { 161   try   { 162  // Get the current row  163   int   row = jTable1.getSelectedRow(); 164 165  // Gather data from the UI and update the database fields  166   for   (   int   i =   1   ; 167 i <= resultSet.getMetaData().getColumnCount(); i++) { 168  resultSet.updateObject(i, tableModel.getValueAt(row, i -   1   ));  169 } 170 171  // Invoke the update method in the result set  172  resultSet.updateRow();  173  refreshResultSet();  174 } 175   catch   (Exception ex) { 176 jlblStatus.setText(ex.toString()); 177 } 178 } 179 180  /** Move cursor to the next record */  181   private void   moveCursor(String whereToMove) { 182   try   { 183   if   (whereToMove.equals(   "first"   )) 184  resultSet.first();  185   else if   (whereToMove.equals(   "next"   )) 186  resultSet.next();  187   else if   (whereToMove.equals(   "previous"   )) 

[Page 1142]
 188  resultSet.previous();  189   else if   (whereToMove.equals(   "last"   )) 190  resultSet.last();  191 setTableCursor(); 192 } 193   catch   (Exception ex) { 194 jlblStatus.setText(ex.toString()); 195 } 196 } 197 198  /** Refresh the result set */  199   private void   refreshResultSet() { 200   try   { 201 resultSet = statement.executeQuery( 202   "SELECT * FROM "   + tableName); 203  // Set the cursor to the first record in the table  204  moveCursor(   "first"   );  205 } 206   catch   (SQLException ex) { 207 ex.printStackTrace(); 208 } 209 } 210 211  /** Set database connection and table name in the TableEditor */  212   public void   setConnectionAndTable(Connection newConnection, 213 String newTableName) { 214 connection = newConnection; 215 tableName = newTableName; 216   try   { 217 statement = connection.createStatement(ResultSet. 218 TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATEABLE); 219 showTable(); 220 moveCursor(   "first"   ); 221 } 222   catch   (SQLException ex) { 223 ex.printStackTrace(); 224 } 225 } 226 227  /** Display database table to a Swing table */  228   private void   showTable()   throws   SQLException { 229  // Clear vectors to store data for a new table  230 rowVectors.clear(); 231 columnHeaderVector.clear(); 232 233  // Obtain table contents  234 resultSet = statement.executeQuery( 235   "select * from "   + tableName +   ";"   ); 236 237  // Get column count  238 columnCount = resultSet.getMetaData().getColumnCount(); 239 240  // Store rows to rowVectors  241   while   (resultSet.next()) { 242 Vector singleRow =   new   Vector(); 243   for   (   int   i =     ; i < columnCount; i++) 244  // Store cells to a row  245 singleRow.addElement(resultSet.getObject(i +   1   )); 246 rowVectors.addElement(singleRow); 247 } 248 

[Page 1143]
 249  // Get column name and add to columnHeaderVector  250   for   (   int   i =   1   ; i <= columnCount; i++) 251 columnHeaderVector.addElement( 252 resultSet.getMetaData().getColumnName(i)); 253 254  // Set new data to the table model  255  tableModel.setDataVector(rowVectors, columnHeaderVector);  256 } 257 258  /** Handle the selection in the table */  259   void   listSelectionModel_valueChanged(ListSelectionEvent e) { 260   int   selectedRow = jTable1.getSelectedRow(); 261 262   try   { 263 resultSet.absolute(selectedRow +   1   ); 264 setTableCursor(); 265 } 266   catch   (Exception ex) { 267 jlblStatus.setText(ex.toString()); 268 } 269 } 270 } 

Listing 33.5. NewRecordDialog.java
(This item is displayed on pages 1143 - 1144 in the print version)
 1   import   java.util.*; 2   import   java.awt.*; 3   import   java.awt.event.*; 4   import   javax.swing.*; 5   import   javax.swing.table.*; 6 7   public class   NewRecordDialog   extends   JDialog { 8   private   JButton jbtOK =   new   JButton(   "OK"   ); 9   private   JButton jbtCancel =   new   JButton(   "Cancel"   ); 10 11   private    DefaultTableModel tableModel =   new   DefaultTableModel();  12   private    JTable jTable1 =   new   JTable(tableModel);  13   private    Vector newRecord;  14 15  /** Creates new form NewRecordDialog */  16   public   NewRecordDialog(Frame parent,   boolean   modal) { 17   super   (parent, modal); 18 setTitle(   "Insert a New Record"   ); 19 setModal(   true   ); 20 21 JPanel jPanel1 =   new   JPanel(); 22 jPanel1.add(jbtOK); 23 jPanel1.add(jbtCancel); 24 25 jbtOK.addActionListener(   new   ActionListener() { 26   public void   actionPerformed(ActionEvent evt) { 27 setVisible(   false   ); 28 } 29 }); 30 jbtCancel.addActionListener(   new   ActionListener() { 31   public void   actionPerformed(ActionEvent evt) { 32 newRecord =   null   ; 33 setVisible(   false   ); 34 } 35 }); 36 

[Page 1144]
 37 add(jPanel1, BorderLayout.SOUTH); 38 add(   new   JScrollPane(jTable1), BorderLayout.CENTER); 39 } 40 41   public   NewRecordDialog() { 42   this   (   null   ,   true   ); 43 } 44 45   public   Vector getNewRecord() { 46   return   newRecord; 47 } 48 49  /** Display the table */  50   void   displayTable(Vector columnHeaderVector) { 51   this   .setSize(   new   Dimension(   400   ,   100   )); 52 53  tableModel.setColumnIdentifiers(columnHeaderVector);  54 55  // Must create a new vector for a new record  56  tableModel.addRow(newRecord =   new   Vector());  57 setVisible(   true   ); 58 } 59 } 

The key class in this example is TableEditor , which can be used to navigate and modify the table contents. To use it, simply create an instance of TableEditor (line 19 in TestTableEditor.java), set the database connection and the table name in the instance, and place it in a graphical user interface. The setConnectionAndTableName method (lines 56 “57 in TestTableEditor.java) involves creating a statement, obtaining a result set, and displaying the result set in the Swing table. The statement is created with the arguments TYPE_SCROLL_SENSITIVE and CONCUR_UPDATEABLE for obtaining scrollable and updateable result sets (lines 217 “218 in TableEditor.java).


[Page 1144]

The showTable() method (lines 228 “256 in TableEditor.java) is responsible for transferring data from the database table to the Swing table. The column names and column count are obtained using the ResultSetMetaData interface. An instance of the ResultSetMetaData interface is obtained using the getMetaData method for the result set. Each record from the result set is copied to a row vector. The row vector is added to another vector that stores all the rows for the table model ( tableModel ) for the JTable .

The handling of the navigation buttons First , Next , Prior , and Last is simply to invoke the methods first() , next() , previous() , and last() to move the cursor in the result set and, at the same time, set the selected row in the Swing table.

The handling of the Insert button involves displaying the "Insert a New Record" dialog box ( newRecordDialog1 ) for receiving the new record. Once the record is entered, clicking the OK button dismisses the dialog box. The new record is obtained by invoking the newRecordDialog1.getNewRecord() method. To insert the new record into the database, use the updateObject method (line 142 in TableEditor.java) to update the fields, and then use the insertRow method to insert the record to the database table. Finally, you need to refresh the result set by re-executing the query. Theoretically, you should not have to refresh the result set (line 146 in TableEditor.java). The driver should automatically reflect the changes in the database to the result set. However, none of the drivers I have tested supports this. So it is safe to refresh the result set.

To implement the Delete button, invoke the deleteRow() method (line 117 in TableEditor.java) in the result set to remove the record from the database, and use the removeRow method in TableModel to remove a row from JTable .

To implement the Update button, invoke the updateObject method (line 168 in TableEditor.java) in the result set, and then invoke the updateRow method (line 172 in TableEditor.java) to update the result set.


[Page 1145]

To implement the handler for list-selection events on jTable1 , set the cursor in the result set to match the row selected in jTable1 (lines 259 “269 in TableEditor.java).

Note

The TableEditor class in this example uses only the updateObject(columnIndex, object) method. This updates a string column. To update a column of double type, you have to use updateDouble(columnIndex, doubleValue) . See Programming Exercise 33.3 to revise the program to handle all types of columns.


Tip

To ensure the effect of editing a field in the table, you need to press the Enter key or move the cursor to other fields.


Note

Many JDBC drivers, including the MySQL and Oracle drivers, support the read-only scrollable result set but not the updateable scrollable result set. Thus you cannot modify the result set. You can use supportsResultSetType(int type) and supportsResultSetConcurrency(int type, int concurrency) in the DatabaseMetaData interface to find out which result type and currency modes are supported by the JDBC driver. But even if a driver supports the scrollable and updateable result set, a result set for a complex query might not be able to perform an update. For example, the result set for a query that involves several tables is likely not to support update operations.


 


Introduction to Java Programming-Comprehensive Version
Introduction to Java Programming-Comprehensive Version (6th Edition)
ISBN: B000ONFLUM
EAN: N/A
Year: 2004
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net