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
|
/*---- 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