Through the
java.sql.DatabaseMetaData interface you can obtain meta data about the database you have connected to. For instance, you can see what tables are defined in the database, and what columns each table has, whether given features are supported etc.
The
DatabaseMetaData interface contains a lot of methods, and not all of them will be covered in this tutorial. You should check out the JavaDoc's. This text will just cover enough to give you a feeling for what you can do with it.Obtaining a DatabaseMetaData Instance
You obtain the
DatabaseMetaData object from a Connection, like this:DatabaseMetaData databaseMetaData = connection.getMetaData();
Once you have obtained this
DatabaseMetaData instance, you can call methods on it to obtain the meta data about the database.Database Product Name and Version
You can obtain the database product name and version, like this:
int majorVersion = databaseMetaData.getDatabaseMajorVersion(); int minorVersion = databaseMetaData.getDatabaseMinorVersion(); String productName = databaseMetaData.getDatabaseProductName(); String productVersion = databaseMetaData.getDatabaseProductVersion();
If you already know exactly what database your application is running against, you may not need this. But, if you are developing a product that needs to be able to run against many different database products, this information can be quite handy in determining what database specific features it supports, SQL it supports etc.
Database Driver Version
You can obtain the driver version of the JDBC driver used, like this:
int driverMajorVersion = databaseMetaData.getDriverMajorVersion(); int driverMinorVersion = databaseMetaData.getDriverMinorVersion();
Again, if your application runs against a very specific database, this may not really be so informative. However, for applications that need to be able to run against many different database products and versions, knowing the exact version of the used driver may be an advantage. For instance, a certain driver version may contain a bug that the application need to work around. Or, the driver may be missing a feature that the application then needs to work around.
Listing Tables
You can obtain a list of the defined tables in your database, via the
DatabaseMetaData. Here is how that is done:String catalog = null;
String schemaPattern = null;
String tableNamePattern = null;
String[] types = null;
ResultSet result = databaseMetaData.getTables(
catalog, schemaPattern, tableNamePattern, types );
while(result.next()) {
String tableName = result.getString(3);
}
First you call the
getTables() method, passing it 4 parameters which are all null. The parameters can help limit the number of tables that are returned in the ResultSet. However, since I want all tables returned, I passed null in all of these parameters. See the JavaDoc for more specific details about the parameters.
The
ResultSet returned from the getTables() method contains a list of table names matching the 4 given parameters (which were all null). This ResultSet contains 10 columns, which each contain information about the given table. The column with index 3 contains the table name itself. Check the JavaDoc for more details about the rest of the columns.Listing Columns in Table
You can obtain the columns of a table via the
DatabaseMetaData too. Here is how:String catalog = null;
String schemaPattern = null;
String tableNamePattern = "my_table";
String columnNamePattern = null;
ResultSet result = databaseMetaData.getColumns(
catalog, schemaPattern, tableNamePattern, columnNamePattern);
while(result.next()){
String columnName = result.getString(4);
int columnType = result.getInt(5);
}
First you call the
getColumns() method, passing 4 parameters. Of these, only thetableNamePattern is set to a non-null value. Set it to the name of the table you want to obtain the columns of.
The
ResultSet returned by the getColumns() method contains a list of columns for the given table. The column with index 4 contains the column name, and the column with index 5 contains the column type. The column type is an integer matching one of the type constants found injava.sql.Types
To get more details about obtaining column information for tables, check out the JavaDoc.
Primary Key for Table
It is also possible to obtain the primary key of a table. You do so, like this:
String catalog = null;
String schema = null;
String tableName = "my_table";
ResultSet result = databaseMetaData.getPrimaryKeys(
catalog, schema, tableName);
while(result.next()){
String columnName = result.getString(4);
}
First you call the
getPrimaryKeys() method, passing 3 parameters to it. Only the tableName is non-null in this example.
The
ResultSet returned by the getPrimaryKeys() method contains a list of columns which make up the primary key of the given table. The column with index 4 contains the column name.
A primary key may consist of multiple columns. Such a key is called a compound key. If your tables contains compound keys, the
ResultSet will contain multiple rows. One row for each column in the compound key.Supported Features
The
DatabaseMetaData object also contains information about the features the JDBC driver and the database supports. Many of these features are represented by a method you can call, which will return true or false depending on whether the given feature is supported or not.
I will not cover all the feature support related methods here. I will just give you a few examples. Consult the JavaDoc for a full list of feature support methods, and their meaning.
databaseMetaData.supportsGetGeneratedKeys(); databaseMetaData.supportsGroupBy(); databaseMetaData.supportsOuterJoins();
