在QT中如何使用sqlite？

Posted on | In , | |
Words count in article: 3.6k | Reading time ≈ 20

本节介绍在qt中使用sqlite的一些基本操作。

pro文件中添加：
QT += core sql

main.cpp中可以适当添加常用的库：

1
2
3
4
5
6
#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlError>

一个简单的实战demo：

widget.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>

namespace Ui {
class Widget;
}

class Widget : public QWidget
{
    Q_OBJECT

public:
    explicit Widget(QWidget *parent = nullptr);
    void init();
    void SHOW();
    ~Widget();

private slots:
    void on_tableView_clicked(const QModelIndex &index);

    void on_lineEdit_textChanged(const QString &arg1);

    void on_comboBox_currentTextChanged(const QString &arg1);

private:
    Ui::Widget *ui;
};

#endif // WIDGET_H

main.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#include "widget.h"
#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlError>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);


    //添加数据库驱动
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

    //设置数据库名称
    db.setDatabaseName("./testDB.db");

    if(db.open())
    {
        qDebug()<<"open success";
    }
    else
    {
        qDebug()<<"open failed";
        return false;
    }

    Widget w;
    w.show();

    return a.exec();
}

widget.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
#include "widget.h"
#include "ui_widget.h"
#include<QSqlQueryModel>
#include<QString>
#include<QSettings>
#include<QProcess>
#include<QMutex>
#include<QWaitCondition>

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


void Widget::init()
{
    QSqlQueryModel *model=new QSqlQueryModel;
    model->setQuery("SELECT name FROM company");
    ui->tableView->setModel(model);
}

void Widget::SHOW()
{
    QVariant currentData = ui->tableView->currentIndex().data();
    QSqlQueryModel * modell = new QSqlQueryModel;
    QString sqll;
    sqll = "select ADDRESS from COMPANY where NAME = '"+currentData.toString()+"'";
    modell->setQuery(sqll);
    QModelIndex indexl = modell->index(0,0);
    QString word = indexl.data().toString();
    ui->textEdit->setText(word);
}

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

void Widget::on_tableView_clicked(const QModelIndex &index)
{
    SHOW();
}

void Widget::on_lineEdit_textChanged(const QString &arg1)
{
    QSqlQueryModel *model=new QSqlQueryModel;
    QString a,b;
    b=ui->lineEdit->text();
    a="select NAME from COMPANY where NAME LIKE '%"+b+"%'";
    model->setQuery(a);
    ui->tableView->setModel(model);
}

void Widget::on_comboBox_currentTextChanged(const QString &arg1)
{
    QSqlQueryModel *model=new QSqlQueryModel;
    QString a,b;
    b = ui->comboBox->currentText();
    a="select NAME from COMPANY where AGE LIKE '"+b+"%'";
    model->setQuery(a);
    ui->tableView->setModel(model);
}

ui图：

1

db文件：

2

功能：

comboBox的功能是 选择表中AGE开头为数字多少的， 然后下面的lineedit的功能是 输入随机字母 可以匹配含有对应字母的name， 然后点击下面viewtable的对应格子， 可以右边显示对应name的address

注意:

qmake之后要在build的文件夹中手动复制进去原先项目文件里的db文件，否则build里的db并非我们想用的，而是新建的。

3

