How to Get the Datatype of a Column of a Table using JDBC?

Java supports many databases and for each database, we need to have their respective jar files to be placed in the build path to proceed for JDBC connectivity.

• MySQL: mysql-connector-java-8.0.22 or similar mysql connectors with different versions. In this article, we are using mysql-connector-java-8.0.22
• SQL Server: sqljdbc4.jar
• Oracle: ojdbc14.jar
• MongoDB (NoSQL database): mongo-java-driver-3.12.7

First, need to decide, which database we are using and accordingly, we need to add the jars. For another database like Progress, Cassandra, etc also we have jars and need to include them in the build path. For various data processing-related matters from RDBMS(MySQL, SQL Server, etc.) we have a java.sql package. To get the ResultSetMetaData object, we need to follow the below steps.

1. Register the driver

For database wise it differs. As we are using MySQL, we can do via

Class.forName("com.mysql.cj.jdbc.Driver");
or
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver");

2. Get the connection of MySQL way

• test is a database name used in the code.
• serverTimezone=UTC, If not provided we will have java.sql.SQLException. The server time zone value xxxx is unrecognized or represents more than one time zone.
• We need to configure either the server or JDBC driver via the serverTimezone configuration property) to use a more specific time zone value if we want to utilize time zone support.
• So either in MySQL configuration we can set or like below we can provide Credentials here are root/”” i.e. username is root and password is “”.

Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test?serverTimezone=UTC”, “root”, “”);

3. Create a statement object

Statement st = connection.createStatement();

4. Execute the query

String query = "Select * from doctorsdetails";
// Executing the query
ResultSet resultSet = statement.executeQuery(query);

5. Get the ResultSetMetaData object: 

Retrieve the ResultSetMetadata object of the current ResultSet by invoking the getMetaData() method. Below methods are there in ResultSetMetadata object and they are printed in the program

• int getColumnCount(): Returns the number of columns of a table.
• String getColumnName(int columnNumber): Returns the name of the column whose index number is passed as a parameter.
• int getColumnType(int columnNumber): Returns the name of the column as an integer value whose index number is passed as parameter.

For example, 3 indicates DECIMAL, and 12 indicates VARCHAR, and 4 indicates INT.  

• 12 is represented as java.sql.Types.VARCHAR
• 4 is represented as java.sql.Types.INTEGER

Following is the list of values returned by various datatypes of java.sql.Type − i.e. getColumnType() gives these results.

• Array: 2003
• Big int: -5
• Binary: -2
• Bit: -7
• Blob: 2004
• Boolean: 16
• Char: 1
• Clob: 2005
• Date: 91
• Datalink:70
• Decimal: 3
• Distinct: 2001
• Double: 8
• Float: 6
• Integer: 4
• JavaObject: 2000
• Long var char: -16
• Nchar: -15
• NClob: 2011
• Varchar: 12
• VarBinary: -3
• Tiny int: -6
• Time stamp with time zone: 2014
• Timestamp: 93
• Time: 92
• Struct: 2002
• SqlXml: 2009
• Smallint: 5
• Rowid: -8
• Refcursor: 2012
• Ref: 2006
• Real: 7
• Nvarchar: -9
• Numeric: 2
• Null: 0
• Smallint: 5

Let us check that by creating a table in MySQL and find the datatype by using ResultSetMetaData

Let a table named doctorsdetails available in MySQL under test database,

CREATE TABLE `doctorsdetails` (
 `id` int(6) unsigned NOT NULL,
 `Name` varchar(50) DEFAULT NULL,
 `AvailableDays` varchar(200) DEFAULT NULL,
 `consultation_fees` int(11) DEFAULT NULL,
 `qualification` varchar(20) DEFAULT NULL,
 `experience` int(11) DEFAULT NULL,
 `gender` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

As per the above table, getColumnType() for the columns of the above table are as follows

In the code, let us use the steps, and they are given as comments

On execution of the program, we will get the output as below. i.e. similar to the table value that is shown above