Shulou(Shulou.com)05/31 Report--

What is the implementation of database connection pool based on MysqlConnector/C++? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Introduction to connection pooling:

1.1 Application background:

General applications will access the database, when the program accesses the database, each data access request must go through the following steps: establishing a database connection, opening the database, operating on the data in the database, and closing the database connection. Establishing database connection and opening database is a very resource-consuming and time-consuming work. If this kind of database connection occurs frequently in the system, it will inevitably affect the performance of the system, and even lead to the collapse of the system.

1.2 Technical ideas:

During the system initialization phase, a certain number of database connection objects (Connection) are established and stored in the container defined in the connection pool. When there is a database access request, take a connection from this container in the connection pool. When the connection in the container has been used up and the maximum number of connections defined by the system has not been reached, you can create a new connection. When the number of connections currently in use reaches the maximum number of connections, you have to wait for other access requests to put the connection back into the container before using it. When you finish using the connection, you must put the connection back into the container so that different database access requests can share these connections. By reusing these established database connections, you can solve the shortcomings of frequent connection establishment mentioned in the previous section, thus improving the performance of the system.

From the above description, we can summarize the main operations of database connection pooling:

(1) first set up a database connection pool object

(2) initialize a certain number of database connections and put them in the container of the connection pool object

(3) when there is a database access request, get a connection directly from the container of the connection pool. Here are three situations:

(a) when there is a connection in the container, a connection is returned to the database access requestor

(B) when there are no connections in the container and the number of connections currently established does not reach the maximum number of connections defined by the system, a new database connection is created.

(C) when there are no connections in the container and the number of connections currently established reaches the maximum number of connections defined by the system, the current access database request has to wait for other access requests to release the connection.

(4) when the database access is complete, the connection should be put back into the container of the connection pool.

(5) when the service stops, you need to release all database connections in the database connection pool first, and then release the database connection pool object.

two。 Programming implementation:

Header file (connection_pool.h):

[html] view

Plaincopy

/ *

* File: connection_pool.h

* Author: csc

, /

# ifndef_CONNECTION_POOL_H

# define _ CONNECTION_POOL_H

# include

# include

# include

# include

# include

# include

# include

# include

# include

# include

Usingnamespace std

Usingnamespace sql

ClassConnPool {

Private:

Number of database connections currently established by intcurSize;//

Maximum number of database connections defined in the intmaxSize;// connection pool

Stringusername

Stringpassword

Stringurl

Container queue for listconnList;// connection pool

Pthread_mutex_tlock;// thread lock

StaticConnPool * connPool

Driver*driver

Connection*CreateConnection (); / / create a connection

VoidInitConnection (int iInitialSize); / / initialize the database connection pool

VoidDestoryConnection (Connection * conn); / / destroy database connection objects

VoidDestoryConnPool (); / / destroy the database connection pool

ConnPool (stringurl,string user,string password,int maxSize); / / Construction method

Public:

~ ConnPool ()

Connection*GetConnection (); / / get the database connection

VoidReleaseConnection (Connection * conn); / / put the database connection back into the container of the connection pool

StaticConnPool * GetInstance (); / / get the database connection pool object

}

# endif / * _ CONNECTION_POOL_H * /

A container connList is defined in the header file, which stores many unused connections; when operating on the connections in the container, locks are needed to ensure the security of the program, so a lock is defined in the header file, which ensures that only one thread operates on the container at a time by using lock.

Connection pooling class needs to manage connections in the whole application, so only one connection pooling object needs to be maintained in the whole system. Imagine: if multiple connection pooling objects are defined in the system, then each object can establish maxSize connections, thus losing the original intention of creating connection pooling and breaking the idea of unified connection management in the system through connection pooling. So here we use the singleton pattern to write the connection pool class, which ensures that there is only one instance of a class, instantiates itself and provides this instance to the entire system. In the header file, we define a static connection pool object connPool. The connection pool class provides a static public method GetInstance (), which the external program calls to get the connection pool object. And the constructor of the connection pool class is defined as private, and external applications cannot instantiate the connection pool class through new, but can only obtain the connection pool object through the GetInstance () method; in the GetInstance () method, you need to determine whether the connPool defined in the connection pool class is NULL, if it is NULL, call the private constructor to instantiate connPool, and if it is not empty, directly return connPool. In this way, the singleton mode of connection pool class is realized, which ensures that only one instance object of connection pool class is established during the operation of the system.

When instantiating the object of the connection pool class, you should do some initialization to the connection pool, that is, establish a certain number of database connections. In the program, the connection pool is initialized by the InitConnection (intiInitialSize) method, iInitialSize connections are created, and these connections are placed in the container connList in the connection pool. Each new connection is added to the curSize. When there is a database access request, you need to obtain a connection from the connection pool, which is realized by the GetConnection () method: first, determine whether there is a connection in the container, if so, take out the first connection in the container and move the connection out of the container; the obtained connection is judged, if the connection has been closed, the memory space of the connection is reclaimed and a connection is recreated Then determine whether the newly created connection is empty, and if so, the current number of established connections is not curSize, but (curSize-1) (this empty connection should be removed). If there is no connection in the container, determine whether the current curSize value has reached the specified maxSize, and if it is not less than maxSize, a new connection (+ + curSize) will be established. If the maxSize is exceeded, wait for other database access requests to release the database connection.

