SqLite教程

uniapp + sqlite 缓存聊天数据

本文主要是介绍uniapp + sqlite 缓存聊天数据,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

【前言】缓存,这是一个老生常谈的话题,也常被作为前端面试的一个知识点。H5端为localStorage,浏览器限制5M大小,是缓存概念,可能会被清理;App端为原生的plus.storage,无大小限制,不是缓存,是持久化的。今天这里主要演示sqlite在uniapp中的使用!
除此之外,其他数据存储方案:

  • H5端还支持websql、indexedDB、sessionStorage
  • App端还支持IO文件等本地存储方案。

SQLite

  • sqlite语法学习
首先需要开启sqlite

在这里插入图片描述
这样你就可以在uniapp中使用sqlite了!

sqlite基本操作方法封装
/*
 * @Descripttion: sqlite 方法集合
 * @version: 
 * @Author: wtzhang
 * @Date: 2021-10-12 16:26:39
 * @LastEditors: wtzhang
 * @LastEditTime: 2021-10-20 09:25:12
 * study:https://www.runoob.com/sqlite/sqlite-tutorial.html
 */


// 监听数据是否打开
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) {
        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) {
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    })
  }
}

// 查询数据库数据
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("错误")
    });
  }
}

// 获取数据库分页数据
/**
 * 
 * @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);
				},
				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 default {
  openDB,
  closeDB,
  isOpenDB,
  queryDBTable,
  queryIsTable,
  createTable,
  isTable,
  addSaveData,
  selectDataList,
  queryCount,
  updateSqlData,
  queryDataList
}

基本操作演示

示例源码

这篇关于uniapp + sqlite 缓存聊天数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!