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.

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(); } }` — learner, May 13 '16 at 08:53

Buhake Sindi · Accepted Answer · 2022-07-26T00:17:21.653

38

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

edited Jul 26 '22 at 00:17

answered Sep 11 '12 at 10:45

Buhake Sindi

87,898
29
167
228

1

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. – informatik01 Aug 06 '13 at 21:31
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. – Buhake Sindi Aug 07 '13 at 15:26
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. – informatik01 Aug 07 '13 at 17:57
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. – Buhake Sindi Aug 07 '13 at 19:56
3

Sorry, didn't quite catch your idea. I have tested the situation when I get the *ResultSetMetaData* object before entering the while`loop 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()](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#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`. – informatik01 Aug 07 '13 at 23:41
And [ResultSet#next()](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#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 ) – informatik01 Aug 07 '13 at 23:52
You are right. I just do it to prevent non-JDBC complaint libraries. – Buhake Sindi Aug 09 '13 at 18:26
@BuhakeSindi which libraries are you talking about? – gouessej Apr 25 '22 at 22:11
@gouessej Oracle had different ways of mapping datatypes accordingly. I don't know about now since my answer was in 2013 – Buhake Sindi Jul 26 '22 at 00:18
@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. – gouessej Jul 27 '22 at 10:26

score 3 · Answer 2 · answered Sep 11 '12 at 10:40

3

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)

answered Sep 11 '12 at 10:40

KV Prajapati

93,659
19
148
186

score 3 · Answer 3 · answered Sep 11 '12 at 10:43

3

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

answered Sep 11 '12 at 10:43

Jignesh

471
6
17

score 1 · Answer 4 · answered Jun 26 '14 at 15:41

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

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. — Robert Newton, Aug 23 '18 at 23:40

How can I get different datatypes from ResultSetMetaData in Java?

4 Answers4

Linked