本篇文章介绍如何在Linux平台使用Connector/C++ 连接数据库,假定MySQL已经安装完成。如果还没有安装MySQL,请先安装。MySQL版本:8.0.19
目录
示例是一个图书馆管理系统,拥有添加、删除、修改、查询功能。先看下实现效果图:
在看具体代码之前,先看下示例的设计文档:
下面进入正式教程:
1. 安装Connector/C++
下载地址(https://dev.mysql.com/downloads/connector/cpp/)。
下载解压完成后,如下图:
lib64存放库文件,include存放头文件。
2. 编写代码
新建library目录。然后在该目录下新建三个文件。library_main.cpp, library_sql.h, library_book.h Makefile 。代码如下:
library_main.cpp
/*
* Desc: Linux c++ MySQL 示例,图书馆管理系统,主程序
* Author: mason
* Date: 20200203
*/
#include "library_book.h"
#include "library_sql.h"
#define LIBRARY_ADD_BOOK 1 // 添加
#define LIBRARY_DELETE_BOOK 2 // 删除
#define LIBRARY_UPDATE_BOOK 3 // 修改
#define LIBRARY_QUERY_BOOK 4 // 查询
#define LIBRARY_QUIT 5 // 退出
// MySQL单例类静态变量初始化
sql::Driver* sql_loader::driver = nullptr;
sql::Connection* sql_loader::con = nullptr;
sql::Statement* sql_loader::stmt = nullptr;
sql::ResultSet* sql_loader::res = nullptr;
bool sql_loader::init = false;
// 系统使用说明
void Usage()
{
cout<<"\n欢迎使用图书管理系统\n"
"1:添加图书\n"
"2:删除图书\n"
"3:修改图书\n"
"4:查询图书\n"
"5:退出系统\n"
<<endl;
return ;
}
// 读取图书信息
void ReadBook(Book &new_book)
{
cout<<"请输入添加的书名:";
cin>>new_book.book_name;
cout<<"请输入作者:";
cin>>new_book.author;
//cout<<"[DEBUG]"<<author<<endl;
cout<<"请输入价格:";
cin>>new_book.price;
cout<<"请输入数量:";
cin>>new_book.quantity;
return ;
}
int main()
{
bool is_quit = false;
int op_code = -1, book_id;
string book_name, author;
unsigned int price, quantity;
Book new_book;
vector<Book> book_list;
// 主循环
while (!is_quit)
{
Usage();
cout<<"请输入你的选择:";
cin>>op_code;
switch (op_code)
{
// 添加操作
case LIBRARY_ADD_BOOK:
// 读取图书信息
ReadBook(new_book);
sql_loader::add(new_book);
break;
// 删除操作
case LIBRARY_DELETE_BOOK:
sql_loader::delete_book();
break;
// 修改操作
case LIBRARY_UPDATE_BOOK:
cout<<"请输入待修改的编号:";
cin>>new_book.id;
ReadBook(new_book);
sql_loader::update(new_book);
break;
// 查询操作
case LIBRARY_QUERY_BOOK:
sql_loader::query(book_list);
// 打印查询结果
cout << "------------- 查询开始 ----------------" <<endl;
for (auto &iter : book_list)
{
cout <<"编号: "<< iter.id << endl;
cout <<"书名: "<< iter.book_name << endl;
cout <<"作者: "<< iter.author << endl;
cout <<"数量: "<< iter.quantity << endl;
cout <<"价格: "<< iter.price << endl;
cout << "-----------------------------" <<endl;
}
cout << "------------- 查询结束 ----------------" <<endl;
// 清空上次查询结果
book_list.erase(book_list.begin(), book_list.end());
break;
// 退出系统
case LIBRARY_QUIT:
is_quit = true;
break;
default:
cout<<"未识别的操作:"<<op_code<<endl;
break;
}
}
cout<<"感谢使用"<<endl;
return 0;
}
library_book.h
/*
* Desc: Linux c++ MySQL 示例,图书馆管理系统,图书类
* Author: mason
* Date: 20200203
*/
#pragma once
#include <string>
#include <iostream>
using namespace std;
class Book
{
public:
int id; // 图书编号
string book_name; // 书名
string author; // 作者
unsigned int price; // 价格
unsigned int quantity; // 数量
public:
// 默认构造函数
Book(){}
// 含参构造函数
Book(string book_name, string author, int price, int quantity, int book_id = 0):book_name(book_name),author(author),
price(price),quantity(quantity), id(book_id){}
// 析构函数
~Book(){}
};
library_sql.h
/*
* Desc: Linux c++ MySQL 示例,图书馆管理系统,MySQL单例
* Author: mason
* Date: 20200203
*/
#pragma once
#include <vector>
#include "library_book.h"
#include <mysql_connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#define SQL_QUERY_ALL 0 // 查询所有
#define SQL_QUERY_BY_AUTHOR 1 // 查询作者
#define SQL_QUERY_BY_ID 2 // 查询编号
#define SQL_QUERY_BY_BOOK_NAME 3 // 查询书名
#define SQL_DELETE_ALL 0 // 删除所有
#define SQL_DELETE_BY_AUTHOR 1 // 删除作者
#define SQL_DELETE_BY_ID 2 // 删除编号
#define SQL_DELETE_BY_BOOK_NAME 3 // 删除书名
using namespace std;
class sql_loader
{
public:
static sql::Driver *driver;
static sql::Connection *con;
static sql::Statement *stmt;
static sql::ResultSet *res;
static bool init;
// sql 初始化
static void sql_loader_init()
{
if (sql_loader::init)
{
cout<<"sql 已经完成初始化"<<endl;
return ;
}
/* Create a connection */
driver = get_driver_instance();
// 密码和用户名
con = driver->connect("tcp://127.0.0.1:3306", "root", "123456");
/* Connect to the MySQL test database ,数据库名*/
con->setSchema("library");
init = true;
cout<<"sql 初始化成功"<<endl;
return ;
}
// 添加图书
static void add(Book &new_book)
{
// SQL初始化
sql_loader_init();
// 创建并构造sql语句,用完后手动删除
stmt = con->createStatement();
string sql_cmd = "INSERT INTO books(name, author, price, quantity) VALUES ('";
sql_cmd.append(new_book.book_name);
sql_cmd.append("','");
sql_cmd.append(new_book.author);
sql_cmd.append("', ");
sql_cmd.append(to_string(new_book.price));
sql_cmd.append(", ");
sql_cmd.append(to_string(new_book.quantity));
sql_cmd.append(")");
cout<<"insert cmd : "<<sql_cmd<<endl;
try{
stmt->execute(sql_cmd);
} 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;
}
// 删除资源
delete stmt;
return ;
}
// 修改图书
static void update(Book& new_book)
{
// SQL初始化
sql_loader_init();
// 创建并构造sql语句,用完后手动删除
stmt = con->createStatement();
string sql_cmd = "update books set name='";
sql_cmd.append(new_book.book_name);
sql_cmd.append("', author='");
sql_cmd.append(new_book.author);
sql_cmd.append("', price=");
sql_cmd.append(to_string(new_book.price));
sql_cmd.append(", quantity=");
sql_cmd.append(to_string(new_book.quantity));
sql_cmd.append(" where id=");
sql_cmd.append(to_string(new_book.id));
cout<<"update cmd : "<<sql_cmd<<endl;
try{
stmt->execute(sql_cmd);
} 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;
}
// 释放资源
delete stmt;
return ;
}
// 删除图书
static void delete_book()
{
int op_code = -1, book_id;
string author, book_name;
string sql_cmd = "delete from books";
cout<<"请输入删除方式:\n"
"0: 删除所有图书\n"
"1: 删除指定作者\n"
"2: 删除指定编号\n"
"3: 删除指定图书"<<endl;
cin>>op_code;
switch (op_code)
{
case SQL_DELETE_ALL:
break;
case SQL_DELETE_BY_AUTHOR:
cout<<"请输入要删除的作者:";
cin>>author;
sql_cmd.append(" where author='");
sql_cmd.append(author);
sql_cmd.append("'");
break;
case SQL_DELETE_BY_ID:
cout<<"请输入要删除的编号:";
cin>>book_id;
sql_cmd.append(" where id=");
sql_cmd.append(to_string(book_id));
break;
case SQL_DELETE_BY_BOOK_NAME:
cout<<"请输入要删除的书名:";
cin>>book_name;
sql_cmd.append(" where name='");
sql_cmd.append(book_name);
sql_cmd.append("'");
break;
default:
cout<<"不存在 "<<op_code<<" 对应的删除方式,删除失败"<<endl;
return ;
}
cout<<"delete cmd : "<<sql_cmd<<endl;
// 初始化SQL
sql_loader_init();
// 创建并构造sql语句,用完后手动删除
stmt = con->createStatement();
try{
// 执行SQL语句
stmt->execute(sql_cmd);
// 释放资源
delete stmt;
} 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;
}
return ;
}
// 查询图书
static void query(vector<Book>& book_list)
{
int op_code = -1, book_id;
string author, book_name;
string sql_cmd = "select id, name, author, quantity, price from books";
cout<<"请输入查询方式:\n"
"0: 查询所有\n"
"1: 查询作者\n"
"2: 查询编号\n"
"3: 查询书名"<<endl;
cin>>op_code;
switch (op_code)
{
case SQL_QUERY_ALL:
break;
case SQL_QUERY_BY_AUTHOR:
cout<<"请输入要查询的作者:";
cin>>author;
sql_cmd.append(" where author='");
sql_cmd.append(author);
sql_cmd.append("'");
break;
case SQL_QUERY_BY_ID:
cout<<"请输入要查询的编号:";
cin>>book_id;
sql_cmd.append(" where id=");
sql_cmd.append(to_string(book_id));
break;
case SQL_QUERY_BY_BOOK_NAME:
cout<<"请输入要查询的书名:";
cin>>book_name;
sql_cmd.append(" where name='");
sql_cmd.append(book_name);
sql_cmd.append("'");
break;
default:
cout<<"不存在 "<<op_code<<" 对应的查询方式,查询失败"<<endl;
return ;
}
cout<<"query cmd : "<<sql_cmd<<endl;
// 初始化SQL
sql_loader_init();
// 创建并构造sql语句,用完后手动删除
stmt = con->createStatement();
try{
// 执行查询
res = stmt->executeQuery(sql_cmd);
while (res->next()) {
/* Access column data by alias or column name */
cout << res->getInt("id") << res->getString("name") << res->getString("author") << res->getInt("quantity") << endl;
book_list.push_back(Book(res->getString("name"), res->getString("author"), res->getInt("price"), res->getInt("quantity"), res->getInt("id")));
/* Access column data by numeric offset, 1 is the first column */
//cout << res->getString(1) << endl;
}
// 资源释放
delete res;
delete stmt;
} 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;
}
return ;
}
private:
sql_loader(){}
};
Makefile,编译路径根据实际路径做调整。
default:
g++ -std=c++11 -I /data/home/code/github/cpp-test/mysql/include/jdbc -L /data/home/code/github/cpp-test/mysql/lib64 library_main.cpp -o app -lmysqlcppconn
clean:
rm -rf app
3. 创建数据库
// 创建数据库
create database library;
// 创建图书表
create table books (
id int not null auto_increment,
name varchar(1024) not null,
author varchar(1024) not null,
price int not null default 0,
quantity int not null default 1,
primary key(id)
)DEFAULT CHARACTER SET = utf8;
4. 可能遇到的编译问题解决方案
1. jdbc/mysql_connection.h:37:32: fatal error: boost/shared_ptr.hpp: No such file or directory
解决方式是安装boost-devel,yum安装命令如下,ubuntu或者其它平台请自行搜索。
yum install boost-devel
2. # ERR: Host '127.0.0.1' is not allowed to connect to this MySQL server (MySQL error code: 1130, SQLState: HY000 )
无法连接MySQL,先查看本地msyqld是否允许以及能否登陆,都正常可能是权限问题,参考这篇博客:
https://blog.csdn.net/eric_sunah/article/details/18567091
参考文档:
1. https://docs.oracle.com/cd/E17952_01/connector-cpp-1.1-en