下面展示一些qt——sqlite常用的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlError>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    /*查看qt支持的数据库驱动
    qDebug() << "Available drivers:";
    QStringList drivers = QSqlDatabase::drivers();
    foreach(QString driver, drivers)
        qDebug() << driver;
    */

    //添加数据库驱动
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","Connection_Name");

    //设置数据库名称
    db.setDatabaseName("sqlitedata.db");

    //法1：
    //打开数据库
    if(!db.open())
    {
        qDebug() << "Connect to Sqlite error: " << db.lastError().text();
        exit(128);

    }

    //以下执行相关sql语句：
    QSqlQuery query(db);

    //新建student表，id设置为主键，还有一个name项
    query.exec("create table student(id int primary key,name varchar)");

    //向表中插入3条记录
    query.exec("insert into student values(1,'xiaogang')");
    query.exec("insert into student values(2,'xiaoming')");
    query.exec("insert into student values(3,'xiaohong')");

    //查找表中id >=2 的记录的id项和name项的值
    query.exec("select id,name from student where id >= 2");

    //query.next()指向查找到的第一条记录，然后每次后移一条记录
    while(query.next())
    {
        //query.value(0)是id的值，将其转换为int型
        int value0 = query.value(0).toInt();
        QString value1 = query.value(1).toString();
        //输出两个值
        qDebug() << value0 << value1 ;
     }


        /*法2
        // 3. 打开数据库连接，成功返回 true，不成功则程序退出
        if (!db.open()) {
            qDebug() << "Connect to Sqlite error: " << db.lastError().text();
            exit(128);
        }

        // 4. 在数据库中创建表
        QSqlQuery createTableQuery(db);
        QString sql("CREATE TABLE user("
                    "id integer primary key autoincrement, "
                    "username text not null, "
                    "password text not null, "
                    "email text, "
                    "mobile text)");
        createTableQuery.exec(sql);

        // 5. 向表中插入一条数据
        QSqlQuery insertQuery(db);
        insertQuery.exec("INSERT INTO user (username, password) VALUES ('Alice', 'passw0rd')");

        // 6. 查询刚才插入的数据
        QSqlQuery selectQuery(db);
        selectQuery.exec("SELECT id, username, password FROM user");

        while (selectQuery.next()) {
            qDebug() << QString("Id: %1, Username: %2, Password: %3")
                        .arg(selectQuery.value("id").toInt())
                        .arg(selectQuery.value("username").toString())
                        .arg(selectQuery.value("password").toString());
        }
        */

    return a.exec();
}

connection.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#ifndef CONNECTION_H
#define CONNECTION_H
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
       QMessageBox::critical(0, qApp->tr("Cannot open database"),
           qApp->tr("Unable to establisha database connection."
                     ), QMessageBox::Cancel);
       return false;
    }
    QSqlQuery query(db);
    query.exec("create table student (id int primary key, "
               "name varchar(20))");
    query.exec("insert into student values(0, 'first')");
    query.exec("insert into student values(1, 'second')");
    query.exec("insert into student values(2, 'third')");
    query.exec("insert into student values(3, 'fourth')");
    query.exec("insert into student values(4, 'fifth')");
    return true;
}
#endif // CONNECTION_H

mainwindow.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_pushButton_clicked();

private:
    Ui::MainWindow *ui;
};

#endif // MAINWINDOW_H

main.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#include "mainwindow.h"
#include <QApplication>
#include "connection.h"
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    if (!createConnection())
           return 1;

    MainWindow w;
    w.show();

    return a.exec();
}

mainwindow.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlQuery>
#include <QDebug>
#include <QSqlRecord>
#include <QSqlError>
MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
}

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

void MainWindow::on_pushButton_clicked()
{
        /*
        QSqlQuery query;
        query.exec("select * from student");
        while(query.next())
        {
           qDebug() << query.value(0).toInt()
                                          << query.value(1).toString();
        }
        */


        /*
        QSqlQuery query;
        query.exec("select * from student");
        qDebug() << "exec next() :";
        //开始就先执行一次next()函数，那么query指向结果集的第一条记录
        if(query.next())
        {
           //获取query所指向的记录在结果集中的编号
           int rowNum = query.at();
           //获取每条记录中属性（即列）的个数
           int columnNum = query.record().count();
           //获取"name"属性所在列的编号，列从左向右编号，最左边的编号为0
           int fieldNo = query.record().indexOf("name");
           //获取id属性的值，并转换为int型
           int id = query.value(0).toInt();
           //获取name属性的值
           QString name = query.value(fieldNo).toString();
           //将结果输出
           qDebug() << "rowNum is : " << rowNum
                     << " id is : " << id
                     << " name is : " << name
                     << " columnNum is : " << columnNum;
        }
    //定位到结果集中编号为2的记录，即第三条记录，因为第一条记录的编号为0
        qDebug() << "exec seek(2) :";
        if(query.seek(2))
        {
           qDebug() << "rowNum is : " << query.at()
                     << " id is : " << query.value(0).toInt()
                     << " name is : " << query.value(1).toString();
        }
        //定位到结果集中最后一条记录
        qDebug() << "exec last() :";
        if(query.last())
        {
           qDebug() << "rowNum is : " << query.at()
                     << " id is : " << query.value(0).toInt()
                     << " name is : " << query.value(1).toString();
        } */

    /*
        QSqlQuery query;
        int id = ui->spinBox->value();
        query.exec(QString("select name from student where id =%1").arg(id));
        query.next();
        QString name = query.value(0).toString();
        qDebug() << name;
    */

    /*
        QSqlQuery query;
        query.prepare("insert into student (id, name) "
                      "values (:id, :name)");
        query.bindValue(0, 5);
        query.bindValue(1, "sixth");
        query.exec();
        query.exec("select * from student");
        query.last();
        int id = query.value(0).toInt();
        QString name = query.value(1).toString();
        qDebug() << id << name;
     */


        QSqlQuery query;
        query.prepare("select name from student where id = ?");
        int id = ui->spinBox->value();
        query.addBindValue(id);
        query.exec();
        query.next();
        qDebug() << query.value(0).toString();


      /*
        QSqlQuery q;
        q.prepare("insert into student values (?, ?)");
        QVariantList ints;
        ints << 10 << 11 << 12 << 13;
        q.addBindValue(ints);
        QVariantList names;
        // 最后一个是空字符串，应与前面的格式相同
            names << "xiaoming" << "xiaoliang"
                          << "xiaogang" << QVariant(QVariant::String);
        q.addBindValue(names);
        if (!q.execBatch()) //进行批处理，如果出错就输出错误
           qDebug() << q.lastError();
        //下面输出整张表
        QSqlQuery query;
        query.exec("select * from student");
        while(query.next())
        {
           int id = query.value(0).toInt();
           QString name = query.value(1).toString();
           qDebug() << id << name;
        }
        */

}

