Database connection pool demo

1. What is singleton mode
be careful:

1. A singleton class can only have one instance.
2. A singleton class must create its own unique instance.
3. A singleton class must provide this instance to all other objects.
Intent: ensure that a class has only one instance and provide a global access point to access it.
Main solution: a globally used class is frequently created and destroyed.
When to use: when you want to control the number of instances and save system resources.
How to solve it: judge whether the system already has this single instance. If yes, it will be returned. If not, it will be created.
Key code: constructors are private.

advantage:
1. There is only one instance in memory, which reduces the memory overhead, especially the frequent creation and destruction of instances (such as the homepage page cache of the school of management).
2. Avoid multiple occupation of resources (such as writing files).
Ensure that there is only one instance of a class. Why would anyone want to control the number of instances a class has? The most common reason is to control access to certain shared resources, such as databases or files.
It works like this: if you create an object and later you decide to create a new object, you will get the previously created object instead of a new object.

The implementation of all singletons includes the following two same steps:

Make the default constructor private to prevent other objects from using the new operator of the singleton class.
Create a new static build method as the constructor. This function "stealthily" calls the private constructor to create the object and save it in a static member variable. After that, all calls to this function will return this cache object.

The concept of applying singleton pattern in database connection pool is to ensure that only one database instance is created. Every time we create different objects, we are facing the same database.

2. Learn about the mysql functions used
(1) MySQL *mysql_init(MYSQL *mysql);

Allocate or initialize MySQL_ real_ MySQL object of connect(). If MySQL is a NULL pointer, the function allocates, initializes and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, then MySQL is called_ Close() releases the object when the connection is closed.

(2)MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)

mysql_real_connect() attempts to build a connection to a MYSQL server running on host. The Client-side program must successfully connect to the server before executing any other API functions that require a valid MYSQL connection handler structure.

For the first parameter, specify the address of the existing MYSQL structure. Calling MYSQL_ real_ Call MYSQL before connect()_ Init() initializes the MYSQL structure.
The value of host can be a host name or an IP address. The Client side tries to connect as follows:

If the host is NULL or the string "localhost", the connection to localhost is assumed:
If port is not 0, this value is used as the port number of the TCP/IP connection. Note that the host parameter determines the type of connection.
If UNIX_ If the socket is not NULL, the string specifies the socket or named pipe to use. Note that the host parameter determines the type of connection.

client_ The value of flag is usually 0, but it can be set to a combination of the following flags to enable some functions:

3. Semaphores, locks, etc. will also be used here, so it was written after the last practice of locking mechanism.
A semaphore is a nonnegative integer, which is a common resource that can be used.

4. Understand the data structure of list
Lists stores elements in a linked list in order. Compared with vectors, it allows fast insertion and deletion, but random access is slow
assign() assigns a value to the list
back() returns the last element
begin() returns an iterator that points to the first element
clear() deletes all elements
empty() returns true if the list is empty
end() returns the iterator at the end
erase() deletes an element
front() returns the first element
get_allocator() returns the configurator of the list
insert() inserts an element into the list
max_size() returns the maximum number of elements that the list can hold
merge() merges two list s
pop_back() deletes the last element
pop_front() deletes the first element
push_back() adds an element to the end of the list
push_front() adds an element to the head of the list
rbegin() returns the inverse iterator pointing to the first element
remove() deletes the element from the list
remove_if() deletes the element according to the specified condition
rend() points to the inverse iterator at the end of the list
resize() changes the size of the list
reverse() inverts the elements of the list
size() returns the number of elements in the list
sort() sorts the list
splice() merges two list s
swap() swaps two list s
unique() deletes duplicate elements in the list

There is a bug in the process of executing the file and it can't run

Two methods were tried:
1. The first method is to change to the root user


2. Use the change permission command

Both failed

Finally, it is found that this is not the problem. We need to introduce mysql library functions and use the locking mechanism, so we introduce the pthread compilation link

#include <stdio.h>
#include <list>
#include <mysql/mysql.h>
#include <error.h>
#include <string.h>
#include <iostream>
#include <string>
#include <stdlib.h>
#include <pthread.h>
#include "./lock.h"

using namespace std;

class connection_pool
{
public:
	MYSQL *GetConnection();				 //Get database connection
	bool ReleaseConnection(MYSQL *conn); //Release connection
	int GetFreeConn();					 //Get connection
	void DestroyPool();					 //Destroy all connections

	//Singleton mode
	static connection_pool *GetInstance();

	void init(string url, string User, string PassWord, string DataBaseName, int Port, int MaxConn); 

private:
	connection_pool();
	~connection_pool();

	int m_MaxConn;  //maximum connection
	int m_CurConn;  //Number of connections currently in use
	int m_FreeConn; //Number of currently idle connections
	locker lock;
	list<MYSQL *> connList; //Connection pool
	sem reserve;

public:
	string m_url;			 //Host address
	string m_Port;		 //Database port number
	string m_User;		 //Login database user name
	string m_PassWord;	 //Login database password
	string m_DatabaseName; //Use database name
};
connection_pool::connection_pool()
{
	m_CurConn = 0;
	m_FreeConn = 0;
}

connection_pool *connection_pool::GetInstance()
{
	static connection_pool connPool;
	return &connPool;
}

