• Home
  • About Us
  • Privacy Policy
  • Contact Us

BenchResources.Net

Java tutorial for beginners

  • Home
  • Java
    • Java Basics
    • OOPS
    • String Handling
    • Exception Handling
    • Generics
    • Serialization
    • Collection
    • Concurrent Collection
    • JDBC
    • JAXB
    • Java 8
  • Spring
    • Spring Core
    • Spring AOP
    • Spring MVC
    • Spring JDBC
    • Spring ORM
    • Spring Security
  • Web Services
    • Apache CXF – REST
    • JBoss RestEasy
    • Jersey 2.x
    • Spring Restful
    • Apache Axis2 RI
    • Apache CXF – SOAP
    • Metro JAX-WS – SOAP
  • Tools
    • Apache Maven
    • Eclipse + Maven
    • FileZilla FTP
    • Eclipse IDE
  • Oracle SOA
    • Oracle SOA 12c
    • Interview Question on SOA
  • Cloud
    • AWS
    • Microsoft Azure
  • Android
  • Interview Q
  • Jobs

Java JDBC: Metadata of database using DatabaseMetaData Interface

June 28, 2016 SJ JDBC 0

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
    1. Driver name
    2. Driver version
    3. User name
    4. Product name
    5. Product version
    6. Table count
    7. 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:

  • JDBC Database Metadata
  • mysql-connector-java-5.1.38.jar

 

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 !!

Java JDBC: Metadata of ResultSet using ResultSetMetaData Interface
Java JDBC: Order By clause using JDBC Statement Interface

Related posts:

  1. Java JDBC: Metadata of ResultSet using ResultSetMetaData Interface
  2. Interview Questions and Answers on Java JDBC
  3. Java JDBC: Getting all lists of record using JDBC Statement Interface
  4. Java JDBC: Like clause condition using JDBC Statement Interface
  • database
  • database table
  • Java
  • JDBC
  • JDBC Connection steps
  • metadata
  • mysql
  • select query

Search tutorials




Subscribe via Email

Join 197 other subscribers

Popular Articles

  • JDBC: An example to connect MS Access database in Java 8
  • Java JDBC: An example to connect MS Access database
  • Spring JDBC: An example on JdbcTemplate using Annotation
  • Oracle OSB 12c: Service Callout and Routing Table example
  • Returning value from method having try-catch-finally blocks
Proudly powered by Tuto WordPress theme from MH Themes