第十二章：QML使用Sqlite数据库

QtQuick通过LocalStorage访问SQLite数据库，完成增删改查 ， 使用流程总结如下：

1、设置数据库存储路径：setOfflineStoragePath("xxx")   如果不设置路径，会发现数据库不知道存放在什么位置

QQmlApplicationEngine engine;
engine.setOfflineStoragePath("./");  //设置数据库存储路径
engine.load(QUrl(QStringLiteral("qrc:/main.qml")));

2、qml中导入LocalStorage：import QtQuick.LocalStorage 2.0

3、添加一个数据库操作JS文件：database.js，该文件存放所有数据库操作函数，便于维护和方便模块化管理数据库

如：创建数据库表

var db;

function initDatabase() {
    db = LocalStorage.openDatabaseSync("CrazyBox", "1.0", "A box who remembers its position", 100000);
    try {
        db.transaction( function(tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT, value TEXT)');
        })
    } catch (err) {
           console.log("Error creating table in database: " + err)
       };
}

4、在qml文件中直接调用函数即可

导入：import "database.js" as DB

DB.initDatabase()

DB.insertData("color", "red")

官方文档：https://doc.qt.io/qt-5/qtquick-localstorage-qmlmodule.html

---

完整示例

效果

 

main.cpp

#include <QGuiApplication>
#include <QQmlApplicationEngine>

int main(int argc, char *argv[])
{
    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    engine.setOfflineStoragePath("./");  //设置数据库存储路径
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));

    if (engine.rootObjects().isEmpty())
        return -1;

    return app.exec();
}

database.js

var db;

function initDatabase() {
    db = LocalStorage.openDatabaseSync("CrazyBox", "1.0", "A box who remembers its position", 100000);
    try {
        db.transaction( function(tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT, value TEXT)');
        })
    } catch (err) {
           console.log("Error creating table in database: " + err)
       };
}

function readData(name) {
    var res="";
    if(!db) { return; }
    db.transaction( function(tx) {
        var result = tx.executeSql('select value from data where name=?', [name]);
        if (result.rows.length > 0) {
             res = result.rows.item(0).value;
        } else {
            res = "Unknown";
        }
    })
    return res
}

function insertData(name, value) {
    var res = "";
    if(!db) { return; }
    db.transaction( function(tx) {
        var result = tx.executeSql('INSERT OR REPLACE INTO data VALUES (?,?);', [name, value]);
        if (result.rowsAffected > 0) {
          res = "OK";
        } else {
          res = "Error";
        }
    })
    return res
}

main.qml

import QtQuick 2.7
import QtQuick.Window 2.2
import QtQuick.Layouts 1.3
import QtQuick.LocalStorage 2.0
import "database.js" as DB
import QtQuick.Controls 1.4


Window {
    id: window
    visible: true
    width: 640
    height: 280
    property alias window: window
    title: qsTr("数据库")
    Component.onCompleted: {
        DB.initDatabase()
        DB.insertData("color", "red")
    }
    ColumnLayout {
        anchors.fill: parent
        anchors.margins: 10
        spacing: 10
        RowLayout {
            Layout.fillWidth: true
            TextField {
                id: mInputText
                Layout.fillWidth: true
                focus: true
                placeholderText: "please input name"
            }
            Button {
                id: mButtonSearch
                text: "SEARCH"
                onClicked: {
                    console.log("button click event")
                    myModel.append({name: mInputText.text, value: DB.readData(mInputText.text)})
                }
            }
        }
        TableView {
            id: myTableView
            Layout.fillHeight: true
            anchors {
                left: parent.left
                right: parent.right
                bottom: parent.bottom
            }
            model: ListModel {
                id: myModel
            }
            TableViewColumn {
                id: titleColumn
                role: "name"
                title: "Name"

                width: myTableView.viewport.width /2
            }
            TableViewColumn {
                id: authorColumn
                role: "value"
                title: "Value"
                width: myTableView.viewport.width / 2
            }
        }
    }

}