uni-app使用sqlite数据库

       在开发中，我们也会遇到在离线状态的情况下对数据进行存储；我们常用的有session、localstroage等操作；但是存储大小都是有限制的；因此前端可以使用sqlite对数据库的数据进行存储；
       sqlite数据库简介：SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。

1、对数据库操作的方法进行封装；

module.exports ={dbName:'gather',dbPath:"_downloads/gather.db",// 判断数据库是否打开isOpen(){// 数据库打开了就返回 true,否则返回 falsevar open = plus.sqlite.isOpenDatabase({name:this.dbName,// 数据库名称path:this.dbPath  // 数据库地址})return open;},// 创建数据库 或 有该数据库就打开openSqlite(){returnnewPromise((resolve, reject)=>{// 打开数据库
            plus.sqlite.openDatabase({name:this.dbName,path:this.dbPath,success(e){resolve(e);// 成功回调},fail(e){reject(e);// 失败回调}})})},// 关闭数据库closeSqlite(){returnnewPromise((resolve, reject)=>{
            plus.sqlite.closeDatabase({name:this.dbName,success(e){resolve(e);},fail(e){reject(e);}})})},// 数据库建表 sql:'CREATE TABLE IF NOT EXISTS dbTable("id" varchar(50),"name" TEXT) // 创建 CREATE TABLE IF NOT EXISTS 、 dbTable 是表名，不能用数字开头、括号里是表格的表头createTable(dbTable, data){returnnewPromise((resolve, reject)=>{// executeSql: 执行增删改等操作的SQL语句
            plus.sqlite.executeSql({name:this.dbName,name:"gather",sql:`CREATE TABLE IF NOT EXISTS ${dbTable}(${data})`,success(e){resolve(e);},fail(e){reject(e);}})})},// 数据库删表 sql:'DROP TABLE dbTable'dropTable(dbTable){returnnewPromise((resolve, reject)=>{
           plus.sqlite.executeSql({name:this.dbName,sql:`DROP TABLE ${dbTable}`,success(e){resolve(e);},fail(e){reject(e);}})})},// 向表格里添加数据 sql:'INSERT INTO dbTable VALUES('x','x','x')'   对应新增// 或者 sql:'INSERT INTO dbTable ('x','x','x') VALUES('x','x','x')'   具体新增// 插入 INSERT INTO  、 dbTable 是表名、根据表头列名插入列值insertTableData(dbTable, data, condition){// 判断有没有传参if(dbTable !==undefined&& data !==undefined){// 判断传的参是否有值var bol =(JSON.stringify(data)=="{}");if(!bol){if(condition ==undefined){var sql =`INSERT INTO ${dbTable} VALUES('${data}')`;}else{var sql =`INSERT INTO ${dbTable} (${condition}) VALUES(${data})`;}returnnewPromise((resolve, reject)=>{// 表格添加数据
              plus.sqlite.executeSql({name:this.dbName,sql: sql,success(e){resolve(e);},fail(e){reject(e);}})})}else{returnnewPromise((resolve, reject)=>{reject("错误添加")})}}else{returnnewPromise((resolve, reject)=>{reject("错误添加")})}},// 根据条件向表格里添加数据  有数据更新、无数据插入// (建表时需要设置主键) 例如 --- "roomid" varchar(50) PRIMARY KEYinsertOrReplaceData(dbTable, data, condition){// 判断有没有传参if(dbTable !==undefined&& data !==undefined){if(condition ==undefined){var sql =`INSERT OR REPLACE INTO ${dbTable} VALUES('${data}')`;}else{var sql =`INSERT OR REPLACE INTO ${dbTable} (${condition}) VALUES(${data})`;}// console.log(sql);returnnewPromise((resolve, reject)=>{// 表格添加数据
              plus.sqlite.executeSql({name:this.dbName,sql: sql,success(e){resolve(e);},fail(e){reject(e);}})})}else{returnnewPromise((resolve, reject)=>{reject("错误添加")})}},// 查询获取数据库里的数据 sql:'SELECT * FROM dbTable WHERE lname = 'lvalue''// 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值// selectTableData(dbTable, uname, namevalue, upass, passvalue,urrn,rrnvalue) {selectTableData(dbTable, uname,namevalue){if(dbTable !==undefined){//    // 第一个是表单名称，后两个参数是列表名，用来检索// if (uname !== undefined && upass !== undefined && urrn!==undefined) {//   // 三个检索条件//   var sql = `SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' AND ${upass} = '${passvalue}' AND ${urrn}='${rrnvalue}'`;// }//    if (uname !== undefined && upass !== undefined &&urrn==undefined) {//      // 两个检索条件//      var sql = `SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' AND ${upass} = '${passvalue}'`;//    }if(uname !==undefined){// 一个检索条件var sql =`SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}'`;// console.log(sql);}if(uname ==undefined){var sql =`SELECT * FROM ${dbTable}`;}returnnewPromise((resolve, reject)=>{// 表格查询数据  执行查询的SQL语句
            plus.sqlite.selectSql({name:this.dbName,sql: sql,success(e){resolve(e);},fail(e){reject(e);}})})}else{returnnewPromise((resolve, reject)=>{reject("错误查询")});}},// 删除表里的数据 sql:'DELETE FROM dbTable WHERE lname = 'lvalue''// 删除 DELETE FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值// deleteTableData(dbTable, lname, lvalue, ww, ee) {deleteTableData(dbTable, lname, lvalue){
            console.log("lname:"+ lname +","+"lvalue"+ lvalue);if(dbTable !==undefined){if(lname ==undefined){var sql =`DELETE FROM ${dbTable}`;}else{// if (ww !== undefined) {//   // 两个检索条件//   var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${ww} = '${ee}'`;// } else {// 一个检索条件var sql =`DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}'`;// }}returnnewPromise((resolve, reject)=>{// 删除表数据
            plus.sqlite.executeSql({name:this.dbName,sql: sql,success(e){resolve(e);},fail(e){reject(e);}})})}else{returnnewPromise((resolve, reject)=>{reject("错误删除")});}},// 修改数据表里的数据 sql:"UPDATE dbTable SET 列名 = '列值',列名 = '列值' WHERE lname = 'lvalue'"// 修改 UPDATE 、 dbTable 是表名, data: 要修改的列名=修改后列值, lname,lvalue 是查询条件的列名和列值updateTableData(dbTable, data, lname, lvalue){if(lname ==undefined){var sql =`UPDATE ${dbTable} SET ${data}`;}else{var sql =`UPDATE ${dbTable} SET ${data} WHERE ${lname} = '${lvalue}'`;}// WHERE 前面是要修改的列名、列值，后面是条件的列名、列值returnnewPromise((resolve, reject)=>{// 修改表数据
          plus.sqlite.executeSql({name:this.dbName,sql: sql,success(e){resolve(e);},fail(e){reject(e);}})})},// 获取指定数据条数  sql:"SELECT * FROM dbTable ORDER BY 'id' DESC LIMIT 15 OFFSET 'num'"// dbTable 表名, ORDER BY 代表排序默认正序, id 是排序的条件 DESC 代表倒序，从最后一条数据开始拿// LIMIT 15 OFFSET '${num}',这句的意思是跳过 num 条拿 15 条数据, num 为跳过多少条数据是动态值// 例 初始num设为0，就从最后的数据开始拿15条，下次不拿刚获取的数据，所以可以让num为15，这样就能一步一步的拿完所有的数据pullSQL(dbTable, id, num){returnnewPromise((resolve, reject)=>{
          plus.sqlite.selectSql({name:this.dbName,sql:`SELECT * FROM ${dbTable} ORDER BY '${id}' DESC LIMIT 15 OFFSET '${num}'`,success(e){resolve(e);},fail(e){reject(e);}})})}}

