【数据库连接池】DBCP

数据库连接池概述

在JDBC编程中，每次创建和断开Connection对象都会消耗一定的时间和IO资源。 这是因为在Java程序与数据库之间建立连接时，数据库端要验证用户名和密码并为该连接分配资源，而程序则要把代表连接Connection对象等加载到内存中，所以建立数据库连接的开销很大。尤其是在大量的并发访问时，频繁地创建、断开数据库 连接势必会影响数据库的访问效率，甚至导致数据库崩溃。

为了解决该类问题的发生诞生了数据库连接池技术。数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用现有的数据库连接，而不是每次都重新建立连接。

DBCP连接池

第一步添加jar包

• commons-dbcp.jar
• commons-pool.jar
• mysql-connector-java-5.0.8-bin.jar

第二步：在src下创建DBCP的配置文件dbcpconfig.properties

#
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test0222
username=root
password=root#
initialSize=10#
maxActive=50#
maxIdle=20#
minIdle=5#
maxWait=50000#
connectionProperties=useUnicode=true;characterEncoding=utf8##
defaultAutoCommit=true#
defaultTransactionIsolation=REPEATABLE_READ

第三步：编写操作DBCP的工具类DBCPUtil

package com.etime1;import org.apache.commons.dbcp.BasicDataSourceFactory;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;public class DBCPUtil1 {private static DataSource dataSource = null;//创建数据库连接池static{Properties properties = new Properties();try {ClassLoader classLoader=DBCPUtil1.class.getClassLoader();properties.load(classLoader.getResourceAsStream("dbcpconfig.properties"));dataSource = BasicDataSourceFactory.createDataSource(properties);} catch (Exception e) {throw new ExceptionInInitializerError("DBCP始化错误，请检查配置文件");}}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;}}public static DataSource getDataSource(){return dataSource;}
}

第四步：使用DBCP

(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;}@Overridepublic String toString() {return "[id=" + id + ", username=" + username + ", password=" + password + ", email=" + email+ ", birthday=" + birthday + "]";}
}

(3)、在JDBC中使用DBCP

package com.etime1;import com.etime.DBCPUtil;import java.sql.*;public class Test01 {public static void main(String[] args) throws Exception {getSelect();}public static void getSelect() throws Exception{Connection connection = DBCPUtil1.getConnection();String sql = "select * from membershipcard";PreparedStatement preparedStatement = connection.prepareStatement(sql);ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()){int id = resultSet.getInt("id");String username = resultSet.getString("username");String password = resultSet.getString("password");String email = resultSet.getString("email");Date birthday = resultSet.getDate("birthday");Membershipcard membershipcard = new Membershipcard(id,username,password,email,birthday);System.out.println(membershipcard);}}
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！