QT 简单的MySQL数据库使用例子

港控/mmm° 2023-06-08 14:58 136阅读 0赞

###                                               QT 简单的MySQL数据库使用例子 ###

**一、简述**

**       **  记--简单的使用MySQL数据库的编程例子。

例子：链接: https://pan.baidu.com/s/1fkJXhyYzyDghx7ciOUcUtA&shfl=shareset 提取码: d7ap

**二、效果**

![20191018211350674.gif][]

**三、工程结构**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 1][]

相关软件版本：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 2][]

**四、源文件**

** **       main.cpp文件**        **

#include "mainwindow.h"
    #include "logindialog.h"
    #include <QApplication>
    
    int main(int argc, char *argv[])
    {
        QApplication a(argc, argv);
    
        MainWindow *main = new MainWindow();
    
        LoginDialog login;
        if (login.exec() == QDialog::Accepted)//调用login.exec()，阻塞主控制流，直到完成返回，继续执行主控制流
        {
           main->loadData();
           main->show();
           return a.exec();
        }
        return 0;
    }

adddialog.h文件

#ifndef ADDDIALOG_H
    #define ADDDIALOG_H
    
    #include <QDialog>
    #include <QIntValidator>
    
    #ifndef STUDENTSTRUCT
    //自定义信息结构体
    struct StudentInfo
    {
        int id;
        QString name;
    };
    #define STUDENTSTRUCT
    #endif
    
    namespace Ui {
    class AddDialog;
    }
    
    class AddDialog : public QDialog
    {
        Q_OBJECT
    
    public:
        explicit AddDialog(QWidget *parent = 0);
        ~AddDialog();
    
    signals:
        void sig_addData(StudentInfo info);
    
    private slots:
        void on_pushButtonAdd_clicked();
        void on_pushButtonReturn_clicked();
    
    private:
        Ui::AddDialog *ui;
        QIntValidator* mIntValidator;
    };
    
    #endif // ADDDIALOG_H

adddialog.cpp文件

#include "adddialog.h"
    #include "ui_adddialog.h"
    #include <QMessageBox>
    #include <QDebug>
    
    AddDialog::AddDialog(QWidget *parent) :
        QDialog(parent),
        ui(new Ui::AddDialog)
    {
        ui->setupUi(this);
        //去掉问号按钮
        Qt::WindowFlags flags= this->windowFlags();
        setWindowFlags(flags&~Qt::WindowContextHelpButtonHint);
    
        //设置ID范围
        mIntValidator = new QIntValidator(1, 65535, this);
        ui->lineEditID->setValidator(mIntValidator);
    }
    
    AddDialog::~AddDialog()
    {
        delete ui;
    }
    
    //添加数据
    void AddDialog::on_pushButtonAdd_clicked()
    {
        StudentInfo info;
        QString sid = ui->lineEditID->text();
        QString name = ui->lineEditName->text();
        if(sid.isEmpty() || name.isEmpty())
        {
            QMessageBox::information(this, "提示", "ID和姓名不能为空");
            return;
        }
    
        info.id = sid.toInt();
        info.name = name;
    
        //将数据发送主界面
        emit sig_addData(info);
    
        //accept();//关闭窗体，并设置返回值为Accepted
    }
    
    //取消
    void AddDialog::on_pushButtonReturn_clicked()
    {
        this->close();
    }

logindialog.h文件

#ifndef LOGINDIALOG_H
    #define LOGINDIALOG_H
    
    #include <QDialog>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QIntValidator>
    
    namespace Ui {
    class LoginDialog;
    }
    
    class LoginDialog : public QDialog
    {
        Q_OBJECT
    
    public:
        explicit LoginDialog(QWidget *parent = 0);
        ~LoginDialog();
        bool connDataBase(QSqlDatabase& db, QString dataBaseName, QString userName, QString Pass, QString hostName = "127.0.0.1", QString port = "3306", QString sqlType = "QMYSQL");
        void setSqlDatabase(QSqlDatabase& db);
    
    private slots:
        void on_pushButtonLogin_clicked();
    
        void on_pushButtonExit_clicked();
    
        void on_pushButtonInit_clicked();
    
    private:
        Ui::LoginDialog *ui;
        QSqlDatabase mDb;
        QIntValidator* mIntValidator;
    };
    
    #endif // LOGINDIALOG_H

logindialog.cpp文件

