技术: mysql-connector-cpp

技术

programming with mysql database in cpp language

就像JDBC接口一样, 利用 Connector 在 C++ 操作 mysql 数据库(而不是C api)进行数据操作, 更加简洁, 更加符合通用接口标准.
mysql connector 会通过 mysqlclient-dev 库帮你去连接 mysql-server , 然后进行数据操作(效率上可能有些损失,相比C api).

引子

不管那种语言支持数据库操作, 它无可回避的要解决这样一些事情:

1. 如何连接数据库(相关类如何设计的)
2. 如何使用查询语句
3. 如何取得结果集/运行结果(可能要在结果集中用到游标)
4. 如何使用存储过程(prepared statement)
5. 如何使用事务
...

(一般开发中用的内容差不多如上)

Cpp对于操作mysql数据库的支持:

libmysqlcppconn.so或者libmysqlcppconn-static.a

关于libmysqlcppconn.so:

你的应用程序---> mysql connector ---> mysqlclient库 ---> mysqlserver

为什么不使用CAPI:

使用connector的好处: 以面向对象的方式编程(当然你也可以自己封装CAPI); 和JDBC等标准统一.

主要涉及的头文件：(/usr/local/include/cppconn/)

  • build_config.h
  • config.h
  • connection.h
  • datatype.h
  • driver.h
  • exception.h
  • metadata.h
  • prameter_metadata.h
  • prepared_statement.h
  • resultset.h
  • resultset_metadata.h
  • statement.h
  • sqlstring.h
  • warning.h
  • version_info.h
  • variant.h

一般用最多的头文件是下面几个头文件

<cppconn/driver.h>
<cppconn/exception.h>
<cppconn/resultset.h>
<cppconn/statement.h>
<cppcon/prepared_statement.h>

正文

该部分按如下展开:

代码片段(核心代码) —> 完整的demo —> debug&tracing

代码片段

连接数据库

1
2
3
4
5
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;

注意协议格式: tcp://host:port/db

其中 driver->connect() 也可以介绍一个 sql::ConnectOptionsMap 作为参数,原型如下:

1
sql::Connection * MySQL_Driver::connect(sql::ConnectOptionsMap & properties)

案例代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
sql::mysql::MySQL_Driver *driver;
sql::Connection *con;
sql::ConnectOptionsMap connection_properties;

connection_properties["hostName"] = hostName;
connection_properties["userName"] = userName;
connection_properties["password"] = password;
connection_properties["schema"] = "information_schema";
connection_properties["port"] = 13306;
connection_properties["OPT_RECONNECT"] = true;

driver = sql::mysql::MySQL_Driver::get_mysql_driver_instance();
con = driver->connect(connection_properties);

需要检查返回值么？需要的.

These methods can be used to check the connection state or reconnect:

  • sql::Connection::isValid() checks whether the connection is alive
  • sql::Connection::reconnect() reconnects if the connection has gone down

执行语句

主要涉及下面的api:

1. sql::Statement::execute(),
2. sql::Statement::executeQuery()
3. sql::Statement::executeUpdate()

值得一说的是, execute()方法更加通用,不论执行的语句是否返回结果集,都可以使用该方法.
(if your query does not return a result set, or if your query returns more than one result set)

代码可以是这样的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 " EXAMPLE_DB);
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;

得到结果

一般就是查询结果集.

1. sql::Statement::executeQuery()
2. sql::PreparedStatement::executeQuery()
3. sql::ResultSet

(By default, Connector/C++ buffers all result sets on the client to support cursors)

完整的代码可以是这样的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  // ...variable definitions
  sql::Connection *con;
  sql::Statement *stmt;
  sql::ResultSet *res;

  //...connect

  stmt = con->createStatement();
  
  //...check

  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;

PreparedStatement

PreparedStatement的好处是可以结合占位符, 操作sql语句.
如果你没有相关的基础可以去看一下源码目录的 examples/prepared_statement.cpp文件.

由于preparedstatement是继承自statement, 所以两者的使用方式其实是相似的.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

// ...
sql::Connection *con;
sql::PreparedStatement *prep_stmt;


prep_stmt = con->prepareStatement("INSERT INTO test(id, label) VALUES (?, ?)");

prep_stmt->setInt(1, 1);
prep_stmt->setString(2, "a");
prep_stmt->execute();

prep_stmt->setInt(1, 2);
prep_stmt->setString(2, "b");
prep_stmt->execute();

delete prep_stmt;
delete con;

使用存储过程

程序能够调用 stored procedures 的前提是你的数据库中存在相关的存储过程(无返回结果的,返回OUT参数的,返回多个结果集).

A stored procedure can be called using a Statement or PreparedStatement object.

调用代码非常简单:
(注意下面代码不要使用auto_ptr)

无返回结果

  1. 使用sql::Statement的情况:
