对JDBC进行简单的封装，实现对数据库的增、删、改、查功能

通过的jdbc的简单封装，实现了对数据库的增删改查的功能，在某些小的项目中，不用hibernate等框架时有用，在此做个备份，以便以后查阅。需要用到第三方包：

(1)commons-dbutils-1.3.jar

(2)commons-logging-1.1.1.jar

(3)commons-pool-1.3.jar

(4)mysql-connector-java-5.0.5.jar

(5)commons-dbcp-1.4.jar

1.创建数据库链接池接口定义

import java.sql.Connection;
import java.sql.SQLException;

/**
*
*数据库链接池接口定义
**/
public interface Pool {
	public void setDriverClassName(String driverClassName) ;
	public void setUrl(String url);
	public void setUserName(String userName);
	public void setPassword(String password) ;
	public  Connection getConnection()throws SQLException;
    public void init()throws SQLException;
}

2.对上面接口的简单实现，完成数据库链接

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 *获取数据库链接的基本实现
 */
public class BasicDataSourceConnectionPool implements Pool {
	private static Log logger = LogFactory.getLog(BasicDataSourceConnectionPool.class);
	// ********************数据库设置*************************/
	//test_db:数据库名
	//这些基本的参数可以写在配置文件中
	public final static String MYSQL_DB_URL = "jdbc:mysql://192.168.1.20/test_db";
	public final static String MYSQL_DB_USER ="username";
	public final static String MYSQL_DB_PASSWORD ="password";
	public final static String MYSQL_CLASS_NAME = "com.mysql.jdbc.Driver";
	public final static int DB_POOL_MIN = 10;
	public final static int DB_POOL_MAX = 30;
	public final static int DB_ACTIVITY_MAX = 40;
	// **********************END****************************/
	private BasicDataSource datasource = new BasicDataSource();

	public int getActivepPoolSize() {
		return datasource.getInitialSize();
	}

	public synchronized Connection getConnection() throws SQLException {
		try {
			return datasource.getConnection();
		} catch (SQLException e) {
			throw e;
		}
	}

	public int getIdlepPoolSize() {
		return datasource.getNumIdle();
	}

	public void init() throws SQLException{
		datasource.setDefaultAutoCommit(true);
		datasource.setRemoveAbandoned(false);
		datasource.setRemoveAbandonedTimeout(120);
		datasource.setTestOnBorrow(true);
		datasource.setMaxActive(SrvConstants.DB_ACTIVITY_MAX);//  -1 stands for no limit
		datasource.setMaxIdle(SrvConstants.DB_POOL_MAX);
		datasource.setMinIdle(SrvConstants.DB_POOL_MIN);
		datasource.setValidationQuery("select 1 from dual");
	}

	public void setDriverClassName(String driverClassName) {
		datasource.setDriverClassName(driverClassName);
	}

	public void setInitSize(int initSize) {
		datasource.setInitialSize(initSize);
	}

	public void setPassword(String password) {
		datasource.setPassword(password);
	}

	public void setUrl(String url) {
		datasource.setUrl(url);
	}

	public void setUserName(String userName) {
		datasource.setUsername(userName);
	}
}

3.实现数据底层的增删改查操作

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 数据库操作底层类
 * 
 *
 */
public class DbPoolUtil {

	private static Log log = LogFactory.getLog(DbPoolUtil.class);
	static DbPoolUtil m_instance;

	private static Pool datasource =null;
	
	
	/**
	 * 初始化datasource 的基本数据
	 * @throws SQLException
	 */
	DbPoolUtil() throws SQLException {
		if (datasource == null) {
			datasource = new BasicDataSourceConnectionPool();
			datasource.setDriverClassName(datasource.MYSQL_CLASS_NAME);
			datasource.setUrl(datasource.MYSQL_DB_URL);
			datasource.setUserName(datasource.MYSQL_DB_USER);
			datasource.setPassword(datasource.MYSQL_DB_PASSWORD);
			datasource.init();
		}
	}

	/**
	 * 获取数据库连接
	 * @return
	 * @throws SQLException
	 */
	public static final Connection getPoolConnection() throws SQLException {
		if (m_instance == null) {
			synchronized (DbPoolUtil.class) {
				if (m_instance == null) {
					try {
						m_instance = new DbPoolUtil();
					} catch (SQLException e) {
						m_instance = null;
						log.error("Failed to init DB connection:", e);
					}
				}
			}
			System.out.println("实例化 DbPoolUtil");
		}

		return datasource.getConnection();
	}
	/**
	 * 返回一个实例对象
	 * @param <T>
	 * @param sql
	 * @param type
	 * @param params
	 * @param isList  表明返回值是否是List
	 * @return
	 * @throws SQLException
	 */
	 public static <T> T query(Class<T> type,boolean isList, String sql,Object...params) throws SQLException{
		 if (isList) {
			 return query(sql,new BeanListHandler(type),params);	    
		 } else {
			 return query(sql, new BeanHandler(type),params);
		 }
	 }
	 
	 /**
	  * 
	  * 返回一个Map
	  * key：字段名 value:字段值。只支持一条数据，若通过sql查询的结果是多条数据则取第一条
	  */
	 public static  Map query(String sql,Object...params) throws SQLException{
	    return (Map) query(sql,new MapHandler(),params);
	 }
	 
