Qt5.9数据库操作sqlite完整例子

先上效果图吧:

 

 本例子连接：

链接：https://pan.baidu.com/s/17JmeJGvoj7oJROcNsBX6Kg   提取码：b051

    有关数据库的操作部分，可以学习下sqlite编程，如果你还不会数据库编程的话，那要加油去看看基本的东西了。本例子是基于
sqlite3 的编程，下面我们一起来看看吧。

首先是工程文件中一定要加：

QT       += sql

主界面的头文件：

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include    <QLabel>
#include    <QString>

#include <QtSql>
#include <QDataWidgetMapper>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

private:
    QSqlDatabase DB; //数据库
    QSqlQueryModel *qryModel; //数据库模型
    QItemSelectionModel *theSelection; //选择模型
private:
    void  openTable();//打开数据表
    void  updateRecord(int recNo); //更新记录
public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_actOpenDB_triggered();

    void on_actRecInsert_triggered();

    void on_actRecDelete_triggered();

    void on_actRecEdit_triggered();

    void on_actScan_triggered();

    void on_tableView_doubleClicked(const QModelIndex &index);

private:
    Ui::MainWindow *ui;
};

#endif // MAINWINDOW_H

主页面布局： 

 

设置界面的布局：

这个类是WDialogData 继承为 QDialog

来看：

#ifndef WDIALOGDATA_H
#define WDIALOGDATA_H

#include <QDialog>
#include    <QSqlRecord>

namespace Ui {
class WDialogData;
}

class WDialogData : public QDialog
{
    Q_OBJECT
private:
     QSqlRecord  mRecord; //保存一条记录的数据

public:
    explicit WDialogData(QWidget *parent = nullptr);
    ~WDialogData();

     void    setUpdateRecord(QSqlRecord &recData); //更新记录
     void    setInsertRecord(QSqlRecord &recData); //插入记录

     QSqlRecord  getRecordData();//获取录入的数据

private slots:
     void on_btnSetPhoto_clicked();

     void on_btnClearPhoto_clicked();

private:
    Ui::WDialogData *ui;
};

#endif // WDIALOGDATA_H

这里确定与取消要设定为:

这样目的可以与主页面进行页面的交互。

对应的cpp文件：

#include "wdialogdata.h"
#include "ui_wdialogdata.h"
#include    <QFileDialog>

WDialogData::WDialogData(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::WDialogData)
{
    ui->setupUi(this);
}

WDialogData::~WDialogData()
{
    delete ui;
}

void WDialogData::setUpdateRecord(QSqlRecord &recData)
{
  //编辑记录，更新记录数据到界面
    mRecord = recData;
    ui->spinEmpNo->setEnabled(false); //员工编号不许编程
    setWindowTitle("跟新记录");
    //根据recData的数据更新界面显示

    ui->spinEmpNo->setValue(recData.value("empNo").toInt());
    ui->editName->setText(recData.value("Name").toString());
    ui->comboSex->setCurrentText(recData.value("Gender").toString());
    ui->spinHeight->setValue(recData.value("Height").toFloat());
    ui->editBirth->setDate(recData.value("Birthday").toDate());
    ui->editMobile->setText(recData.value("Mobile").toString());
    ui->comboProvince->setCurrentText(recData.value("Province").toString());
    ui->editCity->setText(recData.value("City").toString());
    ui->comboDep->setCurrentText(recData.value("Department").toString());
    ui->comboEdu->setCurrentText(recData.value("Education").toString());
    ui->spinSalary->setValue(recData.value("Salary").toInt());
    ui->editMemo->setPlainText(recData.value("Memo").toString());

    QVariant va=recData.value("Photo");//
    if (!va.isValid())  //图片字段内容为空
        ui->LabPhoto->clear();
    else {
        QByteArray data=va.toByteArray();
        QPixmap pic;
        pic.loadFromData(data);
        ui->LabPhoto->setPixmap(pic.scaledToWidth(ui->LabPhoto->size().width()));
    }

}

void WDialogData::setInsertRecord(QSqlRecord &recData)
{
    //插入记录，无需更新界面显示，但是要存储recData的字段结构
    mRecord=recData; //保存recData到内部变量
    ui->spinEmpNo->setEnabled(true); //插入的记录，员工编号允许编辑
    setWindowTitle("插入新记录");
    ui->spinEmpNo->setValue(recData.value("empNo").toInt());
}

