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.
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.
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 .
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 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 } |
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.*; 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); 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 } 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" )) 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 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 } |
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 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).
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.
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. |