	/**
	 * 多条件查询最终执行的方法
	 * @param <T>
	 * @param sql
	 * @param resultSetHandler
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	 private static <T> T query(String sql, ResultSetHandler resultSetHandler,Object... params) throws SQLException {
		QueryRunner queryRunner = new QueryRunner();
	    Connection conn = null;
	    try {
	     conn = getPoolConnection();
	     return (T) queryRunner.query(conn,sql,resultSetHandler,params);
	    } catch (SQLException e) {
	     throw new SQLException(e);
	    } finally {
	       close(conn);
	    }
	 }
	 
	
	/**
     * 仅通过sql语句对数据库进行INSERT, UPDATE, or DELETE 操作
     * @param sql The SQL statement to execute.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated. 
     */
    public static int update(String sql) throws SQLException {
        return update(sql, (Object[]) null);
    }

    /**
     * 执行INSERT, UPDATE, or DELETE 操作，支持一个参数
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public static int update(String sql, Object param) throws SQLException {
        return update(sql, new Object[] { param });
    }

    /**
     * 执行INSERT, UPDATE, or DELETE 操作，支持多个参数
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?') 
     * parameters.
     * @return The number of rows updated.
     * @throws Exception 
     */
    public static int update(String sql, Object... params) throws SQLException {
        return update(getPoolConnection(), sql, params);
    }

	/**
     * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
     * parameters.
     * 
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    private static int update(Connection conn, String sql) throws SQLException {
        return update(conn, sql, (Object[]) null);
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
     * parameter.
     * 
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    private static int update(Connection conn, String sql, Object param)
        throws SQLException {

        return update(conn, sql, new Object[] { param });
    }

    /**
     * 执行数据的insert update  delete操作的最终方法
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param params The query replacement parameters.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    private static int update(Connection conn, String sql, Object... params)
        throws SQLException {

        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = prepareStatement(conn, sql);
            fillStatement(stmt, params);
            rows = stmt.executeUpdate();

        } catch (SQLException e) {
            rethrow(e, sql, params);

        } finally {
            close(stmt);
            close(conn);
        }

        return rows;
    }

    /**
     * Fill the <code>PreparedStatement</code> replacement parameters with 
     * the given objects.
     * @param stmt PreparedStatement to fill
     * @param params Query replacement parameters; <code>null</code> is a valid
     * value to pass in.
     * @throws SQLException if a database access error occurs
     */
    private static void fillStatement(PreparedStatement stmt, Object... params)
        throws SQLException {

        if (params == null) {
            return;
        }
       
        for (int i = 0; i < params.length; i++) {
            if (params[i] != null) {
                stmt.setObject(i + 1, params[i]);
            } 
        }
    }

    /**
     * Throws a new exception with a more informative error message.
     * 
     * @param cause The original exception that will be chained to the new 
     * exception when it's rethrown. 
     * 
     * @param sql The query that was executing when the exception happened.
     * 
     * @param params The query replacement parameters; <code>null</code> is a 
     * valid value to pass in.
     * 
     * @throws SQLException if a database access error occurs
     */
    private static void rethrow(SQLException cause, String sql, Object... params)
        throws SQLException {

        String causeMessage = cause.getMessage();
        if (causeMessage == null) {
            causeMessage = "";
        }
        StringBuffer msg = new StringBuffer(causeMessage);

        msg.append(" Query: ");
        msg.append(sql);
        msg.append(" Parameters: ");

        if (params == null) {
            msg.append("[]");
        } else {
            msg.append(Arrays.deepToString(params));
        }

        SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
                cause.getErrorCode());
        e.setNextException(cause);

        throw e;
    }

    /**
     * Factory method that creates and initializes a 
     * <code>PreparedStatement</code> object for the given SQL.  
     * <code>QueryRunner</code> methods always call this method to prepare 
     * statements for them.  Subclasses can override this method to provide 
     * special PreparedStatement configuration if needed.  This implementation
     * simply calls <code>conn.prepareStatement(sql)</code>.
     *  
     * @param conn The <code>Connection</code> used to create the 
     * <code>PreparedStatement</code>
     * @param sql The SQL statement to prepare.
     * @return An initialized <code>PreparedStatement</code>.
     * @throws SQLException if a database access error occurs
     */
	private static PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {

		return conn.prepareStatement(sql);
	}

	public static void destroy(ResultSet rs, Statement ps, Connection conn) throws SQLException {
		close(rs);
		close(ps);
		close(conn);
	}

	 /**
     * Close a <code>ResultSet</code>, avoid closing if null.
     *
     * @param rs ResultSet to close.
     * @throws SQLException if a database access error occurs
     */
    public static void close(ResultSet rs)  {
        if (rs != null) {
            try {
				rs.close();
			} catch (SQLException e) {
				
			}
        }
    }
    
	/**
	 * Closes connection hiding any exceptions that might happen.
	 * 
	 * @param c
	 *            connection to close (can be <code>null</code>)
	 */
	public static void close(Connection c) {
		if (c != null) {
			try {
				c.close();
			} catch (Throwable thr) {
			}
		}
	}

	/**
	 * Closes statement hiding any exceptions that might happen.
	 * 
	 * @param ps
	 *            statement to close (can be <code>null</code>)
	 */
	public static void close(Statement ps) {
		if (ps != null) {
			try {
				ps.close();
			} catch (Throwable thr) {
			}
		}
	}

	public static void close(ResultSet rst ,Statement ps,Connection con)
	{
		close(rst);
		close(ps);
		close(con);
	}
	
}