SQLite+Qt 图书管理系统设计(具体实现)

数据库 专栏收录该内容
3 篇文章 0 订阅

https://github.com/birdy-C/BookSystem.git


详细设计

SQLite是一款轻型的数据库,占用资源低,与Qt的连接便易。

数据库逻辑结构设计

创建

PRAGMA foreign_keys = ON;

 

-----------------------------------------------------------------------------------------------

create table type(

Book_type nvarchar(10)        primary key         ,

Type_number     int     NOT NULL UNIQUE ,  check (Type_number  >0)        );

----------------------------------------------------------------------------------------------

create table book(

Book_ID    int     primary key         ,

Type  nvarchar(10)        REFERENCES type(Book_type)   ,

Title  nvarchar              ,

Publisher    nvarchar              ,

Publish_year       int     check(publish_year>1800 )   ,

Author       NVARCHAR               ,

Price decimal(6,2)                  ,

Number      int     check(Number>=0)      ,

Stocks        int     check(Stocks>=0)        );

--------------------------临时表,处理批量入库-------------------------------------

create table book_temp(

Type  nvarchar(10)        ,

Title  nvarchar              ,

Publisher    nvarchar              ,

Publish_year       int     ,

Author       NVARCHAR               ,

Price decimal(6,2)                  ,

Number      int     );

------------------------------------------------------------------------------------------------

create table library_card(

card_ID      int     primary key         ,

Username   varchar(10)          ,

Company   nvarchar              ,

Type  varchar(10) check (Type in ( 'student', 'teacher' ) ));

-----------------------------------------------------------------------------------------------

create table manager(

manager_ID        int     primary key         ,

Password   varchar(10) not null       ,

Name         varchar(10)          ,

Connection int               );

-----------------------------------------------------------------------------------------------

create table record (

Book_ID    int     REFERENCES book(Book_ID)    ,

card_ID      int     REFERENCES Library_card(card_ID) ,

borrow_data        data            ,

return_data data   check ( return_data >= borrow_data )      ,

manager_ID        int     REFERENCES manager(manager_ID)    ,

primary key(Book_ID,card_ID) );

 

 

 

 

触发器

 

--------------检查出版年份-----------------

 

CREATE TRIGGER Tr_check_date AFTER insert ON book

FOR EACH ROW

WHEN(

(select Book_ID from book where publish_year > (SELECT strftime('%Y',date('now')) )

)is not null)

BEGIN

SELECT RAISE(ROLLBACK, 'IMPOSSIBLE DATE') ;

END;

 

--------------检查输入时 库存与数量是否相等-----------------

 

 

CREATE TRIGGER Tr_check_number BEFORE insert ON book

FOR EACH ROW

WHEN

   ( new.Number <> new.Stocks )

BEGIN

 

SELECT RAISE(ROLLBACK, 'ANY WRONG ABOUT NUMBER?');

 

END;

 

--------------借出检查是否有余量-----------------

CREATE TRIGGER Tr_borrow_check BEFORE insert ON record

FOR EACH ROW

WHEN 0=(select Stocks from book where Book_ID=new.Book_ID)

BEGIN

  SELECT RAISE(ROLLBACK, 'NO REMAINING') ;

END;

 

 

--------------借出检查借书证 老师5学生3-----------------

CREATE TRIGGER Tr_borrow_card AFTER insert ON record

FOR EACH ROW

WHEN

(

(select count(*) from record join

         (select card_ID as S from library_card

         where card_ID = new.card_ID and Type = 'student')

         on

         record.card_ID = S

         ) >3   --学生

or

(select count(*) from record join

         (select card_ID as T from library_card

         where card_ID = new.card_ID and Type = 'teacher')

         on

         record.card_ID = T

         ) >5   --老师

 

)

BEGIN

     SELECT RAISE(ROLLBACK, 'MORE THAN PERMITTED') ;

END;

 

--------------借出更新数据-----------------

CREATE TRIGGER Tr_borrow AFTER insert ON record

FOR EACH ROW

BEGIN

  UPDATE Book

  SET Stocks = (

                   select         Stocks - 1

                   FROM Book

                   WHERE 

                   Book_ID = new.Book_ID

                   )

  WHERE

  Book_ID = new.Book_ID ;

END;

 

--------------返回更新数据-----------------

 

CREATE TRIGGER Tr_return AFTER delete ON record

FOR EACH ROW

BEGIN

  UPDATE Book

  SET Stocks = (

                   select         Stocks + 1

                   FROM Book

                   WHERE 

                   Book_ID = old.Book_ID

                   )

  WHERE

  Book_ID = old.Book_ID ;

END;

 

--------------插入检查是否已经存在-----------------

---------这一段大概有很多可以优化的地方吧……

 

CREATE TRIGGER Tr_check_insert AFTER insert ON book

FOR EACH ROW

WHEN

(

         (

         select count(Book_ID)  from book

         where Type = new.Type and Title = new.Title and Publisher = new.Publisher

                   and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price

         )

         >1--except for new

)

BEGIN

