spring jdbc封装通用DAO方法
1、接口定义
package com.gkhb.mis.core.dao;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.SqlParameter;
/**
?* @corporation gkhb
?* @author?
?* @date 2008-4-9 下午03:03:54
?* @path com.gkhb.mis.core.dao
?* @description
?*/
public interface IOperationGenericDao {
?
?public static final String DAO = "operationGenericDao";
?
?public Page findByPage(String sql, Page page, Object... values);
?public List<?> findRow(String sql, Object... values);
?public Map<?, ?> getRow(String sql, Object... values);
?public int saveRow(String sql, Object... values);
?public Map<?, ?> executeStroeProcedure(String procedureName,
???SqlParameter[] procedureParameters, Object... parameterValues);
}
2、接口实现
package com.gkhb.mis.core.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.StoredProcedure;
/**
?* @corporation gkhb
?* @author?
?* @date 2008-4-9 下午03:04:47
?* @path com.gkhb.mis.core.dao
?* @description 通用业务数据访问处理
?*/
public class OperationGenericDaoJdbcImp extends JdbcDaoSupport implements
??IOperationGenericDao {
?protected final Log LOG = LogFactory.getLog(this.getClass());
?/**
? * @desc
? * @date 2008-11-7
? * @author?
? * @parameter List={map}
? * @see com.gkhb.mis.core.dao.IOperationGenericDao#findByPage(java.lang.String,
? *????? com.gkhb.mis.core.dao.Page, java.lang.Object[])
? */
?public Page findByPage(String sql, Page page, Object... values) {
??LOG.debug("===findByPage===sql=====" + sql);
??StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
??totalSQL.append(sql);
??totalSQL.append(" ) totalTable ");
??int totalCount = getJdbcTemplate().queryForInt(totalSQL.toString(),values);
??int lastIndex = page.getStart() + page.getPageSize();
??StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
??paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
??paginationSQL.append(sql);
??paginationSQL.append(" ) temp where ROWNUM <= " + lastIndex);
??paginationSQL.append(" ) WHERE num > " + page.getStart());
??page.setResult(getJdbcTemplate().queryForList(paginationSQL.toString(),
????values));
??page.setTotalCount(totalCount);
??return page;
?}
?/**
? * @desc list={Map}
? * @date 2008-11-7
? * @author
? * @parameter
? * @see com.gkhb.mis.core.dao.IOperationGenericDao#findRow(java.lang.String,
? *????? java.lang.Object[])
? */
?public List<?> findRow(String sql, Object... values) {
??LOG.debug("===findRow===sql=====" + sql);
??return getJdbcTemplate().queryForList(sql, values);
?}
?public Map<?, ?> getRow(String sql, Object... values) {
??LOG.debug("===getRow===sql=====" + sql);
??return getJdbcTemplate().queryForMap(sql, values);
?}
?public int saveRow(String sql, Object... values) {
??LOG.debug("===saveRow===sql=====" + sql);
??return getJdbcTemplate().update(sql, values);
?}
?/**
? * @description
? * @date 2008-7-7
? * @author?
? * @param procedureName
? * @param procedureParameters
? * @return}
? */
?public Map<?, ?> executeStroeProcedure(String procedureName,
???SqlParameter[] procedureParameters, Object... parameterValues) {
??CustomStoredProcedure sproc = new CustomStoredProcedure(
????getDataSource(), procedureName, procedureParameters);
??Map<?, ?> out = sproc.execute(parameterValues);
??return out;
?}
?private class CustomStoredProcedure extends StoredProcedure {
??public CustomStoredProcedure(DataSource ds, String proname,
????SqlParameter[] parameters) {
???setDataSource(ds);
???setFunction(false);
???setSql(proname);
???this.setParameters(parameters);
???// declareParameter(new SqlParameter("g_gdfgsbm", Types.VARCHAR));
???// declareParameter(new SqlParameter("g_cbyear", Types.NUMERIC));
???// declareParameter(new SqlParameter("g_cbmonth", Types.NUMERIC));
???compile();
???
???
???
??}
??@SuppressWarnings("unchecked")
??public Map execute(Object... p) {
???Map inParams = new HashMap();
???int dpsize = this.getDeclaredParameters().size();
???for (int i = 0; i < dpsize; i++) {
????SqlParameter s = (SqlParameter) this.getDeclaredParameters()
??????.get(i);
????inParams.put(s.getName(), p[i]);
???}
???Map out = execute(inParams);
???return out;
??}
?}
}
3、实际调用
package com.gkhb.t3.crm.reportImpl;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.gkhb.mis.core.dao.IOperationGenericDao;
import com.gkhb.t3.crm.report.CashierPaymentReport;
import com.gkhb.t3.crm.report.ICashierPaymentReport;
/**
?* @author?
?*
?*/
@Service(ICashierPaymentReport.SERVICE)
@Transactional
public class CashierPaymentReportImpl implements ICashierPaymentReport {
?@Autowired
?private IOperationGenericDao jdbcDao;
?@Override
?public List<CashierPaymentReport> reportFind(Map sc) {
??String hql = "select * from crm_report_cnszrb t where? to_char(t.rq,'yyyy-mm-dd')? between ? and? ?? and t.groupId like ?? order by t.rq desc ";
??// String hql = "select * from crm_report_cnszrb ";
??String startRq = (String) sc.get("startRq");
??String endRq = (String) sc.get("endRq");
??String groupId = (String) sc.get("groupId");
??SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
??Date startRq1 = null;
??try {
???startRq1 = format.parse(startRq);
??} catch (ParseException e) {
???e.printStackTrace();
??}
??Date endRq1 = null;
??try {
???endRq1 = format.parse(endRq);
??} catch (ParseException e) {
???e.printStackTrace();
??}
??SqlParameter rq = new SqlParameter(Types.VARCHAR);
??// 统计报表
??System.out.println("========== start ===========");
??jdbcDao.executeStroeProcedure("crm_fee_cnszrbreport",
????new SqlParameter[] {
??????new SqlParameter("l_start", Types.DATE),//存储过程参数名称及数据类型l_start
??????new SqlParameter("l_end", Types.DATE),//存储过程参数名称l_end
??????new SqlParameter("l_groupid", Types.INTEGER)// 存储过程参数名称l_groupid
??????},
????new Object[] {
????startRq1, endRq1, new Integer(groupId) //三个参数对应的值
????});
??System.out.println("========== end ==============");
??// 查询报表
??List reportMap = jdbcDao.findRow(hql, new Object[] {startRq, endRq,
????groupId });
??List<CashierPaymentReport> CashierPaymentReportVo = new ArrayList<CashierPaymentReport>();
??for (int j = 0; j < reportMap.size(); j++) {
???CashierPaymentReport temp = new CashierPaymentReport();
???Map tem = (Map) reportMap.get(j);
???temp.setRQ((Date) tem.get("rq"));
???temp.setDJBH((String) tem.get("djbh"));
???temp.setDJLX((String) tem.get("djlx"));
???temp.setZJ((String) tem.get("zj"));
???temp.setJBR((String) tem.get("jbr"));
???temp.setSRJE((Number) tem.get("srje"));
???temp.setZCJE((Number) tem.get("zcje"));
???CashierPaymentReportVo.add(temp);
??}
??return CashierPaymentReportVo;
?}
}
