DBUtils和DBCP连接池

连接池用具类

public class MyDbUtils {
    /*静态常量*/

    /**驱动包**/
    private final static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";

    /**连接路径url**/
    private final static String URL = "jdbc:mysql://127.0.0.1:3306/java0723?characterEncoding=UTF-8";

    /**用户名**/
    private final static String USERNAME = "root";

    /**密码**/
    private final static String PASSWORD = "123456";

    /**创建连接池**/
    public static BasicDataSource dataSource = new BasicDataSource();

    //静态代码块
    static {
        //对连接池对象 进行基本的配置
        /*这是要连接的数据库的驱动*/
        dataSource.setDriverClassName(DRIVER_CLASS_NAME);
        /*指定要连接的数据库地址*/
        dataSource.setUrl(URL);
        /*指定要连接数据的用户名*/
        dataSource.setUsername(USERNAME);
        /*指定要连接数据的密码*/
        dataSource.setPassword(PASSWORD);
        /*设置最大连接数*/
        dataSource.setMaxActive(10);
    }

    /*获取连接池对象*/
    public static DataSource getDataSource(){
        return dataSource;
    }

    /*获取单个连接对象*/
    public static Connection getConn(){
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

 

Dao层

public class UserDao {

    /*新增数据*/
    public void add(Users user) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "insert into user (uname,pwd) values (?,?)";
        QueryRunner qr = new QueryRunner();
        int count = qr.update(conn, sql, user.getUname(), user.getPwd());
        System.out.println(count);
        conn.close();
    }

    /*更改数据*/
    public void update(Users user) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "update user set uname=?,pwd=? where uid=?";
        QueryRunner qr = new QueryRunner();
        int count = qr.update(conn, sql, user.getUname(), user.getPwd(), user.getUid());
        System.out.println(count);
        conn.close();
    }

    /*删除数据*/
    public void del(Users user) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "delete from user where uid=?";
        QueryRunner qr = new QueryRunner();
        int count = qr.update(conn, sql, user.getUid());
        System.out.println(count);
        conn.close();
    }

    /*获取结果集的第一条数据*/
    public Object[] get1() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from user";
        QueryRunner qr = new QueryRunner();
        Object[] query = qr.query(conn, sql, new ArrayHandler());
        conn.close();
        return query;
    }

    /*把查询的所有数据返回一个object类型的list集合*/
    public List<Object[]> get2() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from user";
        QueryRunner qr = new QueryRunner();
        List<Object[]> query = qr.query(conn, sql, new ArrayListHandler());
        conn.close();
        return query;
    }

    /*获取结果集的第一条数据并封装一个实例类*/
    public Users get3() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from user";
        QueryRunner qr = new QueryRunner();
        Users query = qr.query(conn, sql, new BeanHandler<Users>(Users.class));
        conn.close();
        return query;
    }

    /*获取结果集的并封装一个实例类,并加到list集合中*/
    public List<Users> get4() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from user";
        QueryRunner qr = new QueryRunner();
        List<Users> query = qr.query(conn, sql, new BeanListHandler<Users>(Users.class));
        conn.close();
        return query;
    }

    /*获取指定类的结果集,并封装到一个list集合中*/
    public List<String> get5() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        /*第一种*/
        /*String sql = "select uname from user";*/
        /*第二种*/
        String sql = "select * from user";
        QueryRunner qr = new QueryRunner();
        /*第一种*/
        /*List<String> query = qr.query(conn, sql, new ColumnListHandler<String>());*/
        /*第二种*/
        List<String> query = qr.query(conn, sql, new ColumnListHandler<String>("uname"));
        conn.close();
        return query;
    }

    /*查询一个参数，并返回一个object类型*/
    public Object get6() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select uname FROM `user` where uid = 6";
        QueryRunner qr = new QueryRunner();
        Object query = qr.query(conn, sql, new ScalarHandler<>(1));
        conn.close();
        return query;
    }

    /*查取结果集的一个条数据,封装为map集合中*/
    public Map<String, Object> get7() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select * FROM `user`";
        QueryRunner qr = new QueryRunner();
        Map<String, Object> query = qr.query(conn, sql, new MapHandler());
        conn.close();
        return query;
    }

    /*查取结果集所有数据,封装为map集合中,并加载到list集合中*/
    public List<Map<String, Object>> get8() throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "select * FROM `user`";
        QueryRunner qr = new QueryRunner();
        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler());
        conn.close();
        return query;
    }

    /*连接池*/
    public List<Map<String, Object>> get9() throws SQLException {
        String sql = "select * FROM `user`";
        /*创建 QueryRunner 对象是 传入一个 连接池对象*/
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
        return query;
    }
}