1
2
3
4
5
6
7
8
9
sql::Driver* driver = get_driver_instance();
std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr<sql::Statement> stmt(con->createStatement());

// We need not check the return value explicitly. If it indicates
// an error, Connector/C++ generates an exception.
stmt->execute("CALL add_country('ATL', 'Atlantis', 'North America')");
  1. 使用sql::PreparedStatement
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
vector<string> code_vector;
code_vector.push_back("SLD");
code_vector.push_back("DSN");
code_vector.push_back("ATL");

vector<string> name_vector;
name_vector.push_back("Sealand");
name_vector.push_back("Disneyland");
name_vector.push_back("Atlantis");

vector<string> cont_vector;
cont_vector.push_back("Europe");
cont_vector.push_back("North America");
cont_vector.push_back("Oceania");

sql::Driver * driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr< sql::PreparedStatement > pstmt;
pstmt.reset(con->prepareStatement("CALL add_country(?,?,?)"));

for (int i=0; i<3; i++)
{
  pstmt->setString(1,code_vector[i]);
  pstmt->setString(2,name_vector[i]);
  pstmt->setString(3,cont_vector[i]);

  pstmt->execute();
}

有OUT参数的存储过程

调用存储过程并没有直接返回OUT参数给程序
一般的做法是后面在执行一个subsequent-query stmt->executeQuery("") ,在这个子查询里专门处理OUT参数, 例如:

1
SELECT @pop AS _reply

1.sql::Statement

可以参考下面的代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
sql::Driver* driver = get_driver_instance();
std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr<sql::Statement> stmt(con->createStatement());
stmt->execute("CALL get_pop('Uganda', @pop)");

std::auto_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT @pop AS _reply"));
while (res->next())
  cout << "Population of Uganda: " << res->getString("_reply") << endl;

stmt->execute("CALL get_pop_continent('Asia', @pop)");
res.reset(stmt->executeQuery("SELECT @pop AS _reply"));
while (res->next())
  cout << "Population of Asia: " << res->getString("_reply") << endl;

stmt->execute("CALL get_pop_world(@pop)");
res.reset(stmt->executeQuery("SELECT @pop AS _reply"));

while (res->next())
  cout << "Population of World: " << res->getString("_reply") << endl;

2.sql::PreparedStatement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
vector<string> cont_vector;
cont_vector.push_back("Europe");
cont_vector.push_back("North America");
cont_vector.push_back("Oceania");

sql::Driver * driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr< sql::Statement > stmt(con->createStatement());
std::auto_ptr< sql::PreparedStatement > pstmt;
std::auto_ptr< sql::ResultSet > res;

pstmt.reset(con->prepareStatement("CALL get_pop_continent(?,@pop)"));

for (int i=0; i<3; i++)
{
    pstmt->setString(1,cont_vector[i]);
    pstmt->execute();

    res.reset(stmt->executeQuery("SELECT @pop AS _population"));
    
    //上面执行结果只有一个结果集合返回, 但是为了避免连接不稳定,还是使用loop
    while (res->next()){
      cout << "Population of "
         << cont_vector[i]
	    << " is "
	       << res->getString("_population") << endl;
    }
}

返回多个结果集

返回一个结果集合的时候,代码如下:

1
2
3
4
5
while (res->next()) {
  cout << "Name: " << res->getString("Name")
       << " Population: " << res->getInt("Population")
       << endl;
}

返回多行结果的时候,　就要首先拿到结果集:

1
2
3
4
5
6
7
8
do {
     res.reset(stmt->getResultSet());
     while (res->next()) {
       cout << "Name: " << res->getString("Name")
            << " Population: " << res->getInt("Population")
            << endl;
   }
} while (stmt->getMoreResults());

而使用存储过程时:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
sql::Driver* driver = get_driver_instance();
std::auto_ptr<sql::Connection> con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr<sql::Statement> stmt(con->createStatement());


//调用存储过程
stmt->execute("CALL get_data()");
std::auto_ptr< sql::ResultSet > res;

do {
  //从stat的结果中获取multi-resultset中的一个
  res.reset(stmt->getResultSet());
  while (res->next()) { //从多个结果集中抽取行
    cout << "Name: " << res->getString("Name")
         << " Population: " << res->getInt("Population")
         << endl;
  }
} while (stmt->getMoreResults());

使用sql::PreparedStatement的情况如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
sql::Driver * driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr< sql::PreparedStatement > pstmt;
std::auto_ptr< sql::ResultSet > res;

pstmt.reset(con->prepareStatement("CALL get_data()"));
res.reset(pstmt->executeQuery());

for(;;)
  {
    while (res->next()) {
      cout << "Name: " << res->getString("Name")
         << " Population: " << res->getInt("Population")
	    << endl;
    }
    if (pstmt->getMoreResults())
      {
      res.reset(pstmt->getResultSet());
      continue;
      }
    break;
  }

完整demo