2、使用示例：（提供创建数据库的表，新增数据的操作）
创建数据库表

createTable(){let open =DB.isOpen();if(open){let sql ='"id" INTEGER PRIMARY KEY AUTOINCREMENT,"monomerId" text,"monomerName" text,"code" text,"administrativeLocation" text,"longitude" real,"latitude" real,"newFind" text,"mainType" text,'+'"subtype" text,"basicType" text,"imageStr" text,"videoStr" text,"appearance" text,"intrinsicProperty" text,"composition" text,"geneticMechanism" text,"scaleAndVolume" text,'+'"envBackground" text,"relatedThings" text,"externalRoad" text,"ambitusCity" text,"ambitusCityDistance" text,"centreName" text,"centreDistance" text,"touristAreaName" text,"touristAreaDistance" text,'+'"otherInstructionsOne" text,"preservationStatus" text,"developed" text,"protective" text,"scenicSpotName" text,"scenicSpotGrade" text,"scenicAreaName" text,"scenicAreaGrade" text,"visitorReception" text,'+'"otherInstructionsTwo" text,"monomerScore" text,"maybeGrade" text,"filledBy" text,"contactNumber" text,"censusDate" text,"status" integer,"createTime" text,"createBy" text,"typeCode" text,"tourismResourcesJson" text,"directionlist" text';// 创建表 DB.createTable(表名, 表的列)DB.createTable("gatherTable", sql).then(res=>{
                            console.log("创建gatherTable表成功");}).catch(error=>{
                            console.log("创建表失败");});}else{
                    console.log("数据库未打开");}},

数据的插入

let open =DB.isOpen();DB.insertTableData("gatherTable", sql, condition).then(res=>{
    uni.showToast({title:"数据保存成功！",position:'center',icon:'none'});
    uni.navigateBack({delta:1});}).catch(error=>{
    console.log("失败", error);});

以上是本人亲测有效的操作方法；有不懂的小伙伴欢迎私聊哦；有需要的也可以点赞评论加关注哦。