实现简单数据库功能 （增删改查）
widget.h
#ifndef WIDGET_H
#define WIDGET_H
#include <QSqlDatabase> // 数据库管理类
#include <QWidget> //
#include <QSqlQuery>
#include <QSqlRecord> //记录类
#include <QTableWidgetItem>QT_BEGIN_NAMESPACE
namespace Ui { class Widget; }
QT_END_NAMESPACEclass Widget : public QWidget
{Q_OBJECTpublic:Widget(QWidget *parent = nullptr);~Widget();void setupConnections();
private slots:void on_addBtn_clicked();void on_showBtn_clicked();void on_deleteBtn_clicked();void on_sortBtn_clicked();void on_msgTable_itemChanged(QTableWidgetItem *item);private:Ui::Widget *ui;//实例化一个数据库对象QSqlDatabase db;//添加一个用来指示是否在更新数据。bool isUpdating = false;
};
#endif // WIDGET_Hwidget.cpp
#include "widget.h"
#include "ui_widget.h"
#include <QMessageBox>
#include <QDebug>Widget::Widget(QWidget *parent): QWidget(parent), ui(new Ui::Widget)
{ui->setupUi(this);//想要添加某个数据库if(!db.contains("stu.db")){//如果当前对象中没有包含所需的数据库，则添加一个数据库db = QSqlDatabase::addDatabase("QSQLITE"); //添加一个sqlite3的数据库db.setDatabaseName("stu.db"); //设置数据库的名称}//打开数据库if(!db.open()){QMessageBox::information(this,"提示","数据库打开失败");return ;}//实例化一个sql语句执行者QSqlQuery querry;QString sql = "create table if not exists STU(id int,name char,sex char,score double)";if(!querry.exec(sql)){QMessageBox::information(this,"提示","数据库创建表失败");return;}//修改操作会自动触发该连接setupConnections();
}Widget::~Widget()
{delete ui;
}//添加
void Widget::on_addBtn_clicked()
{//1.获取ui界面上的相关信息int ui_num = ui->numEdit->text().toUInt(); //获取学号QString ui_name = ui->nameEdit->text(); //姓名QString ui_sex = ui->sexEdit->text(); //性别double ui_score = ui->scoreEdit->text().toDouble();//分数//2.判断信息是否为空if(ui_num == 0 || ui_name.isEmpty() || ui_sex.isEmpty() || ui_score == 0){QMessageBox::information(this,"提示","请将信息填写完整");return;}//3.准备sql语句QString sql = QString("insert into STU(id,name,sex,score) values(%1,'%2','%3',%4);").arg(ui_num).arg(ui_name).arg(ui_sex).arg(ui_score);qDebug()<<sql;//4.实例化sql执行者QSqlQuery querry;//5.执行Sql语句if(!querry.exec(sql)){QMessageBox::information(this,"提示","添加信息失败");}else{QMessageBox::information(this,"提示","添加成功");ui->numEdit->clear();ui->nameEdit->clear();ui->sexEdit->clear();ui->scoreEdit->clear();}
}
//展示
void Widget::on_showBtn_clicked()
{isUpdating = true; // 开始查询，避免触发itemChanged//1.实例化一个sql语句执行者QSqlQuery querry;//2.准备sql语句QString sql = "select * from STU;";//3.执行Sql语句if(!querry.exec(sql)){QMessageBox::information(this,"提示","查询失败");return ;}//程序执行至此，表示已经完成数据库的查询，并将结果存放到querry对象中//清空之前的表格内容ui->msgTable->clear();//设置表头ui->msgTable->setHorizontalHeaderLabels(QStringList() << "学号" << "姓名" << "性别" << "分数");//可以通过next函数进行遍历每个结果集qDebug()<<"查询数据库信息：";int i = 0;while(querry.next()){//获取当前记录QSqlRecord record = querry.record();//对当前记录进行操作,遍历当前记录的所有字段for(int j=0;j<record.count();j++){qDebug() <<record.value(j).toString();//该循环中的value(j)，表示的就是第i行j列的元素QTableWidgetItem *item = new QTableWidgetItem(record.value(j).toString());//QString m = record.value(j).toString();ui->msgTable->setItem(i,j,item);}i++;}isUpdating = false; // 恢复状态
}
//删除后在msgTab上显示
void Widget::on_deleteBtn_clicked()
{//1.获取ui界面上的相关信息int ui_num = ui->numEdit->text().toUInt(); //获取学号QString ui_name = ui->nameEdit->text(); //姓名QString ui_sex = ui->sexEdit->text(); //性别double ui_score = ui->scoreEdit->text().toDouble();//分数if(ui_num == 0 && ui_name.isEmpty() && ui_sex.isEmpty() && ui_score == 0){QMessageBox::information(this,"提示","请填写信息来删除");return;}QSqlQuery querry;QString sql = QString("delete from STU where ""id = %1 or name = '%2' or sex = '%3' or score = %4;").arg(ui_num).arg(ui_name).arg(ui_sex).arg(ui_score);if(!querry.exec(sql)){QMessageBox::information(this,"提示","删除失败");return ;}Widget::on_showBtn_clicked();}
//排序 升序 后在msgTab上显示
void Widget::on_sortBtn_clicked()
{isUpdating = true; // 开始查询，避免触发itemChanged// 1. 实例化一个SQL语句执行者QSqlQuery querry;// 2. 准备SQL语句，按ID升序排序QString sql = "select * from STU order by score ASC;";// 3. 执行SQL语句if(!querry.exec(sql)){QMessageBox::information(this,"提示","查询失败");return ;}// 4. 清空之前的表格内容ui->msgTable->clear();// 5. 设置表头ui->msgTable->setHorizontalHeaderLabels(QStringList() << "学号" << "姓名" << "性别" << "分数");// 6. 读取并显示查询结果int i = 0;while(querry.next()){QSqlRecord record = querry.record();for(int j=0; j<record.count(); j++){QTableWidgetItem *item = new QTableWidgetItem(record.value(j).toString());ui->msgTable->setItem(i, j, item);}i++;}isUpdating = false; // 恢复状态
}void Widget::on_msgTable_itemChanged(QTableWidgetItem *item)
{if (isUpdating) return; // 如果在查询中，直接返回// 获取被修改的行和列int row = item->row();int column = item->column();// 获取学号作为唯一标识int id = ui->msgTable->item(row, 0)->text().toInt();// 根据列的不同，更新对应的字段QString value = item->text();QString sql;if (column == 1) {// 修改姓名sql = QString("update STU set name = '%1' where id = %2;").arg(value).arg(id);} else if (column == 2) {// 修改性别sql = QString("update STU set sex = '%1' where id = %2;").arg(value).arg(id);} else if (column == 3) {// 修改分数double score = value.toDouble();sql = QString("update STU set score = %1 where id = %2;").arg(score).arg(id);}// 执行SQL语句QSqlQuery query;if (!query.exec(sql)) {QMessageBox::information(this, "提示", "修改失败");}
}void Widget::setupConnections()
{connect(ui->msgTable, &QTableWidget::itemChanged, this, &Widget::on_msgTable_itemChanged);
}运行结果
思维导图
