JDBC 取一个结果集得简单处理方法

 

1:首先可以看下SQL语句

select task.task_code_id as task_code_id  ,count(task.id) as distributedCount,  
(select count(1)  from t_approval_task t1 where t1.approval_status is null and task.task_code_id = t1.task_code_id ) as waitCount, 
(select count(1)  from t_approval_task t1 where t1.approval_status=1 and task.task_code_id = t1.task_code_id  ) as passCount, 
(select count(1)  from t_approval_task t1 where t1.approval_status=0 and task.task_code_id = t1.task_code_id ) as unpassCount, 
(select count(1)  from t_approval_task t1 where t1.approval_status=-1  and task.task_code_id = t1.task_code_id) as suspendCount 
from t_approval_task task where 1=1 GROUP BY  task.task_code_id ORDER BY  task.task_code_id

 

 

2:然后导入的 Import

 

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

 

 

3:将jdbcTemplate 注入进service方法

 

@Autowired
private JdbcTemplate jdbcTemplate ;

 

4:然后最后是取结果集

final List<Map> list=new ArrayList<Map>();
		jdbcTemplate.query(sb.toString(), new Object[0], new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				while(rs.next()){
					Map map=new HashMap();
					map.put("taskCodeId", rs.getObject("task_code_id"));
					map.put("distributedCount", rs.getObject("distributedCount"));
					map.put("waitCount", rs.getObject("waitCount"));
					map.put("passCount", rs.getObject("passCount"));
					map.put("unpassCount", rs.getObject("unpassCount"));
					map.put("suspendCount", rs.getObject("suspendCount"));
					list.add(map);	
				}
			}
		});

 

 

由于第一取不到数据.

直接:

final List<Map> list=new ArrayList<Map>();
		jdbcTemplate.query(sb.toString(), new Object[0], new RowCallbackHandler(){
			/* (non-Javadoc)
			  * 这里会出现一个BUG,就是第一条取不到.然后就强制把游标指向第一条.
			 * @see org.springframework.jdbc.core.RowCallbackHandler#processRow(java.sql.ResultSet)
			 */
			public void processRow(ResultSet rs) throws SQLException {
				if(rs.first()){
					rs.first(); //直接将游标指向第一个条记录.然后往下判断 .取到了第一条数据
					Map map=new HashMap();
					map.put("taskCodeId", rs.getObject("task_code_id"));
					map.put("distributedCount", rs.getObject("distributedCount"));
					map.put("waitCount", rs.getObject("waitCount"));
					map.put("passCount", rs.getObject("passCount"));
					map.put("unpassCount", rs.getObject("unpassCount"));
					map.put("suspendCount", rs.getObject("suspendCount"));
					list.add(map);	
									while(rs.next()){
						Map map2=new HashMap();
						map2.put("taskCodeId", rs.getObject("task_code_id"));
						map2.put("distributedCount", rs.getObject("distributedCount"));
						map2.put("waitCount", rs.getObject("waitCount"));
						map2.put("passCount", rs.getObject("passCount"));
						map2.put("unpassCount", rs.getObject("unpassCount"));
						map2.put("suspendCount", rs.getObject("suspendCount"));
						list.add(map2);	
					}
				}
			}
		}); 

  

评论

1 楼 pigming 2012-01-08  

有些数据库游标不能后退，rs.first()可能无效