Get Column Count using ResultSet in SQL

This example counts the number of columns in a table. The ResultSetMetaData interface can be used to get type and properties information of columns in a ResultSet.

Get Column Count using ResultSet in SQL

Get Column Count using ResultSet in SQL

  

This example counts the number of columns in a table. The ResultSetMetaData interface can be used to get type and properties information of columns in a ResultSet. The ResultSetMetaData object can be obtained by invoking getMetaData() method on ResultSet object.

In this program you will see how JDBC connection is established and how to get required data, like the number of columns.

GetColumnCount.java

import java.sql.*;
public class GetColumnCount {
  public static void main(String[] args) throws Exception {
  String driver = "com.mysql.jdbc.Driver";
  String url = "jdbc:mysql://localhost:3306/";
  String username = "vineej";
  String password = "vineej";
  String dbName= "no";
  Class.forName(driver);
  Connection conn = DriverManager.getConnection(url+dbName, username, password);
  System.out.println("Connected");
  Statement st = conn.createStatement();
  ResultSet rs = st.executeQuery("SELECT * FROM Student");
ResultSetMetaData rsmd = rs.getMetaData();
   int NumOfCol=rsmd.getColumnCount();
System.out.println("Number of Columns="+NumOfCol);
  st.close();
  conn.close();
  }
} 

Java Database Connectivity Steps
1.Connection
An interface in java.sql package that provides connection with the database like- MySQL and java files. The SQL statements are executed within the context of the Connection interface.

2.Class.forName(String driver)
Class.forName method is static. This driver load the class and returns class instance and takes string type value and after that it will match the class with string.

3.DriverManager
It is a class of java.sql package that controls the JDBC drivers. Each driver has to be register with this class.

4.getConnection(String url, String userName, String password)
This method establishes a connection to specified database url. It takes three string types of arguments like:

url: - Database url where stored or created your database
userName: - User name of MySQL
password: -Password of MySQL

5.con.close()
This method is used for disconnecting the connection. It frees all the resources occupied by the database.

6.printStackTrace()
The method is used to show error messages. If the connection is not established then exception is thrown and print the message.

Description of code

1. First import the java packages

import java.sql.*;

The star '*' indicates that all of the classes in the java.sql and java.io packages are to be imported.

2. Loading a database driver
In this step, the driver class loaded by calling Class.forName() with the Driver class. Class is a class in java.lang package.

Class.forName("com.mysql.jdbc.Driver");

3. Creating a jdbc Connection
The objects defined by DriverManager class to establish the applications with the JDBC driver. This class manages the JDBC drivers which is installed on the system. getConnection() is the method by which it can connect. It uses the username, password, and a jdbc url to make the connection with database.

Connection conn = DriverManager.getConnection(url+dbName, username, password);

4. Creating a jdbc Statement object
When an connection is established then it can interact with the database. Connection interface defines methods for managing the database. It used to instantiate a Statement by using the createStatement() method.

Statement st = conn.createStatement();

5. Executing a statement with the Statement object, and returning a jdbc resultSet
This interface defines methods which is used to communicate with database. This class has three methods to execute the statements- executeQuery(), executeUpdate(), and execute(). To SELECT statement, executeQuery() method will be used. For create or modify tables, the method to use is executeUpdate.

ResultSet rs = st.executeQuery("SELECT * FROM Student");

6. Getting ResultSetMetaData object
ResultSet has method
getMetaData() which returns the ResultSet MetaData object which provides meta information of the result set.

ResultSetMetaData rsmd = rs.getMetaData();

7. Getting no of columns
getColumnCount() method on
ResultSetMetaData object returns the number of columns for the result set returned from the query.

int NumOfCol=rsmd.getColumnCount();

Download Source Code