JDBC调用存储过程返回无结果集报错，Hibernate调用正常排查

问题场景 #

使用JDBC调用存储过程时触发SQLServerException，但相同逻辑用Hibernate调用则无异常。

JDBC调用代码 #

Connection con = DriverManager.getConnection("jdbc:sqlserver://myhost;databaseName=mydb;encrypt=false","user", "pass");
con.setAutoCommit(false);
CallableStatement cs = con.prepareCall("{call abc(?,?,?,?,?,?,?)}");
cs.setDate(1, java.sql.Date.valueOf(LocalDate.of(2023, 1, 10)));
cs.setDate(2, java.sql.Date.valueOf(LocalDate.of(2023, 1, 9)));
cs.setString(3, "PROD");
cs.registerOutParameter(4, Types.NUMERIC);
cs.registerOutParameter(5, Types.NUMERIC);
cs.registerOutParameter(6, Types.NUMERIC);
cs.registerOutParameter(7, Types.NUMERIC);
cs.executeQuery();

异常信息 #

2023-01-13T11:22:48.975-06:00 DEBUG 21888 --- [restartedMain]
c.m.s.jdbc.internals.SQLServerException : ***
SQLException:SQLServerCallableStatement:5
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not
return a result set. The statement did not return a result set.

Hibernate调用代码 #

StoredProcedureQuery query = em.createStoredProcedureQuery("abc")
        .registerStoredProcedureParameter(
                "date1",
                LocalDate.class,
                ParameterMode.IN
                )
        .registerStoredProcedureParameter(
                "date2",
                LocalDate.class,
                ParameterMode.IN
                )
        .registerStoredProcedureParameter(
                "name",
                String.class,
                ParameterMode.IN
                )
        .registerStoredProcedureParameter(
                "value1",
                BigDecimal.class,
                ParameterMode.OUT
                )
        .registerStoredProcedureParameter(
                "value2",
                BigDecimal.class,
                ParameterMode.OUT
                )
        .registerStoredProcedureParameter(
                "value3",
                BigDecimal.class,
                ParameterMode.OUT
                )
        .registerStoredProcedureParameter(
                "value4",
                BigDecimal.class,
                ParameterMode.OUT
                )
        .setParameter("date1", LocalDate.of(2023, 1, 10))
        .setParameter("date2", LocalDate.of(2023, 1, 9))
        .setParameter("name", "PROD");

query.execute();
log.info("value2 is {}", query.getOutputParameterValue("value2"));

驱动TRACE日志字节差异 #

JDBC发送的字节

03 01 02 26 00 5E 01 00 16 00 00 00 12 00 00 00   ...&.^..........
02 00 00 00 00 00 00 00 00 00 01 00 00 00 FF FF   ................
0A 00 00 00 00 00 E7 40 1F 09 04 D0 00 34 96 00   .......@.....4..
45 00 58 00 45 00 43 00 20 00 74 00 65 00 6D 00   E.X.E.C. .a.b.c.

Hibernate发送的字节

03 01 02 26 26 00 48 01 00 16 00 00 12 00 00 00   ...&.H..........
02 00 00 00 00 00 00 00 00 00 01 00 00 00 FF FF   ................
0A 00 00 00 00 00 E7 40 1F 09 04 D0 00 34 96 00   .......@.....4..
45 00 58 00 45 00 43 00 20 00 74 00 65 00 6D 00   E.X.E.C. .a.b.c.

问题分析与解决方案 #

核心问题 #

JDBC代码中调用了executeQuery()，这个方法仅适用于返回结果集的SQL语句，而你的存储过程只输出参数、不返回结果集，因此触发异常。

Hibernate的execute()方法会自动适配存储过程类型：对于无结果集、仅含输出参数的存储过程，它会选择合适的执行逻辑，不会强制要求返回结果集，所以能正常运行。

从TDS协议字节差异也能看出：

• JDBC发送的5E（对应字符^）代表SQLExecDirect指令，强制期望返回结果集
• Hibernate发送的48（对应字符H）代表SQLExecute指令，仅执行已准备的语句，不强制要求结果集

修改方案 #

将JDBC代码中的cs.executeQuery()替换为cs.execute()，之后通过getBigDecimal()等方法获取输出参数：

Connection con = DriverManager.getConnection("jdbc:sqlserver://myhost;databaseName=mydb;encrypt=false","user", "pass");
con.setAutoCommit(false);
CallableStatement cs = con.prepareCall("{call abc(?,?,?,?,?,?,?)}");
cs.setDate(1, java.sql.Date.valueOf(LocalDate.of(2023, 1, 10)));
cs.setDate(2, java.sql.Date.valueOf(LocalDate.of(2023, 1, 9)));
cs.setString(3, "PROD");
cs.registerOutParameter(4, Types.NUMERIC);
cs.registerOutParameter(5, Types.NUMERIC);
cs.registerOutParameter(6, Types.NUMERIC);
cs.registerOutParameter(7, Types.NUMERIC);

// 替换executeQuery()为execute()
cs.execute();

// 获取输出参数
BigDecimal value1 = cs.getBigDecimal(4);
BigDecimal value2 = cs.getBigDecimal(5);
BigDecimal value3 = cs.getBigDecimal(6);
BigDecimal value4 = cs.getBigDecimal(7);

// 后续业务处理...
con.commit();
con.close();

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