#include "logindialog.h"
    #include "ui_logindialog.h"
    #include <QIntValidator>
    #include <QMessageBox>
    #include <QSqlError>
    #include <QDebug>
    
    QSqlDatabase gDb;//多窗体共用对象
    
    LoginDialog::LoginDialog(QWidget *parent) :
        QDialog(parent),
        ui(new Ui::LoginDialog)
    {
        ui->setupUi(this);
    
        //设置窗体标题
        this->setWindowTitle("登录");
    
        //去掉问号按钮
        Qt::WindowFlags flags= this->windowFlags();
        setWindowFlags(flags&~Qt::WindowContextHelpButtonHint);
    
        //设置端口范围
        mIntValidator = new QIntValidator(1, 65535, this);
        ui->lineEditPort->setValidator(mIntValidator);
    
        //填充comboBox 数据库类型
        QStringList drivers = QSqlDatabase::drivers();
        if(drivers.size()>0)
        {
            ui->comboBoxSqlType->addItems(drivers);
        }
        else
        {
            ui->comboBoxSqlType->addItem("None");
            ui->pushButtonInit->setVisible(false);
            ui->pushButtonLogin->setVisible(false);
        }
    }
    
    LoginDialog::~LoginDialog()
    {
        delete ui;
    }
    
    /* 登录
     *
     */
    void LoginDialog::on_pushButtonLogin_clicked()
    {
    
        QString sqlType = ui->comboBoxSqlType->currentText();
        if(sqlType.isEmpty() || sqlType == "None")
        {
            QMessageBox::information(this, "提示", "请安装数据库！");
            return;
        }
    
        QString hostName = ui->lineEditHostName->text();
        QString port = ui->lineEditPort->text();
        QString dataBaseName = ui->lineEditDatabaseName->text();
        QString userName = ui->lineEditUserName->text();
        QString pwd = ui->lineEditPass->text();
    
        if(hostName.isEmpty() || port.isEmpty() || dataBaseName.isEmpty() || userName.isEmpty())
        {
            return;
        }
    
        bool ok = connDataBase(mDb, dataBaseName, userName, pwd, hostName, port, sqlType);
        if(ok)
        {
            gDb = mDb;
            accept();//关闭窗体，并设置返回值为Accepted
        }
        else
        {
            QMessageBox::information(this, "提示", "连接失败！");
            //qDebug()<<mDb.lastError().number();
        }
    
    }
    
    /* 连接数据库
     *
     */
    bool LoginDialog::connDataBase(QSqlDatabase& db, QString dataBaseName, QString userName, QString Pass,QString hostName, QString port, QString sqlType)
    {
        if(db.isDriverAvailable(sqlType))
        {
            if(QSqlDatabase::contains("qt_sql_default_connection"))
            {
                db = QSqlDatabase::database("qt_sql_default_connection");
            }
            else
            {
                db = QSqlDatabase::addDatabase(sqlType);
            }
    
            db.setHostName(hostName);
            db.setPort(port.toInt());
            db.setDatabaseName(dataBaseName);
            db.setUserName(userName);
            db.setPassword(Pass);
    
            if(db.open())
            {
                return true;
            }
            db.close();
            db.removeDatabase(sqlType);
        }
    
        return false;
    }
    
    //退出
    void LoginDialog::on_pushButtonExit_clicked()
    {
        this->close();
    }
    
    //用于第一次创建数据库和表
    void LoginDialog::on_pushButtonInit_clicked()
    {
        //先使用自带的数据库mysql进行初始化（安装好MySQL数据库就存在的）
        QSqlDatabase myDb;
        if(QSqlDatabase::contains("qt_sql_default_connection"))
        {
            myDb = QSqlDatabase::database("qt_sql_default_connection");
        }
        else
        {
            myDb = QSqlDatabase::addDatabase("QMYSQL");
        }
    
        myDb.setHostName("127.0.0.1");
        myDb.setPort(3306);
        myDb.setDatabaseName("mysql");
        myDb.setUserName("root");
        myDb.setPassword("123456");
        if(!myDb.open())
        {
            return;
        }
    
        QSqlQuery sqlQuery(myDb);
    
        //创建数据库
        sqlQuery.exec("create database if not exists Mydatabase");
    
        //指定当前使用的数据库
        sqlQuery.exec("use Mydatabase");
    
        //创建表
        sqlQuery.exec("create table Student(id int(9) not null primary key,name varchar(10) not null);");
    
        //插入2条数据
        sqlQuery.exec("insert into Student values (1001,'liang'),(1002,'lisi')");
    
        myDb.close();
        QMessageBox::information(this, "提示", "数据库:Mydatabase\n 表:Student\n创建完成!");
    
    }

