在开始本文的时候,我们先了解SQL SERVER中的一个命令
SET NOCOUNT ON;
执行该命令 表示不返回计数行,什么是计数行了,比如我们执行 DELETE ,UPDATE,INSERT的时候,对多少条数据进行了修改,计数行的值就是多少
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
在JDBC的操作数据库的过程中,你可以把Statement理解成为指向ResultSet的指针,如果数据库允许返回记数行的话,Statement将指向该计数行
比如
SET NOCOUNT ON; update TABLEA SET A='aa';--假设共100条数据被修改 SELECT * FROM TABLEA;
调用
callableStatement.execute();后callableStatement指向受影响的计数行,当你再调用
rs = callableStatement.getResultSet();
的时候,结果集rs 为空。 无法查询出表TABLEA 的数据
Statement提供了一个getMoreResults()的方法,该方法能将当前Statement "指针" 移动到下一个结果集.
如果 callableStatement.getUpdateCount()==-1&&getMoreResults()==true的话表明当前statement对象正指向一个真正的结果集
For Examle:
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=null; // 是否有结果集返回 boolean hasResultSet = callableStatement.execute(); // callableStatement--------->update System.out.println("执行存储过程后Statement是否指向真正的结果集:"+hasResultSet); System.out.println("受影响的行数:"+callableStatement.getUpdateCount()); callableStatement.getMoreResults();//------->select rs = callableStatement.getResultSet(); System.out.println("受影响的行:"+callableStatement.getUpdateCount()); while (rs.next()) { //System.out.println(rs.getObject(1)); } callableStatement.getMoreResults();//-------->update System.out.println("受影响的行:"+callableStatement.getUpdateCount()); callableStatement.getMoreResults();//-------->update System.out.println("受影响的行:"+callableStatement.getUpdateCount()); callableStatement.getMoreResults();//-------->select System.out.println("受影响的行:"+callableStatement.getUpdateCount()); rs = callableStatement.getResultSet();// 获取到真实的结果集 while (rs.next()) { //System.out.println(rs.getObject(1)); } callableStatement.getMoreResults();//--------->update System.out.println("受影响的行:"+callableStatement.getUpdateCount()); if (rs != null) rs.close(); if (callableStatement != null) callableStatement.close(); if (con != null) con.close(); } }
输出
执行存储过程后是否返回结果集:false 受影响的行数:262 受影响的行:-1 ,此处返回结果集 受影响的行:262 受影响的行:262 受影响的行:-1,此处返回结果集 受影响的行:262
存储过程
ALTER PROCEDURE GetBasics( @PERSON_NAME VARCHAR(32), @COUNT INT OUT ) AS BEGIN SET NOCOUNT ON; update TABLE_A SET NAME='aa'; SELECT @COUNT = COUNT(*) FROM TABLE_A; update TABLE_A SET NAME='aa'; SELECT * FROM TABLE_A; update TABLE_A SET NAME='aa'; update TABLE_A SET NAME='aa'; SELECT * FROM ORGS; update TABLE_A SET NAME='aa'; END GO