Linux MySQL Connector/C++ 编程实例

本篇文章介绍如何在Linux平台使用Connector/C++ 连接数据库，假定MySQL已经安装完成。如果还没有安装MySQL，请先安装。MySQL版本：8.0.19

目录

1. 安装Connector/C++

2. 编写代码

3. 创建数据库

4. 可能遇到的编译问题解决方案

参考文档：

---

示例是一个图书馆管理系统，拥有添加、删除、修改、查询功能。先看下实现效果图：

在看具体代码之前，先看下示例的设计文档：

下面进入正式教程：

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