效果

image.png

在common文件夹下面新建一个js文件，用于存放封装的基本方法

// 创建数据库 ｜ 打开数据库
function openSqllite() {
    return new Promise((resolve, reject) => {
        plus.sqlite.openDatabase({
            name: 'asset', // 数据库名称
            path: '_doc/asset.db', // 数据库地址
            success(e) {
                resolve(e)
            },
            fail(e) {
                reject(e)
            }
        })
    })
}

// 关闭数据库
function closeSqllite() {
    return new Promise((resolve, reject) => {
        plus.sqlite.closeDatabase({
            name: 'asset', // 数据库名称
            success(e) {
                resolve()
            },
            fail(e) {
                reject()
            }
        })
    })
}

// 监听数据库是否开启 return type : Boolean
function isOpen(name, path) {
    return plus.sqlite.isOpenDatabase({
        name: 'asset',
        path: '_doc/asset.db'
    })
}

// 执行 sql 语句 for create table
/* 例子
    创建 table
    'create table if not exists userInfo
    (
        "index" INTEGER PRIMARY KEY AUTOINCREMENT,  自动增加
        "id" TEXT,  字符串
        "name" TEXT,
        "gender" TEXT,
        "avatar" TEXT
    )'
    
    删除表
    'drop table name'
    
    添加数据
    'insert into userInfo (id,name,gender,avatar) values("'+id+'","'+name+'","'+gender+'","'+avatar+'")'
    'insert into userInfo (name, age) values ("demo1", "23")'

    删除数据
    'delete from '+name+' where '+sol+'="'+qq+'" and '+ww+'='+ee+''
    'delete from '+name+' where '+sol+'="'+qq+'"'
    
    修改数据
    UPDATE table_name SET field1=new-value1, field2=new-value2
    'update '+listName+' set '+name+'="'+cont+'" where '+use+'="'+sel+'"'
    'update '+listName+' set '+name+'="'+cont+'"'
*/
function executeSqlCode(sqlCode) {
    return new Promise((resolve, reject) => {
        plus.sqlite.executeSql({
            // 数据库名称
            name: 'asset', // 数据库名称
            sql: sqlCode, // sql 语句
            success(e) {
                resolve(e)
            },
            fail(e) {
                reject(e)
            }
        })
    })
}

// 查询数据
/* 例子
    'select * from '+name+' where '+aa+'='+bb+' and '+cc+'='+dd+''
    'select * from '+name+' where '+aa+'='+bb+''
    'select * from '+name+''
    
    // 高级查询
    'select * from '+id+' order by list desc limit 15 offset '+num+'',
*/
function selectFromTable(sqlCode) {
    return new Promise((resolve, reject) => {
        plus.sqlite.selectSql({
            name: 'asset', // 数据库名称
            sql: sqlCode,
            success(e) {
                resolve(e)
            },
            fail(e) {
                reject(e)
            }
        })
    })
}

export default {
    openSqllite,
    closeSqllite,
    isOpen,
    executeSqlCode,
    selectFromTable,
}

使用

<template>
    <view class="container">
        <button type="default" @click="openSqllite">开启数据库</button>
        <button type="default" @click="closeSqllite">关闭数据库</button>
        <button type="default" @click="createTable">创建table</button>
        <button type="default" @click="delTable">删除table</button>
        <button type="default" @click="addData">添加数据</button>
        <button type="default" @click="delData">删除数据</button>
        <button type="default" @click="getData">查询数据</button>
        状态：{{ status }}
    </view>
</template>

<script>
import sqlLite from '@/common/sqllite-utils.js';

export default {
    data() {
        return { status: '' };
    },
    onLoad() {},
    methods: {
        // 开启数据库
        openSqllite() {
            if (sqlLite.isOpen()) {
                this.status = '开启成功!';
            } else {
                // 开启数据库
                sqlLite
                    .openSqllite()
                    .then(e => (this.status = '开启成功!'))
                    .catch(e => (this.status = '开启失败!'));
            }
        },
        // 关闭数据库
        closeSqllite() {
            if (sqlLite.isOpen()) {
                // 关闭数据库
                sqlLite
                    .closeSqllite()
                    .then(e => (this.status = '关闭成功!'))
                    .catch(e => (this.status = '关闭失败!'));
            } else {
                this.status = '关闭成功!';
            }
        },
        // 创建数据库 (index, name, age)
        createTable() {
            sqlLite
                .executeSqlCode(
                    'create table if not exists sys_user ( "index" INTEGER PRIMARY KEY AUTOINCREMENT,"username" TEXT,"user_id" TEXT,"password" TEXT,"phone" TEXT,"dept_id" TEXT)'
                )
                .then(e => (this.status = '创建成功!'))
                .catch(e => (this.status = '创建失败!'));
        },
        delTable() {
            sqlLite
                .executeSqlCode('drop table sys_user')
                .then(e => (this.status = '删除成功!'))
                .catch(e => (this.status = '删除失败!'));
        },
        // 添加数据
        addData() {
            var username = 'admin';
            var user_id = '1';
            var password = '123456';
            var phone = '15956013940';
            var dept_id = '12';
            sqlLite
                .executeSqlCode(
                    'insert into sys_user (username, user_id,password,phone,dept_id) values ("' + username + '","' + user_id + '","' + password + '","' + phone + '","' + dept_id + '")'
                )
                .then(e => (this.status = '添加成功!'))
                .catch(e => (this.status = '添加失败!'));
        },
        // 删除数据
        delData() {
            var tableName = 'sys_user';
            var user_id = 'user_id';
            var qq = '1';
            sqlLite
                .executeSqlCode('delete from ' + tableName + ' where ' + user_id + '="' + qq + '"')
                .then(e => (this.status = '删除成功!'))
                .catch(e => (this.status = '删除失败!'));
        },
        // 查询数据
        getData() {
            var tableName = 'sys_user';
            sqlLite
                .selectFromTable('select * from ' + tableName + '')
                .then(date => (this.status = date))
                .catch(e => (this.status = '查询失败!'));
        }
    }
};
</script>

<style>
.container {
    padding: 20px;
    font-size: 14px;
    line-height: 24px;
}
</style>