数据库连接池:管理数据库的连接,可以重复使用连接,这里的关闭连接只是把连接归还,并不销毁。
常见的数据库连接池有三个:DBCP连接池、C3P0连接池、Druid连接池(阿里)。
创建数据表
CREATE DATABASE db5 CHARACTER SET utf8;
USE db5;
#创建员工表
CREATE TABLE employee (
eid INT PRIMARY KEY AUTO_INCREMENT ,
ename VARCHAR (20),
age INT ,
sex VARCHAR (6),
salary DOUBLE ,
empdate DATE
);
#插入数据
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李清照',22,'女',4000,'2018-11-12');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'林黛玉',20,'女',5000,'2019-03-14');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'杜甫',40,'男',6000,'2020-01-01');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李白',25,'男',3000,'2017-10-01');
DBCP连接池
编写DBCP工具类
public class DBCPUtils {
// 1. 定义常量(保存数据库连接信息)
public static final String drivername = "com.mysql.cj.jdbc.Driver";
public static final String url = "jdbc:mysql:///db6?characterEncoding=UTF-8";
public static final String username = "root";
public static final String pwd = "password";
// 2. 创建连接池对象(由DBCP提供的实现类)
public static BasicDataSource data_source= new BasicDataSource();
// 3. 使用静态代码块进行配置
static{
data_source.setDriverClassName(drivername);
data_source.setUsername(username);
data_source.setPassword(pwd);
data_source.setUrl(url);
}
// 4. 获取链接方法
public static Connection getConnection() throws Exception {
Connection conn = data_source.getConnection();
return conn;
}
// 5. 释放资源方法
public static void close(Connection conn, Statement stmt) throws Exception {
if (stmt != null&&conn!=null) {
stmt.close();
conn.close();
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs) throws Exception {
if (stmt != null&&conn!=null&&rs!=null) {
rs.close();
stmt.close();
conn.close();
}
}
}
DBCP测试
public class TestDBCP {
// 测试DBCP
public static void main(String[] args) throws Exception{
// 从DBCP获取链接
Connection conn = DBCPUtils.getConnection();
// 获取Statement对象
Statement stmt = conn.createStatement();
// 查询所有员工姓名
String sql = "select ename from employee0";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("ename");
System.out.println(name);
}
// 释放资源
DBCPUtils.close(conn, stmt, rs);
}
}
参数配置
常见配置项 | 描述 |
driverClassName | 数据库驱动名称 |
url | 数据库地址 |
username | 登陆用户名 |
password | 密码 |
maxActive | 最大连接数量 |
maxIdle | 最大空闲连接 |
minIdle | 最小空闲连接 |
initialSize | 初始化连接数目 |
C3P0连接池
C3P0要通过XML来配置信息。
public class C3P0Utils {
// 1. 创建连接池对象
// 使用的配置是配置文件中的默认配置(c3p0-config中默认的)
// public static ComboPooledDataSource datasource = new ComboPooledDataSource();
// 使用指定配置
public static ComboPooledDataSource datasource = new ComboPooledDataSource("mysql");
// 获取链接方法
public static Connection getConnection() throws Exception {
return datasource.getConnection();
}
// 释放资源
public static void close(Connection conn, Statement stmt) throws Exception {
if (stmt != null&&conn!=null) {
stmt.close();
conn.close();
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs) throws Exception {
if (stmt != null&&conn!=null&&rs!=null) {
rs.close();
stmt.close();
conn.close();
}
}
C3P0测试
public class TestC3P0 {
// 查询姓名为‘李白’的记录
public static void main(String[] args) throws Exception {
// 获取链接
Connection conn = C3P0Utils.getConnection();
// 获取预处理对象
String sql = "select * from employee0 where ename = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
// 设置占位符的值
pstmt.setString(1,"李白");
// 执行查询
ResultSet rs = pstmt.executeQuery();
//处理结果
while (rs.next()) {
int id = rs.getInt("eid");
String name = rs.getString("ename");
int age = rs.getInt("age");
String sex = rs.getString("sex");
int salary = rs.getInt("salary");
Date date = rs.getDate("empdate");
System.out.println(id + " " + name + " " + age + " "+sex + " " + salary + " " + date.getTime());
}
// 释放资源
C3P0Utils.close(conn,pstmt,rs);
}
}
Druid连接池
同样是使用配置文件,不过用的不是XML,用的properties。
public class DruidUtils {
// 定义成员变量
public static DataSource datasource;
// 静态代码块
static{
try {
// 1. 创建属性集对象
Properties props = new Properties();
// 2. Druid不能主动加载配置文件,要手动指定文件位置,加载
InputStream inputStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
// 3. Properties对象的load方法,从字节流中读取配置信息
props.load(inputStream);
// 4. 传入工厂类,获取连接池对象
datasource = DruidDataSourceFactory.createDataSource(props);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接的方法
public static Connection getConnection() {
try {
return datasource.getConnection();
} catch (SQLException e) {
return null;
}
}
// 释放连接方法
public static void close(Connection conn, Statement stmt) throws Exception {
if (stmt != null&&conn!=null) {
stmt.close();
conn.close();
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs) throws Exception {
if (stmt != null&&conn!=null&&rs!=null) {
rs.close();
stmt.close();
conn.close();
}
}
}
Druid测试
public class TestDruid {
// 查询薪资在3000-5000间员工的姓名
public static void main(String[] args) throws Exception {
// 获取链接
Connection conn = DruidUtils.getConnection();
// 获取Statement
Statement stmt = conn.createStatement();
String sql = "select ename from employee0 where salary between 3000 and 5000";
ResultSet rs = stmt.executeQuery(sql);
// 处理结果
while (rs.next()) {
String name = rs.getString("ename");
System.out.println(name);
}
// 释放资源
DruidUtils.close(conn,stmt,rs);
}
}