更新记录
1.0.0(2024-07-23)
iOS、安卓Sqlite
平台兼容性
uni-app
app-vue |
app-nvue |
app-android |
app-ios |
? |
? |
? |
? |
H5-Safari |
Android Browser |
微信浏览器(Android) |
QQ浏览器(Android) |
Chrome |
IE |
Edge |
Firefox |
PC-Safari |
? |
? |
? |
? |
? |
? |
? |
? |
? |
微信小程序 |
支付宝小程序 |
百度小程序 |
字节小程序 |
QQ小程序 |
钉钉小程序 |
快手小程序 |
飞书小程序 |
京东小程序 |
? |
? |
? |
? |
? |
? |
? |
? |
? |
uni-app x
H5-Safari |
Android Browser |
微信浏览器(Android) |
QQ浏览器(Android) |
Chrome |
IE |
Edge |
Firefox |
PC-Safari |
? |
? |
? |
? |
? |
? |
? |
? |
? |
yzc-sqlite 兼容安卓、iOS端
SqliteManager类基础用法示例
// 导入
import { SqliteManager } from '@/uni_modules/yzc-sqlite';
const kTableName = 'testTable1'
// 优先设置数据库,目前只支持全局一个数据库
SqliteManager.setupSqlite('test', '<&&>')
SqliteManager.executeSql(`DROP TABLE ${kTableName}`, (result) => { // 删除表
if(result.errCode == 0) {
console.log('删除表成功')
} else {
console.log('表不存在,或者删除失败', result.errMsg)
}
})
// 执行sql语句
SqliteManager.executeSql(`create table if not exists ${kTableName} (id integer)`, (result) => {
if (result.errCode == 0 && result.data[0]) {
console.log('创建表成功')
} else {
console.log('创建表失败')
}
})
// 查询sql语句
SqliteManager.selectSql(`select count(*) as isTable FROM sqlite_master where type='table' and name='${kTableName}'`, (result) => {
if (result.errCode == 0) {
console.log('表存在')
} else {
console.log('表不存在')
}
})
// 执行事务
SqliteManager.transaction('begin', (success, errMsg) => {
if (success) {
SqliteManager.executeSql(`alter table ${kTableName} add column field1 text <&&> alter table ${kTableName} add column field2 text`, (result) => {
if (result.errCode == 0) {
console.log('执行commit')
SqliteManager.transaction('commit', (success, errMsg) => {
if (success) {
SqliteManager.queryDBAllTable((result) => {
console.log('查询所有表', result)
})
}
})
} else {
console.log('执行rollback', result.sql)
SqliteManager.transaction('rollback', (success, errMsg) => {
})
}
})
}
})
// 关闭数据库
// SqliteManager.close()
SqliteManager类拓展用法示例
// 导入
import { SqliteManager } from '@/uni_modules/yzc-sqlite';
SqliteManager.setupSqlite('test', null)
const kTableName = 'testTable1'
// 查询数据库所有表
SqliteManager.queryDBAllTable(result => {
console.log(result.data)
})
// 判断表是否存在
SqliteManager.isTableExist(kTableName, (exit, errMsg) => {
if (exit) { // 存在
// 删除表
SqliteManager.deleteTable(kTableName, (success, errMsg) => {
if (success) {
console.log('删除表成功')
} else {
console.log('删除表失败')
}
})
}
})
// 创建表
SqliteManager.createTable(kTableName, 'field1 text', (success, errMsg) => {
if (success) {
console.log('创建表成功')
} else {
console.log('创建表失败')
}
})
// 表新增字段
SqliteManager.tableAddColumns(kTableName, {field2: 'string', field3: 'integer'}, (success, errMsg) => {
if (success) {
console.log('新增表字段成功')
} else {
console.log('新增表字段失败')
}
})
// 查询数据库所有表
SqliteManager.queryDBAllTable(result => {
console.log(result.data)
})
// 添加数据
for (var i = 0; i < 5; i++) {
SqliteManager.insertModel(kTableName, 'field1,field2,field3', `${(i > 2 ? 1 : 0) + ''},${2 * i + ''},${3 * i}`, (success, errMsg) => {
if (success) {
console.log('添加数据成功')
} else {
console.log('添加数据失败')
}
})
}
// 查询数据 无筛选条件
SqliteManager.queryModelList(kTableName, null, null, null, null, (success, errMsg, list) => {
console.log(list)
})
// 查询数据
SqliteManager.queryModelList(kTableName, {field1: '0'}, 'field3 > 3', 'field3', 'ASC', (success, errMsg, list) => {
console.log(success, list)
})
// 查询表数据总条数
SqliteManager.queryTableDataListCount(kTableName, {field1: '0'}, 'field3 > 3',(success, count, errMsg) => {
console.log(count)
})
// 分页查询数据
SqliteManager.queryModelPageList(kTableName, 1, 10, null, 'field3 > 3', 'field3', 'DESC', (success, errMsg, list, currentPage, totalPage) => {
console.log(list, totalPage)
})
// 更新数据
SqliteManager.updateTableData(kTableName, {field1: '3'}, {field2: 2}, 'field3 > 0', (success, errMsg) =>{
if (success) {
console.log('更新数据成功')
} else{
console.log('更新数据失败')
}
})
// 删除数据
SqliteManager.deleteTableData(kTableName, {field1: '1'}, 'field3 = 12', (success, errMsg) => {
if (success) {
console.log('删除数据成功')
} else{
console.log('删除数据失败')
}
})
// 查询数据
SqliteManager.queryModelList(kTableName, {field1: '0'}, 'field3 > 3', 'field3', 'ASC', (success, errMsg, list) => {
console.log(list)
})
为了简化繁琐的sql代码,更关注业务逻辑,提供了SqliteModel类处理一些自定义对象嵌套的问题
-
比如示例中新建一个uts文件(TestModelSaveUtil.uts)专门用于处理存储对象的的逻辑,代码如下
-
⚠️ 若示例项目中pages\index\manager\TestModelSaveUtil.uts无法查看,新建一个TestModelSaveUtil.uts文件将以下的代码复制进该文件替换掉无法查看的TestModelSaveUtil.uts
// TestModelSaveUtil.uts
import { SqliteModel } from "@/uni_modules/yzc-sqlite"
export type AddressModel = {
country: string,
city: string
}
export type UserModel = {
user_id: string,
name: string,
age: number,
address?: AddressModel | null // 存储时将其转为json字符串
}
export const userTableName = 'User_Table'
let shareInstance: SqliteModel | null = null
export function getShareInstance(): SqliteModel {
if (shareInstance == null) {
// 需要一个模版对象 便于SqliteModel类根据对象分析字段和类型创建表描述
const templateModel = {
user_id: '',
name: '',
age: 0,
address: {
country: '',
city: ''
} as AddressModel
} as UserModel;
const instance = new SqliteModel(userTableName, templateModel, null, false, ['address'])
shareInstance = instance
}
return shareInstance as SqliteModel
}
export function destoryShareInstance() {
shareInstance = null
}
-
在需要处理的文件中引入
// 导入
import { SqliteManager } from '@/uni_modules/yzc-sqlite';
import { UserModel, AddressModel, getShareInstance } from './manager/TestModelSaveUtil.uts'
// 导入
SqliteManager.setupSqlite('test', null)
const shareInstance = getShareInstance()
// 删除表
shareInstance.deleteModelTable((success: boolean, errMsg: string) => {
console.log('删除表', success, errMsg)
})
// 表不存在就创建表
shareInstance.createModelTable((success: boolean, errMsg: string) => {
console.log('创建表', success, errMsg)
})
// 添加数据
for (var i = 0; i < 5; i++) {
const model = {
user_id: i.toString(),
name: 'name' + i,
age: 18 + i,
address: {
country: 'country' + i,
city: 'city' + i
} as AddressModel
} as UserModel;
// 插入数据
shareInstance.insertModel(model, (success: boolean, errMsg: string) => {
console.log('插入数据', success, errMsg)
})
}
//查询数据
shareInstance.queryModelList(null, null, null, null, (success: boolean, errMsg: string, list: string[]) => {
const jsonStr = list[0]
const model = JSON.parse<UserModel>(jsonStr)
console.log('查询数据', success, errMsg, model?.user_id, model?.address?.country)
})
// 修改更新数据
shareInstance.updateTableData({age: 30}, {user_id: '1'}, null, (success: boolean, errMsg: string) => {
console.log('修改数据', success, errMsg)
})
// 删除数据
shareInstance.deleteTableData({user_id: '2'}, null, (success: boolean, errMsg: string) => {
console.log('删除数据', success, errMsg)
})
// 分页查询
shareInstance.queryModelPageList(1, 20, null, null, null, null, (success: boolean, errMsg: string, list: string[], currentPage: number, totalPage: number) => {
for (var i = 0; i < list.length; i++) {
const jsonStr = list[i]
const model = JSON.parse<UserModel>(jsonStr)
console.log('分页查询', model?.user_id, model)
}
})
// 新增表字段
shareInstance.modelTableAddColumns({phoneNum: 'string'}, (success: boolean, errMsg: string) => {
console.log('新增字段', success, errMsg)
})
// 查询所有表
SqliteManager.queryDBAllTable((result) => {
console.log('查询所有表', result)
})