For the development project to use the local storage, the general use of cache storage is enough, but if the amount of data is large, you need to use SQLite database for data storage.

The problem

For example, I encountered a business, because the operating environment is in the case of disconnection, so the user’s data needs to be stored locally, data including a series of text content and pictures; Therefore, I adopted the method of separate storage, the picture is stored in the album, including the image path of other data stored in SQLite.

sqlite

Note: HBuilderX1.7.2 and later versions support this feature.

Open/create database

void plus.sqlite.openDatabase(options)
Copy the code

The API needs to pass in an object containing at least a name and a path, where name is the name of the database and path is the address of the database. The official recommendation is to start with _doc/, such as doc/demo.db. Example code:

function openDatabase () {
    return new Promise((resolve, reject) = > {
        plus.sqlite.openDatabase({
            name: 'demo'.path: '_doc/demo.db'.success: () = > {
                resolve('ok.')},fail: () = > {
                reject('fail.')}})})}Copy the code

Check whether the database is open

Boolean plus.sqlite.isOpenDatabase(options)
Copy the code

This API is used to determine whether the database is open. If the database is open again, the callback function will fail. Therefore, before opening the database again, you should determine the current state. The object passed in also contains two mandatory parameters, name and path, and returns Boolean values. Example code:

const isOpen = plus.sqlite.isOpenDatabase({
    name: 'demo'.path: '_doc/demo.db'
})
Copy the code

Closing the database

void plus.sqlite.closeDatabase(options)
Copy the code

To shut down the database, pass in the mandatory parameter name and the optional parameters SUCCESS and FAIL.

Execute a query statement

void plus.sqlite.selectSql(options)
Copy the code

This API is used to query table data. In addition to parameters such as name, success, fail, etc., you need to pass SQL parameters. SQL is an SQL statement (basically the same syntax as mysql). Example code:

function findData () {
    return new Promise((resolve, reject) = > {
        plus.sqlite.selectSql({
            name: 'demo'.sql: 'select * from users'.success: data= > {
                resolve(data)
            },
            fail: () = > {
                reject('fail.')}})})}Copy the code

The success callback of the selectSql method returns a value as an array of JSON objects, or an empty array without data from the database.

Add, delete, modify, create tables and other statements

void plus.sqlite.executeSql(options)
Copy the code

Similar to selectSql, SQL fields are passed in, but the difference here is that the SQL passed in with executeSql can be an array of strings in addition to a string type, indicating that multiple operations are performed. Example code:

function createUsers () {
    return new Promise((resolve, reject) = > {
        plus.sqlite.executeSql({
            name: 'demo'.sql: Create table if not EXISTS Users (" ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL COMMENT "ID", "Name" varchar(32) NOT NULL COMMENT "name") '.success: () = > {
                resolve('ok.')},fail: () = > {
                reject('fail.')}})})}Copy the code
return new Promise((resolve, reject) = > {
        plus.sqlite.executeql({
            name: 'demo'.sql: `insert into users (name) values ('lihua')`.success: () = > {
                plus.sqlite.selectSql({
                    name: 'demo'.sql: 'select last_insert_rowid()'.success: data= > {
                        // Get the inserted primary key ID
                        resolve(data)
                    }
                })
            },
            fail: () = > {
                reject('fail.')}})})Copy the code

Open the transaction

This is to ensure that the data consistency and atomicity, simply put, in a transaction to do multiple SQL operations, either successful or not successful.

void plus.sqlite.transaction(options)
Copy the code

In addition to name, SUCCESS, and fail, there is an operation parameter, which has three values: begin, COMMIT, and rollback. It is important to remember to close the transaction after starting it, either commit it or roll it back (the rollback is usually performed as an exception).

The end of the

Uni-app provides a very simple API for operating SQLite, which is useful when you need to cache large amounts of data. It is important to note that if you uninstall the app, the SQLite database will be lost.