一:manifest.json 选择APP模块配置勾选SQLite(数据库)
二:在根目录新建 文件夹 utils ,在 utils 里新建 文件 sqlite.mixin.js 和 sqlite_init.mixin.js
sqlite.mixin.js (sqlite 数据执行语句)
/*
* @Descripttion: sqlite 方法集合
* @version:
*/
let databaseName="bookkeep";//数据库名
let tabName="bills";//数据库表名
/**
* @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,
succ