天天看點
傳回

SQLite+Qt 圖書管理系統設計（具體實作）

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

    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);

    }

}

界面：

SQLite+Qt 圖書管理系統設計（具體實作）
SQLite+Qt 圖書管理系統設計（具體實作）
SQLite+Qt 圖書管理系統設計（具體實作）

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;

}

界面：

單本插入：

SQLite+Qt 圖書管理系統設計（具體實作）
SQLite+Qt 圖書管理系統設計（具體實作）

删除：

SQLite+Qt 圖書管理系統設計（具體實作）

批量入庫（QSqlRecord介紹時附過相關代碼）

SQLite+Qt 圖書管理系統設計（具體實作）
SQLite+Qt 圖書管理系統設計（具體實作）

3.圖書查詢

模糊查詢 使用”$”比對

SQLite+Qt 圖書管理系統設計（具體實作）

4.借書

查詢資訊

SQLite+Qt 圖書管理系統設計（具體實作）

無庫存

SQLite+Qt 圖書管理系統設計（具體實作）

超過借書限制

SQLite+Qt 圖書管理系統設計（具體實作）

5.還書

1.      借書證管理

SQLite+Qt 圖書管理系統設計（具體實作）
SQLite+Qt 圖書管理系統設計（具體實作）

2.      顯示

SQLite+Qt 圖書管理系統設計（具體實作）
SQLite+Qt 圖書管理系統設計（具體實作）

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

資料庫 圖書管理系統 SQLite 結構
上一篇: Java的CAS樂觀鎖原了解析J.U.C包内的原子操作封裝類
下一篇: Pandas之loc&amp;iloc

繼續閱讀