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 |
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(); |