Row Sets


Scrollable result sets are powerful, but they have a major drawback. You need to keep the database connection open during the entire user interaction. However, users can walk away from their computer for a long time, leaving the connection occupied. That is not gooddatabase connections are scarce resources. In such a situation, use a row set. The RowSet interface extends the ResultSet interface, but row sets don't have to be tied to a database connection.

Row sets are also suitable if you need to move a query result to a different tier of a complex application, or to another device such as a cell phone. You would never want to move a result setits data structures can be huge, and it is tethered to the database connection.

The javax.sql.rowset package provides the following interfaces that extend the RowSet interface:

  • A CachedRowSet allows disconnected operation. We discuss cached row sets in the following section.

  • A WebRowSet is a cached row set that can be saved to an XML file. The XML file can be moved to another tier of a web application, where it is opened by another WebRowSet object.

  • The FilteredRowSet and JoinRowSet interfaces support lightweight operations on row sets that are equivalent to SQL SELECT and JOIN operations. These operations are carried out on the data stored in row sets, without having to make a database connection.

  • A JdbcRowSet is a thin wrapper around a ResultSet. It adds useful getters and setters from the RowSet interface, turning a result set into a "bean." (See Chapter 8 for more information on beans.)

Sun Microsystems expects database vendors to produce efficient implementations of these interfaces. Fortunately, they also supply reference implementations so that you can use row sets even if your database vendor doesn't support them. The reference implementations are part of JDK 5.0. You can also download them from http://java.sun.com/jdbc. The reference implementations are in the package com.sun.rowset. The class names end in Impl, for example, CachedRowSetImpl.

Cached Row Sets

A cached row set contains all data from a result set. Because CachedRowSet extends the ResultSet interface, you can use a cached row set exactly as you would use a result set. Cached row sets confer an important benefit: You can close the connection and still use the row set. As you will see in our sample program, this greatly simplifies the implementation of interactive applications. Each user command simply opens the database connection, issues a query, puts the result in a row set, and then closes the database connection.

It is even possible to modify the data in a cached row set. Of course, the modifications are not immediately reflected in the database. Instead, you need to make an explicit request to accept the accumulated changes. The CachedRowSet then reconnects to the database and issues SQL commands to write the accumulated changes.

Of course, cached row sets are not appropriate for large query results. It would be very inefficient to move large numbers of records from the database into memory, particularly if users only look at a few of them.

You can populate a CachedRowSet from a result set:

 ResultSet result = stat.executeQuery(queryString); CachedRowSet rowset = new com.sun.rowset.CachedRowSetImpl();    // or use an implementation from your database vendor rowset.populate(result); conn.close(); // now ok to close the database connection 

Alternatively, you can let the CachedRowSet object establish a connection automatically. Set up the database parameters:

 rowset.setURL("jdbc:mckoi://localhost/"); rowset.setUsername("dbuser"); rowset.setPassword("secret"); 

Then set the query command.

 rowset.setCommand("SELECT * FROM Books"); 

Finally, populate the row set with the query result:

 rowset.execute(); 

This call establishes a database connection, issues the query, populates the row set, and disconnects.

You can inspect and modify the row set with the same commands you use for result sets. If you modified the row set contents, you must write it back to the database by calling

 rowset.acceptChanges(conn); 

or

 rowset.acceptChanges(); 

The second call works only if you configured the row set with the information (such as URL, user name, and password) that is required to connect to a database.

On page 218, you saw that not all result sets are updatable. Similarly, a row set that contains the result of a complex query will not be able to write back changes to the database. You should be safe if your row set contains data from a single table.

CAUTION

If you populated the row set from a result set, the row set does not know the name of the table to update. You need to call setTable to set the table name.


Another complexity arises if data in the database have changed after you populated the row set. This is clearly a sign of trouble that could lead to inconsistent data. The reference implementation checks whether the original row set values (that is, the values before editing) are identical to the current values in the database. If so, they are replaced with the edited values. Otherwise, a SyncProviderException is thrown, and none of the changes are written. Other implementations may use other strategies for synchronization.

The program in Example 4-5 is identical to the database viewer in Example 4-4. However, we now use a cached row set. The program logic is now greatly simplified.

  • We simply open and close the connection in every action listener.

  • We no longer need to trap the "window closing" event to close the connection.

  • We no longer worry whether the result set is scrollable. Row sets are always scrollable.

