In this article, we will use DatabaseMetaData interface to extract database information like driver name and its version, product name and its version, number of tables and views inside that database
1. Pre-requisite:
- Java JDK 1.8.0_77
- MySQL database 5.5.16
- Eclipse Luna IDE 4.4.0
- mysql-connector-java-5.1.38.jar file
2. Database parameters:
Let us move on and code an example to connect MySQL database from Java application to extract database information using JDBC API. But before that, we will list down required things to connect database
- database server IP or address (localhost)
- sever port (3306)
- database name (PLAYER_INFO)
- username (root)
- password (root@123)
Note: All bold are database values to connect MySQL database
3. Metadata of database using JDBC DatabaseMetaData Interface
- As we are ready with required things to connect MySQL database from Java application
- We can retrieve below listed database information
- Driver name
- Driver version
- User name
- Product name
- Product version
- Table count
- View count
- Let us code a simple example using Statement Interface
DatabaseInfoUsingJDBCDatabaseMetaData.java
package in.bench.resources.mysql.db.metadata; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class DatabaseInfoUsingJDBCDatabaseMetaData { public static void main(String[] args) { // variables Connection connection = null; ResultSet resultSet = null; // Step 1: Loading or registering MySQL JDBC driver class try { Class.forName("com.mysql.jdbc.Driver"); } catch(ClassNotFoundException cnfex) { System.out.println("Problem in loading MySQL JDBC driver"); cnfex.printStackTrace(); } // Step 2: Opening database connection try { // Step 2.A: Create and get connection using DriverManager connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/PLAYER_INFO", "root", "root@123"); // get database metadata through connection DatabaseMetaData databaseMetaData = connection.getMetaData(); System.out.println("=== Database Metdata information ===\n"); // database information System.out.println("Database Product Name: \t\t" + databaseMetaData.getDatabaseProductName()); System.out.println("Database Product Version: \t" + databaseMetaData.getDatabaseProductVersion()); System.out.println("Database Major Version: \t" + databaseMetaData.getDatabaseMajorVersion()); System.out.println("Database Minor Version: \t" + databaseMetaData.getDatabaseMinorVersion()); System.out.println(); // driver information System.out.println("Driver Name: \t\t\t" + databaseMetaData.getDriverName()); System.out.println("Driver Version: \t\t" + databaseMetaData.getDriverVersion()); System.out.println("Driver Major Version: \t\t" + databaseMetaData.getDriverMajorVersion()); System.out.println("Driver Minor Version: \t\t" + databaseMetaData.getDriverMinorVersion()); // retrieving table information String table[] = {"TABLE"}; resultSet = databaseMetaData .getTables(null, null, null, table); while(resultSet.next()){ System.out.println("Table Information: \t\t" + resultSet.getString(3)); } // retrieving view information String views[] = {"VIEW"}; resultSet = databaseMetaData .getTables(null, null, null, views); while(resultSet.next()){ System.out.println("View Information: \t\t" + resultSet.getString(3)); } System.out.println("\n== End of MetaData information =="); } catch(SQLException sqlex){ sqlex.printStackTrace(); } finally { // Step 3: Closing database connection try { if(null != connection) { // and then finally close connection connection.close(); } } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } }
Output:
=== Database Metdata information === Database Product Name: MySQL Database Product Version: 5.5.16 Database Major Version: 5 Database Minor Version: 5 Driver Name: MySQL Connector Java Driver Version: mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) Driver Major Version: 5 Driver Minor Version: 1 Table Information: player == End of MetaData information ==
4. Download:
Read Also:
- JDBC Tutorial index
- Introduction to JDBC
- Different types of JDBC drivers
- JDBC Driver list for all leading databases
- Basic components of JDBC
- JDBC connection steps
- MySQL database connection steps
- Oracle database connection steps
References:
- https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
- https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html
- http://www.benchresources.net/spring-jdbc-introduction-and-jdbc-example-without-spring/
- https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
- https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
- http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
- https://en.wikipedia.org/wiki/JDBC_driver
- http://www.devx.com/tips/Tip/28818
- https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)
Happy Coding !!
Happy Learning !!