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