spring中JdbcTemplate使用
1：建立dao
?
public abstract interface DAO
{
? public abstract void setDataSource(DataSource paramDataSource);
?
? public abstract DataSource getDataSource();
}
?
?
2：建立jdbcdao
?
?public abstract class JdbcDAO
?? implements DAO, Serializable
?{
? protected DataSource dataSource;
?? protected JdbcTemplate jt;
?? public Object fetchObject(List list, int row, String fieldName)
? {
???? int n = list.size();
??? if ((n < 1) || (row >= n))
???? return null;
?? Map map = (Map)list.get(row);
?? return ((map == null) ? null : map.get(fieldName));
? }
?
// 返回值只有一行时，取得这一行中某个字段的值,由此jtqueryForList返回的list中每一个值都是一个map（字段，值）
? public Object fetchObject(List list, String fieldName)
? {
? return fetchObject(list, 0, fieldName);
?? }
?
?? public DataSource getDataSource()
?? {
?? return this.dataSource;
?}
?? public JdbcTemplate getJt()
? {
??? return this.jt;
? }
?public void setDataSource(DataSource dataSource)
? {
?? this.dataSource = dataSource;
?? this.jt = new JDBCTemplate(this.dataSource);? //spring注入数据源的同事设置jdbcTemplate的值
}
?
?
}
?
3：使用jdbcTemplate
?
public class SettleTallyGateway extends JdbcDAO implements TallyGateway {
?/** 利随本清扣收策略参数，由spring注入 */
?private DischargePolicy dischargePolicy;
?/** 是否为老结算 */
?private boolean oldSettle;
?DateHelper dateHelper = DateHelper.newInstance(true);
?public SettleTallyGateway() {
?}
?public DischargePolicy getDischargePolicy() {
??return dischargePolicy;
?}
?public void setDischargePolicy(DischargePolicy dischargePolicy) {
??this.dischargePolicy = dischargePolicy;
?}
?public boolean isOldSettle() {
??return oldSettle;
?}
?public void setOldSettle(boolean oldSettle) {
??this.oldSettle = oldSettle;
?}
?
?public Double getAcntRval(String bz, String km, String no, Date edate) {
??Double result = new Double(0);
??StringBuffer sql = new StringBuffer("declare @Rval numeric(15,2) \n");
??sql.append("execute Ns_St_GetRVal '").append(bz).append("','");
??sql.append(km).append("','");
??sql.append(no).append("',");
??sql.append(dateHelper.enclose(edate)).append(",").append("@Rval OUTPUT,'0' \n");
??sql.append("select @Rval as Rval");
??try {
???List list = jt.queryForList(new String(sql));
???Object o = this.fetchObject(list, "Rval");???
???if (o instanceof Number) {
????Number n = (Number) o;
????result = new Double(n.toString()); ?????
???}
??} catch (DataAccessException e) {
???System.out.println(e.getMessage());
???e.printStackTrace();
??}
??return result;
?}
?
?private Account getAccount(String accountNo){
??? ?String sql = "select cnt.no as accountNo,cnt.kmh,cnt.bz,cnt.brno,cnt.quota as quota_ from cntacnt cnt where cnt.no='"+accountNo+"'";
??? ?List list = jt.query(sql, new RowMapper() {
??????????? public Object mapRow(ResultSet rs, int index) throws SQLException {
??????????? ?Account account = new Account();
??????????? ?account.setAccountNo(rs.getString("accountNo"));
??????????? ?account.setSubject(rs.getString("kmh"));
??????????? ?account.setBz(rs.getString("bz"));
??????????? ?account.setQuota(rs.getDouble("quota_"));
??????????????? return account;
??????????? }
??????? });
??? ?return list.size()==0?null:(Account)list.get(0);
??? }
?
?
?
?/**
?? * @param key
? *??????????? 属性Key
? * @return 属性值
? */
?public String getProperty(String key) {
??String paramId = getParamId(key);
??String sql = "select ParamVal from WFPar where ParamNo='" + paramId
????+ "'";
??try {
???return (String) jt.queryForObject(sql, String.class);
??} catch (DataAccessException e) {
???return null;
??}
?}
?/**
? *
? * @param sheetId
? * @return
? */
?public Date getSheetDate(Integer sheetId) {
??String sql = "select ActDate from CntBuss? where SheetId="
????+ sheetId;
??try {
???String date = (String) jt.queryForObject(sql, String.class);
???return TextFormat.parseDate(date);
??} catch (DataAccessException e) {
???return null;
??}
?}
?/**
? * @param key
? *??????????? 属性Key
? * @param value
? *??????????? 属性值
? * @return
? */
?public void setProperty(String key, String value) {
??String paramId = getParamId(key);
??String where = "ParamNo='" + paramId + "'";
??StringBuffer s = new StringBuffer();
??s.append("if exists(select * from WFParam where ").append(where)
????.append(") ").append("\n");
??s.append("?? update WFParam set ParamVal=").append(
????TextFormat.enclose(value)).append(" where ").append(where)
????.append("\n");
??// 2007-12-17修改APPNO=fb1
??s
????.append(
??????"else insert into WfParam (ParamNo,AppNo,ParamName,ParamVal,ParamTxt,InpMode,ValList)")
????.append("\n");
??s.append(" values(").append(TextFormat.enclose(paramId))
????.append(",'FB'").append(",'',").append(
??????TextFormat.enclose(value)).append(",'',0,'')");
??jt.execute(s.toString());
?}
?
}?
4：spring如何配置
?
<bean id="gateway.tally" class="com.SettleTallyGateway">
??????????????? <property name="dataSource"><ref local="dataSource.ECM"/></property>
??????????????? <property name="dischargePolicy"><ref local="dischargePolicy"/></property>
? </bean>
?
