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 楼 huter111 2008-11-18