4

connection.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#ifndef CONNECTION_H
#define CONNECTION_H
#include <QSqlDatabase>
#include <QSqlQuery>
static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database.db");
    if(!db.open()) return false;
    QSqlQuery query;
    query.exec("create table student (id int primary key, name vchar)");
    query.exec("insert into student values (0,'yafei0')");
    query.exec("insert into student values (1,'yafei1')");
    query.exec("insert into student values (2,'yafei2')");
    return true;
}
#endif // CONNECTION_H

mainwindow.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_pushButton_clicked();

private:
    Ui::MainWindow *ui;
};

#endif // MAINWINDOW_H

mysqlquerymodel.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#ifndef MYSQLQUERYMODEL_H
#define MYSQLQUERYMODEL_H

#include <QObject>
#include <QSqlQueryModel>

class MySqlQueryModel : public QSqlQueryModel
{
private:
    bool setName(int studentId, const QString &name);
    void refresh();

public:
    MySqlQueryModel() = default;
    MySqlQueryModel(QObject *parent);
    Qt::ItemFlags flags(const QModelIndex &index) const;
    bool setData(const QModelIndex &index, const QVariant &value, int role);
    QVariant data(const QModelIndex &item, int role=Qt::DisplayRole) const;
};

#endif // MYSQLQUERYMODEL_H

main.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
#include "mainwindow.h"
#include <QApplication>
#include "connection.h"

int main(int argc, char *argv[])
{
     QApplication a(argc, argv);
     if(!createConnection())
          return 1;
     MainWindow w;
     w.show();
     return a.exec();
}

mainwindow.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlQueryModel>
#include <QTableView>
#include <QSqlRecord>
#include <QModelIndex>
#include <QDebug>
#include <QSqlQuery>
#include "mysqlquerymodel.h"

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

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

/*
void MainWindow::on_pushButton_clicked()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("select * from student");
    model->setHeaderData(0, Qt::Horizontal, tr("id"));
    model->setHeaderData(1, Qt::Horizontal, tr("name"));
    QTableView *view = new QTableView;
    view->setModel(model);
    view->show();

    int column= model->columnCount(); //获得列数
    int row = model->rowCount();    // 获得行数
    QSqlRecord record = model->record(1); //获得一条记录
    QModelIndex index = model->index(1,1);   //获得一条记录的一个属性的值
    qDebug() << "column num is:" << column << endl
            << "row num is:" << row << endl
            <<"the second record is:" << record << endl
            << "the data of index(1,1) is:"<< index.data();
}
*/

