Java第二十二天：DBUtils轻量级的ORM和c3p0连接池

最新推荐文章于 2024-09-08 10:40:56 发布

yuemmm

最新推荐文章于 2024-09-08 10:40:56 发布

阅读量272

点赞数

文章标签： java jdbc

本文链接：https://blog.csdn.net/yuemmm/article/details/104974201

版权

文章目录

- - DBUtils轻量级的ORM（封装好的框架）
  - c3p0数据库的连接池

DBUtils轻量级的ORM（封装好的框架）

ORM:就是增删改查

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。

org.apache.commons.dbutils
org.apache.commons.dbutils.handlers
org.apache.commons.dbutils.wrappers

作用：DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。

https://www.jianshu.com/p/4bfb8266eaa0

public class TestDBUtils extends BaseDao {
    @Test
    public void findOne () throws SQLException {
        //1.把数据库连接对象搞出来
        Connection connection = JdbcUtil.getConnection();
        //2.创建DBUtils的核心类
        QueryRunner queryRunner = new QueryRunner();
        //3.写一个sql语句
        String sql = "select * from people where id = 1";
        //4.直接拿核心类对象进行查询操作
        //ResultSetHandler
        People p = queryRunner.query(connection, sql, new ResultSetHandler<People>() {
            @Override
            public People handle(ResultSet resultSet) throws SQLException {
                //结果集
                People people = null;
                if (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    people = new People(id, name, age);

                }
                return people;
            }
        });
        System.out.println(p);

    }

    @Test
    public void findOne2 () throws SQLException {
        Connection connection = JdbcUtil.getConnection();

        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from people where id = 1";
        People query = queryRunner.query(connection, sql, new BeanHandler<People>(People.class));
        System.out.println(query);
    }
    @Test
    public void findOne3 () throws SQLException {
        Connection connection = JdbcUtil.getConnection();

        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from people where id = ?";
        People query = queryRunner.query(connection, sql, new BeanHandler<People>(People.class), 1);
        System.out.println(query);
    }

    @Test
    public void findAll () throws SQLException {
        Connection connection = JdbcUtil.getConnection();

        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from people ";
        List<People> query = queryRunner.query(connection, sql, new BeanListHandler<People>(People.class));
        for (People people : query) {
            System.out.println(people);
        }


    }
    @Test
    public void findAll1 () throws SQLException {
        Connection connection = JdbcUtil.getConnection();

        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from people ";
        Map<Integer, People> map = queryRunner.query(connection, sql, new BeanMapHandler<Integer, People>(People.class));
        //变成实体集合
        Set<Map.Entry<Integer, People>> entries = map.entrySet();
        for (Map.Entry<Integer, People> entry : entries) {
            System.out.println(entry);
        }


    }

在JdbcUtils和BaseDao基础使用DBUtils再次封装

/*
    * 是单例模式
    * */
    public static QueryRunner getQueryRunner () {
        synchronized (JdbcUtil.class) {
            if (qr == null) {
                qr = new QueryRunner();
            }
        }
        return qr;
    }

public class BaseDao {
    /*
    * update 是增删改
    *@param   需要执行的sql语句
    *@param   对应sql语句的一个参数数组
    * */
    public int update (String sql, Object [] parameters) throws SQLException {
        if (sql == null) {
            throw new SQLException("SQL is null");
        }
        //1.获取连接数据库的对象
        Connection connection = JdbcUtil.getConnection();
        //2.获取DButils核心对象
        QueryRunner queryRunner = JdbcUtil.getQueryRunner();
        //3.执行sql语句
        int i = queryRunner.update(connection, sql, parameters);
        JdbcUtil.close(connection);

        return i;
    }
    /*
    * 通用的查询数据库的方法
    * 形式参数的列表
    *   sql:需要执行的sql语句
    *   Object : 对应查询sql语句参数数组
    *   Class<T> cls：
    *          1.需要使用泛型
    *           2.会反射一个具体的类
    *
    * */
    public <T> List<T> query (String sql, Object[] parameters, Class<T> cls) throws SQLException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        if (sql == null) {
            throw new SQLException("sql is null");
        }
        //1.获取数据库的连接对象
        Connection connection = JdbcUtil.getConnection();
        //2.获取queryruner对象
        QueryRunner queryRunner = JdbcUtil.getQueryRunner();
        //3.执行sql语句
        List<T> ts = queryRunner.query(connection, sql, new BeanListHandler<T>(cls), parameters);

        JdbcUtil.close(connection);

        return ts.size()!= 0 ? ts : null ;
    }
}

c3p0数据库的连接池

