How can I get different datatypes from ResultSetMe

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.

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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