数据库连接池
介绍
概念
- 一个容器(集合),存放数据库连接
- 在系统初始化时创建
- 按需请求连接,用完返回
好处:
避免频繁创建和销毁连接对象,提供用户访问速度
标准接口:
javax.sql包下的DataSource类:
- getConection:获取连接对象
- Connection.close():归还连接
数据库厂商:c3p0和druid(阿里巴巴)
注意:在使用这两者的时候都需要导入驱动jar包
c3p0
1)引入jar包(两个)
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.12</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
2)定义配置文件:c3p0.properties或者c3p0-config.xml
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="user">root</property>
<property name="password">3145tj</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/study?useSSL=false&serverTimezone=UTC&characterEncoding=utf8</property>
</default-config>
</c3p0-config>
3)创建核心对象ComboPooledDataSource
4)获取连接
DataSource dataSource = new ComboPooledDataSource();
Connection connection = dataSource.getConnection();
String sql = "select * from tb_user";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getInt(1)+",账号:"+resultSet.getString(2)+",密码:"+resultSet.getString(3));
}
resultSet.close();
ps.close();
connection.close();
((ComboPooledDataSource) dataSource).close();
druid
1)引入jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
2)定义配置文件:properties格式
username=root
password=3145tj
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/study?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
3)加载配置文件
4)获取数据库连接池对象
5)获取连接
Properties properties = new Properties();
InputStream inputStream = DruidTest.class.getClassLoader().getResourceAsStream("db.properties");
properties.load(inputStream);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
String sql = "select * from tb_user";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getInt(1)+",账号:"+resultSet.getString(2)+",密码:"+resultSet.getString(3));
}
resultSet.close();
ps.close();
connection.close();