1 Encapsulate a Global Object

Create a new C++ class named sqlmange, and add sql to the .pro file.

Use the C++ singleton pattern to ensure only one instance exists and provide a global access point.

The following code implements the SQLManager class, which manages database connections and operations using the singleton pattern.

// sqlmanager.h
#ifndef SQLMANAGER_H
#define SQLMANAGER_H

#include <QDebug>

class SQLManager
{
public:
    static SQLManager* getInstance();
    void test();

private:
    SQLManager();
    SQLManager(const SQLManager&) = delete;
    SQLManager& operator=(const SQLManager&) = delete;

    static SQLManager* instance;
};

#endif // SQLMANAGER_H

// sqlmanager.cpp
#include "sqlmanager.h"

SQLManager* SQLManager::instance = nullptr;

SQLManager::SQLManager() {}

SQLManager* SQLManager::getInstance()
{
    if (nullptr == instance) {
        instance = new SQLManager();
    }
    return instance;
}

void SQLManager::test()
{
    qDebug() << "test";
}

Call the interface in main():

SQLManager::getInstance()->test();

2 Design All Interfaces

// Constructor
SQLManager();

// Singleton instance
static SQLManager* getInstance();

// Initialize database
void init();

// Login
bool login(const QString& username, const QString& password);

// Get all users
QVector<QStringList> getUsers(const QString& condition = "");

// Add user
void addUser(const QVector<QStringList>& data);

// Delete user
void deleteUser(const QString& id);

// Get all books
QVector<QStringList> getBooks(const QString& condition = "");

// Add book
void addBook(const QVector<QStringList>& data);

// Modify book
void modifyBook(const QStringList& data);

// Delete book
void deleteBook(const QString& id);

// Return book
QString returnBook(const QString& userId, const QString& bookId);

// Borrow book
QString borrowBook(const QString& userId, const QString& bookId);

// Get records
QVector<QStringList> getRecords(const QString& condition = "");

// Clear records
QString clearRecord();

2.1 Initialize Database Interface

Use QSqlDatabase functions provided by Qt.

Place the database file in a db folder under the application directory, then implement:

void SQLManager::init()
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(QCoreApplication::applicationDirPath() + "/db/book.db");
    qDebug() << m_db.open();
}

If no driver is found, ensure the database file is included in the build path.

2.2 Login Interface

Use a query to verify username and password.

bool SQLManager::login(const QString& username, const QString& password)
{
    QSqlQuery query(m_db);
    QString sql = QString("SELECT * FROM user WHERE username = '%1' AND password = '%2'")
                      .arg(username).arg(password);
    bool ret = query.exec(sql);
    if (!ret) {
        qDebug() << query.lastError().text();
    }
    return ret;
}

// Usage in main:
qDebug() << "login:" << SQLManager::getInstance()->login("xiaoming", "123456");

2.3 Conditional Query Users Interface

Pass the WHERE clause as a condition.

QVector<QStringList> SQLManager::getUsers(const QString& condition)
{
    QSqlQuery query(m_db);
    QString sql = QString("SELECT * FROM user %1").arg(condition);
    QVector<QStringList> result;
    bool ret = query.exec(sql);
    if (!ret) {
        qDebug() << query.lastError().text();
    } else {
        int cols = query.record().count();
        while (query.next()) {
            QStringList row;
            for (int i = 0; i < cols; ++i) {
                row << query.value(i).toString();
            }
            result.push_back(row);
        }
    }
    return result;
}

Example usage:

qDebug() << "getuser:" << SQLManager::getInstance()->getUsers("WHERE username LIKE '%xiao%' OR nickname LIKE '%小%'");

3 User Interface

3.1 Initialize Page

Fetch data from the database and display it in a table view.

void UserManagementPage::initPage(const QString& condition)
{
    QVector<QStringList> data = SQLManager::getInstance()->getUsers(condition);
    m_model.clear();
    m_model.setHorizontalHeaderLabels({"User ID", "Name", "Grade", "Permission", "College", "Username", "Password"});
    for (const auto& row : data) {
        QList<QStandardItem*> items;
        for (const auto& cell : row) {
            items.append(new QStandardItem(cell));
        }
        m_model.appendRow(items);
    }
}

