|
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:
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 SetsA 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
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.
Example 4-5. RowSetTest.java1. 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
javax.sql.rowset.CachedRowSet 5.0
|
|