【链接】https://dev.mysql.com/downloads/connector/cpp/
MySQL Connector/C++ 中的 X DevAPI 是一种新型的、面向文档的编程接口，它为开发者提供了一种与MySQL数据库交互的新方法。X DevAPI 主要用于支持MySQL的文档存储功能，允许开发者以一种更自然的方式操作JSON文档，并提供了对关系数据的支持。
X DevAPI 的主要特性
- NoSQL和SQL双模支持：既可以使用传统的关系型数据库表模型，也可以利用文档模型来存储和查询JSON格式的数据。
- 异步API：支持非阻塞式调用，可以提高应用性能，尤其是在处理大量并发请求时。
- CRUD操作：简化了创建、读取、更新和删除（CRUD）操作的执行过程，无论是对于关系型数据还是文档型数据。
- 表达式语言：内置了一个强大的表达式语言，可以用于构建复杂的查询条件。
- 事务支持：虽然文档存储模型通常不强调事务，但X DevAPI仍然支持在必要时使用事务来确保数据一致性。
使用X DevAPI的基本步骤
- 初始化Session：首先需要创建一个
Session对象来连接到MySQL服务器。 - 选择Schema：通过
Session对象选择或创建一个数据库schema。 - 访问或创建Collection/Table：可以选择已有的collection（用于文档存储）或table（用于关系型数据），也可以创建新的。
- 执行CRUD操作：根据需要对选定的collection或table执行插入、查找、更新或删除操作。
- 关闭Session：完成所有操作后，记得关闭session释放资源。
示例代码
下面是一个简单的示例，演示如何使用X DevAPI向MySQL数据库添加一个文档：
#include <mysqlx/xdevapi.h>
int main() {
try {
// 创建会话
mysqlx::Session session("localhost", 33060, "user", "password");
// 选择或创建数据库
mysqlx::Schema schema = session.getSchema("testdb");
// 获取或创建集合
mysqlx::Collection collection = schema.createCollection("my_collection", true);
// 插入文档
collection.add(R"({"name": "John", "age": 30})").execute();
// 关闭会话
session.close();
}
catch (const mysqlx::Error &err) {
std::cout << "ERROR: " << err.what() << std::endl;
}
catch (std::exception &ex) {
std::cout << "STD EXCEPTION: " << ex.what() << std::endl;
}
return 0;
}这个例子展示了如何连接到MySQL服务器、选择数据库、创建集合并插入JSON文档的过程。请注意，为了使这段代码工作，你需要有正确的MySQL服务器设置以及适当的权限。此外，也需要正确配置你的项目以包含和链接MySQL Connector/C++库。
VS2017+Qt数据库客户端开发
工程介绍
头文件包含
库文件链接
主界面设计
编码实现
头文件
#pragma once
#include <QtWidgets/QMainWindow>
#include "ui_ServerMainwindow.h"
#include <fstream>
#include <mysqlx/xdevapi.h>
#include <iostream>
#include <QStandardItemModel>
using namespace mysqlx;
class ServerMainwindow : public QMainWindow
{
Q_OBJECT
public:
ServerMainwindow(QWidget *parent = nullptr);
~ServerMainwindow();
QString mysqlxValue2Qstring(const mysqlx::Value &value);
public slots:
void logPrint(QString strLog);
void on_pbMysqlConnect_clicked();
void on_pbMysqlDisconnect_clicked();
void on_pbDataBaseInquire_clicked();
void on_pbTablesInquire_clicked();
void on_pbMysqlExec_clicked();
void on_pbSqlExec_clicked();
signals:
bool startServer(quint16 port);
private:
Ui::ServerMainwindowClass ui;
QStandardItemModel* m_model;
Session* m_pCurSession = nullptr;
};
cpp实现
#include "ServerMainwindow.h"
#include <QDateTime>
#include <thread>
#include "PL_Manager.h"
ServerMainwindow::ServerMainwindow(QWidget *parent)
: QMainWindow(parent),
m_model(new QStandardItemModel(this))
{
ui.setupUi(this);
ui.splitter->setStretchFactor(0, 2);
ui.splitter->setStretchFactor(1, 1);
ui.splitterSubR->setStretchFactor(0, 2);
ui.splitterSubR->setStretchFactor(1, 5);
ui.tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
ui.tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
ui.tableView->setModel(m_model);
ui.gbSql->setEnabled(false);
}
ServerMainwindow::~ServerMainwindow()
{
}
void ServerMainwindow::logPrint(QString strLog)
{
QString currentTimeText = QDateTime::currentDateTime().toString("yyyy-MM-dd:hh-mm-ss-zzz ");
ui.plainTextEdit->appendPlainText(currentTimeText+strLog);
}
QString ServerMainwindow::mysqlxValue2Qstring(const mysqlx::Value &value)
{
//mysqlx::Value value = /* 从结果集中获取的值 */;
if (value.isNull())
{
return QString();
}
QString qstr;
switch (value.getType()) {
case mysqlx::Value::Type::STRING:
qstr = QString::fromStdString(value.get<string>());
break;
case mysqlx::Value::Type::INT64:
qstr = QString::number(value.get<int>());
break;
case mysqlx::Value::Type::UINT64:
qstr = QString::number(value.get<unsigned int>());
break;
case mysqlx::Value::Type::FLOAT:
qstr = QString::number(value.get<float>());
break;
case mysqlx::Value::Type::DOUBLE:
qstr = QString::number(value.get<double>());
break;
case mysqlx::Value::Type::BOOL:
qstr = value.get<bool>() ? "true" : "false";
break;
default:
qstr = "Unsupported type";
break;
}
return qstr;
}
void ServerMainwindow::on_pbMysqlConnect_clicked()
{
int nPort = ui.spMysqlPort->value();
QString ip = ui.cbMysqlIP->currentText();
if (m_pCurSession == nullptr)
{
try {
//Session sess("mysqlx://user:password@localhost:33060/mydb");
//SessionSettings settings(SessionOption::USER, "test");
//settings.set(SessionOption::PWD, "test123");
//settings.set(SessionOption::HOST, ip.toStdString());
//settings.set(SessionOption::PORT, std::to_string(nPort));
//settings.set(SessionOption::SSL_MODE, SSLMode::DISABLED);
//settings.set(SessionOption::CONNECT_TIMEOUT, 5000); // 5秒超时
//m_pCurSession = new Session(settings/*ip, nPort, "root", "root"*/);
m_pCurSession = new Session("mysqlx://root:root@localhost:33060"/*ip, nPort, "root", "root"*/);
// 执行简单查询验证
//SqlResult result = sess.sql("SELECT 1").execute();
logPrint("Mysql connect successfully.");
ui.gbSql->setEnabled(true);
}
catch (const mysqlx::Error &err) {
std::string strErr = err.what();
logPrint(QString::fromStdString("Mysql connect failed: " + strErr));
}
}
}
void ServerMainwindow::on_pbMysqlDisconnect_clicked()
{
if (m_pCurSession)
{
ui.gbSql->setEnabled(false);
m_pCurSession->close();
logPrint("Mysql disconnect");
}
}
void ServerMainwindow::on_pbDataBaseInquire_clicked()
{
if (m_pCurSession)
{
try {
// 获取所有数据库(schema)列表
std::vector<mysqlx::Schema> schemas = m_pCurSession->getSchemas();
ui.cbDataBaseList->clear();
QStringList dbList;
for (const auto &schema/*const Schema &schema*/ : schemas) {
std::string avaiDB = schema.getName();
logPrint(QString::fromStdString("Available DB: " + avaiDB));
dbList.append(QString::fromStdString(avaiDB));
}
ui.cbDataBaseList->addItems(dbList);
}
catch (const Error &err) {
std::string strErr = err.what();
logPrint(QString::fromStdString("Mysql getSchemas failed: " + strErr));
}
}
}
void ServerMainwindow::on_pbTablesInquire_clicked()
{
if (m_pCurSession)
{
QString dbName = ui.cbDataBaseList->currentText();
if (dbName.isEmpty())
return;
try {
Schema db = m_pCurSession->getSchema(dbName.toStdString());
ui.cbTableList->clear();
QStringList tableList;
// 获取所有表
std::vector<mysqlx::Table> tables = db.getTables();
for (const auto &table : tables) {
std::string avaiTable = table.getName();
logPrint(QString::fromStdString("Available Table: " + avaiTable));
tableList.append(QString::fromStdString(avaiTable));
}
ui.cbTableList->addItems(tableList);
// 获取所有集合(Collections)
std::vector<mysqlx::Collection> collections = db.getCollections();
std::cout << "Collections in database:" << std::endl;
for (const auto &coll : collections) {
std::cout << "- " << coll.getName() << std::endl;
}
}
catch (const Error &err) {
std::string strErr = err.what();
logPrint(QString::fromStdString("Mysql getTables failed: " + strErr));
}
}
}
void ServerMainwindow::on_pbMysqlExec_clicked()
{
if (!m_pCurSession)
{
return;
}
QString dbName = ui.cbDataBaseList->currentText();
if (dbName.isEmpty())
return;
QString tableName = ui.cbTableList->currentText();
if (tableName.isEmpty())
return;
try {
Schema db = m_pCurSession->getSchema(dbName.toStdString());
if (tableName == "country")
{
Table employees = db.getTable("country");
RowResult result = employees.select("*").execute();
m_model->clear();
QStringList labels = { "Code", "Name", "Continent","Region","SurfaceArea","IndepYear",
"Population", "LifeExpectancy", "GNP","GNPOld","LocalName","GovernmentForm",
"HeadOfState","Capital","Code2" };
m_model->setHorizontalHeaderLabels(labels);
int nRowCnt = result.count();
m_model->setRowCount(nRowCnt); // 预分配行数
for (int r = 0; r < nRowCnt; ++r) {
auto row = result.fetchOne();
for (int c = 0; c < labels.size(); ++c) {
m_model->setData(m_model->index(r, c), mysqlxValue2Qstring(row[c]));
}
}
//QTableView view;
ui.tableView->resizeColumnsToContents();
logPrint(QString::fromStdString("SELECT * FROM country successfully."));
}
}
catch (const Error &err) {
std::string strErr = err.what();
logPrint(QString::fromStdString("Mysql getTables failed: " + strErr));
}
}
void ServerMainwindow::on_pbSqlExec_clicked()
{
QString ip = ui.cbMysqlIP->currentText();
int nPort = ui.spMysqlPort->value();
QString db = ui.leDB->text();
QString table = ui.leTable->text();
if (db.isEmpty() || table.isEmpty())
return;
try {
// 创建会话
Session sess2(ip.toStdString(), nPort, "root", "root", db.toStdString());
auto result = sess2.sql("SELECT * FROM "+ table.toStdString()).execute();
m_model->clear();
QStringList labels = { "Code", "Name", "Continent","Region","SurfaceArea","IndepYear",
"Population", "LifeExpectancy", "GNP","GNPOld","LocalName","GovernmentForm",
"HeadOfState","Capital","Code2" };
m_model->setHorizontalHeaderLabels(labels);
int nRowCnt = result.count();
m_model->setRowCount(nRowCnt); // 预分配行数
for (int r = 0; r < nRowCnt; ++r) {
auto row = result.fetchOne();
for (int c = 0; c < labels.size(); ++c) {
m_model->setData(m_model->index(r, c), mysqlxValue2Qstring(row[c]));
}
}
//QTableView view;
ui.tableView->resizeColumnsToContents();
sess2.close();
}
catch (const Error &err) {
std::cerr << "ERROR: " << err << std::endl;
}
}
数据库端
C:\Program Files\MySQL\MySQL Server 8.2\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| hello |
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
6 rows in set (0.01 sec)
mysql> USE world;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM country;
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-------------------------------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-------------------------------------+---------+-------+
| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW |
| AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF |
| AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO |
| AIA | Anguilla | North America | Caribbean | 96.00 | NULL | 8000 | 76.1 | 63.20 | NULL |
///.............
Zambia | Republic | Frederick Chiluba | 3162 | ZM |
| ZWE | Zimbabwe | Africa | Eastern Africa | 390757.00 | 1980 | 11669000 | 37.8 | 5951.00 | 8670.00 | Zimbabwe | Republic | Robert G. Mugabe | 4068 | ZW |
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-------------------------------------+---------+-------+
239 rows in set (0.00 sec)
mysql>程序实现效果
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
