JDBC取sqlserver存储过程多结果集的解决方法
因为sqlserver2k的官方驱动问题，一直以来用jdbc没有很好的办法取多结果集，公司系统里采用的是2种方式来搞，一种是增删查改用jdbc方式，对报表统计那块就采用odbc的方式，因为最近要改为连接池访问数据库，所以才有时间专门针对取多结果集的问题进行研究。原来在odbc中取结果集的方式是
Connection DBConn = null;Statement stmt = null;ResultSet RS = null;DataBase db = new DataBase();String temp = "";StringBuffer sb = new StringBuffer();DBConn = db.connect(Database);try {stmt = DBConn.createStatement();stmt.execute(SQL);RS = stmt.getResultSet();while (RS == null && stmt.getMoreResults()) {RS = stmt.getResultSet();}int c;sb.append("<?xml version=\"1.0\" encoding=\"" + this.encoding + "\"?>");sb.append("<root>");while (true) {if (RS != null) {while (RS.next()) {InputStream is = RS.getBinaryStream(1);InputStreamReader isr = new InputStreamReader(is,"UnicodeLittle");while ((c = isr.read()) != -1) {sb.append((char) c);}}}if (stmt.getMoreResults()) {RS.close();RS = stmt.getResultSet();} else {break;}}sb.append("</root>");}catch(Exception e){return e.getMessage(); }finally{db.close(RS, stmt, DBConn);}return sb.toString();
上面是由存储过程生成xml文件用odbc读取并转化为字符串，现在使用了jdbc方式，取结果集的代码如下：
Connection DBConn = null;CallableStatement stmt = null;ResultSet RS = null;DataBase db = new DataBase();String temp = "";StringBuffer sb = new StringBuffer();DBConn = db.connectjdbc(Database);try{stmt=DBConn.prepareCall(SQL, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);stmt.executeQuery();int updateCount=-1;sb.append("<?xml version=\"1.0\" encoding=\"" + this.encoding+ "\"?>");sb.append("<root>");do{ updateCount = stmt.getUpdateCount(); if(updateCount != -1){//说明当前行是一个更新计数 // 处理. stmt.getMoreResults(); continue;//已经是更新计数了,处理完成后应该移动到下一行 // 不再判断是否是ResultSet } else { while (true) { RS = stmt.getResultSet(); if (RS != null) {ResultSetMetaData rsmd = RS.getMetaData();//获取字段名int numberOfColumns = rsmd.getColumnCount(); //获取字段数int i=0;while(RS.next()){ //将查询结果取出 for (i=1; i<=numberOfColumns;i++){ String date=RS.getString(i); sb.append(date); } }RS.close();}if (stmt.getMoreResults()) {RS = stmt.getResultSet();}else {break;}} } }while(!(updateCount == -1 && RS == null)); }catch(Exception e){return e.getMessage();}finally{db.close(RS, stmt, DBConn);}sb.append("</root>");return sb.toString();
页:
[1]