根据自己学习的内容,有关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, "删除成功", "所选信息删除成功");
}
}
else
QMessageBox::information(this, "提示", "请选择你要删除的信息行");
}
6.总代码
mainwidow.h
#ifndef MAINWINDOW_H
#define MAINWINDOW_H
#include <QMainWindow>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>
#include <QSqlQueryModel>
QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE
class MainWindow : public QMainWindow
{
Q_OBJECT
public:
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, "删除成功", "所选信息删除成功");
}
}
else
QMessageBox::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);
}