Metadata


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 application


Many 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.

1.

Add the table name combo box, the panel that displays the table values, and the button panel.

2.

Connect to the database. Find out if it supports scrollable result sets. If so, create the Statement object to yield scrollable result sets. Otherwise, just create a default Statement.

3.

Get the table names and fill them into the choice component.

4.

If scrolling is supported, add the Previous button. Always add the Next button.

5.

When the user selects a table, make a query to see all its values. Get the result set metadata. Throw out the old scroll pane from the center panel. Create a panel containing a grid bag layout of labels and text fields. Add it to the frame and call the validate method to recompute the frame layout. Then, call showNextRow to show the first row.

6.

Call the showNextRow method to show the first record and also whenever the user clicks the Next button. The showNextRow method gets the next row from the table and fills the column values into the text boxes.

7.

There is a slight subtlety in detecting the end of the result set. When the result set is scrollable, we can simply use the isLast method. But when it isn't scrollable, that method call will cause an exception (or even a JVM error if the driver is a JDBC 1 driver). Therefore, we use a different strategy for non-scrollable result sets. When rs.next() returns false, we close the result set and set rs to null.

8.

The Previous button calls showPreviousRow, which moves the result set backwards. Because this button is only installed when the result set is scrollable, we know that the previous and isFirst method are supported.

9.

The showRow method simply fills in all the result set fields into the text fields of the data panel.

Example 4-4 is the program.

Example 4-4. ViewDB.java
   1. 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 

  • DatabaseMetaData getMetaData()

    returns the metadata for the connection as a DatabaseMetaData object.


 java.sql.DatabaseMetaData 1.1 

  • ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])

    gets a description of all tables in a catalog that match the schema and table name patterns and the type criteria. (A schema describes a group of related tables and access permissions. A catalog describes a related group of schemas. These concepts are important for structuring large databases.)

    The catalog and schema parameters can be "" to retrieve those tables without a catalog or schema, or null to return tables regardless of catalog or schema.

    The types array contains the names of the table types to include. Typical types are TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, and SYNONYM. If types is null, then tables of all types are returned.

    The result set has five columns, all of which are of type String, as shown in Table 4-9.

    Table 4-9. Five Columns of the getTables Method

    1

    TABLE_CAT

    Table catalog (may be null)

    2

    TABLE_SCHEM

    Table schema (may be null)

    3

    TABLE_NAME

    Table name

    4

    TABLE_TYPE

    Table type

    5

    REMARKS

    Comment on the table


  • int getJDBCMajorVersion()

  • int getJDBCMinorVersion()

    (JDBC 3) return the major and minor JDBC version numbers of the driver that established the database connection. For example, a JDBC 3.0 driver has major version number 3 and minor version number 0.

  • int getMaxConnections()

    returns the maximum number of concurrent connections to this database.

  • int getMaxStatements()

    returns the maximum number of concurrently open statements per database connection, or 0 if the number is unlimited or unknown.


 java.sql.ResultSet 1.1 

  • ResultSetMetaData getMetaData()

    gives you the metadata associated with the current ResultSet columns.


 java.sql.ResultSetMetaData 1.1 

  • int getColumnCount()

    returns the number of columns in the current ResultSet object.

  • int getColumnDisplaySize(int column)

    tells you the maximum width of the column specified by the index parameter.

    Parameters:

    column

    The column number


  • String getColumnLabel(int column)

    gives you the suggested title for the column.

    Parameters:

    column

    The column number


  • String getColumnName(int column)

    gives the column name associated with the column index specified.

    Parameters:

    column

    The column number




    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