Mac环境下JDBC连MySQL遇两类错误：认证插件加载失败与ClassCastException

Hey there! Let's work through these two JDBC problems you're facing now that you're back to Java development on a Mac. I've been in your shoes with new environment setup headaches, so let's break this down step by step.

First Issue Recap: caching_sha2_password Authentication Error #

You mentioned you already fixed this, but just to confirm you took the right approach (and for clarity), this error pops up because MySQL 8.0+ uses caching_sha2_password as the default authentication plugin—something older JDBC drivers don't support. The two reliable fixes are:

• Upgrade your MySQL JDBC driver: Make sure you're using mysql-connector-java version 8.0 or higher. In NetBeans, you can add this via the library manager, or through Maven/Gradle if you're using build tools.
• Switch your MySQL user to use mysql_native_password: If you prefer sticking with an older driver (not recommended long-term), run these commands in your MySQL shell:

  ALTER USER 'your_db_username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_db_password';
  FLUSH PRIVILEGES;
  

Second Issue: java.lang.ClassCastException: BigInteger cannot be cast to Long #

This is your current blocker, and it all boils down to mismatched data types between your MySQL table and Java code. Here's what's going on and how to fix it:

Why This Happens #

MySQL has numeric types that can exceed the range of Java's Long type. For example:

• A BIGINT UNSIGNED column maxes out at 18446744073709551615, which is way larger than Long's maximum value of 9223372036854775807. JDBC will return this as a BigInteger instead of a Long.
• Even a regular BIGINT might return BigInteger if the driver isn't configured to map it to Long, or if the stored value is too big for Long to handle.

Fixes to Try #

1. Check your database table structure
   Run DESCRIBE your_table_name; in MySQL to inspect the data type of the column causing the error. Look for UNSIGNED flags or high-precision types like DECIMAL that might be returning BigInteger.

2. Adjust your Java code to handle the type correctly

   • If you're certain the column values will never exceed Long's range, safely convert the BigInteger to Long:

     // Instead of (Long) rs.getObject("column_name")
     BigInteger bigIntValue = (BigInteger) rs.getObject("column_name");
     Long longValue = bigIntValue.longValue();
     

   • Or use the getLong() method directly (note: this will throw an exception if the value is too big, so only use this if you're 100% sure about the range):

     Long longValue = rs.getLong("column_name");
     

   • If values might be larger than Long can hold, just use BigInteger in your Java code:

     BigInteger value = (BigInteger) rs.getObject("column_name");
     

3. Verify your JDBC connection URL
   Ensure your URL doesn't have settings forcing incorrect type mappings. A standard MySQL 8.0 URL looks like this:

   String url = "jdbc:mysql://localhost:3306/your_db_name?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
   

Quick Debug Tip #

If you're unsure which column is causing the error, print your ResultSet metadata to see the JDBC type of each column:

ResultSetMetaData metaData = rs.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
    System.out.println("Column " + metaData.getColumnName(i) + " Type: " + metaData.getColumnTypeName(i));
}

This will tell you exactly which column is returning a BigInteger.

Don't stress too much—new environment setups are always full of little gotchas, especially when mixing different versions of databases and drivers. You're already halfway there by fixing the first authentication issue!

内容的提问来源于stack exchange，提问作者Freakz0r