UPDATE Book

    SET Stocks = (

                   select         Stocks + new.Stocks

                   FROM Book

                   where Type = new.Type and Title = new.Title and Publisher = new.Publisher

                            and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price

 

                   )

         where Type = new.Type and Title = new.Title and Publisher = new.Publisher

                   and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price;

UPDATE Book

    SET Number = (

                   select         Number + new.Number

                   FROM Book

                   where Type = new.Type and Title = new.Title and Publisher = new.Publisher

                            and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price

                   )

    where Type = new.Type and Title = new.Title and Publisher = new.Publisher

                   and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price;

          

DELETE FROM Book

    WHERE Book_ID = new.Book_ID;

END;

 

图书管理系统设计

一、主要类说明(SQL相关)以及主要使用

 

QsqlDatabase提供了一个Qt和DB的连接口。

以下代码为数据库连接代码,并且打开数据库文件DBSYSTEN.db

    /*---- LOAD IN DASTABASE ----*/

    db = QSqlDatabase::addDatabase("QSQLITE");

    db.setDatabaseName("DBSYSTEM.db");

    if (!db.open()) {

        QMessageBox::warning(0, QObject::tr("Database Error"),db.lastError().text());

        qDebug() << QString("LOAD_IN");

        returnfalse;

    }

此外可以通过boolQSqlDatabase::transaction(),bool QSqlDatabase::commit(),bool QSqlDatabase::rollback()来实现相当于撤销和保存的操作。

 

QsqlQuery提供了一个SQL语句的运行接口。

如下面语句,实现的是读取Book_id对应书的库存和数量。

    QSqlQuery query_getStocks("select Stocks,Number from book where Book_id = '"+ ID +"'");

    int Stocks_int, All_int;

    if (query_getStocks.next())

    {

        Stocks_int = query_getStocks.value(0).toInt();

        All_int = query_getStocks.value(1).toInt();

    }

    else

    {

        QMessageBox::warning(0,QObject::tr("INACCURACY DATA"),"Please check the ID");

        return;

    }

 

QsqlError提供SQL数据库的错误信息。

在这里主要用于处理QSqlError QSqlDatabase::lastError()const返回的错误。

 

 QSqlTableModel 可以很方便地为一个table(或者view)提供可编辑的形式。

在ui界面上显示table book的代码如下:

    select_model = newQSqlTableModel(this);

    select_model->setTable("book");

    ui.tableView_select->setModel(select_model);

    select_model->select();

    ui.tableView_select->setEditTriggers(QAbstractItemView::NoEditTriggers);//不可编辑

    ui.tableView_select->setSelectionBehavior(QAbstractItemView::SelectRows);//整行选中

 

 

QSqlRecord读取数据库记录。

下为读取临时table book_temp中的代码插入book中(SL_insert_book为插入的函数)的代码。

    for (int i = select_model_insert2->rowCount()-1; i >= 0; i--)

    {

        QSqlRecord record = select_model_insert2->record(i);

        bool ok;

        ok = SL_insert_book

        (

            record.value("Type").toString(),

            record.value("Title").toString(),

            record.value("Publisher").toString(),

            record.value("Publish_year").toString(),

            record.value("Author").toString(),

            record.value("Price").toString(),

            record.value("Number").toString()

        );

        if(ok)

        {

            select_model_insert2->removeRow(i);

        }      

    }

 

 

QModelIndex这里用来处理读取QSqlTableModel的选中数据。

读取选中的数据类型,并依次修改ui.lineEdit_8的数据,代码如下:

voidBookSystem::SL_insert1_selectType(constQModelIndex &, constQModelIndex &)

{

    QModelIndex index=ui.tableView_insert1_type->currentIndex();

    QModelIndex i1 = index.sibling(index.row(), 0);

    QString str1 = i1.data().toString();

    ui.lineEdit_8->setText(str1);

}

 

 

二、主要类说明(界面相关)

控件

信号

tabWidget

currentChanged(int)

lineEdit

textEdited(const QString &)

pushButton

clicked()

checkBox

released()

radioButton

released()

spinBox

valueChanged(int)

doubleSpinBox

valueChanged(double)

tableView->selectionModel()

currentRowChanged(const QModelIndex &,const QModelIndex &)

 

三、具体实现及主要界面

1.管理员登陆

检查用户是否存在

boolBookSystem:: checkManager()

{

    QSqlQuery query_temp("select manager_ID, Name from manager where manager_ID = '"+ ui.lineEdit_18->text()

        +"'AND Password = '"+ ui.lineEdit_17->text()+"';");

 

    if (query_temp.next())

    {

        manager_ID = query_temp.value(0).toInt();

        manager_name = query_temp.value(1).toString();

 

    }

    else

    {

        QMessageBox::warning(0,QObject::tr("NO SUCH INFORMATION"),"make sure you add the accurate information");

        returnfalse;

    }

    returntrue;

}

 

更新界面

voidBookSystem::SL_user_loadin()