Call initPage when switching to the user page:

if (str == "btn_user") {
    m_userPage->initPage("");
    ui->stackedWidget->setCurrentIndex(0);
}

3.2 Delete User

First, implement the deletion in SQLManager:

void SQLManager::deleteUser(const QString& id)
{
    QSqlQuery query(m_db);
    QString sql = QString("DELETE FROM user WHERE userid = '%1'").arg(id);
    query.exec(sql);
}

Then connect the delete button:

void UserManagementPage::onDeleteButtonClicked()
{
    int row = ui->tableView->currentIndex().row();
    if (row < 0) {
        QMessageBox::information(nullptr, "Error", "No user selected");
        return;
    }
    QString id = m_model.item(row, 0)->text();
    SQLManager::getInstance()->deleteUser(id);
    QMessageBox::information(nullptr, "Success", "User deleted");
    initPage();
}

3.3 Search User

Connect the text changed signal of the search line edit:

void UserManagementPage::onSearchTextChanged(const QString& text)
{
    QString condition = QString("WHERE username LIKE '%%1%' OR nickname LIKE '%%2%'").arg(text).arg(text);
    initPage(condition);
}

3.4 Import Users

Read a CSV file and ensert data in to the database.

void SQLManager::addUser(const QVector<QStringList>& data)
{
    m_db.transaction();
    QSqlQuery query(m_db);
    for (const auto& row : data) {
        QString sql = QString("INSERT INTO user VALUES(NULL,'%1','%2','%3','%4','%5','%6')")
                          .arg(row[0].toUtf8()).arg(row[1].toUtf8()).arg(row[2].toUtf8())
                          .arg(row[3].toUtf8()).arg(row[4].toUtf8()).arg(row[5].toUtf8());
        query.exec(sql);
    }
    m_db.commit();
}

void UserManagementPage::onImportButtonClicked()
{
    QString path = QFileDialog::getOpenFileName(nullptr, "Select CSV file");
    if (path.isEmpty()) return;
    QFile file(path);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) return;
    QVector<QStringList> data;
    while (!file.atEnd()) {
        QString line = file.readLine();
        QStringList fields = line.split(",");
        if (fields.size() != 6) {
            QMessageBox::information(nullptr, "Error", "Invalid column count");
            return;
        }
        fields.last().chop(2); // Remove trailing newline
        data.push_back(fields);
    }
    SQLManager::getInstance()->addUser(data);
    QMessageBox::information(nullptr, "Success", "Import successful");
    initPage();
}

Note: Encoding isues may occur if the CSV is in GBK.

4 Book Management Interface

4.1 Initialize Book Interface

Similar to user initialization but querying the book table.

4.2 Search Interface

void BookManagementPage::onSearchTextChanged(const QString& text)
{
    QString condition = QString("WHERE name LIKE '%%1%' OR type1 LIKE '%%2%' OR type2 LIKE '%%3%' OR type3 LIKE '%%4%'")
                            .arg(text).arg(text).arg(text).arg(text);
    initPage(condition);
}

4.3 Delete Book Interface

Similar to delete user.

4.4 Add and Modify Book

Create a new dialog form class for adding/editing books.

The cancel button simply hides the dialog.

Implement the SQL logic:

void SQLManager::addBook(const QVector<QStringList>& data)
{
    if (data.isEmpty()) return;
    QSqlQuery query(m_db);
    QStringList row = data[0];
    QString sql = QString("INSERT INTO book VALUES(NULL,'%1','%2','%3','%4','%5','%6','')")
                      .arg(row[1]).arg(row[2]).arg(row[3]).arg(row[4]).arg(row[5]).arg(row[6]);
    query.exec(sql);
}

