uniapp如何在app端操作sqlite数据库？

首先在manifest.json文件中勾选sqlite

在根目录下新建文件夹 utils ,在 utils 里新建 文件 sqlite.mixin.js 和 sqlite_init.mixin.js

/*
 * @Descripttion: sqlite 方法集合
 * @version: 
 */
let databaseName="dbname";//数据库名
let tabName="tablename";//数据库表名
/**
 * @Descripttion: 初始化，进行建表操作
 **/
function createTables(){
	// 注意：tabName不能用数字作为表格名的开头
	//账单表
	let tabNamearray=["bills","plans"];
	
	let name="bookkeep";
	let tabName="bills";
	let tableStructure="id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL";
	return new Promise((resolve, reject) => {
	  plus.sqlite.executeSql({
	    name: name,
	    // sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
	    sql: `create table if not exists ${tabName}(${tableStructure})`,
		//sql: `DROP TABLE sqlite_sequence`,
	    success(e) {
			console.log(e);
	      resolve(e);
	    },
	    fail(e) {
	      console.log(e)
	      reject(e);
	    }
	  })
	})
}
 
/**
 * 执行多条sql语句
 * 
 * @databasename 数据库名
 * @tableName    表名数组["table1","table2"]
 * bills //账单表   plans //计划备忘录表 
 **/
function executeSQL(){
    plus.sqlite.executeSql({  
        name: 'first',  
        sql: ['create table if not exists table_A("where" CHAR(110),"location" CHAR(100),"age" INT(11))','create table if not exists table_B("where" CHAR(110),"location" CHAR(100),"age" INT(11));'],  
        success: function(e){  
            console.log('executeSql success!');  
            plus.sqlite.executeSql({  
                name: 'first',  
                sql: ["insert into table_B values('北京','安乐林:',11)","insert into table_B values('天津','风火轮',22);"],  
                success: function(e){  
                    console.log('executeSql success!');  
                },  
                fail: function(e){  
                    console.log('executeSql failed: '+JSON.stringify(e));  
                }  
            });  
        },  
        fail: function(e){  
            console.log('executeSql failed: '+JSON.stringify(e));  
        }  
    });  
}   
 
 
 
// 监听数据是否打开
function isOpenDB(name) {
  let dbName = name;
  let dbPath = `_doc/${name}_record.db`;
  //数据库打开了就返回true,否则返回false
  let isopen = plus.sqlite.isOpenDatabase({
    name: dbName,
    path: dbPath
  })
  return isopen
}
 
// 创建数据库/打开数据库
function openDB(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.openDatabase({
      name: name || 'testData',
      path: `_doc/${name}_record.db`,
      success: function (e) {
        resolve('openDatabase success!')
      },
      fail: function (e) {
        reject('openDatabase failed: ' + JSON.stringify(e))
      }
    });
  })
}
 