/*
void MainWindow::on_pushButton_clicked()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("select * from student");
    model->setHeaderData(0, Qt::Horizontal, tr("id"));
    model->setHeaderData(1, Qt::Horizontal, tr("name"));
    QTableView *view = new QTableView;
    view->setModel(model);
    QSqlQuery query = model->query();
    query.exec("insert into student values (10,'yafei10')");
    query.exec("insert into student values (20,'yafei20')");
    model->setQuery("select * from student"); //再次查询整张表
    view->show(); //再次进行显示
}
*/

void MainWindow::on_pushButton_clicked()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("select * from student");
    model->setHeaderData(0, Qt::Horizontal, tr("id"));
    model->setHeaderData(1, Qt::Horizontal, tr("name"));
    QTableView *view = new QTableView;
    view->setModel(model);
    view->show();

//创建自己模型的对象
    MySqlQueryModel *myModel = new MySqlQueryModel;    myModel->setQuery("select * from student");
    myModel->setHeaderData(0, Qt::Horizontal, tr("id"));
    myModel->setHeaderData(1, Qt::Horizontal, tr("name"));
    QTableView *view1 = new QTableView;
    view1->setWindowTitle("mySqlQueryModel"); //修改窗口标题
    view1->setModel(myModel);
    view1->show();
}

mysqlquerymodel.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#include "mysqlquerymodel.h"
#include <QSqlQuery>
#include <QColor>

MySqlQueryModel::MySqlQueryModel(QObject *parent) :
    QSqlQueryModel(parent)
{
}

Qt::ItemFlags MySqlQueryModel::flags(
       const QModelIndex &index) const //返回表格是否可更改的标志
{
    Qt::ItemFlags flags = QSqlQueryModel::flags(index);
    if (index.column() == 1) //第二个属性可更改
       flags |= Qt::ItemIsEditable;
    return flags;
}

bool MySqlQueryModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
       //添加数据
{
    if (index.column() < 1 || index.column() > 2)
       return false;
   QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
    int id = data(primaryKeyIndex).toInt(); //获取id号
    clear();
    bool ok;
    if (index.column() == 1) //第二个属性可更改
       ok = setName(id, value.toString());
    refresh();
    return ok;
}

void MySqlQueryModel::refresh() //更新显示
{
    setQuery("select * from student");
    setHeaderData(0, Qt::Horizontal, QObject::tr("id"));
    setHeaderData(1, Qt::Horizontal, QObject::tr("name"));
}

//添加name属性的值
bool MySqlQueryModel::setName(int studentId, const QString &name)
{
    QSqlQuery query;
    query.prepare("update student set name = ? where id = ?");
    query.addBindValue(name);
    query.addBindValue(studentId);
    return query.exec();
}

//更改数据显示样式
QVariant MySqlQueryModel::data(const QModelIndex &index, int role) const
{
QVariant value = QSqlQueryModel::data(index, role);

//第一个属性的字体颜色为红色
    if (role == Qt::TextColorRole && index.column() == 0)
       return qVariantFromValue(QColor(Qt::red));
return value;
}

5

connection.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#ifndef CONNECTION_H
#define CONNECTION_H
#include <QSqlDatabase>
#include <QSqlQuery>
static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database.db");
    if(!db.open()) return false;
    QSqlQuery query;
    query.exec(QString(
      "create table student (id int primary key, name vchar)"));
    query.exec(QString("insert into student values (0,'刘明')"));
    query.exec(QString("insert into student values (1,'陈刚')"));
    query.exec(QString("insert into student values (2,'王红')"));
    return true;
}
#endif // CONNECTION_H

mainwindow.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlTableModel>
namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_pushButton_clicked();

    void on_pushButton_2_clicked();

    void on_pushButton_7_clicked();

    void on_pushButton_8_clicked();

    void on_pushButton_5_clicked();

    void on_pushButton_6_clicked();

    void on_pushButton_4_clicked();

    void on_pushButton_3_clicked();

private:
    Ui::MainWindow *ui;
    QSqlTableModel *model;
};

#endif // MAINWINDOW_H

main.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#include "mainwindow.h"
#include <QApplication>
#include "connection.h"
//#include <QTextCodec>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
//    QTextCodec::setCodecForTr(QTextCodec::codecForName("utf8"));
//    QTextCodec::setCodecForCStrings(QTextCodec::codecForLocale());
    //QTextCodec这玩意从QT5就被删掉了
    if(!createConnection())
       return 1;
    MainWindow w;
    w.show();

    return a.exec();
}