void SQLManager::modifyBook(const QStringList& data)
{
    QSqlQuery query(m_db);
    QString sql = QString("UPDATE book SET name='%1', press='%2', type1='%3', type2='%4', type3='%5', count='%6' WHERE bookid='%7'")
                      .arg(data[1]).arg(data[2]).arg(data[4]).arg(data[5]).arg(data[6]).arg(data[3]).arg(data[0]);
    query.exec(sql);
}

In the dialog, determine whether to add or modify:

void BookAddEditDialog::setType(int id)
{
    m_id = id;
    if (id != -1) {
        auto bookData = SQLManager::getInstance()->getBooks(QString("WHERE bookid = %1").arg(id));
        if (bookData.size() == 1) {
            auto row = bookData[0];
            ui->nameEdit->setText(row[1]);
            ui->pressEdit->setText(row[2]);
            ui->combo1->setCurrentText(row[3]);
            ui->combo2->setCurrentText(row[4]);
            ui->combo3->setCurrentText(row[5]);
            ui->countEdit->setText(row[6]);
        }
    }
}

void BookAddEditDialog::onOkButtonClicked()
{
    QStringList data;
    data << QString::number(m_id);
    data << ui->nameEdit->text();
    data << ui->pressEdit->text();
    data << ui->countEdit->text();
    data << ui->combo1->currentText();
    data << ui->combo2->currentText();
    data << ui->combo3->currentText();
    if (m_id != -1) {
        SQLManager::getInstance()->modifyBook(data);
    } else {
        QVector<QStringList> vec;
        vec.push_back(data);
        SQLManager::getInstance()->addBook(vec);
    }
    accept();
}

Back in the book management page:

void BookManagementPage::onAddButtonClicked()
{
    BookAddEditDialog dialog;
    dialog.exec();
    initPage();
}

4.5 Borrow Book

Create a new dialog for borrowing.

Implement the borrow logic:

void SQLManager::borrowBook(const QString& userId, const QString& bookId)
{
    QSqlQuery query(m_db);
    QString sql1 = QString("UPDATE book SET count = count - 1 WHERE bookid = '%1'").arg(bookId);
    QString sql2 = QString("INSERT INTO record VALUES(NULL,'%1','%2','%3','%4','')")
                       .arg(bookId).arg(userId)
                       .arg(QDateTime::currentSecsSinceEpoch())
                       .arg(QDateTime::currentSecsSinceEpoch() + 3600 * 24 * 10);
    if (!query.exec(sql1)) qDebug() << query.lastError().text();
    if (!query.exec(sql2)) qDebug() << query.lastError().text();
}

In the borrow dialog, verify credentials and execute borrowing:

void BorrowDialog::onOkButtonClicked()
{
    QString username = ui->usernameEdit->text();
    QString password = ui->passwordEdit->text();
    int userId = 0;
    bool loginOk = SQLManager::getInstance()->login(username, password, userId);
    if (!loginOk) {
        reject();
        return;
    }
    SQLManager::getInstance()->borrowBook(QString::number(userId), QString::number(m_bookId));
    accept();
}

void BorrowDialog::setBookId(int id)
{
    m_bookId = id;
    auto bookData = SQLManager::getInstance()->getBooks(QString("WHERE bookid = %1").arg(id));
    if (!bookData.isEmpty()) {
        ui->bookNameLabel->setText(QString("Book Name: %1").arg(bookData[0][1]));
    }
}

5 Record Management Interface

5.1 Initialize Record Interface

Similar to previous initializations but querying the record table.

Return Book

Implement a return dialog similar to borrowing, but with the return logic:

void SQLManager::returnBook(const QString& userId, const QString& bookId)
{
    QSqlQuery query(m_db);
    QString sql1 = QString("UPDATE book SET count = count + 1 WHERE bookid = '%1'").arg(bookId);
    QString sql2 = QString("UPDATE record SET returntime = '%1' WHERE bookid = '%2' AND userid = '%3' AND returntime IS NULL")
                       .arg(QDateTime::currentSecsSinceEpoch()).arg(bookId).arg(userId);
    if (!query.exec(sql1)) qDebug() << query.lastError().text();
    if (!query.exec(sql2)) qDebug() << query.lastError().text();
}