跳至內容

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的實參

外部連結

[編輯]
取自「https://zh.wikibooks.org/w/index.php?title=MySQL_Connector/C%2B%2B&oldid=133839」