// 查询所有数据库表名
function queryDBTable(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: "select * FROM sqlite_master where type='table'",
      success(e) {
		 console.log(e) 
        resolve(e);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}
 
// 查询表是否存在
function queryIsTable(name, tabName) {
  plus.sqlite.selectSql({
    name: name,
    sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
    success(e) {
      resolve(e[0].isTable ? true : false);
    },
    fail(e) {
      console.log(e)
      reject(e);
    }
  })
}
 
// 创建表
function createTable(name, tabName, tableStructure) {
  // 注意：tabName不能用数字作为表格名的开头
  return new Promise((resolve, reject) => {
    plus.sqlite.executeSql({
      name: name,
      // sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
      sql: `create table if not exists ${tabName}(${tableStructure})`,
      success(e) {
        resolve(e);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}
 
// 查询表是否存在
function isTable(name, tabName) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
      success(e) {
        resolve(e[0].isTable ? true : false);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}
 
// 添加数据
function addSaveData(name, tabName, obj) {
  if (obj) {
    let keys = Object.keys(obj)
    let keyStr = keys.toString()
    let valStr = ''
    keys.forEach((item, index) => {
      if (keys.length - 1 == index) {
        valStr += ('"' + obj[item] + '"')
      } else {
        valStr += ('"' + obj[item] + '",')
      }
    })
    // console.log(valStr)
    let sqlStr = `insert into ${tabName}(${keyStr}) values(${valStr})`
    // console.log(sqlStr)
    return new Promise((resolve, reject) => {
      plus.sqlite.executeSql({
        name: name,
        sql: sqlStr,
        success(e) {
			console.log(e);
          resolve(100);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    })
  }
}
 
/*
** 查询数据库数据
* @name     数据库名
* @tabName  表名
* @setData  查询条件{"id":1,"type":"goods"}
* @byName   排序的字段名
* @byType   排序规则  asc升序   desc降序
*/
function selectDataList(name, tabName, setData, byName, byType) {
  let setStr = ''
  let sql = ''
  if (JSON.stringify(setData) !== '{}') {
    let dataKeys = Object.keys(setData)
    dataKeys.forEach((item, index) => {
      console.log(setData[item])
      setStr += (
        `${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`)
    })
    sql = `select * from ${tabName} where ${setStr}`
  } else {
    sql = `select * from ${tabName}`
  }
  if (byName && byType) {
    // desc asc
    sql += ` order by ${byName} ${byType}`
  }
  console.log(sql)
  if (tabName !== undefined) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: sql,
        success(e) {
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    });
  }
}
 
/*
** 查询数据库数据直接使用语句查询
* @name     数据库名
* @tabName  表名
* @setDatasql  查询条件sql语句
* @byName   排序的字段名
* @byType   排序规则  asc升序   desc降序
*/
function selectDataListsql(name, tabName, setDatasql, byName, byType) {
  let setStr = ''
  let sql = ''
  if (setDatasql!='') {
    sql = `select * from ${tabName} where ${setDatasql}`
  } else {
    sql = `select * from ${tabName}`
  }
  if (byName && byType) {
    // desc asc
    sql += ` order by ${byName} ${byType}`
  }
  console.log(sql)
  if (tabName !== undefined) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: sql,
        success(e) {
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    });
  }
}
 
/*
** 查询数据库数据直接使用语句查询
* @name     数据库名
* @setDatasql  查询条件纯sql语句
*/
function selectDataListsqlonly(name, setDatasql) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: setDatasql,
        success(e) {
			console.log(e)
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  
}
 
// 获取数据库分页数据
/**
 * 
 * @param {*} name 
 * @param {*} tabName 
 * @param {*} num 页码
 * @param {*} size 页面大小返回条数
 * @param {*} byName 排序主键字段
 * @param {*} byType 排序类型 desc倒序 / asc正序
 */
async function queryDataList(name, tabName, num, size, byName, byType) {
  let count = 0
	let sql = ''
	let numindex = 0
	await queryCount(name, tabName).then((resNum) => {
		count = Math.ceil(resNum[0].num / size)
	})
	if(((num - 1) * size) == 0) {
	    numindex = 0
	} else {
		numindex = ((num - 1) * size) + 1
	}
	sql = `select * from ${tabName}`
	if(byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	sql += ` limit ${numindex},${size}`
	if (count < num - 1) {
		return new Promise((resolve, reject) => {
			reject("无数据")
		});
	} else {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: name,
				// sql: "select * from userInfo limit 3 offset 3",
				sql:sql ,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}
 
// 查询表数据总条数
function queryCount(name, tabName) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
			name: name,
			sql: "select count(*) as num from " + tabName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
  })
}
 
// 修改（更新）数据
// 示例：UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
// UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6;
/**
 * 
 * @param {*} name 数据库名
 * @param {*} tabName 表名
 * @param {*} setData 设置值 （修改字段 + 修改内容）
 * @param {*} setName 筛选条件
 * @param {*} setVal 筛选值
 * @returns 
 */
function updateSqlData(name, tabName, setData, setName, setVal) {
  if (JSON.stringify(setData) !== '{}') {
    let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
      // console.log(item, setData[item])
			setStr += (
				`${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}`)
    })
    console.log(setStr)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: name,
				sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`,
				success(e) {
					//resolve(e);
					resolve(100);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}
 
// 关闭数据库
function closeDB(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.closeDatabase({
      name: name,
      success: function (e) {
        resolve('closeDatabase success!');
      },
      fail: function (e) {
        reject('closeDatabase failed: ' + JSON.stringify(e));
      }
    });
  })
}
export{
  databaseName,
  tabName,	  
  createTables,	
  openDB,
  closeDB,
  isOpenDB,
  queryDBTable,
  queryIsTable,
  createTable,
  isTable,
  addSaveData,
  selectDataList,
  selectDataListsql,
  selectDataListsqlonly,
  queryCount,
  updateSqlData,
  queryDataList
}

/*
 * @Descripttion: sqlite 方法集合
 * @version: 
 */
let databaseNameinit="bookkeep";//数据库名
let tabNameinit="bills";//数据库表名
/**
 * @Descripttion: 初始化，进行建表操作
 **/
 
 // 创建数据库/打开数据库
 function openDBinit(name) {
   return new Promise((resolve, reject) => {
     plus.sqlite.openDatabase({
       name: name || 'testData',
       path: `_doc/${name}_record.db`,
       success: function (e) {
         resolve('openDatabase success!')
       },
       fail: function (e) {
         reject('openDatabase failed: ' + JSON.stringify(e))
       }
     });
   })
 }
 
/**
 * 执行多条sql语句
 * 
 * @databasename 数据库名
 * @tableName    表名
 * bills //账单表   plans //计划备忘录表
 **/
async function  executeSQLinit(version){
	let sqlversion=9;//数据库版本，只要对不上就进行更新每次需要更新数据库都要重设此字段
	let sqlsqlcode=100;
	if(sqlversion==version){
		console.log("无需更新");
		let recode={
			"code":sqlsqlcode,
			"version":sqlversion
		};
		return recode;
	}
	let databaseName="bookkeep";//数据库名 
	let tabname=["sqldo","bills","plans"];//表名
	//需要进行更新的数据语句依次从后加入 sqlite不能执行set 操作，只能add
	let sqldata=[
		{"sql":'create table if not exists sqldo(id INTEGER PRIMARY KEY AUTOINCREMENT,num INT NOT NULL DEFAULT 0--执行数据语句号num\n); --数据库更新版本库\n',"num":'1'},
		{"sql":'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表
',"num":'2'},
		{"sql":'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录
',"num":'3'},
		
		//{"sql":'insert into sqldo (num) values (4); --完成状态-1未完成1已经完成\n',"num":'4'},
		{"sql":'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',"num":'4'},//计划表更新字段
		{"sql":'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',"num":'5'},
		{"sql":'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n',"num":'6'},
		{"sql":'ALTER TABLE plans ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除
',"num":'7'},
		{"sql":'ALTER TABLE bills ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除
',"num":'8'},
		//版本1.0.992 常规查询时先查询o 状态，然后再通过时间本年月日进行查询，如果存在的就进行数量统计。不存在，完成就为0，当完成时，点击完成时，就进行完成添加。
		//{"sql":'ALTER TABLE plans ADD COLUMN btypeend TINYINT NOT NULL DEFAULT 0; --常规任务终极状态0:进行时;1:完成终极状态;\n',"num":'9'},
		//版本1.0.993
		{"sql":'ALTER TABLE plans ADD COLUMN plansid INTEGER NOT NULL DEFAULT 0; --常规任务完成时对应的常规任务id,0就代变为常规任务根任务
',"num":'10'},
		{"sql":'ALTER TABLE plans ADD COLUMN year TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的年,0就代变为默认\n',"num":'11'},
		{"sql":'ALTER TABLE plans ADD COLUMN quarter TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的季度,0就代变为默认\n',"num":'12'},
		{"sql":'ALTER TABLE plans ADD COLUMN month TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的月,0就代变为默认\n',"num":'13'},
		{"sql":'ALTER TABLE plans ADD COLUMN week TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的周,0就代变为默认\n',"num":'14'},
		{"sql":'ALTER TABLE plans ADD COLUMN day TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的日,0就代变为默认\n',"num":'15'},
		
		{"sql":'ALTER TABLE plans ADD COLUMN btypeend TINYINT NOT NULL DEFAULT 0; --常规任务终极状态0:进行时;1:完成终极状态;\n',"num":'16'},
	];
	//查询数据库版本表是否存在，如果不存在就全部更新
	let istable=await queryIsTableinit(databaseName, "sqldo");
	if(istable){ //先进行判断需要执行哪些语句
		//查询数据库更新的版本
		let setDatasql='select * from sqldo;';
		let sedatalist=await selectDataListsqlonly(databaseName,setDatasql);
		sedatalist.forEach((item, index) =>{ //从数据中循环读取，再进行比对，如果没有的就进行执行语句，并添加到数据库
		    sqldata.forEach((items, indexs) =>{ 
				if(item.num==items.num){
					sqldata.splice(indexs,1);
				}
			});
		});
		if(sqldata.length==0){//空数据没有执行语句
		    console.log("判断完毕，无需更新");
			let r...

点击查看剩余70%