封裝Springjdbc的通用基類

一個通用的BaseDao,簡化開發。包括增刪查改以及分頁。

package oa.common.dao;

import java.util.List;
import oa.common.model.PageModel;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
public abstract class BaseDAO<T> extends NamedParameterJdbcDaoSupport {

protected final static String PAGE_SQL_PREFIX = "select * from(select m.*,rownum num from (";
protected final static String PAGE_SQL_END = ") m where rownum<=?) where num>?";

/**
 * 適用于更新數據庫,insert,update, delete
 * 
 * @param namedSql
 *            :命名參數的ＳＱＬ語句，而且參數的命名必須和ＪａｖａＢｅａｎ中的屬性名對應
 * @param javaBean
 *            ：ｊａｖａｂｅａｎ對象
 * @return
 */
protected int update(String namedSql, Object javaBean) {
    SqlParameterSource paramSource = new BeanPropertySqlParameterSource(
            javaBean);
    return this.getNamedParameterJdbcTemplate().update(namedSql,paramSource);
}

protected int commonUpdate(String sql, Object... paramValue) {
    return this.getJdbcTemplate().update(sql, paramValue);
}

protected T getJavaBean(String sql, Class<T> returnType,
        Object... paramValue) {
    RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(returnType);
    try{
    return this.getJdbcTemplate()
            .queryForObject(sql, rowMapper, paramValue);
    }catch(Exception ex){
        return null;
    }
}

protected List<T> getList(String sql, Class<T> returnType,
        Object... paramValue) {
    RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(returnType);
    return this.getJdbcTemplate().query(sql, rowMapper, paramValue);
}

protected List<T> getList(String sql, Class<T> returnType) {
    RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(returnType);
    return this.getJdbcTemplate().query(sql, rowMapper);
}

/**
 * 計算總記錄數
 * 
 * @param countSQL
 *            計算總記錄數的count語句
 * @param paramValue
 *            語句中對應的參數值
 * @return 總記錄數
 */
protected int getCount(String countSQL, List paramValue) {
    return this.getJdbcTemplate().queryForInt(countSQL,
            paramValue.toArray());
}

protected int getCount(String countSQL, Object... paramValue) {
    return this.getJdbcTemplate().queryForInt(countSQL, paramValue);
}

protected PageModel getPageModel(PageModel model,
        StringBuilder querySQL, StringBuilder countSQL,
        StringBuilder whereSQL, List paramList, Class<T> returnType) {
    querySQL.append(whereSQL);
    countSQL.append(whereSQL);
    // 計算總記錄數
    int allCount = this.getCount(countSQL.toString(), paramList);
    // 獲取分頁記錄集
    // 1。構造完整的分頁語句
    querySQL.insert(0, PAGE_SQL_PREFIX);
    querySQL.append(PAGE_SQL_END);

    // 2.把分頁語句中的參數值加入到paramList中
    paramList.add(model.getNumPerPage()* model.getPageNum());
    paramList.add(( model.getPageNum() - 1) *model.getNumPerPage());
    List result = this.getList(querySQL.toString(), returnType,
            paramList.toArray());
    PageModel models = new PageModel();
    models.setTotalCount(allCount);
    models.setNumPerPage(model.getNumPerPage());
    models.setPageNum(model.getPageNum());
    models.setResult(result);
    models.setOrderDirection(model.getOrderDirection());
    models.setOrderField(model.getOrderField());
    return models;
}

}</pre> 
測試代碼
package oa.dao;
import java.util.List;

import oa.common.dao.BaseDAO;
import oa.entity.TMember;
import org.springframework.stereotype.Repository;
@Repository
public class TmemberDaoImpl extends BaseDAO implements TmemberDao {

 /* (non-Javadoc)
 * @see oa.dao.TmemberDao#deleteInfo(java.lang.Object)
 */
@Override  
    public boolean deleteInfo(String id) {  
        String sql="delete from T_MEMBER where MEMBER_ID=?";  
        return this.commonUpdate(sql,id)>0;  
    }  
    /* (non-Javadoc)
     * @see oa.dao.TmemberDao#getAllInfo()
     */
    @Override  
    public List<TMember> getAllInfo() {  
        String sql="select * from T_MEMBER";  
        return this.getList(sql, TMember.class);  
    }  

    /* (non-Javadoc)
     * @see oa.dao.TmemberDao#getAllInfoById(java.lang.Object)
     */
    @Override  
    public Object getAllInfoById(String id) {  
        String sql="select * from T_MEMBER where member_id=?";  
        return this.getJavaBean(sql, TMember.class, id);  
    }  

    /* (non-Javadoc)
     * @see oa.dao.TmemberDao#insertInfo(java.lang.Object)
     */
    @Override  
    public boolean insertInfo(TMember t) {  
        String sql="insert into T_MEMBER values(?,?,?)";  
        return this.update(sql, t)>0;  
    }  

    /* (non-Javadoc)
     * @see oa.dao.TmemberDao#updateInfo(java.lang.Object)
     */
    @Override  
    public boolean updateInfo(TMember t) {  
        String sql="update T_MEMBER set CONTEL=?,EMAILADDR=? where memberId=?";  
        return this.commonUpdate(sql, t.getConTel(),t.getEmailAddr(), t.getMemberId())>0;  
    }  


}</pre></h3>