Qt连接数据库，并实现数据库的增删改查

根据自己学习的内容，有关QTableView显示数据库，并实现数据库的增删改查，在这里做下总结。

1.连接数据库

先来看下连接数据库的效果图。
“连接数据库”按钮的槽函数如下：

void MainWindow::on_pushButton_connectDataBase_clicked(){//连接数据库db.setHostName("127.0.0.1");db.setPort(3306);db.setDatabaseName("test");//数据库名称db.setUserName("root");//用户名db.setPassword("root");//密码qDebug()<<"available drivers:";QStringList drivers = QSqlDatabase::drivers();for(auto driver: drivers)qDebug() << driver;bool ok = db.open();if(ok){QMessageBox::information(this, "提示","数据库连接成功");qDebug()<<"数据库连接成功";}else{QMessageBox::information(this, "提示","数据库连接失败");this->close();qDebug()<<"数据库连接失败";}//若数据库中没有表，则新建QSqlQuery query;QString createTableUser="create table if not exists user(name varchar(8),age int(255), " "sex varchar(8), number int(255));";query.exec(createTableUser);}

若在数据库名等信息没有填错的情况下，数据库连接失败，并打印以下信息，情参考该链接解决：https://blog.csdn.net/weixin_61196167/article/details/120654423

当运行点击”连接数据库”按钮后，会根据信息连接对应的数据库，若连接的该数据库没有user表时，会自动创建一个user表，创建的表信息如下图所示，该图为Navicat Premium 15软件内的截图。

2.查询数据库并显示在QTableView上

先看下效果图：

首先定义一个QSqlQueryModel数据模型，并于对应的QTableView绑定，如下：

QSqlQueryModel *userMode;//数据模型userMode = new QSqlQueryModel(ui->tableView);//绑定

设置点击查询的槽函数：

//查询void MainWindow::on_pushButton_query_clicked(){userMode->setQuery("select * from user;");ReshowTable();}void MainWindow::ReshowTable(){userMode->setHeaderData(0,Qt::Horizontal,tr("姓名"));userMode->setHeaderData(1,Qt::Horizontal,tr("年龄"));userMode->setHeaderData(2,Qt::Horizontal,tr("性别"));userMode->setHeaderData(3,Qt::Horizontal,tr("手机号"));ui->tableView->setModel(userMode);}

3.添加

先看添加的效果图：

在信息输入内对应填写各信息，填写完成后点击“添加”按钮，可添加完成输入的对应信息，与数据库内容同步，添加按钮的槽函数：

//添加void MainWindow::on_pushButton_add_clicked(){QString name = ui->lineEdit_name->text();QString age = ui->lineEdit_age->text();QString sex = ui->lineEdit_sex->text();QString number = ui->lineEdit_number->text();QSqlQuery query;QString str1 = "'" + name +"','" + age + "','" +sex+"','"+number+"'";QString str = "insert into user(name, age, sex, number) values(" + str1 + ");";query.exec(str);userMode->setQuery("select * from user;");ReshowTable();}

4.修改

修改的效果图：

设置点击表格中的某一列，然后将点击那一列的信息显示在编辑信息的文本框内，修改完成后点击修改即可完成修改，对应函数如下函数：

void MainWindow::on_tableView_pressed(const QModelIndex &index){QString name = userMode->data(userMode->index(index.row(), 0)).toString();QString age = userMode->data(userMode->index(index.row(), 1)).toString();QString sex = userMode->data(userMode->index(index.row(), 2)).toString();QString number = userMode->data(userMode->index(index.row(), 3)).toString();ui->lineEdit_name->setText(name);ui->lineEdit_age->setText(age);ui->lineEdit_sex->setText(sex);ui->lineEdit_number->setText(number);}//修改void MainWindow::on_pushButton_change_clicked(){QSqlQuery query;QString str = "update user set name='" + ui->lineEdit_name->text() + "',age='" + ui->lineEdit_age->text() + "',sex='" + ui->lineEdit_sex->text() + "' where number='" + ui->lineEdit_number->text() + "';";query.exec(str);QMessageBox::information(this, "修改成功", "表信息修改成功");userMode->setQuery("select * from user;");ReshowTable();}

5.删除

删除的效果图：

删除的槽函数如下：

//删除void MainWindow::on_pushButton_delete_clicked(){QSqlQuery query;int currentrow = ui->tableView->currentIndex().row();userMode->removeRow(currentrow);if(currentrow != -1)//若选中一行{int ok=QMessageBox::warning(this,tr("删除当前行!"),tr("你确定删除当前行吗？"), QMessageBox::Yes,QMessageBox::No);//如确认删除if(ok == QMessageBox::Yes){userMode->removeRow(currentrow);QString name = userMode->data(userMode->index(currentrow,0)).toString();//获取姓名QString del = "delete from user where name='" + name + "';";query.exec(del);userMode->setQuery("select * from user");ui->tableView->setModel(userMode);QMessageBox::information(this, "删除成功", "所选信息删除成功");}}elseQMessageBox::information(this, "提示", "请选择你要删除的信息行");}

6.总代码

mainwidow.h

#ifndef MAINWINDOW_H#define MAINWINDOW_H#include #include #include #include #include #include QT_BEGIN_NAMESPACEnamespace Ui { class MainWindow; }QT_END_NAMESPACEclass MainWindow : public QMainWindow{Q_OBJECTpublic:MainWindow(QWidget *parent = nullptr);~MainWindow();private slots:void ReshowTable();void on_pushButton_connectDataBase_clicked();void on_pushButton_add_clicked();void on_pushButton_delete_clicked();void on_pushButton_change_clicked();void on_pushButton_query_clicked();void on_tableView_pressed(const QModelIndex &index);private:Ui::MainWindow *ui;QSqlDatabase db= QSqlDatabase::addDatabase("QMYSQL");QSqlQueryModel *userMode;//数据模型};#endif // MAINWINDOW_H

mainwindow.cpp

#include "mainwindow.h"#include "ui_mainwindow.h"MainWindow::MainWindow(QWidget *parent): QMainWindow(parent), ui(new Ui::MainWindow){ui->setupUi(this);userMode = new QSqlQueryModel(ui->tableView);//绑定}MainWindow::~MainWindow(){delete ui;}void MainWindow::ReshowTable(){userMode->setHeaderData(0,Qt::Horizontal,tr("姓名"));userMode->setHeaderData(1,Qt::Horizontal,tr("年龄"));userMode->setHeaderData(2,Qt::Horizontal,tr("性别"));userMode->setHeaderData(3,Qt::Horizontal,tr("手机号"));ui->tableView->setModel(userMode);}void MainWindow::on_pushButton_connectDataBase_clicked(){//连接数据库db.setHostName("127.0.0.1");db.setPort(3306);db.setDatabaseName("test");//数据库名称db.setUserName("root");//用户名db.setPassword("root");//密码qDebug()<<"available drivers:";QStringList drivers = QSqlDatabase::drivers();for(auto driver: drivers)qDebug() << driver;bool ok = db.open();if(ok){QMessageBox::information(this, "提示","数据库连接成功");qDebug()<<"数据库连接成功";}else{QMessageBox::information(this, "提示","数据库连接失败");this->close();qDebug()<<"数据库连接失败";}//若数据库中没有表，则新建QSqlQuery query;QString createTableUser="create table if not exists user(name varchar(8),age int(255), " "sex varchar(8), number int(255));";query.exec(createTableUser);}//添加void MainWindow::on_pushButton_add_clicked(){QString name = ui->lineEdit_name->text();QString age = ui->lineEdit_age->text();QString sex = ui->lineEdit_sex->text();QString number = ui->lineEdit_number->text();QSqlQuery query;QString str1 = "'" + name +"','" + age + "','" +sex+"','"+number+"'";QString str = "insert into user(name, age, sex, number) values(" + str1 + ");";query.exec(str);userMode->setQuery("select * from user;");ReshowTable();}//删除void MainWindow::on_pushButton_delete_clicked(){QSqlQuery query;int currentrow = ui->tableView->currentIndex().row();userMode->removeRow(currentrow);if(currentrow != -1)//若选中一行{int ok=QMessageBox::warning(this,tr("删除当前行!"),tr("你确定删除当前行吗？"), QMessageBox::Yes,QMessageBox::No);//如确认删除if(ok == QMessageBox::Yes){userMode->removeRow(currentrow);QString name = userMode->data(userMode->index(currentrow,0)).toString();//获取姓名QString del = "delete from user where name='" + name + "';";query.exec(del);userMode->setQuery("select * from user");ui->tableView->setModel(userMode);QMessageBox::information(this, "删除成功", "所选信息删除成功");}}elseQMessageBox::information(this, "提示", "请选择你要删除的信息行");}//修改void MainWindow::on_pushButton_change_clicked(){QSqlQuery query;QString str = "update user set name='" + ui->lineEdit_name->text() + "',age='" + ui->lineEdit_age->text() + "',sex='" + ui->lineEdit_sex->text() + "' where number='" + ui->lineEdit_number->text() + "';";query.exec(str);QMessageBox::information(this, "修改成功", "表信息修改成功");userMode->setQuery("select * from user;");ReshowTable();}//查询void MainWindow::on_pushButton_query_clicked(){userMode->setQuery("select * from user;");ReshowTable();}void MainWindow::on_tableView_pressed(const QModelIndex &index){QString name = userMode->data(userMode->index(index.row(), 0)).toString();QString age = userMode->data(userMode->index(index.row(), 1)).toString();QString sex = userMode->data(userMode->index(index.row(), 2)).toString();QString number = userMode->data(userMode->index(index.row(), 3)).toString();ui->lineEdit_name->setText(name);ui->lineEdit_age->setText(age);ui->lineEdit_sex->setText(sex);ui->lineEdit_number->setText(number);}