QT       += core gui
QT       += sql #添加數(shù)據(jù)庫模塊
greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

CONFIG += c++17

# You can make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0

SOURCES += \
    main.cpp \
    Student.cpp

HEADERS += \
    Student.h

FORMS += \
    Student.ui

# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

#ifndef STUDENT_H
#define STUDENT_H

#include <QWidget>

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QDebug>

QT_BEGIN_NAMESPACE
namespace Ui { class Student; }
QT_END_NAMESPACE

class Student : public QWidget
{
    Q_OBJECT

public:
    Student(QWidget *parent = nullptr);
    ~Student();

    //定義一個(gè)變量，用于增刪改查
    QString queryString;

    void Add();//添加數(shù)據(jù)，不支持大量數(shù)據(jù)快速添加
    void Delete();//刪除數(shù)據(jù)，支持大量數(shù)據(jù)快速刪除
    void Update();//更新數(shù)據(jù)，若更新大量數(shù)據(jù)，可以先快速刪除后在快速添加
    void Select();//查詢數(shù)據(jù)，支持大量數(shù)據(jù)快速查詢

    void FastAdd();//在sqlite中快速添加大量數(shù)據(jù)

private:
    Ui::Student *ui;

    QSqlDatabase DB;//定義數(shù)據(jù)庫名稱
};
#endif // STUDENT_H

#include "Student.h"
#include "ui_Student.h"

Student::Student(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Student)
{
    ui->setupUi(this);

    //打開數(shù)據(jù)庫
    DB = QSqlDatabase::addDatabase("QSQLITE");
    DB.setDatabaseName("./StudentDB.db");//打開數(shù)據(jù)庫
    if (DB.open())
    {
        qDebug() << "Database opened successfully！";
    }
    else
    {
        qDebug() << "無法打開數(shù)據(jù)庫：" << DB.lastError().text();
    }

    Add();//添加數(shù)據(jù)
    Select();//查詢數(shù)據(jù)
    Update();//更新數(shù)據(jù)
    Delete();//刪除數(shù)據(jù)
    
    FastAdd();//對(duì)大量數(shù)據(jù)進(jìn)行快速添加，嘗試過添加幾千條數(shù)據(jù)，不到一秒就添加完成
}

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

void Student::Add()//增
{
    /*
     * 在班級(jí)表中添加數(shù)據(jù),
       添加的數(shù)據(jù)為
       班級(jí)名稱：一班
       班主任：李主任
       班級(jí)人數(shù)：25
    */
    queryString = QString("insert into class(class_name, class_teacher ,student_number) values('%1','%2',%3) ")
                           .arg("一班").arg("李主任").arg(25);
    QSqlQuery query;//執(zhí)行sql語句
    if(query.exec(queryString)){
        qDebug()<<"insert data Successful!";
    }
    else {
        qDebug()<<"insert data Failed!";
    }
}

void Student::Delete()//刪
{
    //在這里有時(shí)候要判斷限制條件是否為空，這時(shí)候可以用這個(gè)sql語句，當(dāng)班級(jí)名稱不為空時(shí)刪除
    //queryString = QString("delete from class where class_name is not null");
    queryString = QString("delete from class where class_name = '%1'").arg("一班");
    QSqlQuery query(queryString);
    if(query.exec()){
        qDebug()<<"Delete data Successful!";
    }
    else {
        qDebug()<<"Delete data Failed!";
    }
}

void Student::Update()//改
{
    //更新數(shù)據(jù)，將班級(jí)名稱作為限制條件進(jìn)行數(shù)據(jù)更新
    queryString = QString("update class set class_teacher='%1' ,student_number=%2 where class_name='%3' ")
                              .arg("張主任").arg(30).arg("一班");
    QSqlQuery query;
    if (query.exec(queryString))
    {
        qDebug()<<"updata data Successful!";
    }
    else
    {
        qDebug()<<"updata data Failed!";
    }
}

void Student::Select()//查
{
    queryString = QString("select * from class where class_name = '%1'").arg("一班");
    QSqlQuery query(queryString);
    while(query.next()){
        QString class_teacher = query.value("class_teacher").toString();
        int student_number = query.value("student_number").toInt();
        qDebug()<<"班主任："<<class_teacher;
        qDebug()<<"班級(jí)人數(shù)："<<student_number;
    }
}

void Student::FastAdd()//對(duì)大量數(shù)據(jù)進(jìn)行快速添加，嘗試過添加幾千條數(shù)據(jù)，不到一秒就添加完成
{
    //使用事務(wù)
    DB.transaction();//開啟事務(wù)
    QSqlQuery query;
    query.prepare("INSERT INTO class (class_name, class_teacher, student_number) VALUES (?, ?, ?)");
    for (int i = 0; i < 100; ++i) {
        query.bindValue(0, "二班");
        query.bindValue(1, "張三");
        query.bindValue(2, i);
        query.exec();
    }
    DB.commit();//一次性提交，省去大量時(shí)間
}