更新记录

1.0.0（2024-07-23）

iOS、安卓Sqlite

平台兼容性

uni-app

uni-app x

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)
})

• ⚠️ 若示例项目中main.uts无法查看，新建一个main.uts文件将以下的代码复制进该文件替换掉无法查看的main.uts

  // main.uts
  import App from './App.uvue'
  import { createSSRApp } from 'vue'
  export function createApp() {
  const app = createSSRApp(App)
  return {
      app
  }
  }

• ⚠️ 若示例项目中pages\index\manager\TestModelSaveUtil.uts无法查看，上文以说明，可查看相关说明