Spring SimpleJdbcTemplate

spring-context.xml:

<!-- bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate" -->
<bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
  <constructor-arg ref="dataSource" />
</bean>

TestQuery.java :

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.ahtec.oa.domain.OecReport;

public class TestQuery {

 /**
  * @param SimpleJdbcTemplate simpleJdbcTemplate
  * @param Sql sql
  * @param Logger logger
  * @param Map<String, Object> map
  * @param boolean "true:查看详细的分数;false:查看总分"
  */
 public void getScoreReportList(SimpleJdbcTemplate simpleJdbcTemplate, String sql, Logger logger,
   Map<String, Object> reportMap, final boolean isDetail) {
  ParameterizedRowMapper<OecReport> rm = new ParameterizedRowMapper<OecReport>() {
   public OecReport mapRow(ResultSet rs, int rowNum) throws SQLException {
    OecReport entity = new OecReport();
    if (isDetail) {
     entity.setOec_date(rs.getDate("oec_date"));
    }
    entity.setSend_user_name(rs.getString("send_user_name"));
    entity.setSend_dept_name(rs.getString("send_dept_name"));
    entity.setScore(rs.getBigDecimal("score"));
    return entity;
   }
  };
  if (isDetail) {
   logger.info("=======================[" + reportMap.get("begin_date") + "至" + reportMap.get("end_date") + "]详细的分数=======================");
  } else {
   logger.info("=======================[" + reportMap.get("begin_date") + "至" + reportMap.get("end_date") + "]总分=======================");
  }
  List<OecReport> oecReportList = simpleJdbcTemplate.query(sql, rm, reportMap);
  for (OecReport o : oecReportList) {
   String oec_date = "";
   if (isDetail) {
    oec_date = DateFormatUtils.format(o.getOec_date(), "yyyy-MM-dd");
   }
   String score = "--";
   if (null != o.getScore()) {
    score = o.getScore().toString();
   }
   if (isDetail) {
    logger.info("[{}] [{}] [{}] [{}]", new String[] { oec_date, o.getSend_user_name(),
      o.getSend_dept_name(), score });
   } else {
    logger.info("[{}] [{}] [{}]", new String[] { o.getSend_user_name(), o.getSend_dept_name(), score });
   }

  }
 }

 public static void main(String[] args) {
  Logger logger = LoggerFactory.getLogger(TestQuery.class);

  ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(new String[] { "spring-context.xml" });
  SimpleJdbcTemplate simpleJdbcTemplate = (SimpleJdbcTemplate) ctx.getBean("simpleJdbcTemplate");

  String sqlUserIds = "select u.id from user_info u where u.is_del = 0 and (u.dept_id in (select id  from dept_info start with par_id = :par_dept_id connect by prior id = par_id) or dept_id = :par_dept_id) and u.dept_id not in (:filter_dept_id)";

  String sqlDetailScoreReport = "select o.oec_date,o.send_user_name,o.send_dept_name,o.score "
    + "from oec_report o " 
    + " where o.is_del = 0 " 
    + " and o.is_send = 1"
    + " and o.oec_date >= to_date(:begin_date, 'yyyy-MM-dd')"
    + " and o.oec_date <= to_date(:end_date, 'yyyy-MM-dd')" 
    + " and o.send_user_id in(" + sqlUserIds + ")"
    + " order by  o.oec_date desc, o.send_dept_id";

  String sqlSumScoreReport = "select o.send_user_name,o.send_dept_name,sum(o.score) as score "
    + "from oec_report o " 
    + " where o.is_del = 0 " 
    + " and o.is_send = 1"
    + " and o.oec_date >= to_date(:begin_date, 'yyyy-MM-dd')"
    + " and o.oec_date <= to_date(:end_date, 'yyyy-MM-dd')" 
    + " and o.send_user_id in(" + sqlUserIds + ")"
    + " group by o.send_user_name, o.send_dept_id, o.send_dept_name" 
    + " order by o.send_dept_id";

  TestQuery t = new TestQuery();

  Map<String, Object> reportMap = new HashMap<String, Object>();
  reportMap.put("par_dept_id", 125);
  reportMap.put("filter_dept_id", 2992);
  reportMap.put("begin_date", "2009-08-25");
  reportMap.put("end_date", "2009-09-01");

  t.getScoreReportList(simpleJdbcTemplate, sqlDetailScoreReport, logger, reportMap, true);

  t.getScoreReportList(simpleJdbcTemplate, sqlSumScoreReport, logger, reportMap, false);
 }

}