【c++】数据库连接池库：libzdb
https://github.com/mverbert/libzdb
1. 引入项目
地址：http://www.tildeslash.com/libzdb/
2、安装
./configure
make
make install
若出现 --without-<database> 错误，说明你电脑上没有安装数据库，因为libzdb在安装过程中为自动检测是否有安装好的数据库。
此外可以通过，./configure --prefix=/opt/libzdb 来指定安装路径。
3、把新共享库目录加入到共享库配置文件/etc/ld.so.conf中, 如下:
# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
# echo "<prefix>/lib" >> /etc/ld.so.conf
# ldconfig
如果在configure命令中没有指定--prefix，则
echo /opt/libzdb/lib >> /etc/ld.so.conf
4. Makefile
CC = gcc
CFLAGS = -Wall -g
LDFLAGS = -L/opt/oldzdb/lib -lzdb
INCLUDES = -I/opt/oldzdb/include/zdb
all: my_program
my_program: main.c
$(CC) $(CFLAGS) $(INCLUDES) main.c -o my_program $(LDFLAGS)
clean:
rm -f my_program
5、使用
- 有zdb.h和zdbpp.h，若使用cpp则引入zdbpp.h
- 使用事务，可将绑定多个参数的过程分开，从而在绑定参数值的时候可以分别判断绑定的值
//这种写法会报错，类型
conn.execute(
"insert into e_tunnel_locInfo "
"(`mining_loc`,`tmstamp`,`day_step`, `heading_mil_S`, `firstChnNO_S`, `firstChnNO_R`, `firstChnNO_E`)"
"values(?,FROM_UNIXTIME(?),?,?,?,?,?)",
hzi::tunnel_mining_loc,
static_cast<double>(tmstamp),
hzi::day_step, hzi::heading_mil,
(firstChnNO_S==-1?nullptr:firstChnNO_S),
(firstChnNO_R==-1?nullptr:firstChnNO_R),
(firstChnNO_E==-1?nullptr:firstChnNO_E));
//使用事务分开绑定，即可判断
//异常定义在zdbpp.h中
zdb::sql_exception
//封装连接池ConnectionPool指针
using conn_pool_t = std::shared_ptr<ConnectionPool>;
static inline conn_pool_t pConnPool;
//设定url和最大连接数，启动
config.pConnPool = std::make_shared<ConnectionPool>(config.mysqlURL);
config.pConnPool->setMaxConnections(maxConnections);
config.pConnPool->start();
//获取连接对象
Connection conn = config.pConnPool->getConnection();
//执行查询（可用?占位符）
ResultSet result = conn.executeQuery(const char*);//传字面量字符串或string::c_str()
auto rslt2 = conn.executeQuery(
"SELECT mining_loc,day_step FROM `e_tunnel_locInfo`"
" WHERE tmstamp <= ? ORDER BY tmstamp DESC LIMIT 1",
(double)sampTime);
//封装
template <typename... Args>
tl::expected<ResultSet, std::string> queryDb(Connection &conn, const char *sql,
Args... args)
{
try
{
ResultSet result = conn.executeQuery(sql, args...);
if (result.next())
{
return result;
} else {
return tl::make_unexpected("not found in query: " + std::string(sql));
}
} catch (sql_exception &e) {
return tl::make_unexpected("sql error for " + std::string(sql) + ": " + std::string(e.what()));
}
}
bool ret = queryDb(conn,"SELECT * FROM e_users where user_name=? and password=PASSWORD(?)",user, pswd)?true:false;
queryDb(conn, sql.c_str())
.map([&](ResultSet set)
{
//对查询结果set进行操作
}
.map_error([&](auto err)
{
//对错误信息err进行操作
}
//执行查询之外的增删改操作（可用?占位符）
conn.execute(const char*);
conn.execute(
"INSERT INTO "
" e_tunnel_RunningDataInfo (start_time, "
"end_time, is_running, "
"start_mining_locx, "
"end_mining_locx, "
"recv_count, "
"conn_status ) "
" values(FROM_UNIXTIME(?),FROM_UNIXTIME(?),?,?,?,?,?)",
start_time / 1000.0, end_time / 1000.0, isRunning,
start_mining_locx, end_mining_locx, recv_count, conn_status);
//插入NULL值（若表字段允许NULL）
直接传入nullptr/NULL即可
//事务（可用?占位符）
Connection conn = hzi::config.pConnPool->getConnection();
PreparedStatement prp = conn.prepareStatement(
"INSERT INTO "
" e_tunnel_locInfo (mining_loc,tmstamp) "
" values(?,?) ON DUPLICATE KEY UPDATE tmstamp = values(tmstamp)");
conn.beginTransaction(); //启动事务
prp.bind(1, mining_locx); //绑定参数值
prp.bind(2, (double)tmstamp);
prp.execute(); //执行事务
conn.commit(); //提交或回滚rollback()
//只取一条记录
//需注意若结果非空，if调用1次next()已经读了一条记录
if(rslt1.next())
{
//取一条记录
}
else
{
//为空
}
//获取结果
while (rslt1.next())
{
//获取全部记录
}
//方法的作用是检查指定列索引的值是否为 SQL 空值（NULL）。如果该列的值为 NULL，则返回 true；否则返回 false
//columnIndex从1开始
bool ResultSet::isnull(int columnIndex);
//若查询出的记录很多，可预先设定先少取一点：预取行数
ResultSet::setFetchSize(int n);//先预取n行记录，避免一次查询太多
zdbpp.h
/*
* Copyright (C) 2016 dragon jiang<jianlinlong@gmail.com>
* Copyright (C) 2019-2022 Tildeslash Ltd.
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files(the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and / or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions :
*
* The above copyright notice and this permission notice shall be included in all
* copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
* SOFTWARE.
*/
#ifndef _ZDBPP_H_
#define _ZDBPP_H_
#include "zdb.h"
#include <tuple>
#include <string>
#include <utility>
#include <stdexcept>
/*
This interface contains all the classes and methods that are needed to
use libzdb from C++ (C++17 or later). To use libzdb in your C++ project,
import zdbpp.h (this interface) and use the namespace zdb:
#include <zdbpp.h>
using namespace zdb;
Query Example
-------------
ConnectionPool pool("mysql://192.168.11.100:3306/test?user=root&password=dba");
pool.start();
Connection con = pool.getConnection();
// Use C++ variadic template feature to bind parameter
ResultSet result = con.executeQuery(
"select id, name, hired, image from employee where id < ? order by id", 100
);
// Optionally set row prefetch, default is 100
result.setFetchSize(10);
while (result.next()) {
int id = result.getInt("id");
const char *name = result.getString("name");
time_t hired = result.getTimestamp("hired");
auto [image, size] = result.getBlob("image");
...
}
Execute statement
-----------------
Connection con = pool.getConnection();
// Any execute or executeQuery statement which takes parameters are
// automatically translated into a prepared statement. Here we also
// demonstrate how to set a SQL null value by using nullptr
con.execute("update employee set image = ? where id = ?", nullptr, 11);
Test for SQL null value
-----------------------
ResultSet result = con.executeQuery("select name, image from employee");
while (result.next()) {
if (result.isnull("image")) {
...
}
}
Insert Data via Prepared Statement
----------------------------------
Connection con = pool.getConnection();
PreparedStatement prep = con.prepareStatement(
"insert into employee (name, hired, image) values(?, ?, ?)"
);
con.beginTransaction();
for (const auto &employee : employees) {
// Polymorphic bind
prep.bind(1, employee.name);
prep.bind(2, employee.hired);
prep.bind(3, employee.image);
prep.execute();
}
con.commit();
Exception Handling
------------------
try {
con = pool.getConnection();
con.executeQuery("invalid query");
} catch (sql_exception& e) {
std::cout << e.what();
}
*/
namespace zdb {
class sql_exception : public std::runtime_error
{
public:
sql_exception(const char* msg = "SQLException")
: std::runtime_error(msg)
{}
};
#define except_wrapper(f) TRY { f; } ELSE {throw sql_exception(Exception_frame.message);} END_TRY
struct noncopyable
{
noncopyable() = default;
// make it noncopyable
noncopyable(noncopyable const&) = delete;
noncopyable& operator=(noncopyable const&) = delete;
// make it not movable
noncopyable(noncopyable&&) = delete;
noncopyable& operator=(noncopyable&&) = delete;
};
class URL: private noncopyable
{
public:
URL(const std::string& url)
:URL(url.c_str())
{}
URL(const char *url) {
t_ = URL_new(url);
}
~URL() {
if (t_)
URL_free(&t_);
}
operator URL_T() {
return t_;
}
public:
const char *protocol() const {
return URL_getProtocol(t_);
}
const char *user() const {
return URL_getUser(t_);
}
const char *password() const {
return URL_getPassword(t_);
}
const char *host() const {
return URL_getHost(t_);
}
int port() const {
return URL_getPort(t_);
}
const char *path() const {
return URL_getPath(t_);
}
const char *queryString() const {
return URL_getQueryString(t_);
}
const char **parameterNames() const {
return URL_getParameterNames(t_);
}
const char *parameter(const char *name) const {
return URL_getParameter(t_, name);
}
const char *tostring() const {
return URL_toString(t_);
}
private:
URL_T t_;
};
class ResultSet : private noncopyable
{
public:
operator ResultSet_T() {
return t_;
}
ResultSet(ResultSet&& r)
:t_(r.t_)
{
r.t_ = nullptr;
}
protected:
friend class PreparedStatement;
friend class Connection;
ResultSet(ResultSet_T t)
:t_(t)
{}
public:
int columnCount() {
return ResultSet_getColumnCount(t_);
}
const char *columnName(int columnIndex) {
except_wrapper( RETURN ResultSet_getColumnName(t_, columnIndex) );
}
long columnSize(int columnIndex) {
except_wrapper( RETURN ResultSet_getColumnSize(t_, columnIndex) );
}
void setFetchSize(int prefetch_rows) {
ResultSet_setFetchSize(t_, prefetch_rows);
}
int getFetchSize() {
return ResultSet_getFetchSize(t_);
}
bool next() {
except_wrapper( RETURN ResultSet_next(t_) );
}
bool isnull(int columnIndex) {
except_wrapper( RETURN ResultSet_isnull(t_, columnIndex) );
}
const char *getString(int columnIndex) {
except_wrapper( RETURN ResultSet_getString(t_, columnIndex) );
}
const char *getString(const char *columnName) {
except_wrapper( RETURN ResultSet_getStringByName(t_, columnName) );
}
int getInt(int columnIndex) {
except_wrapper( RETURN ResultSet_getInt(t_, columnIndex) );
}
int getInt(const char *columnName) {
except_wrapper( RETURN ResultSet_getIntByName(t_, columnName) );
}
long long getLLong(int columnIndex) {
except_wrapper( RETURN ResultSet_getLLong(t_, columnIndex) );
}
long long getLLong(const char *columnName) {
except_wrapper( RETURN ResultSet_getLLongByName(t_, columnName) );
}
double getDouble(int columnIndex) {
except_wrapper( RETURN ResultSet_getDouble(t_, columnIndex) );
}
double getDouble(const char *columnName) {
except_wrapper( RETURN ResultSet_getDoubleByName(t_, columnName) );
}
template <typename T>
std::tuple<const void*, int> getBlob(T v) {
int size = 0;
const void *blob = nullptr;
if constexpr (std::is_integral<T>::value)
except_wrapper( blob = ResultSet_getBlob(t_, v, &size) );
else
except_wrapper( blob = ResultSet_getBlobByName(t_, v, &size) );
return {blob, size};
}
time_t getTimestamp(int columnIndex) {
except_wrapper( RETURN ResultSet_getTimestamp(t_, columnIndex) );
}
time_t getTimestamp(const char *columnName) {
except_wrapper( RETURN ResultSet_getTimestampByName(t_, columnName) );
}
struct tm getDateTime(int columnIndex) {
except_wrapper( RETURN ResultSet_getDateTime(t_, columnIndex) );
}
struct tm getDateTime(const char *columnName) {
except_wrapper( RETURN ResultSet_getDateTimeByName(t_, columnName) );
}
private:
ResultSet_T t_;
};
class PreparedStatement : private noncopyable
{
public:
operator PreparedStatement_T() {
return t_;
}
PreparedStatement(PreparedStatement&& r)
:t_(r.t_)
{
r.t_ = nullptr;
}
protected:
friend class Connection;
PreparedStatement(PreparedStatement_T t)
:t_(t)
{}
public:
void setString(int parameterIndex, const char *x) {
except_wrapper( PreparedStatement_setString(t_, parameterIndex, x) );
}
void setInt(int parameterIndex, int x) {
except_wrapper( PreparedStatement_setInt(t_, parameterIndex, x) );
}
void setLLong(int parameterIndex, long long x) {
except_wrapper( PreparedStatement_setLLong(t_, parameterIndex, x) );
}
void setDouble(int parameterIndex, double x) {
except_wrapper( PreparedStatement_setDouble(t_, parameterIndex, x) );
}
void setBlob(int parameterIndex, const void *x, int size) {
except_wrapper( PreparedStatement_setBlob(t_, parameterIndex, x, size) );
}
void setTimestamp(int parameterIndex, time_t x) {
except_wrapper( PreparedStatement_setTimestamp(t_, parameterIndex, x) );
}
void execute() {
except_wrapper( PreparedStatement_execute(t_) );
}
ResultSet executeQuery() {
except_wrapper(
ResultSet_T r = PreparedStatement_executeQuery(t_);
RETURN ResultSet(r);
);
}
long long rowsChanged() {
return PreparedStatement_rowsChanged(t_);
}
int getParameterCount() {
return PreparedStatement_getParameterCount(t_);
}
public:
void bind(int parameterIndex, const char *x) {
this->setString(parameterIndex, x);
}
void bind(int parameterIndex, const std::string& x) {
this->setString(parameterIndex, x.c_str());
}
void bind(int parameterIndex, int x) {
this->setInt(parameterIndex, x);
}
void bind(int parameterIndex, long long x) {
this->setLLong(parameterIndex, x);
}
void bind(int parameterIndex, double x) {
this->setDouble(parameterIndex, x);
}
void bind(int parameterIndex, time_t x) {
this->setTimestamp(parameterIndex, x);
}
//blob
void bind(int parameterIndex, std::tuple<const void *, int> x) {
auto [blob, size] = x;
this->setBlob(parameterIndex, blob, size);
}
private:
PreparedStatement_T t_;
};
class Connection : private noncopyable
{
public:
operator Connection_T() {
return t_;
}
~Connection() {
if (t_) {
close();
}
}
protected: // for ConnectionPool
friend class ConnectionPool;
Connection(Connection_T C)
:t_(C)
{}
void setClosed() {
t_ = nullptr;
}
public:
void setQueryTimeout(int ms) {
Connection_setQueryTimeout(t_, ms);
}
int getQueryTimeout() {
return Connection_getQueryTimeout(t_);
}
void setMaxRows(int max) {
Connection_setMaxRows(t_, max);
}
int getMaxRows() {
return Connection_getMaxRows(t_);
}
void setFetchSize(int rows) {
Connection_setFetchSize(t_, rows);
}
int getFetchSize() {
return Connection_getFetchSize(t_);
}
//not supported
//URL_T Connection_getURL(T C);
bool ping() {
return Connection_ping(t_);
}
void clear() {
Connection_clear(t_);
}
//after close(), t_ is set to NULL. so this Connection object can not be used again!
void close() {
if (t_) {
Connection_close(t_);
setClosed();
}
}
void beginTransaction() {
except_wrapper( Connection_beginTransaction(t_) );
}
void commit() {
except_wrapper( Connection_commit(t_) );
}
void rollback() {
except_wrapper( Connection_rollback(t_) );
}
long long lastRowId() {
return Connection_lastRowId(t_);
}
long long rowsChanged() {
return Connection_rowsChanged(t_);
}
void execute(const char *sql) {
except_wrapper( Connection_execute(t_, "%s", sql) );
}
template <typename ...Args>
void execute(const char *sql, Args ... args) {
PreparedStatement p(this->prepareStatement(sql, args...));
p.execute();
}
ResultSet executeQuery(const char *sql) {
except_wrapper(
ResultSet_T r = Connection_executeQuery(t_, "%s", sql);
RETURN ResultSet(r);
);
}
template <typename ...Args>
ResultSet executeQuery(const char *sql, Args ... args) {
PreparedStatement p(this->prepareStatement(sql, args...));
return p.executeQuery();
}
PreparedStatement prepareStatement(const char *sql) {
except_wrapper(
PreparedStatement_T p = Connection_prepareStatement(t_, "%s", sql);
RETURN PreparedStatement(p);
);
}
template <typename ...Args>
PreparedStatement prepareStatement(const char *sql, Args ... args) {
except_wrapper(
PreparedStatement p(this->prepareStatement(sql));
int i = 1;
(p.bind(i++, args), ...);
RETURN p;
);
}
const char *getLastError() {
return Connection_getLastError(t_);
}
static bool isSupported(const char *url) {
return Connection_isSupported(url);
}
private:
Connection_T t_;
};
class ConnectionPool : private noncopyable
{
public:
ConnectionPool(const std::string& url)
:ConnectionPool(url.c_str())
{}
ConnectionPool(const char* url)
:url_(url)
{
if (!url_)
throw sql_exception("Invalid URL");
t_ = ConnectionPool_new(url_);
}
~ConnectionPool() {
ConnectionPool_free(&t_);
}
operator ConnectionPool_T() {
return t_;
}
public:
const URL& getURL() {
return url_;
}
void setInitialConnections(int connections) {
ConnectionPool_setInitialConnections(t_, connections);
}
int getInitialConnections() {
return ConnectionPool_getInitialConnections(t_);
}
void setMaxConnections(int maxConnections) {
ConnectionPool_setMaxConnections(t_, maxConnections);
}
int getMaxConnections() {
return ConnectionPool_getMaxConnections(t_);
}
void setConnectionTimeout(int connectionTimeout) {
ConnectionPool_setConnectionTimeout(t_, connectionTimeout);
}
int getConnectionTimeout() {
return ConnectionPool_getConnectionTimeout(t_);
}
void setAbortHandler(void(*abortHandler)(const char *error)) {
ConnectionPool_setAbortHandler(t_, abortHandler);
}
void setReaper(int sweepInterval) {
ConnectionPool_setReaper(t_, sweepInterval);
}
int size() {
return ConnectionPool_size(t_);
}
int active() {
return ConnectionPool_active(t_);
}
void start() {
except_wrapper( ConnectionPool_start(t_) );
}
void stop() {
if (this->active() > 0) {
throw sql_exception("Trying to stop the pool with active Connections. Please close all active Connections first");
}
ConnectionPool_stop(t_);
}
Connection getConnection() {
except_wrapper(
Connection_T c = ConnectionPool_getConnectionOrException(t_);
RETURN Connection(c);
);
}
void returnConnection(Connection& con) {
con.close();
}
int reapConnections() {
return ConnectionPool_reapConnections(t_);
}
static const char *version(void) {
return ConnectionPool_version();
}
private:
URL url_;
ConnectionPool_T t_;
};
} // namespace
#endif
浙公网安备 33010602011771号