DBCP连接池

功能：增删改查

需要的jar包
commons-dbcp-1.4.jar
commons-pool-1.6.jar
mysql-connector-java-5.1.15-bin.jar

/**<p>Title: User</p>
 * <p>Description: 用户</p>
 *
 * @author 志辉
 * @date [2017-8-12 上午09:37:13]
 * @version [1.0]
 */

public class User {
    /**
     * id
     */
    private int id;
    /**
     * 用户名
     */
    private String username;
    /**
     * 密码
     */
    private String password;
    /**<p>Title: getId</p>
     * <p>Description: 获取id</p>
     *
     * @return id
     */
    public int getId() {
        return id;
    }
    /**<p>Title: setId</p>
     * <p>Description: 设置id</p>
     *
     * @param id id
     */
    public void setId(int id) {
        this.id = id;
    }
    /**<p>Title: getUsername</p>
     * <p>Description: 获取用户名</p>
     *
     * @return 用户名
     */
    public String getUsername() {
        return username;
    }
    /**<p>Title: setUsername</p>
     * <p>Description: 设置用户名</p>
     *
     * @param username 用户名
     */
    public void setUsername(String username) {
        this.username = username;
    }
    /**<p>Title: getPassword</p>
     * <p>Description: 获取密码</p>
     *
     * @return 密码
     */
    public String getPassword() {
        return password;
    }
    /**<p>Title: setPassword</p>
     * <p>Description: 设置密码</p>
     *
     * @param password 密码
     */
    public void setPassword(String password) {
        this.password = password;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password="
                + password + "]";
    }
}


/**<p>Title: PoolUtil</p>
 * <p>Description: 连接池工具类</p>
 *
 * @author 志辉
 * @date [2017-8-12 上午10:04:21]
 * @version [1.0]
 */
public class PoolUtil {
    /**
     * 连接池对象
     */
    private static PoolUtil pu = null;
    /**
     * 数据源
     */
    private static BasicDataSource bds = null;

    private PoolUtil(){
        Properties pro = new Properties();
        try {
            // 加载配置文件
            pro.load(PoolUtil.class.getClassLoader().getResourceAsStream("JDBC.properties"));
            // 创建对象
            bds = new BasicDataSource();
            bds.setDriverClassName(pro.getProperty("driver"));
            bds.setUrl(pro.getProperty("url"));
            bds.setUsername(pro.getProperty("username"));
            bds.setPassword(pro.getProperty("passord"));
            bds.setMaxActive(Integer.parseInt(pro.getProperty("maxActive")));
            bds.setMinIdle(Integer.parseInt(pro.getProperty("minIdle")));
            bds.setInitialSize(Integer.parseInt(pro.getProperty("initialSize")));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * <p>Title: getPoolUtil</p>
     * <p>Description: 获取连接池</p>
     * 
     * @return 连接池对象
     */
    public static PoolUtil getPoolUtil(){
        if (null == pu){
            synchronized (PoolUtil.class) {
                if (null == pu)
                    pu = new PoolUtil();
            }
        }
        return pu;
    }
    /**
     * <p>Title: getConnection</p>
     * <p>Description: 获取连接</p>
     * 
     * @return 连接
     */
    public Connection getConnection(){
        Connection conn = null;
        try {
            conn = bds.getConnection();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * <p>Title: closeAll</p>
     * <p>Description: 关流</p>
     * 
     * @param conn 连接
     * @param sta 预加载
     * @param rs 结果集
     */
    public void closeAll(Connection conn, Statement ps, ResultSet rs){
        try {
            if (null != rs)
                rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            try {
                if (null != ps)
                    ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally{
                try {
                    if (null != conn)
                        conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}




/**<p>Title: BaseDao</p>
 * <p>Description: Dao父类</p>
 *
 * @author 志辉
 * @date [2017-8-12 上午10:25:44]
 * @version [1.0]
 */
public class BaseDao {
    /**
     * <p>Title: executeUpdate</p>
     * <p>Description: 增、删、改</p>
     * 
     * @param sql
     * @param params
     * @return
     */
    public int executeUpdate(String sql, Object ...params){
        // 声明连接
        Connection conn = null;
        // 预加载
        PreparedStatement ps = null;
        // 声明结果集
        ResultSet rs = null;
        int count = 0;
        // 获取连接
        conn = PoolUtil.getPoolUtil().getConnection();
        try {
            // 加载sql语句
            ps = conn.prepareStatement(sql);
            // 填充数据
            if (null!=params && params.length>0){
                for (int i=0,len=params.length; i<len; i++){
                    ps.setObject(i+1, params[i]);
                }
            }
            // 执行语句
            count = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            PoolUtil.getPoolUtil().closeAll(conn, ps, rs);
        }
        return count;
    }
    /**
     * <p>Title: executeQuery</p>
     * <p>Description: 查询</p>
     * 
     * @param <T>
     * @param cls
     * @param sql
     * @param params
     * @return
     */
    public <T> List<T> executeQuery(Class<T> cls, String sql, Object ...params){
        // 声明连接
        Connection conn = null;
        // 预加载
        PreparedStatement ps = null;
        // 声明结果集
        ResultSet rs = null;
        int count = 0;
        List<T> list = null;
        T t = null;
        // 获取连接
        conn = PoolUtil.getPoolUtil().getConnection();
        try {
            // 加载sql语句
            ps = conn.prepareStatement(sql);
            // 填充数据
            if (null!=params && params.length>0){
                for (int i=0,len=params.length; i<len; i++){
                    ps.setObject(i+1, params[i]);
                }
            }
            // 执行语句
            rs = ps.executeQuery();
            // 获取表结构
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取列数
            count = rsmd.getColumnCount();
            String[] name = new String[count];
            // 获取列名
            for (int i=0; i<count; i++)
                name[i] = rsmd.getColumnLabel(i+1);
//              name[i] = rsmd.getColumnName(i+1);
            // 创建集合
            list = new ArrayList<T>();
            // 获取实体所有方法
            Method[] method = cls.getDeclaredMethods();
            // 遍历结果集
            while (rs.next()){
                // 创建实体
                t = cls.newInstance();
                for (int j=0; j<count; j++){
                    String newName = "set" + name[j];
//                  String newName = "set" + name[j].substring(0, 1).toUpperCase() + name[j].substring(1);
                    // 遍历方法
                    for (int i=0; i<method.length; i++){
                        if (method[i].getName().equalsIgnoreCase(newName)){
                            method[i].invoke(t, rs.getObject(name[j]));
                        }
                    }
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            PoolUtil.getPoolUtil().closeAll(conn, ps, rs);
        }
        return list;
    }
}



/**<p>Title: EntityImpl</p>
 * <p>Description:实现类 </p>
 *
 * @author 志辉
 * @date [2017-8-12 上午11:08:50]
 * @version [1.0]
 */
public class EntityImpl extends BaseDao implements Entity {

    @Override
    public int insertEntity(String sql, Object... params) {
        // TODO Auto-generated method stub
        return executeUpdate(sql, params);
    }

    @Override
    public int removeEntity(String sql, Object... params) {
        // TODO Auto-generated method stub
        return executeUpdate(sql, params);
    }

    @Override
    public int deleteEntity(String sql, Object... params) {
        // TODO Auto-generated method stub
        return executeUpdate(sql, params);
    }

    @Override
    public <T> List<T> selectEntity(Class<T> cls, String sql, Object... params) {
        // TODO Auto-generated method stub
        return executeQuery(cls, sql, params);
    }

}