DBCP数据库连接池的简单使用
DBCP简介
DBCP(DataBase connection pool)数据库连接池是 apache 上的一个Java连接池项目。DBCP通过连接池预先同数据库建立一些连接放在内存中(即连接池中),应用程序需要建立数据库连接时直接到从接池中申请一个连接使用,用完后由连接池回收该连接,从而达到连接复用,减少资源消耗的目的。
配置过程
1.编写META-INF下的context.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/bkPool" auth="Container" type="javax.sql.DataSource"
username="root" password="123456" driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/bk?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"
maxActive="100" maxIdle="30" maxWait="10000"/>
</Context>
2.拷贝jar包
将mysql-connector-java-8.0.11.jar拷贝至tomcat lib目录下,例如D:\apache-tomcat-9.0.39\lib\
3.创建DbUtil类
相比DbUtil我更习惯命名为DBFactory
package com.icss.util;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLNonTransientConnectionException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class DbFactory {
public static final String dbJndi = "java:comp/env/jdbc/bkPool";
private static ThreadLocal<Connection> tl = new ThreadLocal<>();
private static DataSource ds;
static {
try {
InitialContext context = new InitialContext();
ds = (DataSource)context.lookup(dbJndi) ;
Log.logger.info("创建dataSource对象.....");
} catch (Exception e) {
Log.logger.error(e.getMessage(),e);
}
}
/**
* 获取数据库连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
* @throws SQLNonTransientConnectionException
*/
public static Connection openConnection() throws Exception {
Connection conn = tl.get(); //先查看是否有已打开的数据库连接
if(conn == null || conn.isClosed()) {
conn = ds.getConnection();
Log.logger.info(Thread.currentThread().getId() + "从连接池获取数据库连接...");
tl.set(conn); //把已打开的数据库连接,存入tl
}else {
Log.logger.info(Thread.currentThread().getId() + "使用已有的数据库连接...");
}
return conn;
}
public static void closeConnection() {
Log.logger.info(Thread.currentThread().getId() + "关闭数据库连接...");
Connection conn = tl.get();
tl.set(null);
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
Log.logger.error(e.getMessage(),e);
}
}
}
public static void beginTransaction() throws Exception {
Log.logger.info(Thread.currentThread().getId() + "开启事务...");
Connection conn = openConnection();
if(conn != null) {
conn.setAutoCommit(false);
}else {
Log.logger.info(Thread.currentThread().getId() + "事务开启失败...");
}
}
/**
* 回滚事务
* @throws SQLException
*/
public static void rollback() throws Exception {
Log.logger.info(Thread.currentThread().getId() + "事务回滚...");
Connection conn = tl.get();
if(conn != null) {
conn.rollback();
}
}
/**
* 提交事务
* @throws SQLException
*/
public static void commit() throws Exception {
Log.logger.info(Thread.currentThread().getId() + "事务提交...");
Connection conn = tl.get();
if(conn != null) {
conn.commit();
}
}
}
其他注意事项
- 如果原先项目中有db.properties文件,需要删除;
- 如果原先项目中引入了mysql-connector包,需要删除