How can I get different datatypes from ResultSetMetaData in Java?

Question

I have a ResultSet that returns data of different types. The query is constructed dynamically so, at compile time, I don't know what type of value the query will return.

I have written the following code assuming that all results are Strings. But I want to get the type of each value too. How can I do this?

Below is the code I have written.

while (reportTable_rst.next()) {
    String column = reportTable_rst.getString(columnIterator);
}

At this point, I would like to get the column type, and get the value according to the data type.

Dorladorlisa

Hello all. This is an info for future visitors. If your requirement is to get the field value irrespective of data type of that column, you can use getObject method of resultSet and setObject method of PreparedStatement while inserting. Here is an eg. while ( rsdata.next() ) { for ( int i = 0; i < col_size; i++) { columnValue = rsdata.getObject(i+1); pstmtInsert = conRenameInfo.prepareStatement("INSERT INTO " + tableName + " (" + columnName + ") VALUES (?) "); pstmtInsert.setObject(1, columnValue); pstmtInsert.executeUpdate(); } } – Graticule

Answer 1

The ResultSetMetaData.getColumnType(int column) returns a int value specifying the column type found in java.sql.Types.

Example:

Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
PreparedStatement statement = connection.prepareStatement(JDBC_SELECT);
ResultSet rs = statement.executeQuery();
PrintStream out = System.out;

if (rs != null) {
    while (rs.next()) {
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            if (i > 1) {
                out.print(",");
            }

            int type = rsmd.getColumnType(i);
            if (type == Types.VARCHAR || type == Types.CHAR) {
                out.print(rs.getString(i));
            } else {
                out.print(rs.getLong(i));
            }
        }
            
        out.println();
    }
}

Infatuate

Good answer. Maybe nitpicking, but one question: why retrieving ResultSetMetaData object inside a while loop? I mean it is still the same, column names and their types are also the same. You could just retrieve the ResultSetMetaData object once, before the while loop. – Dickman

Without iteration of ResultSet, you will encounter an exception like so: java.sql.SQLException: Cursor position not valid.. Iteration move the cursor in a direction specified or FORWARD by default. – Infatuate

Of course I know you must use rs.next() to iterate/move to the next row. I was saying about ResultSetMetaData object, which is the same and does not need to be iterated. So you could easily retrieve the ResultSetMetaData object only once, before entering the while loop, and it won't give you any exception. – Dickman

My previous response was done with your case in mind and the exception I posted was what the code had thrown. Yes, you can retrieve ResultSetMetaData once, but only through iteration and logic to see if you never had a ResultSetMetaData before. – Infatuate

Sorry, didn't quite catch your idea. I have tested the situation when I get the ResultSetMetaData object before entering the whileloop and then successfully used it for retrieving meta data inside the while (rs.next())` loop. No exception was thrown. The only case when ResultSet#getMetaData() throws an SQLException is (quote from the API docs): if a database access error occurs or this method is called on a closed result set. – Dickman

And ResultSet#next() method throws an SQLException exactly because of the same reasons like getMetaData() (see the API docs). So if that's the case, in your example an exception will be thrown when calling rs.next() anyways, i.e. before calling getMetaData(). Sorry mate, don't mean to compromise your answer (which is correct), just this moment draw my attention ) – Dickman

You are right. I just do it to prevent non-JDBC complaint libraries. – Infatuate

@BuhakeSindi which libraries are you talking about? – Wilks

@Wilks Oracle had different ways of mapping datatypes accordingly. I don't know about now since my answer was in 2013 – Infatuate

@BuhakeSindi Thank you for the clarification. I have used Oracle SQL for years, I can confirm that retrieving result set metadata before calling next() is possible and works correctly at least since ojdbc9. In the worst case, I advise you to call it only once after the first call to next() instead of calling it once per row if you fear falling on the bug you mentioned. – Wilks

Answer 2

You can call,

To returns designated column's SQL type.

int ResultSetMetaData.getColumnType(int column)

To return designated column's database-specific type name.

String ResultSetMetaData.getColumnTypeName(int column)

Faunie

Answer 3

ResultSet rs;
int column;
.....
ResultSetMetaData metadata = rs.getMetaData();
metadata.getColumnTypeName(column); // database specific type name
metadata.getColumnType(column);  // returns the SQL type

Rafat

Answer 4

I think the above answer is not going in loop and have some lack in details. This code snippet can improve to just show Column Name and corresponding datatype. Here is the fully working code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class Test {

    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String HOST = "192.168.56.101";
    private static final String PORT = "3316";
    private static final String CONNECTION_URL = "jdbc:mysql://"+HOST+":"+PORT+"/";
    private static final String USERNAME = "user";
    private static final String PASSWORD = "pwd";
    private static final String DATABASE = "db";
    private static final String TABLE = "table";
    private static final String QUERY = "select * from "+DATABASE+"."+TABLE+" where 1=0";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection con = DriverManager.getConnection (CONNECTION_URL , USERNAME, PASSWORD);
        ResultSet rs = con.createStatement().executeQuery(QUERY);
        if (rs != null) {
            System.out.println("Column Type\t\t Column Name");

                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.println(rsmd.getColumnTypeName(i)+"\t\t\t"+rsmd.getColumnName(i));
            }
        }   
    }
}

Athwartships

According to the API for Statement.executeQuery(), "a ResultSet object that contains the data produced by the given query; never null". So you don't need the null check for the returned ResultSet, rs. – Archiearchiepiscopacy