jdbc工具类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * 利用Java中单例设计模式完成对数据库连接池的封装
 * 目的：
 * 1. 保证DBConnection类为外部其他提供唯一一个实例对象
 * 2. 保证将DataSource数据源能够不暴露给外部类使用
 * 3. 保证提供统一的Connection会话对象
 * 4. 保证提供统一的Connection对象的资源关闭的操作
 * @author Administrator
 *
 */
public class DBConnection {

    //连接数据库的URL地址
    private static final String URL = "jdbc:mysql://localhost:3306/flower?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
    //连接数据库的用户名称
    private static final String USER = "root";
    //连接数据库的密码
    private static final String PASSWORD = "123456";

    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    static{
        //1.加载驱动程序.
        try {
            Class.forName("com.mysql.jdbc.Driver");//mysql 5.0
            //mysql8.0 用 com.mysql.cj.jdbc.Driver
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库的会话
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = tl.get(); //通过线程来获取当前线程中的数据库会话对象
        if (conn == null) {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);//尝试建立与给定数据库URL的连接。
            tl.set(conn); //将新的Connection对象放入到线程里面
        }
        return conn;
    }

    /**
     * 对数据库的会话进行资源的关闭
     * @throws SQLException
     */
    public static void closeConnection() throws SQLException {
        Connection conn = tl.get();
        if (conn != null && !conn.isClosed()) {
            conn.close();
            tl.remove();
        }
    }

}

工具类：

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.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 封装了JDBC所有常见方法：
 *     1. 封装返回结果是一个ListList<Map<String,Object>>类型的数据
 *     2. 封装返回结果是一个int类型
 *     3. 统一查询返回一个Map集合
 *     4. 统一查询返回一个实体对象
 *     5. 统一查询返回一个List<T>集合的实体对象
 *     6. 封装一个增、删、改方法，返回int类型
 */
public abstract class JdbcUtils {

   //封装返回结果是一个List类型的数据
   //MapListHandler获取数据进行封装
   public List<Map<String,Object>> findAllForList(String sql,Object...params) throws SQLException{//...可变参数的方法使用
    	Connection conn = DBConnection.getConnection();
    	QueryRunner queryRunner=new QueryRunner();
    	List<Map<String,Object>> list = queryRunner.query(conn, sql, new MapListHandler(), params);
    	DBConnection.closeConnection();
		return list;
    }

   //封装返回结果是一个int类型
   public int findAllForInt(String sql,Object...params) throws SQLException{
	 	Connection conn=DBConnection.getConnection();
    	QueryRunner queryRunner=new QueryRunner();
	    int i=0;
	    i=queryRunner.query(conn, sql, new ResultSetHandler<Integer>(){
			@Override
			public Integer handle(ResultSet rs) throws SQLException {
				if(rs.next()){
					return rs.getInt(1);		
				}
				return 0;
			}
	    }, params);
	   DBConnection.closeConnection();
	return i;
	   
   }
   /*
    * 统一查询返回一个Map集合
    */
   public Map<String,Object> findAllForMap(String sql,Object...params) throws SQLException{
	   Connection conn=DBConnection.getConnection();
   	   QueryRunner queryRunner=new QueryRunner();
       Map<String,Object> map=queryRunner.query(conn, sql, new MapHandler(), params);
       DBConnection.closeConnection();//打点调用
	   return map;
   }

	/**
	 * 统一查询返回一个实体对象
	 */
	public  <T> T queryEntity(Class<T> clazz,String sql,Object...params) throws SQLException {
		//获取连接
		Connection conn = DBConnection.getConnection();
		QueryRunner run = new QueryRunner();
		T entity = run.query(conn,sql,new BeanHandler<T>(clazz),params);
		DBConnection.closeConnection();
		return entity;
	}

	/**
	 * 统一查询返回一个List集合的实体对象
	 */
	public <T> List<T> queryListEntity(Class<T> clazz,String sql,Object...params) throws SQLException {
		Connection conn = DBConnection.getConnection();
		QueryRunner run = new QueryRunner();
		List<T> list = run.query(conn,sql,new BeanListHandler<T>(clazz),params);
		DBConnection.closeConnection();
		return list;
	}
	/**
	 * 封装一个增、删、改方法，返回int类型
	 */
    public int executeUpdate(String sql,Object...params) throws SQLException {
		Connection conn = DBConnection.getConnection();
		QueryRunner run = new QueryRunner();
		int result = run.update(conn,sql,params);
		DBConnection.closeConnection();
    	return result;
	}


}

使用方式dao层：

public class DaoImpl extends JdbcUtils implements Dao {
    @Override
    public int add(String dept_full_name, String dept_short_name, String dept_father, String dept_address, String common, String remark, Integer start) throws SQLException {
        String sql = "INSERT INTO sys_dept(dept_full_name, dept_short_name, dept_father, dept_address, common, remark, start) \n" +
                "VALUES (?, ?, ?, ?, ?, ?, ?);";
        Object[] param = {dept_full_name, dept_short_name, dept_father, dept_address, common, remark, start};

        return super.executeUpdate(sql, param);
    }
}

依赖

    <!--Mysql数据库JDBC依赖-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.40</version>
    </dependency>
    <!--dbutils -->
    <dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
      <version>1.7</version>
    </dependency>