# MySqlPool

**Repository Path**: tiancai69/my-sql-pool

## Basic Information

- **Project Name**: MySqlPool
- **Description**: C++封装的数据库连接池
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 2
- **Forks**: 0
- **Created**: 2024-05-22
- **Last Updated**: 2025-04-08

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# C++Mysql连接池

数据库服务器就需要为每次连接创建一次数据库连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、拓机。
数据库连接是一种关键的有限的昂贵的资源,对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性,影响到程序的性能指标.数据库连接池正式针对这个问题提出来的.数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。

![image text](https://gitee.com/tiancai69/my-sql-pool/raw/master/image/1.png)

1.环境配置

包含目录和库目录

![image text](https://gitee.com/tiancai69/my-sql-pool/raw/master/image/2.png)

附加依赖项

![image text](https://gitee.com/tiancai69/my-sql-pool/raw/master/image/3.png)

将mysqlcppconn8-2-vs14.dll和mysqlcppconn-9-vs14.dll拷贝到项目目录下

在生成后事件添加

xcopy $(ProjectDir)config.ini  $(SolutionDir)$(Platform)\$(Configuration)\   /y
xcopy $(ProjectDir)*.dll   $(SolutionDir)$(Platform)\$(Configuration)\   /y

代码：

```
class Defer{
public:
	Defer(std::function<void()> fun):fun_(fun){}
	~Defer() { 
		fun_();
	}
	std::function<void()> fun_;
};

class SqlConnection {
public:
	SqlConnection(sql::Connection* con,std::int64_t lasttime):_con(con),_last_oper_time(lasttime) {};
	std::unique_ptr <sql::Connection> _con;
	std::int64_t _last_oper_time;
};
class MySqlPool {
public:
	MySqlPool(const std::string& url,const std::string& user,const std::string& passwd,const std::string& schema,int poolSize):
	url_(url),user_(user),passwd_(passwd),schema_(schema),b_stop_(false),poolSize_(poolSize){
		try{
			for (int i = 0; i < poolSize_; ++i) {
				//生成驱动
				sql::mysql::MySQL_Driver* driver = sql::mysql::get_driver_instance();
				auto* con = driver->connect(url_,user_,passwd_);
				std::cout << "连接成功" << std::endl;
				con->setSchema(schema_);
				//获取当前时戳
				auto currentTime = std::chrono::system_clock::now();
				auto duration = currentTime.time_since_epoch();
				auto seconds = std::chrono::duration_cast<std::chrono::seconds>(duration).count();
				pool_.push(std::make_unique<SqlConnection>(con,seconds));
			}
			//注意捕获this
			_check_thrad = std::thread([this]() {
				while(!b_stop_){}
				checkConnection();
					std::this_thread::sleep_for(std::chrono::seconds(60));
				});
			_check_thrad.detach();
		}
		catch (sql::SQLException& e) {
			std::cout << "mysql pool init failed, error is " << e.what() << std::endl;
		}
	};
	void checkConnection() {
		std::lock_guard<std::mutex> guard(mutex_);
		int poolSize = pool_.size();
		auto currentTime = std::chrono::system_clock::now();
		auto duration = currentTime.time_since_epoch();
		auto seconds = std::chrono::duration_cast<std::chrono::seconds>(duration).count();
		for (int i = 0; i < poolSize; i++) {
			auto con = std::move(pool_.front());
			pool_.pop();
			Defer defer([this,&con]() {
				pool_.push(std::move(con));
				});
			if (seconds - con->_last_oper_time < 10) {
				continue;
			}
			try {
				std::unique_ptr<sql::Statement> stmt(con->_con->createStatement());
				stmt->executeQuery("select 1");
				con->_last_oper_time = seconds;
			}
			catch(sql::SQLException& e){
				std::cout << "Error keep connection live" << std::endl;
				sql::mysql::MySQL_Driver* driver = sql::mysql::get_driver_instance();
				auto* newcon = driver->connect(url_, user_, passwd_);
				newcon->setSchema(schema_);
				con->_con.reset(newcon);
				con->_last_oper_time = seconds;
			}
		}
	}
	std::unique_ptr<SqlConnection> getConnection() {
		std::unique_lock<std::mutex> lock(mutex_);
		cond_.wait(lock, [this] {
			if (!b_stop_) {
				return true;
			}
			return !pool_.empty();
			});
		if (b_stop_) {
			return nullptr;
		}
		std::unique_ptr<SqlConnection> con(std::move(pool_.front()));
		pool_.pop();
		return con;
	}
	void returnConnection(std::unique_ptr<SqlConnection> con) {
		std::unique_lock<std::mutex> lock(mutex_);
		if (b_stop_) {
			return;
		}
		pool_.push(std::move(con));
		cond_.notify_one();
	}
	void Close() {
		b_stop_ = true;
		cond_.notify_all();
	}
	~MySqlPool() {
		std::unique_lock<std::mutex> lock(mutex_);
		if (!pool_.empty()) {
			pool_.pop();
		}
	}
private:
	std::string url_;
	std::string user_;
	std::string passwd_;
	std::string schema_;
	int poolSize_;
	std::atomic<bool> b_stop_;
	std::queue<std::unique_ptr<SqlConnection>> pool_;
	std::mutex mutex_;
	std::condition_variable cond_;
	std::thread _check_thrad;
};

```

测试

![image text](https://gitee.com/tiancai69/my-sql-pool/raw/master/image/4.png)

成功建立五个连接