MySQL预处理

• Z时代
• 2024-01-10
• 分类：综合

C++预处理接口: 
preparable_stmt 语句中的? 是个占位符，字符串类型需要加上单引号。 
创建预处理对象sql::PreparedStatement* tPreStmt = db_conn->prepareStatement(“update user_table set user_level=? where user_id=?”);  
参数设置接口：setBigInt，setBlob，setBoolean，setDateTime，setDouble，setInt，setUInt，setInt64，setUInt64，setNull，setString。 
每次调用结束之后需要调用clearParameters接口清空参数，方便下次调用。

实例代码：

#pragma once
#include <iostream>
#include <string>
#include <map>
#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/exception.h>
#include <cppconn/driver.h>  
#include <cppconn/connection.h>  
#include <cppconn/resultset.h>  
#include <cppconn/prepared_statement.h>  
#include <cppconn/statement.h>
int main()
{
	std::string url = "127.0.0.1:3306";
	std::string user = "test";
	std::string passwd = "123";
	sql::Driver* driver = sql::mysql::get_driver_instance();
	if (driver)
	{
		sql::Connection*  conn = driver->connect(url, user, passwd);
		if (conn == nullptr)
		{
			return -1;
		}
		std::string stmt_str = "update user_table set user_level=? where user_id=?";
		sql::PreparedStatement* preStmt = m_conn->prepareStatement(stmt_str);
		preStmt->setUInt(0,1);
		preStmt->setUInt64(1,132129938);
		preStmt->executeUpdate();
	}
	else
	{
		return -1;
	}	
	return 0;
}

项目实践中不会有这么简单的逻辑，通常需要在每个连接中初始化好预处理语句，甚至还需要建立MySql连接池，下面给出一个示例代码：

#pragma once
#include <iostream>
#include <string>
#include <map>
#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/exception.h>
#include <cppconn/driver.h>  
#include <cppconn/connection.h>  
#include <cppconn/resultset.h>  
#include <cppconn/prepared_statement.h>  
#include <cppconn/statement.h>
class MysqlConn
{
public:
	MysqlConn();
	~MysqlConn();
	bool ConnectDb(const std::string& url, const std::string& user, const std::string& passwd);
	bool AddPreStatement(int id, const std::string& stmts);
	sql::Connection* GetConn() { return m_conn; }
	sql::PreparedStatement* GetPreStatement(int id);
private:
	std::string m_url;
	std::string m_user;
	std::string m_passwd;
	sql::Driver*   m_driver = nullptr;
	sql::Connection* m_conn = nullptr;
	std::map<int, sql::PreparedStatement*> m_stmts;
};
MysqlConn::MysqlConn()
{
}
MysqlConn::~MysqlConn()
{
	std::map<int, sql::PreparedStatement*>::iterator it = m_stmts.begin();
	while (it != m_stmts.end())
	{
		delete it->second;
		it->second = nullptr;
		it++;
	}
	m_stmts.clear();
	if (m_conn)
	{
		m_conn->close();
		delete m_conn;
		m_conn = nullptr;
	}
}
bool MysqlConn::ConnectDb(const std::string& url, const std::string& user, const std::string& passwd)
{
	m_url    = url;
	m_user   = user;
	m_passwd = passwd;
	m_driver = sql::mysql::get_driver_instance();
	if (m_driver)
	{
		m_conn = m_driver->connect(m_url, m_user, m_passwd);
		if (m_conn == nullptr)
		{
			return false;
		}
	}
	else
	{
		return false;
	}
	return true;
}
bool MysqlConn::AddPreStatement(int id, const std::string& stmts)
{
	sql::PreparedStatement* tVal = m_conn->prepareStatement(stmts);
	if (!tVal)
	{
		return false;
	}
	std::map<int, sql::PreparedStatement*>::iterator it = m_stmts.find(id);
	if (it != m_stmts.end())
	{
		delete it->second;
		it->second = tVal;
	}
	else
	{
		m_stmts[id] = tVal;
	}
	return true;
}
sql::PreparedStatement* MysqlConn::GetPreStatement(int id)
{
	std::map<int, sql::PreparedStatement*>::iterator it = m_stmts.find(id);
	if (it != m_stmts.end())
	{
		it->second->clearParameters();
		return it->second;
	}
	return nullptr;
}

以上是 MySQL预处理 的全部内容， 来源链接： utcz.com/z/532236.html