数据库连接池概述
在JDBC编程中,每次创建和断开Connection对象都会消耗一定的时间和IO资源。 这是因为在Java程序与数据库之间建立连接时,数据库端要验证用户名和密码并为该连接分配资源,而程序则要把代表连接Connection对象等加载到内存中,所以建立数据库连接的开销很大。尤其是在大量的并发访问时,频繁地创建、断开数据库 连接势必会影响数据库的访问效率,甚至导致数据库崩溃。
为了解决该类问题的发生诞生了数据库连接池技术。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用现有的数据库连接,而不是每次都重新建立连接。
————————————————
版权声明:本文为CSDN博主「代码坤」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_65168354/article/details/129168808
C3P0连接池
第一步:添加jar包
- c3p0-0.9.1.2.jar
- mysql-connector-java-5.0.8-bin.jar
第二步:在src下创建C3P0的配置文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test0222</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">15</property>
<property name="maxIdleTime">40</property>
<property name="maxPoolSize">150</property>
<property name="minPoolSize">20</property>
</default-config>
</c3p0-config>
第三步:编写操作C3P0的工具类C3P0Util
package com.etime1;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3P0Util {
//创建数据库连接池
private static DataSource dataSource = new ComboPooledDataSource();
public static DataSource getDataSource(){
return dataSource;
}
//创建连接
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败");
}
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
第四步:使用C3P0
(1)、在数据库中建立membershipcard表并插入数据
mysql> CREATE TABLE membershipcard (
id int primary key auto_increment,
username varchar(40),
password varchar(40),
email varchar(40),
birthday date
);
-- 插入数据
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("lili","abc123","lili@sina.com","1999-08-14");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("koko","efg456","koko@sohu.com","1998-07-15");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("mbmb","mnb333","mbmb@sina.com","1997-06-16");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("zxzx","poi666","zxzx@sohu.com","1996-05-17");
(2)、编写JavaBean即Membershipcard
package com.etime1;
import java.util.Date;
public class Membershipcard {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public Membershipcard() {
}
public Membershipcard(int id, String username, String password, String email, Date birthday) {
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.birthday = birthday;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "[id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
+ ", birthday=" + birthday + "]";
}
}
(3)、在JDBC中使用C3P0
package com.etime1;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Test02 {
public static void main(String[] args) throws Exception {
getSelect();
}
public static void getSelect() throws Exception{
Connection connection = C3P0Util.getConnection();
String sql = "select * from membershipcard";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt(1);
String username = resultSet.getString(2);
String password = resultSet.getString(3);
String email = resultSet.getString(4);
Date birthday = resultSet.getDate(5);
Membershipcard membershipcard = new Membershipcard(id, username, password, email, birthday);
System.out.println(membershipcard);
}
}
}