mainwindow.h

文件

#ifndef MAINWINDOW_H
    #define MAINWINDOW_H
    
    #include <QMainWindow>
    #include <QSqlDatabase>
    #include <QStandardItemModel>
    #include "adddialog.h"
    #include "modifydialog.h"
    #include <QSqlQuery>
    
    namespace Ui {
    class MainWindow;
    }
    
    class MainWindow : public QMainWindow
    {
        Q_OBJECT
    
    public:
        explicit MainWindow(QWidget *parent = 0);
        ~MainWindow();
        int loadData();
    
    private slots:
        void on_pushButtonAdd_clicked();
        void slot_addData(StudentInfo info);
        void slot_showMenu(QPoint);
        void slot_delData(bool b);
        void on_actionAbout_triggered();
        void slot_modifyData(StudentInfo info);
        void slot_showAddDlg(bool b);
        void slot_modifyDataByDoubleClick(QModelIndex modelIndex);
    
    private:
        Ui::MainWindow *ui;
        QStandardItemModel* mStdItemModel;//表头
        AddDialog* mAddDlg;//添加对话框
        ModifyDialog* mMdfDlg;//修改对话框
        QMenu* mMenu;//右键菜单
        QAction* mDelAction;//菜单删除动作
        QAction* mAddAction;//菜单添加动作
    };
    
    #endif // MAINWINDOW_H

mainwindow.cpp文件

