在用JDBC调用存储过程来实现分页的时候，因为要统计分页数据的总数，在存储过程中想到了使用一个输出参数来完成这样的功能，于是就用JDBC调用带输出参数的存储过程来实现这一功能。刚开始还出了点问题，如下：
callableStatement.setString(1, "w"); callableStatement.registerOutParameter(2, java.sql.Types.INTEGER); ResultSet rs = callableStatement.executeQuery(); int out = callableStatement.getInt(2); while (rs.next()) { System.out.println(rs.getObject("PERSON_NAME")); }
假如先调用 int out = callableStatement.getInt(2);的话，结果集就会被关闭，并抛出
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: 结果集已关闭。 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(Unknown Source) at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859) at xx.qq.app.AppTest.main(AppTest.java:24)
就出现了上面的异常现象。
解决方法是将其改为：
ResultSet rs = callableStatement.executeQuery(); while (rs.next()) { System.out.println(rs.getObject("PERSON_NAME")); } int out = callableStatement.getInt(2);
这样就OK了。
附上简单的存储过程及源码：
package xx.qq.app; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import org.springframework.beans.factory.BeanFactory; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.mchange.v2.c3p0.ComboPooledDataSource; /** * @author Jack Zhang * Email:fish2-2@163.com * @date 2011-08-22 */ public class AppTest { public static void main(String[] args) throws Exception { ApplicationContext context = new ClassPathXmlApplicationContext( new String[] { "applicationContext.xml" }); BeanFactory factory = (BeanFactory) context; ComboPooledDataSource dataSource = (ComboPooledDataSource) factory .getBean("dataSource"); Connection con = dataSource.getConnection(); CallableStatement callableStatement = con .prepareCall("{call GetBasics( , )}"); callableStatement.setString(1, "w"); callableStatement.registerOutParameter(2, java.sql.Types.INTEGER); ResultSet rs = callableStatement.executeQuery(); while (rs.next()) { System.out.println(rs.getObject("PERSON_NAME")); } int out = callableStatement.getInt(2); //int out = callableStatement.getInt(2); System.out.println(out); if (rs != null) rs.close(); if (callableStatement != null) callableStatement.close(); if (con != null) con.close(); } } /** * * QueryTemplate queryTemplate =(QueryTemplate)factory.getBean("queryTemplate"); // * queryTemplate.query(new Query(){ // public void executeQuery(Connection con, * Statement st, ResultSet rs) throws Exception { // String sql ="SELECT * FROM * P_BASIC"; // rs = st.executeQuery(sql); // while(rs.next()) // { // * System.out.println(rs.getObject(5)); // } // } // }); * */
存储过程
ALTER PROCEDURE GetBasics( @PERSON_NAME VARCHAR(32), @COUNT INT OUT ) AS BEGIN SELECT @COUNTCOUNT = COUNT(*) FROM P_BASIC; SELECT * FROM P_BASIC END GO
以上就是JDBC调用带输出参数的存储过程来完成分页并统计分页总数的全部过程，本文就介绍到这里了，希望本次的介绍能够对您有所收获！