Example 4-5. RowSetTest.java
   1. import com.sun.rowset.*;   2. import java.net.*;   3. import java.sql.*;   4. import java.awt.*;   5. import java.awt.event.*;   6. import java.io.*;   7. import java.util.*;   8. import javax.swing.*;   9. import javax.sql.*;  10. import javax.sql.rowset.*;  11.  12. /**  13.    This program uses metadata to display arbitrary tables  14.    in a database.  15. */  16. public class RowSetTest  17. {  18.    public static void main(String[] args)  19.    {  20.       JFrame frame = new RowSetFrame();  21.       frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);  22.       frame.setVisible(true);  23.    }  24. }  25.  26. /**  27.    The frame that holds the data panel and the navigation  28.    buttons.  29. */  30. class RowSetFrame extends JFrame  31. {  32.    public RowSetFrame()  33.    {  34.       setTitle("RowSetTest");  35.       setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT);  36.  37.       tableNames = new JComboBox();  38.       tableNames.addActionListener(new  39.          ActionListener()  40.          {  41.             public void actionPerformed(ActionEvent event)  42.             {  43.                showTable((String) tableNames.getSelectedItem());  44.             }  45.          });  46.       add(tableNames, BorderLayout.NORTH);  47.  48.       try  49.       {  50.          Connection conn = getConnection();  51.          try  52.          {  53.             DatabaseMetaData meta = conn.getMetaData();  54.             ResultSet mrs = meta.getTables(null, null, null, new String[] { "TABLE" });  55.             while (mrs.next())  56.                tableNames.addItem(mrs.getString(3));  57.          }  58.          finally  59.          {  60.             conn.close();  61.          }  62.       }  63.       catch (SQLException e)  64.       {  65.          JOptionPane.showMessageDialog(this, e);  66.       }  67.       catch (IOException e)  68.       {  69.          JOptionPane.showMessageDialog(this, e);  70.       }  71.  72.       JPanel buttonPanel = new JPanel();  73.       add(buttonPanel, BorderLayout.SOUTH);  74.  75.       previousButton = new JButton("Previous");  76.       previousButton.addActionListener(new  77.          ActionListener()  78.          {  79.             public void actionPerformed(ActionEvent event)  80.             {  81.                showPreviousRow();  82.             }  83.          });  84.       buttonPanel.add(previousButton);  85.  86.       nextButton = new JButton("Next");  87.       nextButton.addActionListener(new  88.          ActionListener()  89.          {  90.             public void actionPerformed(ActionEvent event)  91.             {  92.                showNextRow();  93.             }  94.          });  95.       buttonPanel.add(nextButton);  96.  97.       deleteButton = new JButton("Delete");  98.       deleteButton.addActionListener(new  99.          ActionListener() 100.          { 101.             public void actionPerformed(ActionEvent event) 102.             { 103.                deleteRow(); 104.             } 105.          }); 106.       buttonPanel.add(deleteButton); 107. 108.       saveButton = new JButton("Save"); 109.       saveButton.addActionListener(new 110.          ActionListener() 111.          { 112.             public void actionPerformed(ActionEvent event) 113.             { 114.                saveChanges(); 115.             } 116.          }); 117.       buttonPanel.add(saveButton); 118.    } 119. 120.    /** 121.       Prepares the text fields for showing a new table, and 122.       shows the first row. 123.       @param tableName the name of the table to display 124.    */ 125.    public void showTable(String tableName) 126.    { 127.       try 128.       { 129.          // open connection 130.          Connection conn = getConnection(); 131.          try 132.          { 133.             // get result set 134.             Statement stat = conn.createStatement(); 135.             ResultSet result = stat.executeQuery("SELECT * FROM " + tableName); 136.             // copy into row set 137.             rs = new CachedRowSetImpl(); 138.             rs.setTableName(tableName); 139.             rs.populate(result); 140.          } 141.          finally 142.          { 143.             conn.close(); 144.          } 145. 146.          if (scrollPane != null) 147.             remove(scrollPane); 148.          dataPanel = new DataPanel(rs); 149.          scrollPane = new JScrollPane(dataPanel); 150.          add(scrollPane, BorderLayout.CENTER); 151.          validate(); 152.          showNextRow(); 153.       } 154.       catch (SQLException e) 155.       { 156.          JOptionPane.showMessageDialog(this, e); 157.       } 158.       catch (IOException e) 159.       { 160.          JOptionPane.showMessageDialog(this, e); 161.       } 162.    } 163. 164.    /** 165.       Moves to the previous table row. 166.    */ 167.    public void showPreviousRow() 168.    { 169.       try 170.       { 171.          if (rs == null || rs.isFirst()) return; 172.          rs.previous(); 173.          dataPanel.showRow(rs); 174.       } 175.       catch (SQLException e) 176.       { 177.          System.out.println("Error " + e); 178.       } 179.    } 180. 181.    /** 182.       Moves to the next table row. 183.    */ 184.    public void showNextRow() 185.    { 186.       try 187.       { 188.          if (rs == null || rs.isLast()) return; 189.          rs.next(); 190.          dataPanel.showRow(rs); 191.       } 192.       catch (SQLException e) 193.       { 194.          JOptionPane.showMessageDialog(this, e); 195.       } 196.    } 197. 198.    /** 199.       Deletes current table row. 200.    */ 201.    public void deleteRow() 202.    { 203.       try 204.       { 205.          rs.deleteRow(); 206.          if (!rs.isLast()) rs.next(); 207.          else if (!rs.isFirst()) rs.previous(); 208.          else rs = null; 209.          dataPanel.showRow(rs); 210.       } 211.       catch (SQLException e) 212.       { 213.          JOptionPane.showMessageDialog(this, e); 214.       } 215.    } 216. 217.    /** 218.       Saves all changes. 219.    */ 220.    public void saveChanges() 221.    { 222.       try 223.       { 224.          Connection conn = getConnection(); 225.          try 226.          { 227.             rs.acceptChanges(conn); 228.          } 229.          finally 230.          { 231.             conn.close(); 232.          } 233.       } 234.       catch (SQLException e) 235.       { 236.          JOptionPane.showMessageDialog(this, e); 237.       } 238.       catch (IOException e) 239.       { 240.          JOptionPane.showMessageDialog(this, e); 241.       } 242.    } 243. 244.    /** 245.       Gets a connection from the properties specified 246.       in the file database.properties 247.       @return the database connection 248.    */ 249.    public static Connection getConnection() 250.       throws SQLException, IOException 251.    { 252.       Properties props = new Properties(); 253.       FileInputStream in 254.          = new FileInputStream("database.properties"); 255.       props.load(in); 256.       in.close(); 257. 258.       String drivers = props.getProperty("jdbc.drivers"); 259.       if (drivers != null) System.setProperty("jdbc.drivers", drivers); 260.       String url = props.getProperty("jdbc.url"); 261.       String username = props.getProperty("jdbc.username"); 262.       String password = props.getProperty("jdbc.password"); 263. 264.       return DriverManager.getConnection(url, username, password); 265.    } 266. 267.    public static final int DEFAULT_WIDTH = 400; 268.    public static final int DEFAULT_HEIGHT = 200; 269. 270.    private JButton previousButton; 271.    private JButton nextButton; 272.    private JButton deleteButton; 273.    private JButton saveButton; 274.    private DataPanel dataPanel; 275.    private Component scrollPane; 276.    private JComboBox tableNames; 277. 278.    private CachedRowSet rs; 279. } 280. 281. /** 282.    This panel displays the contents of a result set. 283. */ 284. class DataPanel extends JPanel 285. { 286.    /** 287.       Constructs the data panel. 288.       @param rs the result set whose contents this panel displays 289.    */ 290.    public DataPanel(RowSet rs) throws SQLException 291.    { 292.       fields = new ArrayList<JTextField>(); 293.       setLayout(new GridBagLayout()); 294.       GridBagConstraints gbc = new GridBagConstraints(); 295.       gbc.gridwidth = 1; 296.       gbc.gridheight = 1; 297. 298.       ResultSetMetaData rsmd = rs.getMetaData(); 299.       for (int i = 1; i <= rsmd.getColumnCount(); i++) 300.       { 301.          gbc.gridy = i - 1; 302. 303.          String columnName = rsmd.getColumnLabel(i); 304.          gbc.gridx = 0; 305.          gbc.anchor = GridBagConstraints.EAST; 306.          add(new JLabel(columnName), gbc); 307. 308.          int columnWidth = rsmd.getColumnDisplaySize(i); 309.          JTextField tb = new JTextField(columnWidth); 310.          fields.add(tb); 311. 312.          gbc.gridx = 1; 313.          gbc.anchor = GridBagConstraints.WEST; 314.          add(tb, gbc); 315.       } 316.    } 317. 318.    /** 319.       Shows a database row by populating all text fields 320.       with the column values. 321.    */ 322.    public void showRow(ResultSet rs) throws SQLException 323.    { 324.       for (int i = 1; i <= fields.size(); i++) 325.       { 326.          String field = rs.getString(i); 327.          JTextField tb = (JTextField) fields.get(i - 1); 328.          tb.setText(field); 329.       } 330.    } 331. 332.    private ArrayList<JTextField> fields; 333. } 


 javax.sql.RowSet 1.4 

  • String getURL()

  • void setURL(String url)

    get or set the database URL.

  • String getUsername()

  • void setUsername(String username)

    get or set the user name for connecting to the database.

  • String getPassword()

  • void setPassword(String password)

    get or set the password for connecting to the database.

  • String getCommand()

  • void setCommand(String command)

    get or set the command that is executed to populate this row set.

  • void execute()

    populates this row set by issuing the command set with setCommand. For the driver manager to obtain a connection, the URL, user name, and password must be set.


 javax.sql.rowset.CachedRowSet 5.0 

  • void execute(Connection conn)

    populates this row set by issuing the command set with setCommand. This method uses the given connection and closes it.

  • void populate(ResultSet result)

    populates this cached row set with the data from the given result set.

  • String getTableName()

  • void setTableName(String tableName)

    get or set the name of the table from which this cached row set was populated.

  • void acceptChanges()

  • void acceptChanges(Connection conn)

    reconnect to the database and write the changes that are the result of editing the row set. May throw a SyncProviderException if the data cannot be written back because the database data have changed.



    Core JavaT 2 Volume II - Advanced Features
    Building an On Demand Computing Environment with IBM: How to Optimize Your Current Infrastructure for Today and Tomorrow (MaxFacts Guidebook series)
    ISBN: 193164411X
    EAN: 2147483647
    Year: 2003
    Pages: 156
    Authors: Jim Hoskins

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