Qt数据库---图书管理系统

图示结果：

制作一个简单的数据库，可以实现数据的增、删、改、查的图书馆管理，如果在使用数据库后，没有清除记录，那么下一次打开的时候，仍然会保存上一次执行的记录操作。这就是数据库的优势所在，以下是具体的操作以及代码。

一、总结
在Qt中使用书库，执行流程为一下操作：
(1)、安装驱动【Qt中的驱动一般情况下是QSQLITE】
(2)、给数据库起名字，或者也可以从组件上获取自定义数据库名字
(3)、打开数据库
(4)、创建表

二、执行增、删、改、查
将以上4步完成之后，那就可以实现数据库的增、删、改、查。我在下面代码中有详细的解释以及操作，很容易理解。

三、主要知识点
(1)、QSqlQuery：类提供了一种执行和操作SQL语句的方法
(2)、QSqlDatabase类：用来处理与数据库的连接
(3)、QSqlError类：用来输出数据库中的错误信息

增

//添加数据库成员
//添加数据时，不要将添加命令写错，不然就会报错，不能实现数据库的添加
void Library::on_addButton_clicked()
{
     QSqlQuery query;
     //获取组件上的内容
     QString name   = ui->addnameLE->text();
     QString price  = ui->addpriceLE->text();
     QString author = ui->addAutherLE->text();
     QString nums   = ui->addNumsLE->text();
     QString cmd = QString("insert into library values(\"%1\",\"%2\",\"%3\",\"%4\");").arg(name).arg(price).arg(author).arg(nums);
     if(!query.exec(cmd))//添加数据库
     {
         qDebug() << "Error to Insert Into Database " << myDatabase.lastError();
         return;
     }
//数据添加之后，将小部件清空
     ui->addNumsLE->clear();
     ui->addpriceLE->clear();
     ui->addnameLE->clear();
     ui->addAutherLE->clear();
}

删

//删除数据库成员
void Library::on_deleteButton_clicked()
{
    QSqlQuery query;
    //根据名字删除，或者根据作者删除
    QString name   = ui->deleleNameLE->text();
    QString author = ui->deleteAutherLE->text();

    QString cmd = QString("delete from library where name =\"%1\" or author = \"%2\";").arg(name).arg(author);
    if(!query.exec(cmd))//执行删除命令
    {
        qDebug() << "Error to delete Database " << myDatabase.lastError();
        return;
    }

    //清除小部件内容
    ui->deleleNameLE->clear();
    ui->deleteAutherLE->clear();

}

改

//修改数据库成员
void Library::on_updateButton_clicked()
{
    QSqlQuery query;
    //获取修改之前的组件上的内容
    QString name   = ui->newUPNameLE->text();
    QString price  = ui->newUPPriceLE->text();
    QString author = ui->newUPAutherLE->text();
    QString nums   = ui->newUPNumberLE->text();
	
	//获取修改后的组件上的内容
    QString nameLast   = ui->lastNameUPLE->text();
    QString priceLast  = ui->lastPrice->text();
    QString authorLast = ui->lastAuther->text();
    QString numsLast   = ui->lastNums->text();

	//执行修改指令
    QString cmd = QString("update library set name = \"%1\" , price = \"%2\" where name = \"%3\" ;").arg(nameLast).arg(priceLast).arg(name);
    if(!query.exec(cmd))
    {
       qDebug() << "Error Update to Database" << myDatabase.lastError();
       return;
    }

    //清除小部件中的内容
    ui->lastNameUPLE->clear();
    ui->lastNums->clear();
    ui->lastPrice->clear();
    ui->lastAuther->clear();
    ui->newUPNameLE->clear();
    ui->newUPPriceLE->clear();
    ui->newUPAutherLE->clear();
    ui->newUPNumberLE->clear();
}

查

//查询数据库成员
void Library::on_serchButton_clicked()
{

    QSqlQuery query;
    QString cmd = QString("select * from library");//查询数据库
    if(!query.exec(cmd))//判断数据库是否为空
    {
       qDebug() << "Error Select to Database" << myDatabase.lastError();
       return;
    }

    QSqlRecord record = query.record();//获取当前记录
	//获取每一个记录下的索引
    int num1 = record.indexOf("name");
    int num2 = record.indexOf("price");
    int num3 = record.indexOf("author");
    int num4 = record.indexOf("nums");
    int i=0;
    int j=0;
    while (query.next())
    {

        QString info = QString("%1   %2   %3   %4").arg(query.value(num1).toString()).arg(query.value(num2).toDouble()).arg(query.value(num3).toString()).arg(query.value(num4).toInt());

        //在表格中显示信息
        ui->tableWidget->setItem(i,j  ,new QTableWidgetItem(query.value(num1).toString()));
        ui->tableWidget->setItem(i,j+1,new QTableWidgetItem(query.value(num2).toString()));
        ui->tableWidget->setItem(i,j+2,new QTableWidgetItem(query.value(num3).toString()));
        ui->tableWidget->setItem(i,j+3,new QTableWidgetItem(query.value(num4).toString()));
        i++;
    }
}

完整代码如下：

.cpp文件

这是.cpp文件
#include "library.h"
#include "ui_library.h"
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlError>
#include <QDebug>

Library::Library(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::Library)
{
    ui->setupUi(this);
    //1、安装驱动
    myDatabase = QSqlDatabase::addDatabase("QSQLITE");

    //2、给数据库取名字
    myDatabase.setDatabaseName("library.db");

    //3、打开数据库
    if(!myDatabase.open())
    {
        qDebug() << "Error to open database" << myDatabase.lastError();
        return;
    }
}

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