#include "mainwindow.h"
    #include "ui_mainwindow.h"
    #include <QDebug>
    #include <QMessageBox>
    #include <QSqlError>
    
    //要操作的数据库表
    #define TABLENAME "Student"
    
    extern QSqlDatabase gDb;
    
    MainWindow::MainWindow(QWidget *parent) :
        QMainWindow(parent),
        ui(new Ui::MainWindow)
    {
        ui->setupUi(this);
    
        //注册自定义数据类型（作为信号参数）
        qRegisterMetaType<StudentInfo>("StudentInfo");
    
        //设置窗体标题
        this->setWindowTitle("主界面");
    
        //屏蔽最大化按钮
        Qt::WindowFlags flags= this->windowFlags();
        setWindowFlags(flags&~Qt::WindowMaximizeButtonHint);
    
        //固定窗体大小
        this->setFixedSize(this->width(), this->height()-23);
    
        //创建表头
        mStdItemModel = new QStandardItemModel();
        //添加两列
        mStdItemModel->setColumnCount(2);
        mStdItemModel->setHeaderData(0,Qt::Horizontal,"ID");
        mStdItemModel->setHeaderData(1,Qt::Horizontal,"姓名");
    
        //设置到tableView控件
        ui->tableViewDataList->setModel(mStdItemModel);
    
        //设置tableView可以整行选中
        ui->tableViewDataList->setSelectionBehavior(QAbstractItemView::SelectRows);
    
        //设置表头背景色
        //ui->tableViewDataList->horizontalHeader()->setStyleSheet("QHeaderView::section{background:skyblue;}");
    
        //设置不可双击编辑
        ui->tableViewDataList->setEditTriggers(QAbstractItemView::NoEditTriggers);
    
        //添加窗体，修改窗体信号与槽连接需要非空对象
        mAddDlg = new AddDialog();
        mMdfDlg = new ModifyDialog();
    
        //初始化右键菜单对象
        mMenu = new QMenu(ui->tableViewDataList);
        mDelAction = mMenu->addAction("删除");
        mAddAction = mMenu->addAction("添加");
    
        //连接信号槽函数，接收并处理 添加页面发来的数据
        connect(mAddDlg, SIGNAL(sig_addData(StudentInfo)), this, SLOT(slot_addData(StudentInfo)));
    
        //设置菜单相应策略
        ui->tableViewDataList->setContextMenuPolicy(Qt::CustomContextMenu);
    
        //连接右键菜单 信号与槽
        connect(ui->tableViewDataList, SIGNAL(customContextMenuRequested(QPoint)),this, SLOT(slot_showMenu(QPoint)));
        connect(mDelAction, SIGNAL(triggered(bool)), this, SLOT(slot_delData(bool)));
        connect(mAddAction, SIGNAL(triggered(bool)), this, SLOT(slot_showAddDlg(bool)));
    
        //双击列表项时处理
        connect(ui->tableViewDataList, SIGNAL(doubleClicked(QModelIndex)), this, SLOT(slot_modifyDataByDoubleClick(QModelIndex)));
    
        //修改窗体回传修改后的数据到主界面
        connect(mMdfDlg, SIGNAL(sig_modifyData(StudentInfo)), this, SLOT(slot_modifyData(StudentInfo)));
    
    }
    
    MainWindow::~MainWindow()
    {
        delete ui;
    }
    
    //从数据库加载数据
    int MainWindow::loadData()
    {
        QSqlQuery sqlQuery(gDb);
        QString msg = "主机名:" + gDb.hostName() \
                    + "\n数据库:" +gDb.databaseName() \
                    + "\n用户名:" + gDb.userName();
        ui->labelMsg->setText(msg);
        ui->labelMsg->setToolTip(msg);
    
        bool ok = gDb.open();
        if(ok)
        {
            QString sql = "select * from " + QString(TABLENAME);
            ok = sqlQuery.exec(sql);
        }
    
        if(!ok)
        {
            //添加测试数据
            mStdItemModel->setItem(0,0,new QStandardItem("1001"));
            mStdItemModel->setItem(0,1,new QStandardItem("Liang"));
            return 0;
        }
    
        int i = 0;
        while(sqlQuery.next())
        {
            //qDebug()<<sqlQuery.value("id").toString()<<","<<sqlQuery.value("name").toString();
            mStdItemModel->setItem(i,0,new QStandardItem(sqlQuery.value("id").toString()));
            mStdItemModel->setItem(i,1,new QStandardItem(sqlQuery.value("name").toString()));
            i++;
        }
    
        QString tips;
        tips = tips.asprintf("成功加载 %d 条数据!", i);
        ui->statusBar->setToolTip(tips);
        ui->statusBar->showMessage(tips);
        return 0;
    }
    
    //添加数据
    void MainWindow::on_pushButtonAdd_clicked()
    {
        if(nullptr == mAddDlg)
        {
            mAddDlg = new AddDialog();
        }
        mAddDlg->exec();
    }
    
    //处理 来自添加页面的数据
    void MainWindow::slot_addData(StudentInfo info)
    {    
        QSqlQuery sqlQuery(gDb);
    
        //插入语句："insert into Student values (1001,'liang')"
        QString sql = "insert into "+QString(TABLENAME)+" values (" +QString::number(info.id) +",'" + info.name +"')";
    
        QString result = "添加成功!";
        if(sqlQuery.exec(sql))
        {
            int count = mStdItemModel->rowCount();
            mStdItemModel->setItem(count,0,new QStandardItem(QString::number(info.id)));
            mStdItemModel->setItem(count,1,new QStandardItem(info.name));
            result += "id:" + QString::number(info.id);
        }
        else
        {
            result = "添加失败："+sqlQuery.lastError().text();
        }
        ui->statusBar->setToolTip(result);
        ui->statusBar->showMessage(result);
    }
    
    //显示右键菜单
    void MainWindow::slot_showMenu(QPoint)
    {
        mMenu->exec(QCursor::pos());//在当前鼠标位置显示
    }
    
    //删除选中项
    void MainWindow::slot_delData(bool b)
    {
        Q_UNUSED(b);
    
        QSqlQuery sqlQuery(gDb);
    
        //获取的是列索引 ui->tableViewDataList->selectionModel()->currentIndex();
        QModelIndexList indexList = ui->tableViewDataList->selectionModel()->selectedRows();
        QModelIndex index;
    
        QString id;
        int i, sel, sucess = 0;
        int size = indexList.size();
        for(i=size-1; i>=0; i--)//倒着删除，顺着删除要处理索引偏移
        {
            index = indexList.at(i);
            sel = index.row();
            id= mStdItemModel->data(mStdItemModel->index(index.row(),0)).toString();//第row行第0列的内容
            //根据ID删除语句 "delete from Student where id='1001'"
            QString sql = "delete from "+QString(TABLENAME)+" where id='"+id+"'";
            if(sqlQuery.exec(sql))
            {
                sucess++;
                mStdItemModel->removeRow(sel);
            }
        }
    
        if(size>0)
        {
            QString tips;
            tips = tips.asprintf("选中 %d条数据，成功删除 %d 条数据！", size, sucess);
            ui->statusBar->setToolTip(tips);
            ui->statusBar->showMessage(tips);
        }
    
    }
    
    //添加
    void MainWindow::slot_showAddDlg(bool b)
    {
        Q_UNUSED(b);
        mAddDlg->exec();
    }
    
    //菜单的关于动作
    void MainWindow::on_actionAbout_triggered()
    {
        QMessageBox::information(this, "关于", "这是操作数据库的简单例子。");
    }
    
    //弹出修改数据窗口
    void MainWindow::slot_modifyDataByDoubleClick(QModelIndex modelIndex)
    {
        Q_UNUSED(modelIndex);
    
        QString id,name;
        int row;
        QModelIndex index = ui->tableViewDataList->currentIndex();
        if(index.isValid())
        {
            row = index.row();
            id= mStdItemModel->data(mStdItemModel->index(row,0)).toString();//第row行第0列的内容
            name= mStdItemModel->data(mStdItemModel->index(row,1)).toString();//第row行第1列的内容
            mMdfDlg->setContent(id, name);
            mMdfDlg->exec();
        }
    }
    
    //修改数据
    void MainWindow::slot_modifyData(StudentInfo info)
    {
        QSqlQuery sqlQuery(gDb);
    
        //修改语句：update Student set name='liang' where id=1001;
        QString sql = "update "+QString(TABLENAME)+" set name='" + info.name +"' where id=" + QString::number(info.id);
    
        QString result = "修改成功!";
        if(sqlQuery.exec(sql))
        {
            int i;
            QString id;
            int count = mStdItemModel->rowCount();
            for(i=0; i<count; i++)//根据id找到要修改的数据项
            {
                id = mStdItemModel->data(mStdItemModel->index(i,0)).toString();
                if(info.id == id.toInt())
                {
                    mStdItemModel->setItem(i,0,new QStandardItem(QString::number(info.id)));
                    mStdItemModel->setItem(i,1,new QStandardItem(info.name));
                    break;
                }
            }
        }
        else
        {
            result = "修改失败:"+sqlQuery.lastError().text();
        }
        ui->statusBar->setToolTip(result);
        ui->statusBar->showMessage(result);
    }

