MySQL Return Predefined Column Value as Int

Question

I've got a mysql query that predefines a column in the query that is meant to be an integer type:

SELECT id, 1 AS item_id from table WHERE other_id = 1;

When I retrieve this statement in Java I get a ClassCastException:

java.lang.Float cannot be cast to java.lang.Integer

I've tried casting the value to an integer using the UNSIGNED type:

SELECT id, CAST(1 AS UNSIGNED) AS item_id from table WHERE other_id = 1;

But that gives me the same type of error:

java.math.BigInteger cannot be cast to java.lang.Integer

I'm not sure if this is something I need to fix in Java or in the actual sql statement, but i'd like to get the value back as an Integer.

The only place in Java I can imagine in causing the problem could be that I am bringing the value in as an object because i'm storing the record as a hashmap and don't know the exact column type. Unfortunately I can't change this.

ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while(resultSet.next()) {
    HashMap<String, Object> row = new HashMap<String, Object>();
    for(int i=1; i<=columnCount; i++) {
        row.put(metaData.getColumnName(i), resultSet.getObject(i));
    }
    list.add(row);
}

If you can get it as a Float (which seemed to be what you were getting), you can call intValue() to get an int. — khelwood
– khelwood, Commented May 28, 2016 at 16:04
I can make it work that way. But just for curiosity sake is there no way to define the column as an int or do I just have to take what Java gives me when its converting it to an object? — ryandlf
– ryandlf, Commented May 28, 2016 at 16:05
I've never had resultSet.getInt not work when an int is what I was expecting. I don't know what you're doing to get ambiguously typed values. — khelwood
– khelwood, Commented May 28, 2016 at 16:07
use something like this: int value = ((Number)rs.getObject("field")).intValue(). — Hannes
– Hannes, Commented May 28, 2016 at 16:10

Yassin Hajaj · Accepted Answer · 2016-05-28 15:54:18Z

2

Well, if you "think" that the column must contain integers only, you should UPDATE your column within the table.

If you want a quick fix of this problem, just cast the received result within Java.

int a = (int) rs.getObject("item_id");

answered May 28, 2016 at 15:54

Yassin Hajaj

22.1k10 gold badges60 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ryandlf Over a year ago

Weird part is I am casting to int in Java, but i'm getting the error posted above. The column is a virtual column created in the query so i'm not sure how to define that as an INT.

Yassin Hajaj Over a year ago

@ryandlf This is weird because I can not reproduce the problem here.

ryandlf Over a year ago

Take a look at the code I posted. I'm bringing the value in as an object and then casting to int later. Maybe that has something to do with it?

Collectives™ on Stack Overflow

MySQL Return Predefined Column Value as Int

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related