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