Using java.sql.DatabaseMetaData interface you can get metadata about the DB you are connected to.
DatabaseMetaData interface in Java has methods to get information about-
- Database like DB Product name and version, scehemas available in DB.
- JDBC Driver like the driver’s name and version
- Tables in any DB schema,
- Views in the Schema
- Stored procedures and functions
How to get DatabaseMetaData Object
You obtain the DatabaseMetaData instance by calling the getMetaData() method of the Connection class.
DatabaseMetaData databaseMetaData = connection.getMetaData();
DB Product, version and user information using DatabaseMetaData
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MetaDataInfo {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// DBMetaData instance
DatabaseMetaData dbMetaData = connection.getMetaData();
// Database information
System.out.println("DB Name - " + dbMetaData.getDatabaseProductName());
System.out.println("DB Version - " + dbMetaData.getDatabaseProductVersion());
System.out.println("DB Major Version - " + dbMetaData.getDatabaseMajorVersion());
System.out.println("DB Minor Version - " + dbMetaData.getDatabaseMinorVersion());
System.out.println("DB User - " + dbMetaData.getUserName());
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
JDBC Driver Name, version information using DatabaseMetaData
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MetaDataInfo {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// DBMetaData instance
DatabaseMetaData dbMetaData = connection.getMetaData();
// Driver information
System.out.println("Driver Name - " + dbMetaData.getDriverName());
System.out.println("Driver Version - " + dbMetaData.getDriverVersion());
System.out.println("Driver Major Version - " + dbMetaData.getDriverMajorVersion());
System.out.println("Driver Minor Version - " + dbMetaData.getDriverMinorVersion());
System.out.println("JDBC Major Version - " + dbMetaData.getJDBCMajorVersion());
System.out.println("JDBC Minor Version - " + dbMetaData.getJDBCMinorVersion());
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
Listing tables in DB using DatabaseMetaData
You can get a list of tables in the DB using getTables() method.
getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
Parameters passed to this method are-
- catalog- A catalog name
- schemaPattern- A schema name pattern
- tableNamePattern- A table name pattern
- types- A list of table types. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
If you pass values for any of these parameters that helps in narrowing down the search and limit the number of tables returned. You can pass all of these parameters as null to return all the table types.
The getTables() method returns a ResultSet where each row contains a table description. This table description is made up of 10 columns. For getting the table names you will have to get the third column value from each row in the ResultSet.
- TABLE_CAT- Table catalog
- TABLE_SCHEM- Table schema
- TABLE_NAME- Table name
- TABLE_TYPE- Table type
- REMARKS- Explanatory comment on the table
- TYPE_CAT- The types catalog
- TYPE_SCHEM- The types schema
- TYPE_NAME- Type name
- SELF_REFERENCING_COL_NAME- Name of the designated "identifier" column of a typed table
- REF_GENERATION- Specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED".
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MetaDataInfo {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// DBMetaData instance
DatabaseMetaData dbMetaData = connection.getMetaData();
ResultSet rs = dbMetaData.getTables(null, null, null, null);
while (rs.next()){
// Third column for table name
System.out.println("Table name " + rs.getString(3));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
If you want to list only tables then you can limit the search by passing table types as table.
String table[] = {"table"};
ResultSet rs = dbMetaData.getTables(null, null, null, table);
If you want to list only views then you can limit the search by passing table types as view.
String table[] = {"view"};
ResultSet rs = dbMetaData.getTables(null, null, null, table);
Columns of a DB table using DatabaseMetaData
You can get the column names of a specific table by using the getColumns() method.
getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
Parameters passed to this method are-
- catalog- A catalog name
- schemaPattern- A schema name pattern
- tableNamePattern- A table name pattern
- columnNamePattern- A column name pattern
The getColumns() method returns a ResultSet where each row contains a column description. This column description is made up of 24 columns. For getting the column names you will have to get the fourth column value from each row in the ResultSet.
public class MetaDataInfo {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// DBMetaData instance
DatabaseMetaData dbMetaData = connection.getMetaData();
ResultSet rs = dbMetaData.getColumns(null, null, "Employee", null);
while (rs.next()){
System.out.println("Column name-" + rs.getString(4));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
That's all for the topic JDBC DatabaseMetaData Interface. If something is missing or you have something to share about the topic please write a comment.
You may also like
No comments:
Post a Comment