//创建数据库
void Library::on_createButton_clicked()
{
    QSqlQuery query;
    /*
        创建一个表：
        表名：library
        成员：name  、price 、 author  、 nums

        执行命令：create  table （if not exists：如果不存在，则创建）表名（成员 类型,……）
    */

    if(!query.exec("create table if not exists library(name text, price double ,author text, nums int);"))
    {
        qDebug() << "Create Table is error" << myDatabase.lastError();
        return;
    }
}

//添加数据库成员
void Library::on_addButton_clicked()
{
     QSqlQuery query;
     QString name   = ui->addnameLE->text();
     QString price  = ui->addpriceLE->text();
     QString author = ui->addAutherLE->text();
     QString nums   = ui->addNumsLE->text();
     QString cmd = QString("insert into library values(\"%1\",\"%2\",\"%3\",\"%4\");").arg(name).arg(price).arg(author).arg(nums);
     if(!query.exec(cmd))
     {
         qDebug() << "Error to Insert Into Database " << myDatabase.lastError();
         return;
     }
     ui->addNumsLE->clear();
     ui->addpriceLE->clear();
     ui->addnameLE->clear();
     ui->addAutherLE->clear();

}

//删除数据库成员
void Library::on_deleteButton_clicked()
{
    QSqlQuery query;
    QString name   = ui->deleleNameLE->text();
    QString author = ui->deleteAutherLE->text();

    QString cmd = QString("delete from library where name =\"%1\" or author = \"%2\";").arg(name).arg(author);
    if(!query.exec(cmd))
    {
        qDebug() << "Error to delete Database " << myDatabase.lastError();
        return;
    }

    //清除小部件内容
    ui->deleleNameLE->clear();
    ui->deleteAutherLE->clear();

}


//修改数据库成员
void Library::on_updateButton_clicked()
{
    QSqlQuery query;
    QString name   = ui->newUPNameLE->text();
    QString price  = ui->newUPPriceLE->text();
    QString author = ui->newUPAutherLE->text();
    QString nums   = ui->newUPNumberLE->text();

    QString nameLast   = ui->lastNameUPLE->text();
    QString priceLast  = ui->lastPrice->text();
    QString authorLast = ui->lastAuther->text();
    QString numsLast   = ui->lastNums->text();

    QString cmd = QString("update library set name = \"%1\" , price = \"%2\" where name = \"%3\" ;").arg(nameLast).arg(priceLast).arg(name);
    if(!query.exec(cmd))
    {
       qDebug() << "Error Update to Database" << myDatabase.lastError();
       return;
    }

    //清除小部件中的内容
    ui->lastNameUPLE->clear();
    ui->lastNums->clear();
    ui->lastPrice->clear();
    ui->lastAuther->clear();
    ui->newUPNameLE->clear();
    ui->newUPPriceLE->clear();
    ui->newUPAutherLE->clear();
    ui->newUPNumberLE->clear();
}

//查询数据库成员
void Library::on_serchButton_clicked()
{

    QSqlQuery query;
    QString cmd = QString("select * from library");//查询数据库
    if(!query.exec(cmd))//判断数据库是否为空
    {
       qDebug() << "Error Select to Database" << myDatabase.lastError();
       return;
    }

    QSqlRecord record = query.record();//获取当前记录

    int num1 = record.indexOf("name");
    int num2 = record.indexOf("price");
    int num3 = record.indexOf("author");
    int num4 = record.indexOf("nums");
    int i=0;
    int j=0;
    while (query.next())
    {

        QString info = QString("%1   %2   %3   %4").arg(query.value(num1).toString()).arg(query.value(num2).toDouble()).arg(query.value(num3).toString()).arg(query.value(num4).toInt());

        //在表格中显示信息
        ui->tableWidget->setItem(i,j  ,new QTableWidgetItem(query.value(num1).toString()));
        ui->tableWidget->setItem(i,j+1,new QTableWidgetItem(query.value(num2).toString()));
        ui->tableWidget->setItem(i,j+2,new QTableWidgetItem(query.value(num3).toString()));
        ui->tableWidget->setItem(i,j+3,new QTableWidgetItem(query.value(num4).toString()));
        i++;
    }
}


//关闭数据库
void Library::on_closeButton_clicked()
{
    myDatabase.close();
}

//删除数据库
void Library::on_deleteButton_2_clicked()
{
    QSqlQuery query;
    QString cmd = QString("drop table library");//删除整个数据库
    query.exec(cmd);
}

.h文件

这是.h文件
#ifndef LIBRARY_H
#define LIBRARY_H

#include <QMainWindow>
#include <QSqlDatabase>

namespace Ui {
class Library;
}

class Library : public QMainWindow
{
    Q_OBJECT

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

private slots:
    //1、创建表
    void on_createButton_clicked();

    //2、添加数据
    void on_addButton_clicked();

    //3、删除数据
    void on_deleteButton_clicked();

    //4、更新数据
    void on_updateButton_clicked();

    //5、查询数据
    void on_serchButton_clicked();

    //6、关闭数据库
    void on_closeButton_clicked();

    //7、清空数据库
    void on_deleteButton_2_clicked();

private:
    Ui::Library *ui;
    QSqlDatabase myDatabase;
};

#endif // LIBRARY_H