mainwindow.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QMessageBox>
#include <QSqlError>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    model = new QSqlTableModel(this);
    model->setTable("student");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select(); //选取整个表的所有行
    //不显示name属性列,如果这时添加记录，则该属性的值添加不上（下面那段代码的作用）
     //model->removeColumn(1);
    ui->tableView->setModel(model);
    //使其不可编辑（下面那段代码的作用）
    //ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
}

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

void MainWindow::on_pushButton_clicked()
{
        model->database().transaction(); //开始事务操作
        if (model->submitAll()) {
           model->database().commit(); //提交
        } else {
           model->database().rollback(); //回滚
           QMessageBox::warning(this, tr("tableModel"),
                                 tr("数据库错误: %1")
                                 .arg(model->lastError().text()));
        }
}

void MainWindow::on_pushButton_2_clicked()
{
     model->revertAll();
}

void MainWindow::on_pushButton_7_clicked()
{
        QString name = ui->lineEdit->text();
        //根据姓名进行筛选
        model->setFilter(QString("name = '%1'").arg(name));
        //显示结果
        model->select();
}



void MainWindow::on_pushButton_8_clicked()
{
    model->setTable("student");   //重新关联表
    model->select();   //这样才能再次显示整个表的内容
}

void MainWindow::on_pushButton_5_clicked()
{
    model->setSort(0, Qt::AscendingOrder); //id属性即第0列，升序排列
    model->select();
}


void MainWindow::on_pushButton_6_clicked()
{
    model->setSort(0, Qt::DescendingOrder);
    model->select();
}

void MainWindow::on_pushButton_4_clicked()
{
    //获取选中的行
    int curRow = ui->tableView->currentIndex().row();

    //删除该行
    model->removeRow(curRow);

    int ok = QMessageBox::warning(this,tr("删除当前行!"),tr("你确定"
                                                 "删除当前行吗？"),
                                  QMessageBox::Yes,QMessageBox::No);
    if(ok == QMessageBox::No)
    {
       model->revertAll(); //如果不删除，则撤销
    }
    else model->submitAll(); //否则提交，在数据库中删除该行
}

void MainWindow::on_pushButton_3_clicked()
{
    int rowNum = model->rowCount(); //获得表的行数
    int id = 10;
    model->insertRow(rowNum); //添加一行
    model->setData(model->index(rowNum,0),id);
    //model->submitAll(); //可以直接提交
}

6

connection.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#ifndef CONNECTION_H
#define CONNECTION_H
#include <QSqlDatabase>
#include <QSqlQuery>
static bool createConnection()
{
   QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
   db.setDatabaseName("database.db");
   if(!db.open()) return false;
   QSqlQuery query;
   query.exec("create table student (id int primary key, name vchar,course int)");
   query.exec("insert into student values(1,'yafei0',1)");
   query.exec("insert into student values(2,'yafei1',1)");
   query.exec("insert into student values(3,'yafei2',2)");

query.exec("create table course (id int primarykey, name vchar, teacher vchar)");
   query.exec("insert into course values(1,'Math','yafeilinux1')");
   query.exec("insert into course values(2,'English','yafeilinux2')");
   query.exec("insert into course values(3,'Computer','yafeilinux3')");
   return true;
}
#endif // CONNECTION_H

mainwindow.h:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include<QSqlRelationalTableModel>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private:
    Ui::MainWindow *ui;
    QSqlRelationalTableModel *model;
};

#endif // MAINWINDOW_H

main.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
#include "mainwindow.h"
#include <QApplication>
#include "connection.h"
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection()) return 1;
    MainWindow w;
    w.show();

    return a.exec();
}

mainwindow.cpp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlRelationalDelegate>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    model = new QSqlRelationalTableModel(this);
    //属性变化时写入数据库
    model->setEditStrategy(QSqlTableModel::OnFieldChange);
    model->setTable("student");
    //将student表的第三个属性设为course表的id属性的外键，
    //并将其显示为course表的name属性的值
    model->setRelation(2,QSqlRelation("course","id","name"));
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("Course"));
    model->select();
    ui->tableView->setModel(model);
    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));
}

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

7

Donate? comment?