MySQL Connector/C++
MySQL Connector/C++基本遵循JDBC 4.0规范。可用于Solaris, Linux, Windows, FreeBSD, Mac OS X, HP-UX and AIX平台。
在Windows平台从 Connector/C++ 8.0.14开始是用Visual C++ 2015开发的，需要注意开发程序时用完全相同的runtime。你的应用程序应该使用与Connector/C++相同的配置，如/MD, /MDd, /MT, /MTd. 否则，Debug版本使用std::string传参数时可能会抛出异常，因此可用char*指针；Release版本无问题。
64位与32位库分别放在lib64与lib目录下。如果使用静态链接，注意先定义宏STATIC_CONCPP，而且连接库的静态版本是/MDd选项下编译出的。
完整示例
[编辑]/* Standard C++ headers */
#include <iostream>
#include <sstream>
#include <memory>
#include <string>
#include <stdexcept>
/* MySQL Connector/C++ specific headers */
#include <driver.h>
#include <connection.h>
#include <statement.h>
#include <prepared_statement.h>
#include <resultset.h>
#include <metadata.h>
#include <resultset_metadata.h>
#include <exception.h>
#include <warning.h>
#pragma comment(lib,"mysqlcppconn.lib")
using namespace sql;
#define DBHOST "tcp://127.0.0.1:3306"
#define USER "root"
#define PASSWORD ""
#define DATABASE "testdb"
int db_communication()
{
Driver *driver;
std::unique_ptr<Connection> con(nullptr);
std::unique_ptr<Statement> stmt(nullptr);
std::unique_ptr<ResultSet> res(nullptr);
std::unique_ptr<PreparedStatement> prep_stmt(nullptr);
Savepoint* savept(nullptr);
try
{
driver = get_driver_instance();
/* create a database connection using the Driver */
con.reset(driver->connect(DBHOST, USER, PASSWORD));//第一个参数："tcp://[hostname[:port]][/schemaname]"
/* turn off the autocommit */
con->setAutoCommit(0);
cout << "\nDatabase connection\'s autocommit mode = " << con->getAutoCommit() << endl;
/* select appropriate database schema */
con->setSchema(DATABASE);
/* retrieve and display the database metadata */
if (con->isClosed()) {
throw runtime_error("DatabaseMetaData FAILURE - database connection closed");
}
cout << "\nDatabase Metadata" << endl;
cout << "-----------------" << endl;
cout << boolalpha;
DatabaseMetaData *dbcon_meta = con->getMetaData();
cout << "Database Product Name: " << dbcon_meta->getDatabaseProductName() << endl;
cout << "Database Product Version: " << dbcon_meta->getDatabaseProductVersion() << endl;
cout << "Database User Name: " << dbcon_meta->getUserName() << endl << endl;
cout << "Driver name: " << dbcon_meta->getDriverName() << endl;
cout << "Driver version: " << dbcon_meta->getDriverVersion() << endl << endl;
cout << "Database in Read-Only Mode?: " << dbcon_meta->isReadOnly() << endl;
cout << "Supports Transactions?: " << dbcon_meta->supportsTransactions() << endl;
cout << "Supports DML Transactions only?: " << dbcon_meta->supportsDataManipulationTransactionsOnly() << endl;
cout << "Supports Batch Updates?: " << dbcon_meta->supportsBatchUpdates() << endl;
cout << "Supports Outer Joins?: " << dbcon_meta->supportsOuterJoins() << endl;
cout << "Supports Multiple Transactions?: " << dbcon_meta->supportsMultipleTransactions() << endl;
cout << "Supports Named Parameters?: " << dbcon_meta->supportsNamedParameters() << endl;
cout << "Supports Statement Pooling?: " << dbcon_meta->supportsStatementPooling() << endl;
cout << "Supports Stored Procedures?: " << dbcon_meta->supportsStoredProcedures() << endl;
cout << "Supports Union?: " << dbcon_meta->supportsUnion() << endl << endl;
cout << "Maximum Connections: " << dbcon_meta->getMaxConnections() << endl;
cout << "Maximum Columns per Table: " << dbcon_meta->getMaxColumnsInTable() << endl;
cout << "Maximum Columns per Index: " << dbcon_meta->getMaxColumnsInIndex() << endl;
cout << "Maximum Row Size per Table: " << dbcon_meta->getMaxRowSize() << " bytes" << endl;
cout << "\nDatabase schemas: " << endl;
std::unique_ptr < ResultSet > rs(dbcon_meta->getSchemas());
cout << "\nTotal number of schemas = " << rs->rowsCount() << endl;
cout << endl;
int row = 1;
while (rs->next()) {
cout << "\t" << row << ". " << rs->getString("TABLE_SCHEM") << endl;
++row;
} // while
cout << endl;
/* create a statement object */
stmt.reset(con->createStatement());
/* run a query which returns exactly one result set */
res.reset(stmt->executeQuery("SELECT * FROM bd_pred_dut"));
/* retrieve the data from the result set and display on stdout */
/* retrieve the row count in the result set */
cout << "\nRetrieved " << res->rowsCount() << " row(s)." << endl;
/* fetch the data : retrieve all the rows in the result set */
while (res->next()) {
cout << res->getString(2) << endl;
cout << res->getString("ab") << endl;
} // if-else
cout << endl;
/* retrieve and display the result set metadata */
if (rs->rowsCount() == 0) {
throw runtime_error("ResultSetMetaData FAILURE - no records in the result set");
cout << "ResultSet Metadata" << endl;
ResultSetMetaData *res_meta = rs->getMetaData();
int numcols = res_meta->getColumnCount();
cout << "\nNumber of columns in the result set = " << numcols << endl << endl;
cout.width(20);
cout << "Column Name/Label";
cout.width(20);
cout << "Column Type";
cout.width(20);
cout << "Column Size" << endl;
for (int i = 0; i < numcols; ++i) {
cout.width(20);
cout << res_meta->getColumnLabel(i + 1);
cout.width(20);
cout << res_meta->getColumnTypeName(i + 1);
cout.width(20);
cout << res_meta->getColumnDisplaySize(i + 1) << endl << endl;
}
cout << "\nColumn \"" << res_meta->getColumnLabel(1);
cout << "\" belongs to the Table: \"" << res_meta->getTableName(1);
cout << "\" which belongs to the Schema: \"" << res_meta->getSchemaName(1) << "\"" << endl << endl;
/* insert couple of rows of data into table using Prepared Statements */
prep_stmt.reset(con->prepareStatement("INSERT INTO bd_pred_threshold (gas) VALUES (?)"));
cout << "\tInserting \"London\" into the table, City .." << endl;
prep_stmt->setString(1, "London");
int updatecount = prep_stmt->executeUpdate();
cout << "\tCreating a save point \"SAVEPT1\" .." << endl;
savept = con->setSavepoint("SAVEPT1");
cout << "\tInserting \"Paris\" into the table, City .." << endl;
prep_stmt->setString(1, "Paris");
updatecount = prep_stmt->executeUpdate();
cout << "\tRolling back until the last save point \"SAVEPT1\" .." << endl;
con->rollback(savept);
con->releaseSavepoint(savept);
cout << "\tCommitting outstanding updates to the database .." << endl;
con->commit();
/* re-use result set object */
res.reset(stmt->executeQuery("SELECT * FROM bd_pred_threshold"));
/* retrieve the data from the result set and display on stdout */
//........
/* Clean up */
// delete res;
// delete stmt;
// delete prep_stmt;
con->close();
// delete con;
}
}
catch (SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__ << ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl;
if (e.getErrorCode() == 1047) {
/*
Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
Message: Unknown command
*/
cout << "\nYour server does not seem to support Prepared Statements at all. ";
cout << "Perhaps MYSQL < 4.1?" << endl;
}
return EXIT_FAILURE;
}
catch (std::runtime_error &e) {
cout << "ERROR: runtime_error in " << __FILE__;
cout << " (" << __func__ << ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what() << endl;
return EXIT_FAILURE;
}
}
另一个综合示例
[编辑]#include "mysql_connection.h"
#include "mysql_driver.h"
#include "cppconn/statement.h"
#include "cppconn/resultset.h"
#include "cppconn/connection.h"
#include <cppconn/prepared_statement.h>
#include <iostream>
#include <stdio.h>
using namespace std;
using namespace sql;
using namespace sql::mysql;
void RunnConnectionMysql()
{
mysql::MySQL_Driver *driver;
Connection *conn;
Statement *state;
ResultSet *res;
sql::PreparedStatement *prep_stmt;
Savepoint *savept;
driver = sql::mysql::get_driver_instance();
conn = driver->connect("127.0.0.1:3306", "root", "root");
conn->setAutoCommit(0);
state = conn->createStatement();
// state->execute("use test");
conn->setSchema("test");
state->execute("create table if not exists tt (id int not null auto_increment, name varchar(20) not null, age int, primary key(id))");
state->execute("delete from tt");
state->execute("drop table tt");
res = state->executeQuery("select * from user");
ResultSetMetaData * metaData = res->getMetaData();
int rows = (int)res->rowsCount();
int cols = (int)metaData->getColumnCount();
cout << "the cols num:" << cols << endl;
cout << "the rows num:" << rows << endl;
while(res->next())
{
string name = res->getString("name");
int gender = res->getInt("gender");
cout << name << " " <<gender <<endl;
}
//print column name and column type
for(int i=0;i<cols; ++i)
{
cout << metaData->getColumnLabel(i+1) << " " << metaData->getColumnTypeName(i+1) << endl;
}
//print table name and database name
cout << metaData->getTableName(1) << " : " << metaData->getSchemaName(1) << endl;
//change the data of table
prep_stmt = conn->prepareStatement("insert into user(name, sex, password) values(?, ?, ?)");
prep_stmt->setString(1, "lala");
prep_stmt->setInt(2, 24);
prep_stmt->setString(3, "lala");
int updatecount = prep_stmt->executeUpdate();
//set save point
savept = conn->setSavepoint("savept1");
prep_stmt->setString(1, "hehe");
prep_stmt->setInt(2, 2);
prep_stmt->setString(3, "lala");
updatecount = prep_stmt->executeUpdate();
conn->rollback(savept);
conn->releaseSavepoint(savept);
conn->commit();
delete res;
delete state;
delete prep_stmt;
conn->close();
delete conn;
}
int main(int argc, char *argv[])
{
RunnConnectionMysql();
getchar();
return 0;
}
说明
[编辑]如果事先不知道SQL语句是SELECT还是INSERT，UPDATE或DELETE，可以使用Statement::execute函数。当SQL语句是SELECT操作时，execute()返回true，当SQL语句是INSERT，UPDATE，DELETE操作时，execute()返回false。如果语句是SELECT查询操作，可以调用Statement的getResultSet成员函数获取查询结果集。如果语句是INSERT,UPDATE,DELETE操作，可以调用getUpdateCount()获取受影响的行数。getMoreResults()可以检查是否还有其他结果集合（如执行一个存储过程）。
ResultSet维护一个游标（cursor），指向其当前数据行。利用游标可以顺序读取数据集中的行。在一行中，列的值可以以任意顺序访问。可以根据列的位置（offset，从1开始）或它们的列名来访问。后者具有一定的容错性，尤其是在表的架构发生改变后。使用列标签或列名会使代码更加清晰。列标签就是SQL“AS”子句中指定的标签名。存储在ResultSet中的数据可以通过getString() 或 getInt()等不同数据类型的成员函数获取。注意std::string类型参数如果抛出异常，实参使用char*。ResulSet的next和previous函数移动游标指向下一行数据。一旦Statement生成了结果集，该ResultSet会一直有效，直到显示或隐式的关闭结果集才会失效，即使生成他的Statement对象早已关闭。
connect选项
[编辑]有两种创建connection的方法：
//第一种：
sql::Connection * MySQL_Driver::connect(const sql::SQLString& hostName,
const sql::SQLString& userName,
const sql::SQLString& password)
sql::mysql::MySQL_Driver *driver;
sql::Connection *con;
driver = sql::mysql::MySQL_Driver::get_mysql_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "root", "rootpass");
//第二种：
sql::Connection * MySQL_Driver::connect(sql::ConnectOptionsMap & properties)
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);
URL的字符串格式通常为：
tcp://host:port/db_Name
可设置的connection选项：
- characterSetResults: .
- charsetDir:对应于MYSQL_SET_CHARSET_DIR
- CLIENT_COMPRESS： client/server协议中使用压缩。对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_FOUND_ROWS：是否返回被发现（匹配）的行数。对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_IGNORE_SIGPIPE：是否阻止libmysqlclient客户库安装SIGPIPE处理器。对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_IGNORE_SPACE：是否函数名后面允许空格。 对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_INTERACTIVE：是否允许interactive_timeout秒数（而不是wait_timeout秒数）。对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_LOCAL_FILES：是否允许LOAD DATA LOCAL INFILE处理。对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_MULTI_STATEMENTS：客户端在同一字符串中发送多条分号分隔的SQL语句。 对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_MULTI_RESULTS：没有此选项。它总是被允许。对应于C API的mysql_real_connect()参数client_flag的同名选项
- CLIENT_NO_SCHEMA：是否禁止语法 db_name.tbl_name.col_name 对应于C API的mysql_real_connect()参数client_flag的同名选项
- defaultAuth：缺省的authentication插件名字。对应于mysql_options() C API的 MYSQL_DEFAULT_AUTH。
- defaultPreparedStatementResultType：MySQL_Connection::prepareStatement()返回的结果集是否可混动。sql::ResultSet::TYPE_FORWARD_ONLY 或 sql::ResultSet::TYPE_SCROLL_INSENSITIVE。不支持 The sql::ResultSet::TYPE_SCROLL_SENSITIVE
- defaultStatementResultType： MySQL_Connection::createStatement()返回结果集是否可滚动。sql::ResultSet::TYPE_FORWARD_ONLY或sql::ResultSet::TYPE_SCROLL_INSENSITIVE. 不支持sql::ResultSet::TYPE_SCROLL_SENSITIVE
- hostName：用于连接字符串
- OPT_CAN_HANDLE_EXPIRED_PASSWORDS：对应于mysql_options() C API的MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
- OPT_CHARSET_NAME：缺省字符集。对应于mysql_options() C API的 MYSQL_SET_CHARSET_NAME
- OPT_CONNECT_ATTR_ADD：连接时传递给服务器的当前连接属性的键值对。对应于mysql_options4() C API的 MYSQL_OPT_CONNECT_ATTR_ADD 。值是std::map< sql::SQLString, sql::SQLString > 。
- OPT_CONNECT_ATTR_DELETE：对应于mysql_options() C API的MYSQL_OPT_CONNECT_ATTR_DELETE 值为std::list< sql::SQLString >
- OPT_CONNECT_ATTR_RESET ：对应于mysql_options() C API的MYSQL_OPT_CONNECT_ATTR_RESET
- OPT_CONNECT_TIMEOUT：连接超时秒数。对应于mysql_options() C API的MYSQL_OPT_CONNECT_TIMEOUT。值为unsigned integer.
- OPT_ENABLE_CLEARTEXT_PLUGIN：允许mysql_clear_password cleartext authentication插件。对应于mysql_options() C API的MYSQL_ENABLE_CLEARTEXT_PLUGIN。布尔值
- OPT_GET_SERVER_PUBLIC_KEY： 当caching_sha2_password或sha256_password鉴定插件所需。对应于mysql_options() C API的MYSQL_OPT_GET_SERVER_PUBLIC_KEY。布尔值。为MySQL 8.0 GA所要求。
- OPT_LOCAL_INFILE：是否允许语句LOAD DATA LOCAL INFILE。对应于mysql_options() C API的MYSQL_OPT_LOCAL_INFILE
- OPT_NAMED_PIPE ：对应于 mysql_options() C API的 MYSQL_OPT_NAMED_PIPE。未使用。
- OPT_READ_TIMEOUT：读超时秒数。对应于mysql_options() C API的MYSQL_OPT_READ_TIMEOUT
- OPT_RECONNECT：是否允许自动重连接。对应于mysql_options() C API的MYSQL_OPT_RECONNECT。缺省为false
- OPT_REPORT_DATA_TRUNCATION：是否允许在prepared 语句中报告数据截断错误。对应于mysql_options() C API的MYSQL_REPORT_DATA_TRUNCATION
- OPT_TLS_VERSION：例如connection_properties["OPT_TLS_VERSION"] = sql::SQLString("TLSv1.1,TLSv1.2"); 缺省允许所有协议。
- OPT_WRITE_TIMEOUT：写超时秒数。mysql_options() C API的MYSQL_OPT_WRITE_TIMEOUT
- password：用于连接字符串
- pipe：命名的管道
- pluginDir：对应于mysql_options() C API 的 MYSQL_PLUGIN_DIR
- port：用于连接字符串
- postInit：驱动器初始化后执行的语句。
- preInit：驱动器初始化之前执行的居于。对应于mysql_options() C API的MYSQL_INIT_COMMAND。例如 "SET NAMES utf8"
- readDefaultFile：代替my.cnf的配置文件。对应于mysql_options() C API的MYSQL_READ_DEFAULT_FILE
- readDefaultGroup：my.cnf或其他配置文件中的给定用户群的读权限。对应于mysql_options() C API的MYSQL_READ_DEFAULT_GROUP
- rsaKey：服务器RSA公钥的文件路径名。对应于mysql_options() C API的MYSQL_SERVER_PUBLIC_KEY
- schema：缺省数据库名。对应于mysql_real_connect() C API的db参数。
- socket：Unix domain socket文件名，用于socket-file connection。对应于mysql_real_connect() C API参数。
- sslCA：PEM格式的文件路径名，包含一个受信的SSL CA的列表。对应于mysql_options() C API的MYSQL_OPT_SSL_CA
- sslCAPath：包含PEM格式的受信的SSL CA certificates的文件路径名。对应于mysql_options() C API的MYSQL_OPT_SSL_CAPATH
- sslCert ：包含PEM格式的SSLcertificate文件的路径名。对应于mysql_options() C API的MYSQL_OPT_SSL_CERT
- sslCipher：允许的SSL加密器的列表。 对应于mysql_options() C API的MYSQL_OPT_SSL_CIPHER。
- sslCRL：包含PEM格式的文件，其包含了certificate revocation列表。对应于mysql_options() C API的MYSQL_OPT_SSL_CRL。
- sslCRLPath：包含PEM格式的certificate revocation列表的文件路径名。对应于mysql_options() C API的MYSQL_OPT_SSL_CRLPATH
- sslEnforce：是否强制使用SSL做连接。对应于mysql_options() C API的MYSQL_OPT_SSL_ENFORCE
- sslKey：包含PEM格式的SSL key文件的名字。对应于mysql_options() C API的MYSQL_OPT_SSL_KEY
- sslVerify：是否允许连接时验证证书中的服务器的Common Name。对应于mysql_options() C API的MYSQL_OPT_SSL_VERIFY_SERVER_CERT
- useLegacyAuth：是否允许连接到不支持password hashing的服务器。对应于mysql_options() C API的MYSQL_SECURE_AUTH。
- userName：用户名。对应于mysql_real_connect() C API的实参