|
| |
Metadata, that is, data about data, is very valuable for
relational databases, especially if you are writing very
generalized tools. For example, consider the problem of
writing a program to provide an interactive SQL capability.
It should be able to connect to any database and then allow
the user to type in SQL statements for it to execute against
that database. The user would very likely wish to know what
tables are available in the database, and, given a table,
what columns exist in that table, and so on.
Metadata is even more important in JDBC, because its role
is to provide access to many different databases, whose
properties vary somewhat from one database to another. So,
JDBC provides lots of data about data.
Driver Metadata
You can find out information about your JDBC drivers,
using:
- DriverManager.getDrivers()
and:
- Various Driver
informational methods
For example:
package jdbc;
import java.util.Enumeration;
import java.util.Properties;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.SQLException;
/**
* Class to list the loaded JDBC drivers, together with their properties.
*
* @author Bryan J. Higgs, 4 March 2000
*/
public class ListDrivers
{
public static void main(String[] args)
{
try
{
// Cause the JDBC drivers to be automatically loaded
System.getProperties().put("jdbc.drivers",
"sun.jdbc.odbc.JdbcOdbcDriver:" +
"ORG.as220.tinySQL.textFileDriver:" +
"org.gjt.mm.mysql.Driver");
// List details of the loaded drivers
listDrivers();
}
catch (Exception e)
{
e.printStackTrace();
}
}
private static void listDrivers()
throws SQLException
{
Enumeration drivers = DriverManager.getDrivers();
while (drivers.hasMoreElements())
{
Driver driver = (Driver) drivers.nextElement();
// Display information about the driver
print("Driver: " + driver.getClass().getName());
print(" Major version: " + driver.getMajorVersion());
print(" Minor version: " + driver.getMinorVersion());
print(" JDBC compliant: " + driver.jdbcCompliant());
// List details of the driver properties
listDriverProperties(driver);
}
}
private static void listDriverProperties(Driver driver)
throws SQLException
{
DriverPropertyInfo props[] = driver.getPropertyInfo(
"", new Properties());
if (props != null && props.length > 0)
{
print(" Properties: ");
// Display each property name and value
for (int i = 0; i < props.length; i++)
{
print(" Name: " + props[i].name);
print(" Description: " + props[i].description);
print(" Value: " + props[i].value);
if (props[i].choices != null)
{
print(" Choices: ");
for (int choice = 0;
choice < props[i].choices.length;
choice++)
{
print(" " + props[i].choices[choice]);
}
}
print(" Required: " + props[i].required);
}
}
}
private static void print(String text)
{
System.out.println(text);
}
} |
This produces the following results on my machine:
Driver: sun.jdbc.odbc.JdbcOdbcDriver
Major version: 1
Minor version: 1001
JDBC compliant: true
Driver: ORG.as220.tinySQL.textFileDriver
Major version: 0
Minor version: 9
JDBC compliant: false
Driver: org.gjt.mm.mysql.Driver
Major version: 1
Minor version: 2
JDBC compliant: false
Properties:
Name: HOST
Description: Hostname of MySQL Server
Value: null
Required: true
Name: PORT
Description: Port number of MySQL Server
Value: 3306
Required: false
Name: DBNAME
Description: Database name
Value: null
Required: false
Name: user
Description: Username to authenticate as
Value: null
Required: true
Name: password
Description: Password to use for authentication
Value: null
Required: true
Name: autoReconnect
Description: Should the driver try to re-establish bad connections?
Value: false
Choices:
true
false
Required: false
Name: maxReconnects
Description: Maximum number of reconnects to attempt if autoReconnect is true
Value: 3
Required: false
Name: initialTimeout
Description: Initial timeout (seconds) to wait between failed connections
Value: 2
Required: false
The DatabaseMetadata
Interface
Once you have made a connection to a database, you can
call the Connection class' getMetaData()
method to obtain the database's metadata, which is
encapsulated in the DatabaseMetaData
class. This class supplies a lot of useful information, such
as:
- The database product name and version number
- The default transaction isolation level
- JDBC driver name, and major and minor version numbers
- All the "extra" characters that can be used
in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _).
- The string used to quote SQL identifiers
(JDBC-compliant drivers always use the double quote
character, " )
- The maximum sizes of names, literals, etc.
- The maximum columns per table.
- The maximum number of concurrent connections
supported
- The maximum row size
- The tables that exist in the database
- Information about the datatypes available for the
database
- The column names that exist in the database, and
their attributes
- The indexes that exist in the database, and their
statistics.
- The procedures that exist in the database
- The table privileges
- Whether SQL92 entry, intermediate, or full levels are
supported.
- What foreign key columns reference the primary key
columns of a primary key table
- What user name is being used for this connection
- Whether this database connection is read-only
- What keywords are used by the database that are not
SQL-92 standard
- Whether the database supports column aliasing
- Whether multiple result sets from a single execute() call are
supported
- Whether outer joins are supported
- What the primary keys are for a table
and a whole host of other information, both basic and
esoteric. Take a look at the DatabaseMetaData class to get a
sense for how much information is available!
The ResultSetMetaData
Interface
Once you have created a ResultSet,
you can query its attributes by using the ResultSetMetaData
interface.
For example:
package jdbc;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class GetResultSetMetaData
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:Employees");
stmt = conn.createStatement();
rs = stmt.executeQuery(
"SELECT employees.name, NULL, departments.name, age, salary " +
"FROM employees, departments " +
"WHERE employees.department = departmentID");
printResultSetMetaData(rs);
}
catch(ClassNotFoundException ex)
{
ex.printStackTrace();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
finally
{
if (conn != null)
{
try
{
if (stmt != null)
{
if (rs != null)
rs.close();
stmt.close();
}
conn.close();
}
catch(SQLException ex)
{ /* Do nothing */ }
}
}
}
private static void printResultSetMetaData(ResultSet rs)
throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
System.out.println("ResultSet has " + count + " columns:");
for (int i = 1; i <= count; i++)
{
println("[" + i + "]");
println(" Name: " + rsmd.getColumnName(i));
println(" Label: " + rsmd.getColumnLabel(i));
println(" Catalog: " + rsmd.getCatalogName(i));
println(" Schema: " + rsmd.getSchemaName(i));
println(" Table: " + rsmd.getTableName(i));
println(" Type: " + rsmd.getColumnTypeName(i));
println(" Display size: " + rsmd.getColumnDisplaySize(i));
println(" Precision: " + rsmd.getPrecision(i));
println(" Scale: " + rsmd.getScale(i));
println(" CaseSensitive:" + rsmd.isCaseSensitive(i));
println(" Currency: " + rsmd.isCurrency(i));
println(" Nullable: " + rsmd.isNullable(i));
// etc...
}
}
private static void println(String text)
{
System.out.println(text);
}
} |
Which outputs, on my machine, with one ODBC data source:
ResultSet has 5 columns:
[1]
Name: name
Label: name
Catalog:
Schema:
Table:
Type: CHAR
Display size: 40
Precision: 40
Scale: 0
CaseSensitive:true
Currency: false
Nullable: 1
[2]
Name: Expr1001
Label: Expr1001
Catalog:
Schema:
Table:
Type:
Display size: -4
Precision: -100
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1
[3]
Name: name
Label: name
Catalog:
Schema:
Table:
Type: CHAR
Display size: 40
Precision: 40
Scale: 0
CaseSensitive:true
Currency: false
Nullable: 1 |
[4]
Name: age
Label: age
Catalog:
Schema:
Table:
Type: INTEGER
Display size: 11
Precision: 10
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1
[5]
Name: salary
Label: salary
Catalog:
Schema:
Table:
Type: FLOAT
Display size: 22
Precision: 15
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1 |
and the following with a different ODBC data source that
contained the same data (which shows some subtle
differences):
[1]
Name: name
Label: name
Catalog:
Schema:
Table:
Type: CHAR
Display size: 40
Precision: 40
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1
[2]
Name: Expr1001
Label: Expr1001
Catalog:
Schema:
Table:
Type: BINARY
Display size: -4
Precision: -100
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1
[3]
Name: name
Label: name
Catalog:
Schema:
Table:
Type: CHAR
Display size: 40
Precision: 40
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1 |
[4]
Name: age
Label: age
Catalog:
Schema:
Table:
Type: INTEGER
Display size: 11
Precision: 10
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1
[5]
Name: salary
Label: salary
Catalog:
Schema:
Table:
Type: DOUBLE
Display size: 22
Precision: 15
Scale: 0
CaseSensitive:false
Currency: false
Nullable: 1 |
Here is a program that can connect to a database, and
display all the tables in it. Then, if you select one of
those tables, you can see the columns in the table, and the
data in the rows of that table.
Initially, when you run the program, you see a window like
this:
If you select the Employees table, you then see the
following:
which shows the columns in the table, and the values for
the first row of the table. You can click on the Next > button
to move to the next row's data, etc.
If you select the Departments table, you will see the
following:
and so on.
Here is the program that accomplishes this. It is based on
the ViewDB program in CORE Java 2 Vol II: Advanced Features,
by Cay Horstmann & Gary Cornell, with some changes and
restructuring. It also switches back to using AWT, rather
than JFC/Swing.
package jdbc;
import java.awt.BorderLayout;
import java.awt.Button;
import java.awt.Choice;
import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.Dimension;
import java.awt.Frame;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.Label;
import java.awt.Panel;
import java.awt.Rectangle;
import java.awt.TextField;
import java.awt.Toolkit;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.util.Vector;
import messageBox.MessageBox;
/**
* Class to view the tables in a database, and their data.
*
* @author Bryan J. Higgs, 4 March 2000
*/
public class ViewTables extends Frame
{
public static void main(String[] args)
{
Frame f = new ViewTables();
// Center frame on screen
Toolkit toolkit = Toolkit.getDefaultToolkit();
Dimension screen = toolkit.getScreenSize();
Dimension frame = f.getSize();
f.setLocation( (screen.width - frame.width)/2,
(screen.height - frame.height)/2);
// Make it visible
f.setVisible(true);
}
/**
* Constructs an instance of ViewTables.
*/
public ViewTables()
{
super("View Tables");
setSize(300, 200);
addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent ev)
{
dispose();
System.exit(0);
}
}
);
// Do layout...
// Top panel goes to the North
Panel top = new Panel( new BorderLayout() );
top.setBackground(Color.lightGray);
// Contains a label...
Label tableLabel = new Label("Table:", Label.RIGHT);
top.add(tableLabel, BorderLayout.WEST);
// ...and a Choice
Choice tableNames = new Choice();
tableNames.addItemListener(new ItemListener()
{
public void itemStateChanged(ItemEvent ev)
{
if (ev.getStateChange() == ItemEvent.SELECTED)
{
// We have selected a table name, so figure
// out what columns we have and lay them out.
loadColumnDisplay((String)ev.getItem());
}
}
}
);
top.add(tableNames, BorderLayout.CENTER);
add(top, BorderLayout.NORTH);
// Data panel goes in the Center
m_dataPanel = new Panel();
// Initially contains a helpful label
m_dataPanel.add( new Label("Select a table to display data.") );
add(m_dataPanel, BorderLayout.CENTER);
// Bottom panel goes to the South
Panel bottom = new Panel();
// Contains just a button
m_nextButton = new Button("Next >");
m_nextButton.setEnabled(false);
m_nextButton.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ev)
{
// Cause the current data to be displayed
// and the next row to be read.
displayRow();
}
}
);
bottom.add(m_nextButton);
add(bottom, BorderLayout.SOUTH);
// Load the table names into the choice box.
loadTableNames(tableNames);
}
/**
* Gets a connection to the specified database,
* using the specified driver.
*/
private static Connection getConnection()
throws SQLException, ClassNotFoundException
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(
"jdbc:odbc:Employees");
SQLWarning warn = con.getWarnings();
if (warn != null)
printSQLWarnings(warn);
return con;
}
/**
* Loads the specified choice box with the names of all the tables.
*/
private void loadTableNames(Choice tableNames)
{
// Make the connection, then discover all the tables
// and load their names into the table names choice box.
ResultSet metaRs = null;
try
{
m_con = getConnection();
m_stmt = m_con.createStatement();
// Obtain the metadata for the tables
DatabaseMetaData dbMd = m_con.getMetaData();
metaRs = dbMd.getTables(null, // Catalog name
null, // Schema name
null, // Table name
new String [] { "TABLE", "VIEW" }
// Types
);
SQLWarning warn = metaRs.getWarnings();
if (warn != null)
printSQLWarnings(warn);
while (metaRs.next())
{
tableNames.addItem(metaRs.getString(3));
}
}
catch(SQLException ex)
{
printSQLExceptions(ex);
MessageBox.show(this, ex.toString());
}
catch(ClassNotFoundException ex)
{
MessageBox.show(this, ex.toString());
}
finally
{
try
{
if (metaRs != null)
metaRs.close();
}
catch (SQLException ex)
{ /* Do nothing */ }
}
}
/**
* Adds the specified component to the specified container,
* using the specified GridBagConstraints, grid position,
* width & height.
*/
private void addComponent(
Container container, Component component,
GridBagConstraints gbc,
int x, int y, int width, int height)
{
gbc.gridx = x;
gbc.gridy = y;
gbc.gridwidth = width;
gbc.gridheight = height;
container.add(component, gbc);
}
/**
* Loads the information about the table columns, and
* sets up the GUI layout to accommodate the data for a row.
*/
private void loadColumnDisplay(String tableName)
{
// Remove the existing data panel
remove(m_dataPanel);
// and replace it with a new one
m_dataPanel = new Panel()
{
public Insets getInsets()
{
return new Insets(5, 5, 5, 5);
}
};
m_dataPanel.setLayout( new GridBagLayout() );
// Remove all the column textfields from the Vector
m_fields.removeAllElements();
// Now, execute a SELECT * FROM table query against this
// table and then use the ResultSet meta data to find
// column information.
// Then dynamically lay out the data panel.
GridBagConstraints gbc = new GridBagConstraints();
gbc.weighty = 100;
try
{
if (m_rs != null)
m_rs.close();
m_rs = m_stmt.executeQuery("SELECT * from " + tableName);
SQLWarning warn = m_rs.getWarnings();
if (warn != null)
printSQLWarnings(warn);
ResultSetMetaData rsmd = m_rs.getMetaData();
for (int col = 1; col <= rsmd.getColumnCount(); col++)
{
String columnName = rsmd.getColumnLabel(col);
int columnWidth = rsmd.getColumnDisplaySize(col);
TextField colField = new TextField(columnWidth);
// Keep track of the TextFields in a Vector
m_fields.addElement(colField);
// Add a label for the column, containing the column name
gbc.weightx = 0;
gbc.anchor = GridBagConstraints.EAST;
gbc.fill = GridBagConstraints.NONE;
addComponent(m_dataPanel, new Label(columnName + ":"),
gbc, 0, col-1, 1, 1);
// Add a text field for the data in the column
gbc.weightx = 100;
gbc.anchor = GridBagConstraints.WEST;
gbc.fill = GridBagConstraints.HORIZONTAL;
addComponent(m_dataPanel, colField,
gbc, 1, col-1, 1, 1);
}
}
catch(Exception ex)
{
MessageBox.show(this, ex.toString());
}
// Add the new data panel in place of the old one.
add(m_dataPanel, BorderLayout.CENTER);
// Force a layout and sizing.
pack();
// Enable Next button so user can navigate rows
m_nextButton.setEnabled(true);
// Read first row
readRow();
// Display row results
displayRow();
}
/**
* Reads the next row for the table.
* When it comes to the end of the rows, closes the ResultSet
* and disables the Next button so we can't go any further.
*/
private void readRow()
{
if (m_rs != null)
{
try
{
if (!m_rs.next()) // Move to next row
{
m_rs.close(); // We're done
m_rs = null;
// Disable the Next button
m_nextButton.setEnabled(false);
}
}
catch (Exception ex)
{
MessageBox.show(this, ex.toString());
}
}
}
/**
* Displays the current row contents in the GUI text fields.
* Then reads the next row, so we're ready for it, and so we
* can disable the Next button properly, if there is no next row.
*/
private void displayRow()
{
try
{
for (int col = 1; col <= m_fields.size(); col++)
{
String colValue = m_rs.getString(col);
TextField colField = (TextField) m_fields.elementAt(col-1);
colField.setText(colValue);
}
}
catch (Exception ex)
{
MessageBox.show(this, ex.toString());
}
// Read next row
readRow();
}
/**
* Prints out a SQLException and its related SQLExceptions
*/
private static void printSQLExceptions(SQLException ex)
{
System.err.println("\n--- SQLException caught ---\n");
for ( ;ex != null; ex = ex.getNextException())
{
System.err.println("Message: " + ex.getMessage());
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("ErrorCode: " + ex.getErrorCode());
ex.printStackTrace(System.err);
System.err.println();
}
}
/**
* Prints out a SQLWarning and its related SQLWarnings
*/
private static void printSQLWarnings(SQLWarning warn)
{
System.err.println("\n--- SQLWarning ---\n");
for ( ;warn != null; warn = warn.getNextWarning())
{
System.err.println("Message: " + warn.getMessage());
System.err.println("SQLState: " + warn.getSQLState());
System.err.println("ErrorCode: " + warn.getErrorCode());
warn.printStackTrace(System.err);
System.err.println();
}
}
///// Private data /////
private Button m_nextButton; // Next button
private Panel m_dataPanel; // Panel for column data display
private Vector m_fields = new Vector();
// Holds column TextFields
private Connection m_con; // Connection to use
private Statement m_stmt; // Statement to use
private ResultSet m_rs; // ResultSet used to read rows
} |
For completeness, here's the MessageBox class that the
above program uses:
package messageBox;
import java.awt.BorderLayout;
import java.awt.Button;
import java.awt.Dialog;
import java.awt.Dimension;
import java.awt.Frame;
import java.awt.Graphics;
import java.awt.Insets;
import java.awt.Panel;
import java.awt.Rectangle;
import java.awt.TextArea;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
/**
* Shows a message box with the specified text.
* @author Bryan J. Higgs, 4 March 2000
*/
public class MessageBox extends Dialog
{
/**
* Shows a message box with the specified message text
* with the specified parent Frame.
*/
public static void show(Frame frame, String msg)
{
MessageBox box = new MessageBox(frame, msg);
box.setVisible(true);
}
/**
* Constructs a MessageBox instance.
* (Private, so the only way of using a MessageBox is
* via the show() method.)
*/
private MessageBox(Frame frame, String msg)
{
super(frame, "Message");
addWindowListener
(
new WindowAdapter()
{
public void windowClosing(WindowEvent ev)
{
closeWindow();
}
}
);
Panel message = new MessagePanel(msg);
add(message, BorderLayout.CENTER);
Panel p = new Panel();
Button ok = new Button("OK");
p.add(ok);
add(p, BorderLayout.SOUTH);
ok.addActionListener
(
new ActionListener()
{
public void actionPerformed(ActionEvent ev)
{
closeWindow();
}
}
);
pack();
// Ensure that the message box displays close to
// its parent frame.
Rectangle frameRect = frame.getBounds();
Rectangle boxRect = getBounds();
boxRect.x = frameRect.x + 10;
boxRect.y = frameRect.y + 10;
setBounds(boxRect.x, boxRect.y, boxRect.width, boxRect.height);
}
/**
* Closes the MessageBox window.
*/
private void closeWindow()
{
setVisible(false);
dispose();
}
/**
* Main entry point, for testing.
*/
public static void main(String[] args)
{
Frame frame = new Frame("Testing MessageBox");
frame.setBounds(100, 100, 300, 200);
frame.addWindowListener
(
new WindowAdapter()
{
public void windowClosing(WindowEvent ev)
{
System.exit(0);
}
}
);
frame.setVisible(true);
show(frame, "Hello\nHow\nAre\nYou");
}
///// Inner classes /////
/**
* A Message Panel, to display a message in the MessageBox.
*/
class MessagePanel extends Panel
{
MessagePanel(String msg)
{
m_msg = new TextArea(msg, 4, 30,
TextArea.SCROLLBARS_NONE);
m_msg.setEditable(false);
setLayout( new BorderLayout() );
add(m_msg, BorderLayout.CENTER);
}
public Insets getInsets()
{
return new Insets(10, 10, 10, 10);
}
//// Private Data ////
private TextArea m_msg;
private Dimension m_dim = null;
}
} |
|