关于c++中到底用不用异常,各有各的说法, 我个人的意见, 如果基础代码是我写的我习惯性不使用异常.
但是如果你的代码基础模块或者要调用的代码的里面存在异常处理模型,那么你最好还是加上异常, 统一代码风格.

下面的示例代码用到了’mysql_connection.h’.
该文件存储位置在我本机的/usr/local/include/mysql_connection.h.

简单查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
/* Standard C++ includes */
#include <stdlib.h>
#include <iostream>

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include "mysql_connection.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;
}

稍复杂查询

下面演示一个简单的, 使用prepared statement的案例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/* Standard C++ includes */
#include <stdlib.h>
#include <iostream>

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

框架代码

写案例程序的时候,完全可以按照下面这个模板进行改写或者扩展：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <stdexcept>

/* uncomment for applications that use vectors */
/*#include <vector>*/

#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>

#define EXAMPLE_HOST "localhost"
#define EXAMPLE_USER "worlduser"
#define EXAMPLE_PASS "worldpass"
#define EXAMPLE_DB "world"

using namespace std;

int main(int argc, const char **argv)
{
  string url(argc >= 2 ? argv[1] : EXAMPLE_HOST);
  const string user(argc >= 3 ? argv[2] : EXAMPLE_USER);
  const string pass(argc >= 4 ? argv[3] : EXAMPLE_PASS);
  const string database(argc >= 5 ? argv[4] : EXAMPLE_DB);
  cout << "Connector/C++ tutorial framework..." << endl;
  cout << endl;
  try {
    /* INSERT TUTORIAL CODE HERE! */
  } catch (sql::SQLException &e) {
    /*
      MySQL Connector/C++ throws three different exceptions:
      - sql::MethodNotImplementedException (derived from sql::SQLException)
      - sql::InvalidArgumentException (derived from sql::SQLException)
      - sql::SQLException (derived from std::runtime_error)
    */
    cout << "# ERR: SQLException in " << __FILE__;
    cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
    /* what() (derived from std::runtime_error) fetches error message */
    cout << "# ERR: " << e.what();
    cout << " (MySQL error code: " << e.getErrorCode();
    cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    return EXIT_FAILURE;
  }
  cout << "Done." << endl;
  return EXIT_SUCCESS;
}

然后编译运行:

1
2
shell> g++ -o framework -I/usr/local/include -I/usr/local/include/cppconn
          framework.cpp -lmysqlcppconn

debug-tracing

tracing文件一般不是用于debug, 而是用于bottleneck的查找.

(Some problems happen randomly which makes them difficult to debug using a debugger.
In such cases, debug traces and protocol files are more useful because they allow you to trace the activities of all instances of your program.DTrace is a very powerful technology to trace any application without having to develop an extra trace module for your application)

产生trace文件的两个端:

  1. Mysql client library (libmysqlclient)
    (需要connector内部调用mysql_debug())
  2. Connector/C++
    (需要在编译connector的时候在cmake阶段指定-DMYSQLCPPCONN_TRACE_ENABLE=1)

具体的开关列举如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sql::Driver *driver;
int on_off = 1;
/* Using the Driver to create a connection */
driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(host, user, pass));

/*
Activate debug trace of the MySQL client library (C API)
Only available with a debug build of the MySQL client library!
*/
con->setClientOption("libmysql_debug", "d:t:O,client.trace");

/*
Connector/C++ tracing is available if you have compiled the
driver using cmake -DMYSQLCPPCONN_TRACE_ENABLE:BOOL=1
*/
con->setClientOption("clientTrace", &on_off);

说明:
The traces are controlled on a per-connection basis.

You can use the setClientOption() method of a connection object to activate and deactivate trace generation.
The MySQL client library trace always writes its trace to a file, whereas the connector writes protocol messages to the standard output.

尾巴

官方给出了api-reference的话, 可以参考一样官方的网址:

https://dev.mysql.com/doc/connector-cpp/en/

(大部分内容已经在本机文档中给出了)

参考资料

  1. cppconn官方文档

本文标题:技术: mysql-connector-cpp

文章作者:Merlin

发布时间:2016-05-23, 23:21:51

最后更新:2018-04-12, 14:55:17

原始链接:http://www.merlinblog.site/posts/956ee661/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 引子
  2. 2. 正文
    1. 2.1. 代码片段
      1. 2.1.1. 连接数据库
      2. 2.1.2. 执行语句
      3. 2.1.3. 得到结果
      4. 2.1.4. PreparedStatement
    2. 2.2. 使用存储过程
      1. 2.2.1. 无返回结果
      2. 2.2.2. 有OUT参数的存储过程
      3. 2.2.3. 返回多个结果集
    3. 2.3. 完整demo
      1. 2.3.1. 简单查询
      2. 2.3.2. 稍复杂查询
      3. 2.3.3. 框架代码
    4. 2.4. debug-tracing
  3. 3. 尾巴
  4. 4. 参考资料
|