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.
Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more, please refer Complete Interview Preparation Course.
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
- 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
ColumnName ColumnType Value id int 4 Name varchar 12 AvailableDays varchar 12 consultation_fees int 4 qualification varchar 12 experience int 4 gender varchar 12
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