MySQL预处理「建议收藏」

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;
}

代码100分

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

代码100分#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;
}