迁移至MySQL Connector/J 8.x时CallableStatement报OUT参数异常

将代码从MySQL Connector/J 5.1.49迁移至8.1.0（所有8.*版本均出现此问题）时，原本正常运行的CallableStatement代码抛出如下异常：

SQLException: Parameter number 2 is not an OUT parameter

对应的存储过程 #

CREATE PROCEDURE get_system_setting(IN p_setting VARCHAR(200), OUT message_out VARCHAR(200))
BEGIN
  SELECT setting, value, message_out FROM SYSTEM_SETTING WHERE setting = p_setting;
END;

示例Java代码 #

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class CallableTest {

  /* MySQL Connector/J 8.1.0 (所有8.*版本均失败) */
  private static final String DB_DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";

  /* MySQL Connector/J 5.1.49 - 注释上一行，取消注释下一行以使用v5 */
  //private static final String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";

  private static final String DB_URL = "jdbc:mysql://localhost:3306/DB_NAME?useSSL=false";
  private static final String DB_USERNAME = "user";
  private static final String DB_PASSWORD = "password";

  public static Connection getConnection() {
    Connection conn = null;
    try {
      Class.forName(DB_DRIVER_CLASS);
      conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
     e.printStackTrace();
    }
    return conn;
  }

  public static void main(String[] args) {
    Connection conn = null;
    CallableStatement cs = null;

    try {
      conn = CallableTest.getConnection();
      cs = conn.prepareCall("{call DB_NAME.get_system_setting(?, ?)}");
      cs.setString(1, "CONNECT_TIMEOUT");
      cs.registerOutParameter(2, Types.VARCHAR); // <-- 此处报错

      cs.execute();
      String message = cs.getString(2);
      if(message != null) {
        System.out.printf("out parameter: %s\n",  message);
      } else {
        ResultSet rs = cs.getResultSet();
        boolean rowsReturned = ((rs != null) && rs.next());
        if (rowsReturned) {
          String setting = rs.getString("setting");
          String value = rs.getString("value");
          System.out.printf("setting: %s, value: %s\n", setting, value);
        }
     }
   } catch(Exception e) {
     e.printStackTrace();
   } finally {
     try {
       cs.close();
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
  }
}

8.x版本执行报错信息 #

java -cp .:mysql-connector-j-8.1.0.jar CallableTest

java.sql.SQLException: Parameter number 2 is not an OUT parameter
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
    at com.mysql.cj.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:645)
    at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1717)
    at com.mysql.cj.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1725)
    at CallableTest.main(CallableTest.java:49)

5.1.49版本正常运行结果 #

java -cp .:mysql-connector-java-5.1.49.jar CallableTest

setting: CONNECT_TIMEOUT, value: 10000

已尝试使用命名参数和索引参数，查阅Connector/J文档未找到CallableStatement相关版本变更说明。

环境信息 #

• Connector/J: 8.1.0
• MySQL: 8.0.33
• Java: 1.8.0_331

问题原因及解决方案 #

问题根源是存储过程的实现：SELECT语句直接引用了未赋值的OUT参数message_out，MySQL Connector/J 8.x对存储过程参数的校验逻辑更严格，判定该OUT参数未被正确使用（无赋值操作），因此拒绝注册它为输出参数。

修复方案1：先给OUT参数赋值再查询

修改存储过程，先为message_out赋值（可根据业务逻辑设置具体值），再执行查询：

CREATE PROCEDURE get_system_setting(IN p_setting VARCHAR(200), OUT message_out VARCHAR(200))
BEGIN
  -- 先为OUT参数赋值，示例设为空字符串
  SET message_out = '';
  SELECT setting, value, message_out FROM SYSTEM_SETTING WHERE setting = p_setting;
END;

修复方案2：移除SELECT语句中的OUT参数引用

如果无需在结果集中返回message_out，直接去掉SELECT里的该参数，并根据业务逻辑为其赋值：

CREATE PROCEDURE get_system_setting(IN p_setting VARCHAR(200), OUT message_out VARCHAR(200))
BEGIN
  SELECT setting, value FROM SYSTEM_SETTING WHERE setting = p_setting;
  -- 根据查询结果给OUT参数赋值
  IF ROW_COUNT() = 0 THEN
    SET message_out = '未找到对应配置项';
  ELSE
    SET message_out = '查询成功';
  END IF;
END;

修复方案3：临时兼容旧版校验逻辑（不推荐）

若暂时无法修改存储过程，可在JDBC URL中添加useLegacyDatetimeCode=true，强制Connector/J使用旧版参数校验逻辑，但这只是临时 workaround，可能引发其他兼容性问题：

private static final String DB_URL = "jdbc:mysql://localhost:3306/DB_NAME?useSSL=false&useLegacyDatetimeCode=true";

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