QSqlRecord WDialogData::getRecordData()
{
    //"确定"按钮后，界面数据保存到记录mRecord
        mRecord.setValue("empNo",ui->spinEmpNo->value());
        mRecord.setValue("Name",ui->editName->text());
        mRecord.setValue("Gender",ui->comboSex->currentText());
        mRecord.setValue("Height",ui->spinHeight->value());
        mRecord.setValue("Birthday",ui->editBirth->date());
        mRecord.setValue("Mobile",ui->editMobile->text());

        mRecord.setValue("Province",ui->comboProvince->currentText());
        mRecord.setValue("City",ui->editCity->text());
        mRecord.setValue("Department",ui->comboDep->currentText());

        mRecord.setValue("Education",ui->comboEdu->currentText());
        mRecord.setValue("Salary",ui->spinSalary->value());
        mRecord.setValue("Memo",ui->editMemo->toPlainText());
    //照片编辑时已经修改了mRecord的photo字段的值

     return  mRecord; //以记录作为返回值
}

void WDialogData::on_btnSetPhoto_clicked()
{
    QString aFile = QFileDialog::getOpenFileName(this,"选择图片文件","",
                                                 "照片(*.jpg)");
    if(aFile.isEmpty())
        return;

    QByteArray data;
    QFile * file  = new QFile(aFile);
    file->open(QIODevice::ReadOnly); //只读
    data = file->readAll();
    file->close();

    mRecord.setValue("photo",data); //图片保存到Photo字段
    QPixmap pic;
    pic.loadFromData(data);
    ui->LabPhoto->setPixmap(pic.scaledToWidth(ui->LabPhoto->size().width()));
}

void WDialogData::on_btnClearPhoto_clicked()
{
   //清除照片
    ui->LabPhoto->clear();
    mRecord.setNull("Photo"); //Photo字段清空
}

下面是主页面中的程序部分：

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include    <QFileDialog>
#include    <QMessageBox>
#include "wdialogdata.h"
void MainWindow::openTable()
{
    //打开数据表
    qryModel = new QSqlQueryModel(this);
    theSelection = new QItemSelectionModel(qryModel);

    qryModel->setQuery("SELECT empNo,name,Gender,Height,Birthday,Mobile,"
         "Province, City,Department,Education,Salary FROM employee order by empNo");
    //数据查询
    if(qryModel->lastError().isValid())
    {
        QMessageBox::information(this, "错误", "数据表查询错误,错误信息\n"+qryModel->lastError().text(),
                                 QMessageBox::Ok,QMessageBox::NoButton);
        return;
    }

    qryModel->setHeaderData(0,Qt::Horizontal,"工号");
    qryModel->setHeaderData(1,Qt::Horizontal,"姓名");
    qryModel->setHeaderData(2,Qt::Horizontal,"性别");
    qryModel->setHeaderData(3,Qt::Horizontal,"身高");
    qryModel->setHeaderData(4,Qt::Horizontal,"出生日期");
    qryModel->setHeaderData(5,Qt::Horizontal,"手机");
    qryModel->setHeaderData(6,Qt::Horizontal,"省份");
    qryModel->setHeaderData(7,Qt::Horizontal,"城市");
    qryModel->setHeaderData(8,Qt::Horizontal,"部门");
    qryModel->setHeaderData(9,Qt::Horizontal,"学历");
    qryModel->setHeaderData(10,Qt::Horizontal,"工资");

    ui->tableView->setModel(qryModel);
    ui->tableView->setSelectionModel(theSelection);

    ui->actOpenDB->setEnabled(false);

    ui->actRecInsert->setEnabled(true);
    ui->actRecDelete->setEnabled(true);
    ui->actRecEdit->setEnabled(true);
    ui->actScan->setEnabled(true);

}

