uni-app集成sqlite

Sqlite

SQLite 是一种轻量级的关系型数据库管理系统（RDBMS），广泛应用于各种应用程序中，特别是那些需要嵌入式数据库解决方案的场景。它不需要单独的服务器进程或系统配置，所有数据都存储在一个单一的普通磁盘文件中，这使得它非常适合用于开发小型到中型的应用程序。

作用

一般用于app离线缓存

集成方法

第一步勾选中SQLite 模块

第二步
我将代码都放到了一个文件夹里

index.js 该文件参考网上来源

let dbName = 'transfer'
/** 打开数据库或者创建数据库*/
export const openDb = () => {return new Promise((resolve, reject) => {if (isOpen()) {resolve()return}plus.sqlite.openDatabase({name: dbName,path: `_doc/storage/${dbName}.db`,success: (db) => {console.log('打开数据库成功', db)resolve(db)},fail: (e) => {console.log('打开数据库失败', e)reject(e)},})})
}
/** 创建表，执行sql语句* @param {String} tableName 表名* @param {String} data 表结构*/
export const createTable = (tableName, data) => {return new Promise(async (resolve, reject) => {if (!tableName || !data) {return}let flag = await isTable(tableName)if (flag) {resolve(true)return}plus.sqlite.executeSql({name: dbName,sql: `CREATE TABLE IF NOT EXISTS ${tableName} ${data}`,success: (e) => {console.log('创建表成功', e)resolve(e)},fail: (e) => {console.log('创建表失败', e)reject()},})})
}
/** 判断表是否存在* @param {String} tableName 表名*/
export const isTable = (tableName) => {return new Promise((resolve, reject) => {if (!tableName) {return}plus.sqlite.selectSql({name: dbName,sql: `SELECT name FROM sqlite_master WHERE type='table' AND name='${tableName}'`,success: (e) => {console.log('查询表成功', e)resolve(e.length ? true : false)},fail: (e) => {console.log('查询表失败', e)reject(e)},})})
}
/** 删除表* @param {String} tableName 表名*/
export const deleteTable = (tableName) => {return new Promise((resolve, reject) => {if (!tableName) {return}plus.sqlite.executeSql({name: dbName,sql: `DROP TABLE IF EXISTS ${tableName}`,success: (e) => {console.log('删除表成功', e)resolve(e)},fail: (e) => {console.log('删除表失败', e)reject(e)},})})
}
/** 删除所有表数据*/
export const deleteTableData = (tableName, whereSql) => {if (!tableName) {return}let sql = `DELETE FROM ${tableName}`if (whereSql) {sql = `DELETE FROM ${tableName} WHERE ${whereSql}`}return new Promise((resolve, reject) => {plus.sqlite.executeSql({name: dbName,sql,success: (e) => {console.log('删除表数据成功', e)resolve(e)},fail: (e) => {console.log('删除表数据失败', e)reject(e)},})})
}/** 查询所有表*/export const getTable = () => {return new Promise((resolve, reject) => {plus.sqlite.selectSql({name: dbName,sql: `SELECT name FROM sqlite_master WHERE type='table'`,success: (e) => {console.log('查询表成功', e)resolve(e)},fail: (e) => {console.log('查询表失败', e)reject(e)},})})
}
/** 插入数据* @param {String} tableName 表名* @param {Object} data 数据*/
export const insertData = (sql) => {return new Promise((resolve, reject) => {plus.sqlite.executeSql({name: dbName,sql: sql,success: (e) => {console.log('插入数据成功', e)resolve(e)},fail: (e) => {console.log('插入数据失败', e)reject(e)},})})
}/** 查询数据* @param {String} tableName 表名*/
export const selectData = (tableName) => {return new Promise((resolve, reject) => {if (!tableName) {return}plus.sqlite.selectSql({name: dbName,sql: `SELECT * FROM ${tableName}`,success: (e) => {console.log('查询数据成功', e)resolve(e)},fail: (e) => {console.log('查询数据失败', e)reject(e)},})})
}
/** 判断数据库是否打开*/
export const isOpen = () => {let open = plus.sqlite.isOpenDatabase({name: dbName,path: `_doc/storage/${dbName}.db`,})console.log('数据库是否打开', open)return open
}
/** 关闭数据库*/
export const closeDb = () => {return new Promise((resolve, reject) => {plus.sqlite.closeDatabase({name: dbName,path: `_doc/storage/${dbName}.db`,success: (e) => {console.log('关闭数据库成功', e)resolve(e)},fail: (e) => {console.log('关闭数据库失败', e)reject(e)},})})
}export const sqlite = {openDb,createTable,isTable,isOpen,closeDb,insertData,selectData,getTable,deleteTable,deleteTableData,
}

work.js

import {sqlite
} from './index.js'export default class WorkSqlite {constructor(tableName) {this.tableName = tableName}/** 初始化数据库* @param {String} tableName 表名* @param {String} sql 表结构*/initSqlite(sql = '("id" TEXT,"content" TEXT)') {return new Promise((resolve, reject) => {sqlite.openDb().then(async () => {let table = await sqlite.createTable(this.tableName, sql).catch(() => {reject(false)})if (table) {resolve(true)} else {reject(false)}})})}/** 插入单条数据* @param {String} tableName 表名* @param {Object} data 数据*/insertData(data) {let keys = Object.keys(data)let values = Object.values(data)let sql =`INSERT INTO ${this.tableName} (${keys.join(',')}) VALUES (${values.map((item) => `'${item}'`).join(',')})`return sqlite.insertData(sql)}/** 查询数据* @param {String} tableName 表名*/selectData() {return sqlite.selectData(this.tableName)}/** 删除表数据*/deleteTableData(where = null) {return sqlite.deleteTableData(this.tableName, where)}/** 删除表*/deleteTable(tableName) {return sqlite.deleteTable(tableName)}closeDb() {return sqlite.closeDb()}/** 查询所有表*/getTable() {return sqlite.getTable()}/** 批量插入数据* @param {String} tableName 表名* @param {Object} data 数据*/insertBatchData(data) {if (!Array.isArray(data) || !data?.length) {console.log('插入数据必须是数组')return}let sql = []data.forEach((item) => {let keys = Object.keys(item)let values = Object.values(item)sql.push(`INSERT INTO ${this.tableName} (${keys.join(',')}) VALUES (${values.map((item) => `'${item}'`).join(',')})`)})return sqlite.insertData(sql)}/** 判断数据库是否连接* @param {String} tableName 表名*/async isConnect() {let open = sqlite.isOpen()if (!open) {return false}let table = await sqlite.isTable(this.tableName)if (!table) {return false}return true}}

使用示例：

import WorkSqlite from '@/sqlite/work.js';
export default {data(){return {workPending: null,workRuning: null}},onLoad(e) {// 初始化待处理任务数据库链接,传入的是表名，需要几个表就new 几个。this.workPending = new WorkSqlite('workPending');this.workRuning = new WorkSqlite('workRuning');},
}

修改表设计需要自己传入sql语法,initSqlite时传入sql语句即可自定义表设计。

默认支持数据批量插入，表字段只有 id 和 content

最后如果sqlite 没生效，可能需要打自定义基座。sqlite 只适合app，H5不支持！