|
In the preceding sections, you saw how to populate, query, and update database tables. However, JDBC can give you additional information about the structure of a database and its tables. For example, you can get a list of the tables in a particular database or the column names and types of a table. This information is not useful when you are implementing a business application with a predefined database. After all, if you design the tables, you know their structure. Structural information is, however, extremely useful for programmers who write tools that work with any database. In this section, we show you how to write such a simple tool. This tool lets you browse all tables in a database. The combo box on top displays all tables in the database. Select one of them, and the center of the frame is filled with the field names of that table and the values of the first record, as shown in Figure 4-8. Click Next to scroll through the records in the table. Figure 4-8. The ViewDB applicationMany databases come with much more sophisticated tools for viewing and editing tables. If your database doesn't, check out iSQL-Viewer (http://isql.sourceforge.net) or SQuirreL (http://squirrel-sql.sourceforge.net). These programs can view the tables in any JDBC database. Our example program is not intended as a replacement for these tools, but it shows you how to implement a tool for working with arbitrary tables. In SQL, data that describe the database or one of its parts are called metadata (to distinguish them from the actual data stored in the database). You can get three kinds of metadata: about a database, about a result set, and about parameters of prepared statements. To find out more about the database, you request an object of type DatabaseMetaData from the database connection. DatabaseMetaData meta = conn.getMetaData(); Now you are ready to get some metadata. For example, the call ResultSet mrs = meta.getTables(null, null, null, new String[] { "TABLE" }); returns a result set that contains information about all tables in the database. (See the API note for other parameters to this method.) Each row in the result set contains information about a table in the database. We only care about the third column, the name of the table. (Again, see the API note for the other columns.) Thus, rs.getString(3) is the table name. Here is the code that populates the combo box. while (mrs.next()) tableNames.addItem(mrs.getString(3)); rs.close(); The DatabaseMetaData class gives data about the database. A second metadata class, ResultSetMetaData, reports information about a result set. Whenever you have a result set from a query, you can inquire about the number of columns and each column's name, type, and field width. We use this information to make a label for each column name and a text field of sufficient size for each value. ResultSet mrs = stat.executeQuery("SELECT * FROM " + tableName); ResultSetMetaData meta = mrs.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { String columnName = meta.getColumnLabel(i); int columnWidth = meta.getColumnDisplaySize(i); JLabel l = new Label (columnName); JTextField tf = new TextField (columnWidth); . . . } There is a second important use for database metadata. Databases are complex, and the SQL standard leaves plenty of room for variability. Well over a hundred methods in the DatabaseMetaData class can inquire about the database, including calls with exotic names such as meta.supportsCatalogsInPrivilegeDefinitions() and meta.nullPlusNonNullIsNull() Clearly, these are geared toward advanced users with special needs, in particular, those who need to write highly portable code that works with multiple databases. In our sample program, we give only one example of this technique. We ask the database metadata whether the JDBC driver supports scrollable result sets. If so, we open a scrollable result set and add a Previous button for scrolling backward. if (meta.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)) . . . The following steps briefly describe the sample program.
Example 4-4 is the program. Example 4-4. ViewDB.java1. import java.net.*; 2. import java.sql.*; 3. import java.awt.*; 4. import java.awt.event.*; 5. import java.io.*; 6. import java.util.*; 7. import javax.swing.*; 8. 9. /** 10. This program uses metadata to display arbitrary tables 11. in a database. 12. */ 13. public class ViewDB 14. { 15. public static void main(String[] args) 16. { 17. JFrame frame = new ViewDBFrame(); 18. frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 19. frame.setVisible(true); 20. } 21. } 22. 23. /** 24. The frame that holds the data panel and the navigation 25. buttons. 26. */ 27. class ViewDBFrame extends JFrame 28. { 29. public ViewDBFrame() 30. { 31. setTitle("ViewDB"); 32. setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT); 33. 34. tableNames = new JComboBox(); 35. tableNames.addActionListener(new 36. ActionListener() 37. { 38. public void actionPerformed(ActionEvent event) 39. { 40. showTable((String) tableNames.getSelectedItem()); 41. } 42. }); 43. add(tableNames, BorderLayout.NORTH); 44. 45. try 46. { 47. conn = getConnection(); 48. meta = conn.getMetaData(); 49. createStatement(); 50. getTableNames(); 51. } 52. catch (SQLException e) 53. { 54. JOptionPane.showMessageDialog(this, e); 55. } 56. catch (IOException e) 57. { 58. JOptionPane.showMessageDialog(this, e); 59. } 60. 61. JPanel buttonPanel = new JPanel(); 62. add(buttonPanel, BorderLayout.SOUTH); 63. 64. if (scrolling) 65. { 66. previousButton = new JButton("Previous"); 67. previousButton.addActionListener(new 68. ActionListener() 69. { 70. public void actionPerformed(ActionEvent event) 71. { 72. showPreviousRow(); 73. } 74. }); 75. buttonPanel.add(previousButton); 76. } 77. 78. nextButton = new JButton("Next"); 79. nextButton.addActionListener(new 80. ActionListener() 81. { 82. public void actionPerformed(ActionEvent event) 83. { 84. showNextRow(); 85. } 86. }); 87. buttonPanel.add(nextButton); 88. 89. addWindowListener(new 90. WindowAdapter() 91. { 92. public void windowClosing(WindowEvent event) 93. { 94. try 95. { 96. if (conn != null) conn.close(); 97. } 98. catch (SQLException e) 99. { 100. while (e != null) 101. { 102. e.printStackTrace(); 103. e = e.getNextException(); 104. } 105. } 106. } 107. }); 108. } 109. 110. /** 111. Creates the statement object used for executing queries. 112. If the database supports scrolling cursors, the statement 113. is created to yield them. 114. */ 115. public void createStatement() throws SQLException 116. { 117. if (meta.supportsResultSetType( 118. ResultSet.TYPE_SCROLL_INSENSITIVE)) 119. { 120. stat = conn.createStatement( 121. ResultSet.TYPE_SCROLL_INSENSITIVE, 122. ResultSet.CONCUR_READ_ONLY); 123. scrolling = true; 124. } 125. else 126. { 127. stat = conn.createStatement(); 128. scrolling = false; 129. } 130. } 131. 132. /** 133. Gets all table names of this database and adds them 134. to the combo box. 135. */ 136. public void getTableNames() throws SQLException 137. { 138. ResultSet mrs = meta.getTables(null, null, null, new String[] { "TABLE" }); 139. while (mrs.next()) 140. tableNames.addItem(mrs.getString(3)); 141. mrs.close(); 142. } 143. 144. /** 145. Prepares the text fields for showing a new table, and 146. shows the first row. 147. @param tableName the name of the table to display 148. */ 149. public void showTable(String tableName) 150. { 151. try 152. { 153. if (rs != null) rs.close(); 154. rs = stat.executeQuery("SELECT * FROM " + tableName); 155. if (scrollPane != null) 156. remove(scrollPane); 157. dataPanel = new DataPanel(rs); 158. scrollPane = new JScrollPane(dataPanel); 159. add(scrollPane, BorderLayout.CENTER); 160. validate(); 161. showNextRow(); 162. } 163. catch (SQLException e) 164. { 165. JOptionPane.showMessageDialog(this, e); 166. } 167. } 168. 169. /** 170. Moves to the previous table row. 171. */ 172. public void showPreviousRow() 173. { 174. try 175. { 176. if (rs == null || rs.isFirst()) return; 177. rs.previous(); 178. dataPanel.showRow(rs); 179. } 180. catch (SQLException e) 181. { 182. JOptionPane.showMessageDialog(this, e); 183. } 184. } 185. 186. /** 187. Moves to the next table row. 188. */ 189. public void showNextRow() 190. { 191. try 192. { 193. if (rs == null || scrolling && rs.isLast()) return; 194. 195. if (!rs.next() && !scrolling) 196. { 197. rs.close(); 198. rs = null; 199. return; 200. } 201. 202. dataPanel.showRow(rs); 203. } 204. catch (SQLException e) 205. { 206. JOptionPane.showMessageDialog(this, e); 207. } 208. } 209. 210. /** 211. Gets a connection from the properties specified 212. in the file database.properties 213. @return the database connection 214. */ 215. public static Connection getConnection() 216. throws SQLException, IOException 217. { 218. Properties props = new Properties(); 219. FileInputStream in 220. = new FileInputStream("database.properties"); 221. props.load(in); 222. in.close(); 223. 224. String drivers = props.getProperty("jdbc.drivers"); 225. if (drivers != null) System.setProperty("jdbc.drivers", drivers); 226. String url = props.getProperty("jdbc.url"); 227. String username = props.getProperty("jdbc.username"); 228. String password = props.getProperty("jdbc.password"); 229. 230. return DriverManager.getConnection(url, username, password); 231. } 232. 233. public static final int DEFAULT_WIDTH = 300; 234. public static final int DEFAULT_HEIGHT = 200; 235. 236. private JButton previousButton; 237. private JButton nextButton; 238. private DataPanel dataPanel; 239. private Component scrollPane; 240. private JComboBox tableNames; 241. 242. private Connection conn; 243. private Statement stat; 244. private DatabaseMetaData meta; 245. private ResultSet rs; 246. private boolean scrolling; 247. } 248. 249. /** 250. This panel displays the contents of a result set. 251. */ 252. class DataPanel extends JPanel 253. { 254. /** 255. Constructs the data panel. 256. @param rs the result set whose contents this panel displays 257. */ 258. public DataPanel(ResultSet rs) throws SQLException 259. { 260. fields = new ArrayList<JTextField>(); 261. setLayout(new GridBagLayout()); 262. GridBagConstraints gbc = new GridBagConstraints(); 263. gbc.gridwidth = 1; 264. gbc.gridheight = 1; 265. 266. ResultSetMetaData rsmd = rs.getMetaData(); 267. for (int i = 1; i <= rsmd.getColumnCount(); i++) 268. { 269. gbc.gridy = i - 1; 270. 271. String columnName = rsmd.getColumnLabel(i); 272. gbc.gridx = 0; 273. gbc.anchor = GridBagConstraints.EAST; 274. add(new JLabel(columnName), gbc); 275. 276. int columnWidth = rsmd.getColumnDisplaySize(i); 277. JTextField tb = new JTextField(columnWidth); 278. fields.add(tb); 279. 280. gbc.gridx = 1; 281. gbc.anchor = GridBagConstraints.WEST; 282. add(tb, gbc); 283. } 284. } 285. 286. /** 287. Shows a database row by populating all text fields 288. with the column values. 289. */ 290. public void showRow(ResultSet rs) throws SQLException 291. { 292. for (int i = 1; i <= fields.size(); i++) 293. { 294. String field = rs.getString(i); 295. JTextField tb = (JTextField) fields.get(i - 1); 296. tb.setText(field); 297. } 298. } 299. 300. private ArrayList<JTextField> fields; 301. } java.sql.Connection 1.1
java.sql.DatabaseMetaData 1.1
java.sql.ResultSet 1.1
java.sql.ResultSetMetaData 1.1
|
|