生成报表Dao类

package com.sys.volunteer.reportview.services;

import java.util.List;
import java.util.Map;

import com.sys.volunteer.pagemodel.PageView;

/***
 *
 * @author dw
 *
 */
public interface ViewDao {
 
 //函数输出参数名，分页模块数据总条数
 final static String TOTALRECORD_FLAG="totalrecord";
 
 //每页显示数量
 final static String PAGE_SIZE_FLAG="pageSize";
 
 //当期页
 final static String CURRENT_PAGE_FLAG="currentPage";
 /**
  * 返回存储过程执行结果
  *
  * @author dw
  * @param procedureQL
  *            存储过程
  * @param params
  *            对应参数
  * @return List
  */
 List getListByRepAndParams(String procedureQL, Object[] params);
 
 List getListByRepAndParams(String procedureQL, Map params);
 
 void getListByRepAndParams(String procedureQL, Map<String, Object> values, PageView pageView);

}

 

 

 

 

 

 

 

package com.sys.volunteer.reportview.services;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.sys.volunteer.pagemodel.PageView;

/***
 *
 * @author dw
 *
 */
@Service
@Transactional
public class ViewDaoImpl implements ViewDao {
 
 @Resource
 private HibernateTemplate hibernateTemplate;

 /**
  * 返回报表结果
  *
  * @author dw
  * @param procedureQL
  *            存储过程
  * @param params
  *            对应参数
  * @return List
  */
 public List getListByRepAndParams(String procedureQL, Object[] params) {
  List returnList = null;
  CallableStatement cstmt = null;
  ResultSet rs = null;
  Session session = hibernateTemplate.getSessionFactory().openSession();
  Connection con = (Connection) session.connection();
  try {
   cstmt = con.prepareCall(procedureQL);
   setCallParams(params, cstmt);
   rs = (ResultSet) cstmt.executeQuery();
   returnList = listRsToMap(rs);
   cstmt.close();
   rs.close();
   con.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   session.close();
  }
  return returnList;
 }
 
 
 /**
  * 返回报表结果
  *
  * @author dw
  * @param procedureQL
  *            存储过程
  * @param params
  *            对应参数
  * @return List
  */
 public List getListByRepAndParams(String procedureQL, Map params) {
  List returnList = null;
  CallableStatement cstmt = null;
  ResultSet rs = null;
  Session session = hibernateTemplate.getSessionFactory().openSession();
  Connection con = (Connection) session.connection();
  try {
   cstmt = con.prepareCall(procedureQL);
   setCallParams(params, cstmt);
   rs = (ResultSet) cstmt.executeQuery();
   returnList = listRsToMap(rs);
   cstmt.close();
   rs.close();
   con.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   session.close();
  }
  return returnList;
 }
 
 /**
  * 返回报表结果
  *
  * @author dw
  * @param procedureQL
  *            存储过程
  * @param params
  *            对应参数
  * @return List
  */
 public void getListByRepAndParams(String procedureQL, Map params,PageView pageView) {
  List returnList = null;
  CallableStatement cstmt = null;
  ResultSet rs = null;
  
  Session session = hibernateTemplate.getSessionFactory().openSession();
  Connection con = (Connection) session.connection();
  try {
   
   cstmt = con.prepareCall(procedureQL);
   
   setCallParams(params, cstmt);
   //输出参数，总条数
   cstmt.registerOutParameter(TOTALRECORD_FLAG, Types.INTEGER);
   
   rs = (ResultSet) cstmt.executeQuery();

   pageView.setTotalrecord(cstmt.getInt(PAGE_SIZE_FLAG));
   pageView.setTotalrecord(cstmt.getInt(CURRENT_PAGE_FLAG));
   pageView.setTotalrecord(cstmt.getInt(TOTALRECORD_FLAG));
   
   returnList = listRsToMap(rs);
   
   pageView.setRecords(returnList);
   
   cstmt.close();
   rs.close();
   con.close();
   
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   session.close();
  }
 }
 
 /**
  * 返回列名小写的Map集合
  *
  * @author dw
  * @param rs
  * @return List
  * @throws Exception
  */
 private List listRsToMap(ResultSet rs) throws Exception {
  List<Map<String, Object>> rslist = new ArrayList<Map<String, Object>>();
  Map<String, Object> map = null;
  ResultSetMetaData rsmd = rs.getMetaData();
  int col_count = rsmd.getColumnCount();
  while (rs.next()) {
   map = new HashMap<String, Object>();
   for (int i = 1; i <= col_count; i++) {
    map.put(rsmd.getColumnName(i).toLowerCase(), rs.getObject(i));
   }
   rslist.add(map);
  }
  return rslist;
 }

 /**
  * @author dw
  * @param params
  * @param cstmt
  * @throws SQLException
  */
 private void setCallParams(Object[] params, CallableStatement cstmt)
   throws SQLException {
  if (params != null && params.length > 0) {
   for (int i = 0; i < params.length; i++) {
    cstmt.setObject(i+1, params[i]);
   }
  }
 }
 
 /**
  * @author dw
  * @param params
  * @param cstmt
  * @throws SQLException
  */
 private void setCallParams(Map params, CallableStatement cstmt)
   throws SQLException {
  
  if (params != null && !params.isEmpty()) {
   Set set = params.keySet();
   Iterator it = set.iterator();
   while (it.hasNext()) {
    String key = it.next().toString();
    cstmt.setObject(key,params.get(key));
   }
  }
 }
}