void MainWindow::updateRecord(int recNo)
{
    //更新一条记录
    QSqlRecord curRec=qryModel->record(recNo); //获取当前记录
    int empNo = curRec.value("EmpNo").toInt(); //获取EmpNo
    QSqlQuery query; //查询出当前记录的所有字段
    query.prepare("select * from employee where EmpNo = :ID"); //设置准备执行的 SQL 语句，一般用于带参数的 SQL 语句
    query.bindValue(":ID",empNo); //设置 SQL 语句中参数的俏，以占位符表示参数
    query.exec();//执行由 prepare（）和 bindValue（）设置的 SQL 语句
    query.first();

    if(!query.isValid()) //判断是否无效
    {
        return;  //无效返回函数
    }

    curRec = query.record();//返回当前记录
    //另一个界面
    WDialogData * dataDialog = new WDialogData(this);
    Qt::WindowFlags    flags=dataDialog->windowFlags();
    dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint); //设置对话框固定大小

    dataDialog->setUpdateRecord(curRec);//调用对话框函数更新数据和界面
     int ret=dataDialog->exec();// 以模态方式显示对话框
     if(ret == QDialog::Accepted) //OK键被按下
     {
         QSqlRecord recData = dataDialog->getRecordData();//获取返回的数据
         query.prepare("update employee set Name = :Name, Gender = :Gender,Height=:Height,"
                       " Birthday=:Birthday, Mobile=:Mobile, Province=:Province,"
                       " City=:City, Department=:Department, Education=:Education,"
                       " Salary=:Salary, Memo=:Memo, Photo=:Photo "
                       " where EmpNo = :ID");
         query.bindValue(":Name",recData.value("Name"));
         query.bindValue(":Gender",recData.value("Gender"));
         query.bindValue(":Height",recData.value("Height"));
         query.bindValue(":Birthday",recData.value("Birthday"));
         query.bindValue(":Mobile",recData.value("Mobile"));

         query.bindValue(":Province",recData.value("Province"));
         query.bindValue(":City",recData.value("City"));
         query.bindValue(":Department",recData.value("Department"));
         query.bindValue(":Education",recData.value("Education"));

         query.bindValue(":Salary",recData.value("Salary"));
         query.bindValue(":Memo",recData.value("Memo"));
         query.bindValue(":Photo",recData.value("Photo"));

         query.bindValue(":ID",empNo);

         if(!query.exec())
         {
             QMessageBox::critical(this, "错误", "记录更新错误\n"+query.lastError().text(),
                                      QMessageBox::Ok,QMessageBox::NoButton);
         }
         else {
            qryModel->query().exec();//数据模型重新查询数据，更新tableView显示
//             qryModel->setQuery("SELECT empNo,name,Gender,Height,Birthday,Mobile,"
//                  "Province, City,Department,Education,Salary FROM employee order by empNo");
           qryModel->layoutChanged();

         }
     }
}

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    this->setCentralWidget(ui->tableView); //设置为中间

    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->setAlternatingRowColors(true);

}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_actOpenDB_triggered()
{
     QString aFile = QFileDialog::getOpenFileName(this,"选择数据库文件","",
                                                  "SQL Lite数据库(*.db *.db3)");
     if(aFile.isEmpty())
     {
         QMessageBox::warning(this, "错误", "打开数据库失败",
                                  QMessageBox::Ok,QMessageBox::NoButton);
         return;
     }
   //打开数据库
     DB = QSqlDatabase::addDatabase("QSQLITE");  //添加 SQL LITE数据库驱动
     DB.setDatabaseName(aFile);

     if(!DB.open())
     {
         QMessageBox::warning(this, "错误", "打开数据库失败",
                                  QMessageBox::Ok,QMessageBox::NoButton);
         return;
     }
    //打开数据表
    openTable();
}