3P0连接池配置
    数据库连接是一个耗费大量资源且相当慢的操作，所以为了提高性能和连接速度，诞生了连接池这样的概念。
    在多用户并发操作过程中，连接池尤为重要。
    它是将那些已连接的数据库连接存放在一个容器里（连接池），这样以后别人要连接数据库的时候，将不会重新建立数据库连接，会直接从连接池里取出可用的连接，用户使用完毕后，连接又重新还回到连接池中。
    注意：连接池里的连接将会一直保存在内存里，即使你没用也是一样。所以这个时候你得权衡一下连接池的连接数量了。



每一次打开数据库，执行sql语句，关闭数据库的资源
会增加数据库的负担


这里需要一个xml文件


使用c3p0步骤：
	1.导包两个包c3p0-0.9.2.1.jar   mchange-commons-java-0.2.11.jar
	
	2.把xml文件复制到src下面，注意配置信息，一定要对应好
	
	3.在封装好的JdbcUtils  把那个静态代码块替换成成pool

public class JdbcUtil {

    private static QueryRunner qr = null;
    //xml放在 src下面
    private static ComboPooledDataSource pool = new ComboPooledDataSource();
    /*
    * 是单例模式
    * */
    public static QueryRunner getQueryRunner () {
        synchronized (JdbcUtil.class) {
            if (qr == null) {
                qr = new QueryRunner();
            }
        }
        return qr;
    }

    //自己封装  connection这个函数
    /*
    * 返回的是Connection这个对象
    * */
    public static Connection getConnection () {
        Connection connection = null;
        try {
            connection = pool.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;

    }
    /*
    * 关闭数据库的各种连接资源
    * */
    public static void close(Connection connection) throws SQLException {
//        if (connection != null) {
//            connection.close();
//        }
        close(connection, null,null);
    }

    public static void close(Connection connection, Statement statement) throws SQLException {
        close(connection, statement, null);

    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }
    }

}

SQL语句

mysql> create table user (
    -> id int primary key auto_increment,
    -> name varchar(30) not null,
    -> roleId int not null
    -> );
    
mysql> create table role (
    -> id int primary key auto_increment,
    -> roleName varchar(20) not null
    -> );
Query OK, 0 rows affected (0.12 sec)


mysql> create table privilege (
    -> id int primary key auto_increment,
    -> pName varchar(20) not null
    -> );
Query OK, 0 rows affected (0.10 sec)


mysql> create table role_privilege (
    -> id int primary key auto_increment,
    -> roleId int not null,
    -> pId int not null
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> insert into user (name, roleId) values ("张三", 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into user (name, roleId) values ("李四", 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into role (roleName) values ("管理员");
Query OK, 1 row affected (0.02 sec)

mysql> insert into role (roleName) values ("普通用户");
Query OK, 1 row affected (0.01 sec)

mysql> insert into privilege (pName) values("删除用户");
Query OK, 1 row affected (0.01 sec)

mysql> insert into privilege (pName) values("添加用户");
Query OK, 1 row affected (0.01 sec)

mysql> insert into privilege (pName) values("修改用户");
Query OK, 1 row affected (0.01 sec)

mysql> insert into privilege (pName) values("查看用户");
Query OK, 1 row affected (0.02 sec)

mysql> insert into privilege (pName) values("查看所有用户");
Query OK, 1 row affected (0.01 sec)

mysql> insert into role_privilege (roleId, pId) values (1,1)；
Query OK, 1 row affected (0.01 sec)

mysql> insert into role_privilege (roleId, pId) values (1,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into role_privilege (roleId, pId) values (1,3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into role_privilege (roleId, pId) values (1,4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into role_privilege (roleId, pId) values (1,5);
Query OK, 1 row affected (0.04 sec)

mysql> insert into role_privilege (roleId, pId) values (2,3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into role_privilege (roleId, pId) values (2,4);
Query OK, 1 row affected (0.02 sec)


1.找到张三的权限
select u.name, p.pName
from user u
inner join role r
on u.roleId = r.id
inner join role_privilege rp
on r.id = rp.roleId
inner join privilege p
on rp.pId = p.id
where u.name = "张三"；


mysql> select user.name , privilege.pName
    -> from user, role, privilege, role_privilege
    -> where user.roleId = role.id and role.id = role_privilege.roleId and privi
lege.id = role_privilege.pId and user.name = "张三";

希望大家关注我一波,防止以后迷路,有需要的可以加我Q讨论互相学习java ,学习路线探讨,经验分享与java Q:2415773436

yuemmm

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java第二十二天：DBUtils轻量级的ORM和c3p0连接池

文章目录DBUtils轻量级的ORM（封装好的框架）c3p0数据库的连接池DBUtils轻量级的ORM（封装好的框架）ORM:就是增删改查Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。org.apache.commons.dbutilsorg.apache.commons....
复制链接

扫一扫