Qt C/C + + programming case (1) student course selection management system for Sqlite database operation

Tip: if the score is too high, talk or comment privately. I'll reduce it. Thank you for your support

1. Video presentation address:

2. Case requirements:
2.1 basic requirements:
Course information includes: course number, course name, course teacher, credit, course nature, Department, etc.
2.2 functional requirements:
1) Be able to input, modify and delete course information;
2) Query the course information according to the given conditions (number, name, teacher, Department, etc.);
3) Save relevant information in the form of file. You can read the information in the default file for query and other operations.

3. Demand analysis:
3.1 according to the demand, we can choose three schemes: SQL database, Sqlite database and Excel table.
1) SQL Server 2008 installed on the computer can be used for data storage, and Qt can be directly used to directly operate various functions of sql.
2) It is easy to program directly using Sqlite provided by Qt platform.
3) Use Qt to store data using Excel.
In the above choices, I used Sqlite database programming because Sqlite supported by Qt platform is simpler than the other two. When testing the database, Navicat Premium 15 can be used to test the correctness of the local database, as shown in the figure:

The rest is to add, delete, query and modify the database.

4. Function source code analysis:

//Required header file
#include <QtSql>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlQueryModel>
//Variable declaration
QSqlDatabase db;//Establish qt and database connection
QSqlQueryModel model;//Save and traverse select results
QString tableName = "qTableInfo";//Add table name

//1. Establish database
//Establish connection with SQlite QSQLITE database
db = QSqlDatabase::addDatabase("QSQLITE");
//Sets the name of the database file
db.setDatabaseName("schoolTestDB.db");
//Open database
if(db.open() == false){

    QMessageBox::critical(this,"DataError",
                          db.lastError().text());
}
else {
}

//2. Build data sheet
//Build SQL statements that create tables
QString createSql =
            QString("CREATE TABLE %1(\
                'Course number' INT PRIMARY KEY NOT NULL,\
                'Course name' TEXT NOT NULL,\
                'teacher' TEXT NOT NULL,\
                'credit' REAL NOT NULL,\
                'Course nature' TEXT NOT NULL,\
                'Opening Department' TEXT NOT NULL)").arg(tableName);
     QSqlQuery query;
     query.exec(createSql);//Used to submit data to the database and create tables


//3. Database query global query
//Query table
QString str = QString("SELECT * FROM %1").arg(tableName);
    model.setQuery(str);
    ui->sqlite_View->setModel(&model);//It is used to display all data queried by the database in the ui interface

//4. Add data to the database
//Add data
QSqlQuery query;
    //Get course number
    int cNum = ui->cNum_Edit->text().toInt();
    //Get course name
    QString cName = ui->cName_Edit->text();
    //Get teacher name
    QString tName = ui->tName_Edit->text();
    //Get credit
    double score = ui->scoreEdit->text().toDouble();

    //Course nature
    QString cNature = ui -> cb_cNature -> currentText();

    //Get the opening Department
    QString schoolName = ui->school_cmb->currentText();

    //Construct sql statement
    QString insertSql = QString(
        "INSERT INTO %1 VALUES(%2,'%3','%4',%5,'%6','%7');"
                ).arg(tableName).arg(cNum).arg(cName).arg(tName).arg(score).arg(cNature).arg(schoolName);
    //Execute sql statement
    query.exec(insertSql);
//    Commit to commit the transaction to add database data. You must use commit, otherwise the add failure will occur
    query.exec("commit");

//5. Delete database statement
//Database delete data
QSqlQuery query;
    //    Note that symbols are very important in database operations. For example, double quotation marks cannot be changed into single quotation marks
    //Here, you can ask how to obtain the keyId and give the view click event. When you click the corresponding table data, you can get all the data of this row
        QString dropRowId = QString("delete from qTableInfo where Course number = %1").arg(keyId);
        //Execute sql statement
        query.exec(dropRowId);


//Database fuzzy query
//Database fuzzy query
QString selectInfo = QString("SELECT * FROM %1 WHERE %2 LIKE '%%3%'")
            .arg(tableName).arg(columnName).arg(serachKey);
            //%% fuzzy query
    model.setQuery(selectInfo);
    ui->cQueryView->setModel(&model);//Echo to view page

//6. Database modification
//Modify the database information primary key according to the primary key
QSqlQuery query;
//    Teacher
    QString tName = ui->tName_Edit->text();
//    credit
    double score = ui->scoreEdit->text().toDouble();
//    Course name
    QString cName = ui->cName_Edit->text();
//    Course nature
    QString cNature = ui->cb_cNature->currentText();
//    school
    QString scholl = ui->school_cmb->currentText();

    //Construct sql statement
    QString updatatSql = QString(
        "UPDATE %1 SET Course name = '%2', teacher = '%3', credit = %4, Course nature = '%5', Opening Department = '%6' WHERE Course number = %7"
                ).arg(tableName).arg(cName).arg(tName).arg(score).arg(cNature).arg(scholl).arg(sqlIdInfo);
    //Execute sql statement
    query.exec(updatatSql);
    //Question: how to click to bring the data from one window to another
    //The solution can be implemented using signals and slots, or it can be passed through global variables. Here, select global variables

5. How to save view data as Excel table:
You need exportexcel object C and exportexcel object H files can only be operated after they are added to the project.
Source code analysis (this code is modified online and can't save the first line of data. I don't know why a little partner knows it. Remember to chat or comment privately):

//    Get pop-up box input file name
    QString fileName = QFileDialog::getSaveFileName(this, tr("Excel file"), qApp->applicationDirPath (),
                                                    tr("Excel Files (*.xls)"));
    if (fileName.isEmpty())
        return;
//                            File name, created name, data source
    ExportExcelObject obj(fileName, "infoData", ui->InfoView);

//There's a question here. The first row of data can't be saved. I don't know why some friends know. Remember to chat or comment privately
//    obj.addField(0, "course number", "char(20)");
    obj.addField(1, "Course name", "char(20)");
    obj.addField(2, "Teacher", "char(20)");

    obj.addField(3, "credit", "char(20)");
    obj.addField(4, "Course nature", "char(20)");
    obj.addField(5, "Opening Department", "char(20)");

//    Set progress bar
    ui->progressBar->setValue(0);
    ui->progressBar->setMaximum(ui->InfoView->model()->rowCount());

//    Monitor progress bar changes
    connect(&obj, SIGNAL(exportedRowCount(int)), ui->progressBar, SLOT(setValue(int)));
//    Get the number of data pieces written
    int retVal = obj.export2Excel();
    if( retVal > 0)
    {
        QMessageBox::information(this, tr("Done"),
                                 QString(tr("%1 records exported!")).arg(retVal)
                                 );
    }

The above is the basic operations of Qt on adding, deleting, querying and modifying Sqlite database and the conversion of data storage Excel. If in doubt, you can download the source code to view it.
Source address: https://download.csdn.net/download/qq_43122582/21064541