Bug #101823 MySQL JDBC driver incorrectly reports bigint as BigInteger in generated keys
Submitted: 1 Dec 2020 17:47 Modified: 2 Dec 2020 6:05
Reporter: Puf Puf Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.22, 8.0.12 OS:Any
Assigned to: CPU Architecture:Any

[1 Dec 2020 17:47] Puf Puf
Description:
From Stack Overflow:
https://stackoverflow.com/questions/65078455/mysql-jdbc-driver-incorrectly-reports-bigint-...

MySQL (like most SQL databases) does not call its 64-bit integer datatype a "long". It's a "bigint". Ok, fine.

But the MySQL JDBC driver (8.0.22) incorrectly returns this column as java.math.BigInteger, which it isn't.

Yes, I could convert the BigInteger into a long in my application code, but this turns out to be really inconvenient for some internal, application-specific reasons.

Is there any way to force the driver to return the correct data type, which should be a java.lang.Long?

Update

Per the comment, yes, it appears that regular select statements do return the column as a java.lang.Long. My bad.

Where it is failing is when it returns generated keys after an insert. There, the column comes back as a BigInteger. 

How to repeat:
Here's the code:

public static void main(String[] args) throws SQLException {

    Connection conn = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&allowPublicKeyRetrieval=true&user=root");

    Statement state = conn.createStatement();
    state.executeUpdate("drop table if exists foo");
    state.executeUpdate("create table foo (id bigint auto_increment, name varchar(255), primary key(id))");
    state.executeUpdate("insert into foo (name) values ('bob')", Statement.RETURN_GENERATED_KEYS);

    ResultSet rs = state.getGeneratedKeys();
    rs.next();

    Object obj = rs.getObject(1);

    System.out.println(obj);
    System.out.println(obj.getClass());

    conn.close();

}

returns:

1
class java.math.BigInteger
[2 Dec 2020 6:05] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh