15.1 Overview of JDBC ResultSet Meta Data
Meta Data is data about data which means information about data. Now we know that fetching data from a database using Statement or PreparedStatement returns a ResultSet Object. ResultSet Object can give us the useful information about data like table name, column name, column properties, label etc.
In this chapter we will discuss how to get the meta data from ResultSet object.
Following are the useful methods of ResultSetMetaData object.
- int getColumnCount() – This method returns the number of columns that the ResultSet contains.
- boolean isAutoIncrement(int column) – This method is used to determine if the column is defined as auto increment or not.
- int getColumnDisplaySize(int column)- This method is used to determine the size of column.
- String getColumnLabel(int column) – This method is used to determine the alias name of column.
- String getColumnName(int column)- This method is used to determine the name of the column.
- String getColumnTypeName(int column)- This method is used to determine the data type of column.
- int isNullable(int column)- This method is used to determine if the columns is nullable or not. This method returns 0 if the column is not null and 1 if column is nullable.
There are other methods available but these are the most commonly used.
15.3 Examples
We will use the MySQL database .
a. Use the below sql statement to create a schema
Create schema JDBCTutorial;
b. Create table User with 4 columns using below SQL
CREATE TABLE `User` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL , `age` VARCHAR(45) NULL , `email` VARCHAR(45) NULL , PRIMARY KEY (`id`) );
15.3.1 – Write an Example to determine the meta data of the User table
Solution –
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; public class ResultSetMetaDataExample { public static void main(String args[]) { try { Connection conn= getConnection(); String sql = "select * from User"; PreparedStatement stmt = conn.prepareStatement(sql); ResultSet rs = stmt.executeQuery(); ResultSetMetaData metaData= rs.getMetaData(); int numberOfColumns= metaData.getColumnCount(); System.out.println("Total Number of Columns are -" + numberOfColumns); System.out.println(); System.out.println(); for(int i=1;i<=numberOfColumns;i++){ String columnName= metaData.getColumnName(i); int colSize= metaData.getColumnDisplaySize(i); String dataType=metaData.getColumnTypeName(i); String tableName=metaData.getTableName(i); boolean isAutoInc=metaData.isAutoIncrement(i); int isNull=metaData.isNullable(i); System.out.println("Name of Column is = " + columnName); System.out.println("Size of Column is =" +colSize); System.out.println("Data Type of Column is =" +dataType); System.out.println("Table Name is="+tableName); System.out.println("Is the Column is Auto Incremented ?" + isAutoInc); System.out.println("Is column is Defined as Nullable ?"+isNull); System.out.println("- - - - - - - - - - - - - - - - - - - - - - - - "); } rs.close(); stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } } private static Connection getConnection() { Connection con = null; String dbDriver = "com.mysql.jdbc.Driver"; String dbUsername = "root"; String dbPassword="password"; String dbHostname="localhost"; String dbPort="3306"; String schema="JdbcTutorial"; try { String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema; Class.forName(dbDriver); con = DriverManager.getConnection(url, dbUsername, dbPassword); } catch (ClassNotFoundException ex1) { System.out.println("Failed to find driver class " + ex1.getMessage()); System.exit(1); } catch (SQLException ex2) { System.out.println("Connection failed " + ex2.getMessage()); System.exit(2); } return con; } }
On Running above program, we can see below output
Total Number of Columns are -4
Name of Column is = id
Size of Column is = 11
Data Type of Column is = INT
Table Name is=user
Is the Column is Auto Incremented ?true
Is column is Defined as Nullable ?0
- - - - - - - - - - - - - - - - - - - - - - - -
Name of Column is = name
Size of Column is =45
Data Type of Column is =VARCHAR
Table Name is=user
Is the Column is Auto Incremented ?false
Is column is Defined as Nullable ?1
- - - - - - - - - - - - - - - - - - - - - - - -
Name of Column is = age
Size of Column is =45
Data Type of Column is =VARCHAR
Table Name is=user
Is the Column is Auto Incremented ?false
Is column is Defined as Nullable ?1
- - - - - - - - - - - - - - - - - - - - - - - -
Name of Column is = email
Size of Column is =45
Data Type of Column is =VARCHAR
Table Name is=user
Is the Column is Auto Incremented ?false
Is column is Defined as Nullable ?1
- - - - - - - - - - - - - - - - - - - - - - - -