jdbc BaseDao封装

package dao;

import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import db.ConnectionPoll;
import entity.Pager;
import utils.QueryUtils;
import utils.SystemContext;

public abstract class BaseDao<T> {

    private Class<T> clz;

    @SuppressWarnings("unchecked")
    private Class<T> getClz() {
        if(null == clz){
            clz = (Class<T>) (((ParameterizedType) (this.getClass()
                    .getGenericSuperclass()))
                    .getActualTypeArguments()[0]);
        }
        return clz;
    }

    /**
     * 查询一条记录
     * @param sql
     * @param params
     * @return
     */
    protected T query(String sql,Object ...params){
        T result = null;
        try {
            ResultSet rs = executeQuery(sql,params);
            if(rs.next()){
                result = getFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } 
        return result;
    }

    /**
     * 执行一个更新操作
     * @param sql
     * @param params
     * @return
     */
    protected int executeUpdate(String sql,Object ...params){
        Connection conn = null;
        try {
            conn = ConnectionPoll.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            setParameters(pstmt, params);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }   
    }

    /**
     * 统计sql语句对应的记录条数
     * @param sql
     * @param params
     * @return
     */
    protected Integer count(String sql,Object ...params){
        Integer count = null;
        try {
            ResultSet rs = executeQuery(QueryUtils.getCountSQL(sql), params);
            if(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        return count;
    }

    /**
     * 给PreparedStatement设置参数
     * @param pstmt
     * @param params
     * @throws SQLException
     */
    protected void setParameters(PreparedStatement pstmt,Object ... params) throws SQLException{
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i+1, params[i]);
        }
    }

    /**
     * 查询，带分页
     * @param sql
     * @param params
     * @return
     */
    protected Pager<T> getPager(String sql,Object ...params){
        Pager<T> pager = null;
        PreparedStatement pstmt = null;
        Connection conn = null;

        // 获取记录条数
        try {
            // 统计记录条数
            Integer recordCount = count(sql, params);
            // 构造Page对象
            pager = new Pager<>(SystemContext.getPageSize(), SystemContext.getPageOffset(), recordCount);
            // 获取连接
            conn = ConnectionPoll.getConnection();
            pstmt = conn.prepareStatement(sql);
            // 设置查询参数
            setParameters(pstmt, params);
            // 设置分页参数
            pstmt.setMaxRows(pager.getEndIndex()); // 最大记录条数，为终止下标
            // 执行查询
            ResultSet rs = pstmt.executeQuery();
            // 设置这一页的起始记录下标
            rs.absolute(pager.getBeginIndex());
            // 读取记录
            List<T> resultList = new ArrayList<>();
            while (rs.next()) {
                resultList.add(getFromResultSet(rs));
            }
            // 将数据储存在page对象中
            pager.setRecordList(resultList);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pager;
    }

    /**
     * 
     * @param method 将ResultSet转成T的方法
     * @param sql 要执行的Sql语句
     * @param params 传的参数
     * @return
     */
    protected List<T> list(String sql, Object ... params ){
        List<T> resultList = new ArrayList<>();
        try{
            ResultSet rs = executeQuery(sql, params);
            while(rs.next()){
                resultList.add(this.getFromResultSet(rs));
            }
        }catch(SQLException e){
            e.printStackTrace();
        } 
        return resultList;
    }

    /**
     * 抽象方法，从ResultSet到实体的映射
     * @param rs
     * @return
     * @throws SQLException
     */
    protected abstract T getFromResultSet(ResultSet rs) throws SQLException;

    private ResultSet executeQuery(String sql,Object ... params) throws SQLException{
        Connection conn = ConnectionPoll.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        setParameters(pstmt, params);
        return pstmt.executeQuery();
    }
}