Linux下c++使用MySql数据库实例

环境准备

---

• Linux上已经安装Mysql数据库及相关客户端库
• 安装 MySQL Connector/C++ 库
• MySql已创建数据库database_demo及数据表table_demo

先测试Linux本地直接连接到数据库，具体方式请参考Linux下Mysql常用操作。

连接

---

首先从 sql::mysql::MySQL_Driver对象得到 sql::Connection的实例。

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

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

delete con;

使用完毕后，要显式释放sql::Connection对象。不必释放driver，Connector/C++负责释放该实例。

注意，get_mysql_driver_instance()调用 get_driver_instance()，它是非线程安全的，如果使用在多线程环境中，需要使用锁。

查询

---

有三个方法可用于查询： sql::Statement::execute(), sql::Statement::executeQuery(), 和 sql::Statement::executeUpdate()。

如果执行的查询不返回结果或者返回不止一个结果，可以使用 sql::Statement::execute()，如：

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

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

stmt = con->createStatement();
stmt->execute("USE database_name");
stmt->execute("DROP TABLE IF EXISTS test");
stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");
stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");

delete stmt;
delete con;

如果执行的查询只返回一个结果，使用sql::Statement::executeQuery()或者sql::PreparedStatement::executeQuery()。

这两个方法都返回sql::ResultSet对象。默认情况下，Connector / C ++在客户端上缓存所有结果集以支持游标，如：

// ...
sql::Connection *con;
sql::Statement *stmt;
sql::ResultSet  *res;
// ...
stmt = con->createStatement();
// ...

res = stmt->executeQuery("SELECT id, label FROM test ORDER BY id ASC");
while (res->next()) {
  // You can use either numeric offsets...
  cout << "id = " << res->getInt(1); // getInt(1) returns the first column
  // ... or column names for accessing results.
  // The latter is recommended.
  cout << ", label = '" << res->getString("label") << "'" << endl;
}

delete res;
delete stmt;
delete con;

注意，使用完毕后务必释放资源。

完整示例

---

下面展示了使用Connector/C++使用MySql的完整示例。

/* Standard C++ includes */
#include <stdlib.h>
#include <iostream>

/*
  Include directly the different
  headers 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>

using namespace std;

int main(void)
{
cout << endl;
cout << "Running 'SELECT 'Hello World!' AS _message'..." << endl;

try {
  sql::Driver *driver;
  sql::Connection *con;
  sql::Statement *stmt;
  sql::ResultSet *res;

  /* 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();
  res = stmt->executeQuery("SELECT 'Hello World!' AS _message");
  while (res->next()) {
    cout << "\t... MySQL replies: ";
    /* Access column data by alias or column name */
    cout << res->getString("_message") << endl;
    cout << "\t... MySQL says it again: ";
    /* Access column data by numeric offset, 1 is the first column */
    cout << res->getString(1) << endl;
  }
  delete res;
  delete stmt;
  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;
}

参考资料

---

Introduction to Connector/C++