0


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,"删除成功","所选信息删除成功");}}else
        QMessageBox::information(this,"提示","请选择你要删除的信息行");}

6.总代码

mainwidow.h

#ifndefMAINWINDOW_H#defineMAINWINDOW_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:voidReshowTable();voidon_pushButton_connectDataBase_clicked();voidon_pushButton_add_clicked();voidon_pushButton_delete_clicked();voidon_pushButton_change_clicked();voidon_pushButton_query_clicked();voidon_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);}
标签: 数据库 ui qt

本文转载自: https://blog.csdn.net/qq_43638158/article/details/129039716
版权归原作者 小赵的学习之路 所有, 如有侵权,请联系我们删除。

“Qt连接数据库,并实现数据库的增删改查”的评论:

还没有评论