Qt6 QML Book/存储/本地存储-SQL

Local Storage - SQL

本地存储-SQL

Qt Quick supports a local storage API known from the web browsers the local storage API. the API is available under “import QtQuick.LocalStorage 2.0”.

Qt Quick支持一种从web浏览器中得知的本地存储接口，即本地存储API。该API通过“import QtQuick.LocalStorage 2.0”导入。

In general, it stores the content into an SQLite database in a system-specific location in a unique ID based file based on the given database name and version. It is not possible to list or delete existing databases. You can find the storage location from QQmlEngine::offlineStoragePath().

通常，它会根据给定的数据库名称和版本，将内容存储到特定于系统位置的SQLite数据库中，存储在一个基于唯一ID的文件中。无法列出或删除现有数据库。可以从QQmlEngine:：offlineStoragePath（）中找到存储位置。

You use the API by first creating a database object and then creating transactions on the database. Each transaction can contain one or more SQL queries. The transaction will roll-back when a SQL query will fail inside the transaction.

使用API时，首先创建数据库对象，然后在数据库上创建事务。每个事务可以包含一个或多个SQL查询。当SQL查询在事务内部失败时，事务将回滚。

For example, to read from a simple notes table with a text column you could use the local storage like this:

例如，要从带有文本列的简单notes表中读取，可以使用如下本地存储：

import QtQuick
import QtQuick.LocalStorage 2.0

Item {
    Component.onCompleted: {
        const db = LocalStorage.openDatabaseSync("MyExample", "1.0", "Example database", 10000)
        db.transaction( function(tx) {
            const result = tx.executeSql('select * from notes')
            for(let i = 0; i < result.rows.length; i++) {
                print(result.rows[i].text)
            }
        })
    }
}

Crazy Rectangle

疯狂矩形

As an example assume we would like to store the position of a rectangle on our scene.

例如，假设我们想要存储场景中矩形的位置。

Here is the base of the example. It contains a rectange called crazy that is draggable and shows its current x and y position as text.

下面是这个例子的基础。它包含一个名为crazy的矩形区域，可以拖动，并以文本形式显示其当前的x和y位置。

Item {
    width: 400
    height: 400

    Rectangle {
        id: crazy
        objectName: 'crazy'
        width: 100
        height: 100
        x: 50
        y: 50
        color: "#53d769"
        border.color: Qt.lighter(color, 1.1)
        Text {
            anchors.centerIn: parent
            text: Math.round(parent.x) + '/' + Math.round(parent.y)
        }
        MouseArea {
            anchors.fill: parent
            drag.target: parent
        }
    }
    // ...

You can drag the rectangle freely around. When you close the application and launch it again the rectangle is at the same position.

可以随意拖动矩形。关闭应用程序并再次启动时，矩形位于同一位置。

Now we would like to add that the x/y position of the rectangle is stored inside the SQL DB. For this, we need to add an init, read and store database function. These functions are called when on component completed and on component destruction.

现在我们想补充一点，矩形的x/y位置存储在SQL数据库中。为此，我们需要添加一个init、read和store数据库函数。这些函数在组件完成或组件销毁时调用。

import QtQuick
import QtQuick.LocalStorage 2.0

Item {
    // reference to the database object
    property var db

    function initDatabase() {
        // initialize the database object
    }

    function storeData() {
        // stores data to DB
    }

    function readData() {
        // reads and applies data from DB
    }

    Component.onCompleted: {
        initDatabase()
        readData()
    }

    Component.onDestruction: {
        storeData()
    }
}

You could also extract the DB code in an own JS library, which does all the logic. This would be the preferred way if the logic gets more complicated.

您还可以在自己的JS库中提取DB代码，该库执行所有逻辑。如果逻辑变得更复杂，这将是首选方法。

In the database initialization function, we create the DB object and ensure the SQL table is created. Notice that the database functions are quite talkative so that you can follow along on the console.

在数据库初始化函数中，我们创建DB对象并确保创建SQL表。请注意，数据库功能非常灵活，因此您可以在控制台上进行操作。

function initDatabase() {
    // initialize the database object
    print('initDatabase()')
    db = LocalStorage.openDatabaseSync("CrazyBox", "1.0", "A box who remembers its position", 100000)
    db.transaction( function(tx) {
        print('... create table')
        tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT, value TEXT)')
    })
}

The application next calls the read function to read existing data back from the database. Here we need to differentiate if there is already data in the table. To check we look into how many rows the select clause has returned.

接下来，应用程序调用read函数从数据库中读回现有数据。这里我们需要区分表中是否已经有数据。为了检查，我们查看select子句返回了多少行。

function readData() {
    // reads and applies data from DB
    print('readData()')
    if(!db) { return }
    db.transaction(function(tx) {
        print('... read crazy object')
        const result = tx.executeSql('select * from data where name="crazy"')
        if(result.rows.length === 1) {
            print('... update crazy geometry')
            // get the value column
            const value = result.rows[0].value
            // convert to JS object
            const obj = JSON.parse(value)
            // apply to object
            crazy.x = obj.x
            crazy.y = obj.y
        }
    })
}

We expect the data is stored in a JSON string inside the value column. This is not typical SQL like, but works nicely with JS code. So instead of storing the x,y as properties in the table, we store them as a complete JS object using the JSON stringify/parse methods. In the end, we get a valid JS object with x and y properties, which we can apply on our crazy rectangle.

我们希望数据存储在值列中的JSON字符串中。这不是典型的SQL，但可以很好地与JS代码配合使用。因此，我们没有将x，y作为属性存储在表中，而是使用JSON stringify/parse方法将它们存储为一个完整的JS对象。最后，我们得到了一个具有x和y属性的有效JS对象，我们可以将其应用到疯狂的矩形上。

To store the data, we need to differentiate the update and insert cases. We use update when a record already exists and insert if no record under the name “crazy” exists.

为了存储数据，我们需要区分更新和插入案例。当记录已经存在时，我们使用update，如果不存在名为“疯狂”的记录，则使用insert。

function storeData() {
    // stores data to DB
    print('storeData()')
    if(!db) { return }
    db.transaction(function(tx) {
        print('... check if a crazy object exists')
        var result = tx.executeSql('SELECT * from data where name = "crazy"')
        // prepare object to be stored as JSON
        var obj = { x: crazy.x, y: crazy.y }
        if(result.rows.length === 1) { // use update
            print('... crazy exists, update it')
            result = tx.executeSql('UPDATE data set value=? where name="crazy"', [JSON.stringify(obj)])
        } else { // use insert
            print('... crazy does not exists, create it')
            result = tx.executeSql('INSERT INTO data VALUES (?,?)', ['crazy', JSON.stringify(obj)])
        }
    })
}

Instead of selecting the whole recordset we could also use the SQLite count function like this: SELECT COUNT(*) from data where name = "crazy" which would return use one row with the number of rows affected by the select query. Otherwise, this is common SQL code. As an additional feature, we use the SQL value binding using the ? in the query.

除了选择整个记录集，我们还可以像这样使用SQLite count函数：SELECT count（*）from data where name=“crazy”，它将返回查询的行数。否则，这就是常见的SQL代码。作为一个附加功能，我们使用SQL值绑定，在查询中使用？。

Now you can drag the rectangle and when you quit the application the database stores the x/y position and applies it on the next application run.

现在可以拖动矩形，退出应用程序时，数据库存储x/y位置，并在下一次应用程序运行时应用它。

示例源码下载