打开主菜单

MySQL Connector/C++基本遵循JDBC 4.0规范。可用于Solaris, Linux, Windows, FreeBSD, Mac OS X, HP-UX and AIX平台。

目前在Windows平台是用Visual C++ 2015开发的,需要注意开发程序时用完全相同的runtime。Debug版本使用std::string传参数时可能会抛出异常,因此可用char*指针;Release版本无问题。

完整示例编辑

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

说明编辑

如果事先不知道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对象早已关闭。

外部链接编辑