C++ MySQL 常用接口（基于 MySQL Connector/C++）

C++ MySQL 常用接口（基于 MySQL Connector/C++）

1. 数据库连接

接口：

sql::mysql::MySQL_Driver *driver;
sql::Connection *con;

作用：
用于创建 MySQL 连接对象。

示例：

driver = sql::mysql::get_mysql_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "user", "password");

释放资源：

delete con;

---

2. 选择数据库

接口：

con->setSchema("database_name");

作用：
指定要操作的数据库。

示例：

con->setSchema("test_db");

---

3. 创建 SQL 语句对象

接口：

sql::Statement *stmt;
stmt = con->createStatement();

作用：
创建一个 SQL 语句执行对象。

示例：

stmt = con->createStatement();
stmt->execute("CREATE TABLE IF NOT EXISTS users (id INT, name VARCHAR(50))");
delete stmt;

---

4. 执行 SQL 语句

接口：

stmt->execute("SQL语句");
stmt->executeQuery("SQL查询语句");
stmt->executeUpdate("SQL更新语句");

作用：

• execute() 用于执行不返回结果集的 SQL 语句（如 CREATE TABLE）。
• executeQuery() 用于执行 SELECT 查询，返回 ResultSet 结果集。
• executeUpdate() 用于 INSERT、UPDATE、DELETE，返回影响的行数。

示例：

stmt = con->createStatement();
stmt->execute("INSERT INTO users (id, name) VALUES (1, 'Tom')");
sql::ResultSet *res = stmt->executeQuery("SELECT * FROM users");
int rows = stmt->executeUpdate("UPDATE users SET name='Jerry' WHERE id=1");delete res;
delete stmt;

---

5. 获取查询结果

接口：

sql::ResultSet *res;
res->next();
res->getInt("column_name");
res->getString("column_name");

作用：
获取查询结果，并读取列值。

示例：

sql::ResultSet *res = stmt->executeQuery("SELECT * FROM users");
while (res->next()) {std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name") << std::endl;
}
delete res;

---

6. 使用预处理语句

接口：

sql::PreparedStatement *pstmt;
pstmt = con->prepareStatement("SQL语句");
pstmt->setInt(参数索引, 整数值);
pstmt->setString(参数索引, 字符串值);
pstmt->execute();
pstmt->executeQuery();
pstmt->executeUpdate();

作用：

• prepareStatement() 预编译 SQL，提高执行效率并防止 SQL 注入。
• setInt() / setString() 设置 SQL 语句中的参数值。

示例：

sql::PreparedStatement *pstmt = con->prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
pstmt->setInt(1, 2);
pstmt->setString(2, "Alice");
pstmt->executeUpdate();
delete pstmt;

---

7. 事务处理

自动提交（Auto Commit）

MySQL 默认启用 Auto Commit，每条 INSERT、UPDATE、DELETE 语句都会 立即生效，即使程序崩溃，数据也已经修改了。

示例（默认自动提交）

sql::Statement *stmt = con->createStatement();
stmt->execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
delete stmt;  // 即使程序崩溃，数据库的修改依然有效

手动事务（关闭自动提交）

如果希望多个 SQL 语句 全部成功或全部失败，需要 关闭自动提交 并手动 COMMIT 或 ROLLBACK。

接口：

con->setAutoCommit(false);
con->commit();
con->rollback();

作用：

• setAutoCommit(false)：关闭自动提交，进入事务模式。
• commit()：提交事务，所有 SQL 语句的更改生效。
• rollback()：回滚事务，撤销所有未提交的更改。

示例（手动控制事务）

try {con->setAutoCommit(false); // 关闭自动提交，开启事务模式sql::PreparedStatement *pstmt = con->prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");pstmt->setDouble(1, 100.0);pstmt->setInt(2, 1);pstmt->executeUpdate();delete pstmt;pstmt = con->prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?");pstmt->setDouble(1, 100.0);pstmt->setInt(2, 2);pstmt->executeUpdate();delete pstmt;con->commit();  // **手动提交事务，所有修改一起生效**
} catch (sql::SQLException &e) {con->rollback();  // **出错时回滚，保证数据一致性**std::cerr << "Transaction failed: " << e.what() << std::endl;
}