{

    if (false == checkManager())

    {

        ui.lineEdit_17->setText("");

        return;

    }

 

    QMessageBox::about(0,QObject::tr("LOAD IN"),"Welcome ! "+ manager_name+".");

 

    load_statues = true;

    //manager_ID = ui.lineEdit_18->text().toInt();

    ui.label_24->setText("Welcome ! "+ manager_name+".");

    ui.lineEdit_17->setText("");

    ui.lineEdit_18->setText("");

 

    {

        ui.tabWidget->addTab(ui.tab_2,QString::fromLocal8Bit("单本插入/删除"));

        ui.tabWidget->addTab(ui.tab_3,QString::fromLocal8Bit("批量入库"));

        ui.tabWidget->addTab(ui.tab_4,QString::fromLocal8Bit("借阅管理"));

        ui.tabWidget->addTab(ui.tab_5,QString::fromLocal8Bit("用户信息管理"));

        ui.tabWidget->addTab(ui.tab_6,QString::fromLocal8Bit("创建用户"));

        ui.tabWidget->addTab(ui.tab_7,QString::fromLocal8Bit("借书记录"));

        ui.tabWidget->addTab(ui.tab_8,QString::fromLocal8Bit("管理员信息"));

 

        connect(ui.tabWidget, SIGNAL(currentChanged(int)),this,SLOT(empty(int)));

 

    }

    { //set the buttom

        bool ok =false;

        ui.label_25->setVisible(ok);

        ui.label_26->setVisible(ok);

        ui.lineEdit_17->setVisible(ok);

        ui.lineEdit_18->setVisible(ok);

        ui.pushButton_12->setVisible(ok);

        ui.label_24->setVisible(!ok);

        ui.pushButton->setVisible(!ok);

    }

}


界面:

 

 



 

2.图书入库

核心代码

/*单本插入*/

voidBookSystem::SL_insert1()

{

    if(SL_insert_book(ui.lineEdit_8->text(), ui.lineEdit_4->text(), ui.lineEdit_5->text(), ui.lineEdit_6->text(),

        ui.lineEdit_7->text(), ui.lineEdit_9->text(), ui.lineEdit_10->text()))

    {

        select_model_insert1->select();//更新显示

        QMessageBox::about(0,QObject::tr("SUCCESS"),"Success in inserting book <"+ ui.lineEdit_4->text()+">");

        SL_insert1_empty();

 

    }

 

}

 

boolBookSystem::SL_insert_book(QStringType,QStringTitle,QStringPublisher,

    QStringPublish_year,QString Author,QStringPrice,QStringNumber)

{

    QString ID;

 

    //如果标题为空

    if (""==Title )

    {

        QMessageBox::warning(0,QObject::tr("INACCURACY DATA"),"Please put in accurate name");

        return false;

    }

 

 

    //如果得到的是零或者负数

    int number_in =Number.toInt();

    if (number_in <= 0)

    {

        QMessageBox::warning(0,QObject::tr("INACCURACY DATA"),"Please check the number");

        return false;

    }

    QString number =QString::number(number_in, 10);//取整

 

 

    //根据type生成ID

    QSqlQuery query_getID("select MAX(Book_id)+1 from book where Type = '"+Type+"'");

 

    if (query_getID.next())

    {

        ID = query_getID.value(0).toString();

    }

    if (ID=="")//如果这个类型还没有书

    {

        QSqlQuery query_temp("select Type_number * 1000 + 1 from type where Book_type = '"+Type+"'");

        ui.plainTextEdit_3->setPlainText(ID);

 

        if (query_temp.next())

        {

            ID = query_temp.value(0).toString();

        }

        else

        {

            QMessageBox::warning(0,QObject::tr("NO SUCH TYPE"),"make sure you add the accuracy type");

            returnfalse;

        }

    }

 

   

    //插入

    QSqlQuery insert;

    insert.prepare("INSERT INTO book (Book_id,Type,Title,Publisher,Publish_year,Author,Price,Number,Stocks) "

        "VALUES (?,?,?,?,?,?,?,?,?);");

    insert.addBindValue(ID);

    insert.addBindValue(Type);

    insert.addBindValue(Title);

    insert.addBindValue(Publisher);

    insert.addBindValue(Publish_year);

    insert.addBindValue(Author);

    insert.addBindValue(Price);

    insert.addBindValue(Number);

    insert.addBindValue(Number);

    insert.exec();

 

    if (!insert.isActive())

    {

        QMessageBox::warning(0,QObject::tr("Database Error"),"Please check the input ,especially the accurcy in publish year,"

            "or maybe the ID need to be reset.");

        //数据库返回错误的几种可能性 1.主键约束即 ID的生成有一定不合理性 2.trigger的约束 年份大于1800 小于现在的年份

        returnfalse;

    }

 

    returntrue;

}

 

界面:


单本插入:


删除:

批量入库(QSqlRecord介绍时附过相关代码)


3.图书查询

模糊查询 使用”$”匹配


4.借书

查询信息


无库存


超过借书限制


5.还书

1.      借书证管理

 

 

2.      显示

 

3.      撤销操作

    if (!db.rollback()) {

        QMessageBox::warning(0,QObject::tr("Database Error"), db.lastError().text());

    }

    else

    {

        QMessageBox::about(0,QObject::tr("Database Error"),"Success in Rollback");

    }

 

 

 

附录

代码下载

https://github.com/birdy-C/BookSystem.git

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值