modifydialog.h文件

#ifndef MODIFYDIALOG_H
    #define MODIFYDIALOG_H
    
    #include <QDialog>
    #include <QIntValidator>
    
    #ifndef STUDENTSTRUCT
    //自定义信息结构体
    struct StudentInfo
    {
        int id;
        QString name;
    };
    #endif
    
    namespace Ui {
    class ModifyDialog;
    }
    
    class ModifyDialog : public QDialog
    {
        Q_OBJECT
    
    public:
        explicit ModifyDialog(QWidget *parent = 0);
        ~ModifyDialog();
        void setContent(QString id, QString name);
    
    signals:
        void sig_modifyData(StudentInfo);
    
    private slots:
        void on_pushButtonReturn_clicked();
    
        void on_pushButtonModify_clicked();
    
    private:
        Ui::ModifyDialog *ui;
        QIntValidator* mIntValidator;
    };
    
    #endif // MODIFYDIALOG_H

modifydialog.cpp文件

**五、总结**

5.1 使用MySQL数据库需要启动MySQL服务。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 3][]

ps：打开这个服务管理界面：直接搜索services.msc并启动，或者是在命令行(黑窗口)输入services.msc并回车即可弹出该界面。

5.2 编程使用MySQL数据库相关操作类，需要在.pro文件添加: QT += sql

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 4][]

5.3 有一些版本的QtCreator需要将一个libmySQLdll库放在编译器的路径下(一般为mingwxxx/bin)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 5][]

[20191018211350674.gif]: /images/20230531/9ba08349666f4ada957bbd0abda46804.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70]: /images/20230531/44d9564137aa41599b5842d7603c412e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 1]: /images/20230531/4f14c74f1cac4d689510275dad8f81b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 2]: /images/20230531/4ead2f91e08f40a08e0b44b003a5769e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 3]: /images/20230531/3550634693824e31a35341855fb01d9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 4]: /images/20230531/78a18a7447ea48c58528e7f4909be92f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmZlaWJ1eWk_size_16_color_FFFFFF_t_70 5]: /images/20230531/049b7c9b0d7e429798e3fb4d144251da.png