C++：操作MySQL

2019独角兽企业重金招聘Python工程师标准>>>

##下载相关库

$ sudo aptitude install libboost-all-dev

Mysql的库Connector/C++也可以用命令安装，不过我选择自己下载和配置，可以复习下g++的参数和makefile。

在http://dev.mysql.com/downloads/connector/cpp/下载，解压到/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/。

##示例1

---

###编辑test.cpp 来自http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-examples-complete-example-1.html。

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

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;

  // 创建数据库连接
  string user("root");
  string passwd("123456");
  driver = get_driver_instance();
  con = driver->connect("tcp://127.0.0.1:3306", user, passwd);
  // 选择数据库test
  con->setSchema("test");
  
  // 执行sql语句
  stmt = con->createStatement();
  res = stmt->executeQuery("SELECT 'Hello World!' AS _message");
  while (res->next()) {
    cout << "\t... MySQL replies: ";
    cout << res->getString("_message") << endl;
    cout << "\t... MySQL says it again: ";

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

###编辑Makefile

include=/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/include
boost=/usr/include/boost/
lib=/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/lib

all: compile

compile:
	g++ -Wall -I$(include) -o test test.cpp -L/usr/lib -L$(lib) -lmysqlcppconn
run:
	./test

###测试

$ make
g++ -Wall -I/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/include -o test test.cpp -L/usr/lib -L/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/lib -lmysqlcppconn
$ make run
./test

Running 'SELECT 'Hello World!' AS _message'...
	... MySQL replies: Hello World!
	... MySQL says it again: Hello World!

##示例2

###编辑test.cpp 代码来自http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-examples-complete-example-2.html。

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

  // 建立连接
  driver = get_driver_instance();
  con = driver->connect("tcp://127.0.0.1:3306", "root", "123456");
  // 选择数据库test
  con->setSchema("test");
  
  // 建表
  stmt = con->createStatement();
  stmt->execute("DROP TABLE IF EXISTS test");
  stmt->execute("CREATE TABLE test(id INT)");
  // 释放
  delete stmt;

  // 使用预处理语句插入数据，'?'是占位符
  pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");
  for (int i = 1; i <= 10; i++) {
    pstmt->setInt(1, i);
    pstmt->executeUpdate();
  }
  delete pstmt;    // 释放

  // 升序得到数据
  pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");
  res = pstmt->executeQuery();

  // 逆序输出
  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;
}

###Makefile

include=/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/include
boost=/usr/include/boost/
lib=/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/lib

all: compile

compile:
	g++ -Wall -I$(include) -o test test.cpp -L/usr/lib -L$(lib) -lmysqlcppconn
run:
	./test

###测试

$ make    
g++ -Wall -I/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/include -o test test.cpp -L/usr/lib -L/data/letian/Apps/cpp_lib/mysql-connector-c++-1.1.6/lib -lmysqlcppconn
$ make run
./test

Let's have MySQL count from 10 to 1...
	... MySQL counts: 10
	... MySQL counts: 9
	... MySQL counts: 8
	... MySQL counts: 7
	... MySQL counts: 6
	... MySQL counts: 5
	... MySQL counts: 4
	... MySQL counts: 3
	... MySQL counts: 2
	... MySQL counts: 1

##然后

找到感觉了？ 看http://dev.mysql.com/doc/connector-cpp/en/index.html的教程吧！

还有Developing Database Applications Using MySQL Connector/C++。

转载于:https://my.oschina.net/letiantian/blog/469514