After the connection is used, you need to put the connection back into the connection pool and implement it through the ReleaseConnection (sql::Connection* conn) method, which is very simple, which is to add the incoming connection connection to the container of the connection pool.

When you need to reclaim the memory space of the connection pool, you need to reclaim the memory space of all connections in the connection pool first, and then release the memory space of the connection pool object.

These are the main steps to implement database connection pooling, and the specific code implementation is as follows:

[cpp] view

Plaincopy

# include

# include

# include

# include "connection_pool.h"

Usingnamespace std

Usingnamespace sql

ConnPool*ConnPool::connPool=NULL

/ / Constructor of connection pool

ConnPool::ConnPool (stringurl, string userName,string password, int maxSize)

{

This- > maxSize=maxSize

This- > curSize=0

This- > username=userName

This- > password=password

This- > url=url

Try {

This- > driver=sql::mysql::get_driver_instance ()

}

Catch (sql::SQLException&e)

{

Perror ("driver connection error;\ n")

}

Catch (std::runtime_error&e)

{

Perror ("running error\ n")

}

This- > InitConnection (maxSize/2)

}

/ / get connection pool object, singleton mode

ConnPool*ConnPool::GetInstance () {

If (connPool==NULL)

{

ConnPool=newConnPool ("tcp://127.0.0.1:3306", "root", "root", 50)

}

ReturnconnPool

}

/ / initialize the connection pool and create half of the maximum number of connections

VoidConnPool::InitConnection (int iInitialSize)

{

Connection*conn

Pthread_mutex_lock & lock)

For (inti=0;iCreateConnection ()

If (conn) {

ConnList.push_back (conn)

+ + (this- > curSize)

}

Else

{

Perror ("error creating CONNECTION")

}

}

Pthread_mutex_unlock & lock)

}

/ / create a connection and return a Connection

Connection*ConnPool::CreateConnection () {

Connection*conn

Try {

Conn=driver- > connect (this- > url,this- > username,this- > password); / / establish a connection

Returnconn

}

Catch (sql::SQLException&e)

{

Perror ("error creating connection")

ReturnNULL

}

Catch (std::runtime_error&e)

{

Perror ("runtime error")

ReturnNULL

}

}

/ / get a connection in the connection pool

Connection*ConnPool::GetConnection () {

Connection*con

Pthread_mutex_lock & lock)

If (connList.size () > 0) / / there are connections in the connection pool container

{

Con=connList.front (); / / get the first connection

ConnList.pop_front (); / / remove the first connection

If (con- > isClosed ()) / / if the connection has been closed, delete and re-establish one

{

Deletecon

Con=this- > CreateConnection ()

}

/ / if the connection is empty, an error occurred in creating the connection

If (con==NULL)

{

-- curSize

}

Pthread_mutex_unlock & lock)

Returncon

}

Else {

If (curSize

< maxSize){//还可以创建新的连接 con= this->

CreateConnection ()

If (con) {

+ + curSize

Pthread_mutex_unlock & lock)

Returncon

}

Else {

Pthread_mutex_unlock & lock)

ReturnNULL

}

}

The number of connections established by else {/ / has reached maxSize

Pthread_mutex_unlock & lock)

ReturnNULL

}

}

}

/ / Recycle database connection

VoidConnPool::ReleaseConnection (sql::Connection * conn) {

If (conn) {

Pthread_mutex_lock & lock)

ConnList.push_back (conn)

Pthread_mutex_unlock & lock)

}

}

/ / Destructor of connection pool

ConnPool::~ConnPool ()

{

This- > DestoryConnPool ()

}

/ / to destroy the connection pool, you must first destroy the medium connection of the connection pool.

VoidConnPool::DestoryConnPool () {

List::iterator icon

Pthread_mutex_lock & lock)

For (icon=connList.begin (); iconically connected connList.end (); + + icon)

{

This- > DestoryConnection (* icon); / / destroy connections in the connection pool

}

CurSize=0

ConnList.clear (); / / clear connections in the connection pool

Pthread_mutex_unlock & lock)

}

/ / destroy a connection

VoidConnPool::DestoryConnection (Connection* conn)

{

If (conn)

{

Try {

Conn- > close ()

}

Catch (sql::SQLException&e)

{

Perror (e.what ())

}

Catch (std::exception&e)

{

Perror (e.what ())

}

Deleteconn

}

}

[cpp]

View plaincopyprint?

/ *

* main.cpp

*

* Created on: 2013-3-26

* Author: holy

, /

# include "connection_pool.h"

Namespace ConnectMySQL {

/ / initialize connection pooling

ConnPool * connpool = ConnPool::GetInstance ()

Void run () {

Connection * con

Statement * state

ResultSet * result

/ / obtain mysql connections from connection pooling

Con = connpool- > GetConnection ()

State = con- > createStatement ()

State- > execute ("use holy")

/ / query

Result = state- > executeQuery ("select * from student where id"

< 1002"); // 输出查询 while (result->

Next () {

Int id = result- > getInt ("id")

String name = result- > getString ("name")

Cout

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.