学步园

 前段时间项目中遇到存储过程分页的问题，因为分页的时候要统计分页数据的总数，在存储过程中想到了使用一个输出参数，但刚开是出现了点小问题

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 @COUNT = COUNT(*) FROM P_BASIC;
	SELECT * FROM P_BASIC
END
GO