//Construct initialization
void connection_pool::init(string url, string User, string PassWord, string DBName, int Port, int MaxConn)
{
	m_url = url;
	m_Port = Port;
	m_User = User;
	m_PassWord = PassWord;
	m_DatabaseName = DBName;
	for (int i = 0; i < MaxConn; i++)
	{
		MYSQL *con = NULL;
		con = mysql_init(con);
		if (con == NULL)
		{
            cout<<"MySQL Error!"<<endl;
			exit(1);
		}
        else
        {
            cout<<"Initialization succeeded"<<endl;
        }
		con = mysql_real_connect(con, url.c_str(), User.c_str(), PassWord.c_str(), DBName.c_str(), Port, NULL, 0);

		if (con == NULL)
		{
            cout<<"MySQL Error!"<<endl;
			exit(1);
		}
        else
        {
            cout<<"Initialization succeeded"<<endl;
        }
		connList.push_back(con);
		++m_FreeConn;
	}

	reserve = sem(m_FreeConn);

	m_MaxConn = m_FreeConn;
}


//When there is a request, return an available connection from the database connection pool and update the number of used and free connections
MYSQL *connection_pool::GetConnection()
{
	MYSQL *con = NULL;

	if (0 == connList.size())
		return NULL;

	reserve.wait();
	
	lock.lock();

	con = connList.front();
	connList.pop_front();

	--m_FreeConn;
	++m_CurConn;

	lock.unlock();
	return con;
}

//Release the currently used connection
bool connection_pool::ReleaseConnection(MYSQL *con)
{
	if (NULL == con)
		return false;

	lock.lock();

	connList.push_back(con);
	++m_FreeConn;
	--m_CurConn;

	lock.unlock();

	reserve.post();
	return true;
}

//Destroy database connection pool
void connection_pool::DestroyPool()
{

	lock.lock();
	if (connList.size() > 0)
	{
		list<MYSQL *>::iterator it;
		for (it = connList.begin(); it != connList.end(); ++it)
		{
			MYSQL *con = *it;
			mysql_close(con);
		}
		m_CurConn = 0;
		m_FreeConn = 0;
		connList.clear();
	}

	lock.unlock();
}

//Number of currently idle connections
int connection_pool::GetFreeConn()
{
	return this->m_FreeConn;
}

connection_pool::~connection_pool()
{
	DestroyPool();
}
int main()
{
    string user = "root";
    string passwd = "root";
    string databasename = "WebServer";
    connection_pool *m_connPool;
    //Initialize database connection pool
    m_connPool = connection_pool::GetInstance();
    m_connPool->init("localhost", user, passwd, databasename, 3306, 5);
    MYSQL *con;
    con=m_connPool->GetConnection();
    cout<<m_connPool->GetFreeConn()<<endl;
    bool res=m_connPool->ReleaseConnection(con);
    if(res)
    {
        cout<<"Release successful"<<endl;
    }
    else
    {
        cout<<"Release failed"<<endl;
    }
    m_connPool->DestroyPool();
    return 0;
}

Hahaha, I finally succeeded in running.

Many errors occurred during the successful operation.
Finally, point out the mysql library functions you downloaded and connect the library functions you need, and then compile them.
The lock mechanism used in this is still the lock.h folder in front. Let's do it again.
Although the operation was successful, it was obvious that my database could not be connected. I'll take a look again.
The corresponding databases WebServer and port are corresponding.

Then after I got the data right, I finally succeeded!

#include <iostream>
#include <exception>
#include <pthread.h>
#include <semaphore.h>

class sem
{
public:
    sem()
    {
        if (sem_init(&m_sem, 0, 0) != 0)
        {
            throw std::exception();
        }
    }
    sem(int num)
    {
        if (sem_init(&m_sem, 0, num) != 0)
        {
            throw std::exception();
        }
    }
    ~sem()
    {
        sem_destroy(&m_sem);
    }
    bool wait()
    {
        return sem_wait(&m_sem) == 0;
    }
    bool post()
    {
        return sem_post(&m_sem) == 0;
    }

private:
    sem_t m_sem;
};
class locker
{
public:
    locker()
    {
        if (pthread_mutex_init(&m_mutex, NULL) != 0)
        {
            throw std::exception();
        }
    }
    ~locker()
    {
        pthread_mutex_destroy(&m_mutex);
    }
    bool lock()
    {
        return pthread_mutex_lock(&m_mutex) == 0;
    }
    bool unlock()
    {
        return pthread_mutex_unlock(&m_mutex) == 0;
    }
    pthread_mutex_t *get()
    {
        return &m_mutex;
    }

private:
    pthread_mutex_t m_mutex;
};
class cond
{
public:
    cond()
    {
        if (pthread_cond_init(&m_cond, NULL) != 0)
        {
            //pthread_mutex_destroy(&m_mutex);
            throw std::exception();
        }
    }
    ~cond()
    {
        pthread_cond_destroy(&m_cond);
    }
    bool wait(pthread_mutex_t *m_mutex)
    {
        int ret = 0;
        //pthread_mutex_lock(&m_mutex);
        ret = pthread_cond_wait(&m_cond, m_mutex);
        //pthread_mutex_unlock(&m_mutex);
        return ret == 0;
    }
    bool timewait(pthread_mutex_t *m_mutex, struct timespec t)
    {
        int ret = 0;
        //pthread_mutex_lock(&m_mutex);
        ret = pthread_cond_timedwait(&m_cond, m_mutex, &t);
        //pthread_mutex_unlock(&m_mutex);
        return ret == 0;
    }
    bool signal()
    {
        return pthread_cond_signal(&m_cond) == 0;
    }
    bool broadcast()
    {
        return pthread_cond_broadcast(&m_cond) == 0;
    }

private:
    //static pthread_mutex_t m_mutex;
    pthread_cond_t m_cond;
};

Tags: C++ Algorithm Back-end

Posted on Tue, 30 Nov 2021 08:39:36 -0500 by spudly