事务的使用场景

适用于需要保证 数据一致性 的情况：

• 银行转账（A 账户扣钱，B 账户加钱，必须同时成功）
• 库存管理（购买商品时，需要同时更新库存和订单信息）
• 订单处理（下单时，必须同时修改多个表的数据）

总结

• MySQL 默认 Auto Commit = true，每条 SQL 语句都会立即提交。
• 关闭 Auto Commit 后，可以 手动提交或回滚，确保数据一致性。
• 使用 commit() 确保更改生效，使用 rollback() 处理失败情况。

---

8. 关闭连接

接口：

delete res;
delete stmt;
delete pstmt;
delete con;

作用：
释放资源，避免内存泄漏。

示例：

delete res;
delete stmt;
delete pstmt;
delete con;

---

总结

类别	接口	作用
数据库连接	sql::mysql::MySQL_Driver *driver; sql::Connection *con; driver = sql::mysql::get_mysql_driver_instance(); con = driver->connect(...);	连接 MySQL 数据库
选择数据库	con->setSchema("database_name");	选择数据库
执行 SQL	stmt->execute("SQL语句"); stmt->executeQuery("SQL查询语句"); stmt->executeUpdate("SQL更新语句");	执行 SQL 语句
获取查询结果	res->next(); res->getInt("column_name"); res->getString("column_name");	获取查询结果
预处理语句	pstmt = con->prepareStatement("SQL语句"); pstmt->setInt(1, value); pstmt->setString(2, "value"); pstmt->executeUpdate();	预编译 SQL，防止 SQL 注入
事务管理	con->setAutoCommit(false); con->commit(); con->rollback();	控制事务提交和回滚
释放资源	delete res; delete stmt; delete pstmt; delete con;	释放内存，避免泄漏

代码示例

/* Copyright 2008, 2010, Oracle and/or its affiliates.This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.There are special exceptions to the terms and conditions of the GPL
as it is applied to this software. View the full text of the
exception in file EXCEPTIONS-CONNECTOR-C++ in the directory of this
software distribution.This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*//* Standard C++ includes */
#include <stdlib.h>
#include <iostream>/*Include directly the differentheaders from cppconn/ and mysql_driver.h + mysql_util.h(and mysql_connection.h). This will reduce your build time!
*/
#include "mysql_connection.h"#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>using namespace std;int main(void)
{
cout << endl;
cout << "Let's have MySQL count from 10 to 1..." << endl;try {sql::Driver *driver;sql::Connection *con;sql::Statement *stmt;sql::ResultSet *res;sql::PreparedStatement *pstmt;/* Create a connection */driver = get_driver_instance();con = driver->connect("tcp://127.0.0.1:3306", "root", "root");/* Connect to the MySQL test database */con->setSchema("test");stmt = con->createStatement();stmt->execute("DROP TABLE IF EXISTS test");stmt->execute("CREATE TABLE test(id INT)");delete stmt;/* '?' is the supported placeholder syntax */pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");for (int i = 1; i <= 10; i++) {pstmt->setInt(1, i);pstmt->executeUpdate();}delete pstmt;/* Select in ascending order */pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");res = pstmt->executeQuery();/* Fetch in reverse = descending order! */res->afterLast();while (res->previous())cout << "\t... MySQL counts: " << res->getInt("id") << endl;delete res;delete pstmt;delete con;} catch (sql::SQLException &e) {cout << "# ERR: SQLException in " << __FILE__;cout << "(" << __FUNCTION__ << ") on line " »<< __LINE__ << endl;cout << "# ERR: " << e.what();cout << " (MySQL error code: " << e.getErrorCode();cout << ", SQLState: " << e.getSQLState() << »" )" << endl;
}cout << endl;return EXIT_SUCCESS;
}