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);
}
}
}
});