UNIAPP 操作sqlite (APP) 实现操作sqlite 数据库，版本更新时自动新建sqlite 数据表字段等

一：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