void MainWindow::on_actRecInsert_triggered()
{
    //插入记录
    QSqlQuery query;
    query.exec("select * from employee where EmpNo =-1"); //实际不查询出记录，只查询字段信息
    QSqlRecord curRec = query.record(); //获取当前记录,实际为空记录
    curRec.setValue("EmpNo",qryModel->rowCount()+3000);

    WDialogData    *dataDialog=new WDialogData(this);
    Qt::WindowFlags    flags=dataDialog->windowFlags();
    dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint); //设置对话框固定大小

    dataDialog->setInsertRecord(curRec);
    int ret=dataDialog->exec();// 以模态方式显示对话框
    if(ret == QDialog::Accepted) //OK键按下去后
    {
        QSqlRecord recData = dataDialog->getRecordData();

        query.prepare("INSERT INTO employee (EmpNo,Name,Gender,Height,Birthday,Mobile,Province,"
                      " City,Department,Education,Salary,Memo,Photo) "
                      " VALUES(:EmpNo,:Name, :Gender,:Height,:Birthday,:Mobile,:Province,"
                      " :City,:Department,:Education,:Salary,:Memo,:Photo)");
        query.bindValue(":EmpNo",recData.value("EmpNo"));
        query.bindValue(":Name",recData.value("Name"));
        query.bindValue(":Gender",recData.value("Gender"));
        query.bindValue(":Height",recData.value("Height"));
        query.bindValue(":Birthday",recData.value("Birthday"));
        query.bindValue(":Mobile",recData.value("Mobile"));

        query.bindValue(":Province",recData.value("Province"));
        query.bindValue(":City",recData.value("City"));
        query.bindValue(":Department",recData.value("Department"));
        query.bindValue(":Education",recData.value("Education"));

        query.bindValue(":Salary",recData.value("Salary"));
        query.bindValue(":Memo",recData.value("Memo"));
        query.bindValue(":Photo",recData.value("Photo"));

        if(!query.exec())
        {
            QMessageBox::critical(this, "错误", "插入记录错误\n"+query.lastError().text(),
                                     QMessageBox::Ok,QMessageBox::NoButton);
        }
        else {
//            qryModel->query().exec();//数据模型重新查询数据，更新tableView显示
            qryModel->setQuery("SELECT empNo,name,Gender,Height,Birthday,Mobile,"
                 "Province, City,Department,Education,Salary FROM employee order by empNo");
        }
    }

    delete dataDialog;
}

void MainWindow::on_actRecDelete_triggered()
{
    //删除当前记录
    int curRecNo = theSelection->currentIndex().row();
    QSqlRecord curRec = qryModel->record(curRecNo);
    if (curRec.isEmpty()) //当前为空记录
        return;

    int empNo=curRec.value("EmpNo").toInt();//获取员工编号
    QSqlQuery query;
    query.prepare("delete  from employee where EmpNo = :ID");
    query.bindValue(":ID",empNo);
    if (!query.exec())
        QMessageBox::critical(this, "错误", "删除记录出现错误\n"+query.lastError().text(),
                                 QMessageBox::Ok,QMessageBox::NoButton);
    else //插入，删除记录后需要重新设置SQL语句查询
    {
       qryModel->query().exec();
//        qryModel->setQuery("SELECT empNo,name,Gender,Height,Birthday,Mobile,"
//             "Province, City,Department,Education,Salary FROM employee order by empNo");
       qryModel->layoutChanged();
    }

//    ui->tableView->update();
}

void MainWindow::on_actRecEdit_triggered()
{
    int curRecNo=theSelection->currentIndex().row();
    updateRecord(curRecNo);
}

void MainWindow::on_actScan_triggered()
{
   //涨工资，记录遍历
   QSqlQuery qryEmpList; //员工工资信息列表
   qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo");
   qryEmpList.first();

   QSqlQuery qryUpdate; //临时 QSqlQuery
   qryUpdate.prepare("UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID");

   while(qryEmpList.isValid()) //当前记录有效
   {
       int empID=qryEmpList.value("empNo").toInt(); //获取empNo
       float salary=qryEmpList.value("Salary").toFloat(); //获取Salary
       salary=salary+1000; //涨工资

       qryUpdate.bindValue(":ID",empID);
       qryUpdate.bindValue(":Salary",salary); //设置SQL语句参数
       qryUpdate.exec(); //执行UPDATE

       if (!qryEmpList.next()) //移动到下一条记录，并判断是否到末尾了
           break;
   }

    qryModel->query().exec();//数据模型重新查询数据，更新tableView的显示
    QMessageBox::information(this, "提示", "涨工资计算完毕",
                             QMessageBox::Ok,QMessageBox::NoButton);
}


void MainWindow::on_tableView_doubleClicked(const QModelIndex &index)
{
    //双击编辑
    int curRecNo=index.row();
    updateRecord(curRecNo);
}

         这里大部分代码都是qt5.9开发实例书上的代码，其中的知识点可以看这本书，需要资源也可以私信我，这里只是简单记录这个例子，希望大家能够喜欢。