Java Parameter number 2 is not a

人生没有彩排，每天都是现场直播，开弓没有回头箭，努力在当下。

创建存储过程，通过JDBC连接后调用，一直报错Parameter number 2 is not an OUT parameter。仔细核对存储过程名字，发现方法名、参数没有错误。

QQ20191103-192140@2x.png

Connecting to database...
Sun Nov 03 18:35:21 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Creating JDBCCallableStatement1...
try finish
java.sql.SQLException: Parameter number 2 is not an OUT parameter
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
    at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:610)
    at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1795)
    at JDBCCallableStatement1.main(JDBCCallableStatement1.java:23)

代码如下：

import java.sql.*;

public class JDBCCallableStatement1 {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB";
    static final String USER = "root";
    static final String PASS = "123";
    public static void main(String[] args)
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating JDBCCallableStatement1...");
            String sql;
            sql = "call pro_findById0(?,?)";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1,4);
            cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
            cstmt.executeQuery();
            String result = cstmt.getString(2);
            System.out.println(result);
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (cstmt!=null)
                {
                    cstmt.close();
                    cstmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}

方法1:在数据库连接字符串后面加上：noAccessToProcedureBodies=true这样就可以不在授予mysql.proc的SELECT权限了。

import java.sql.*;

public class JDBCCallableStatement1 {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB?noAccessToProcedureBodies=true";
    static final String USER = "root";
    static final String PASS = "123";
    public static void main(String[] args)
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating JDBCCallableStatement1...");
            String sql;
            sql = "call pro_findById0(?,?)";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1,4);
            cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
            cstmt.executeQuery();
            String result = cstmt.getString(2);
            System.out.println(result);
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (cstmt!=null)
                {
                    cstmt.close();
                    cstmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}

输出
Connecting to database...
Creating JDBCCallableStatement1...
Nick
try finish

方法2,权限不够，但是使用的是root用户名，查看权限都有。

WX20191104-210228@2x.png

后查看mysql的版本为8.0.12,于是降为5.7.10下载版本。

WX20191104-210439@2x.png
运行不再报错。

Connecting to database...
Creating JDBCCallableStatement1...
Nick
try finish

加权限的情况在8.0.12中没有复现出来----------------------------------------

grant execute on procedure firstDB.pro_findById to root@'localhost' identified by '123';

WX20191104-211518@2x.png

在mysql8.0.18版本中授权失败,还是报错。

0EA13CB7-2C23-43AE-AE6B-291EC74BC985.png

Connecting to database...
Creating JDBCCallableStatement1...
java.sql.SQLException: Parameter number 2 is not an OUT parameter
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:610)
at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1795)
at JDBCCallableStatement1.main(JDBCCallableStatement1.java:23)
try finish