更新记录

1.0.0（2024-12-10）

1. 增删查,支持批量
2. 分页查询(limit、orderBy、groupBy、having、where)
3. 版本升级
4. 支持事务控制

平台兼容性

原生稳定sqlite数据库

功能

1. 增删查,支持批量
2. 分页查询(limit、orderBy、groupBy、having、where)
3. 版本升级
4. 支持事务控制

集成步骤

1. 集成插件步骤请参考https://www.cnblogs.com/wenrisheng/p/18323027

接口

import {
    UTSSqlite
} from "@/uni_modules/wrs-uts-sqlite"
let sqlite = new UTSSqlite()

• 设置回调

sqlite.setCallback((resp) => {
    let opt = resp.opt
    switch (opt) {
        case "onCreate": {
            this.showMsg("创建数据库")
        }
        break;
        case "onOpen": {
            this.showMsg("打开数据库")
            // 创建表
            this.initTable()
        }
        break;
        case "onUpgrade": {
            var oldVersion = resp.oldVersion;
            var newVersion = resp.newVersion;
            this.showMsg("数据库升级，oldVersion:" + oldVersion + "  newVersion:" + newVersion);
        }
        break;
        default:
            break;
    }
})

• 初始化打开数据库

var params = {};
params.dbName = "database.db";
switch (uni.getSystemInfoSync().platform) {
    case 'android':
        params.dbName = "database.db";
        break;
    case 'ios':
        // params.dbName = "database.sqlite";
            params.dbName = "database.db";
        break;
    default:
        break;
}
params.version = 1; // 版本号，用以做数据库版本升级
sqlite.initDB(params);

• 创建表结构

                var params = {};
                params.sqls = [{
                        sql: "CREATE TABLE IF NOT EXISTS user (id integer PRIMARY KEY AUTOINCREMENT, account varchar(20), password varchar(20), address text, age integer default 0, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"
                    },
                    {
                        sql: "CREATE TABLE IF NOT EXISTS goods (id integer PRIMARY KEY AUTOINCREMENT, name varchar(20), address text, num integer default 0)"
                    },
                ];
                params.transaction = true;
let result =    sqlite.createTable(params)

• 增(支持批量)

var params = {};
params.sqls = [{
        table: "user",
        keyValues: {
            account: "张三",
            password: "aabbcc123456",
            age: 20,
            update_time: "2023-08-20 08:24:00"
        }
    },
    {
        table: "user",
        keyValues: {
            account: "李四",
            password: "aab56",
            age: 25,
            update_time: "2023-08-21 08:24:00"
        }
    }
];
// params.transaction = false; // 是否开启事务
params.transaction = true;
var result = sqlite.insert(params);
var jsonStr = JSON.stringify(result);
this.showMsg("增加结果:" + jsonStr);

• 删(支持批量)

var params = {};
params.sqls = [{
    table: "user",
    where: "account=? and password=?",
    whereValues: ["张三", "aabbcc123456"]
}];
// params.transaction = false; // 是否开启事务
var result = sqlite.delete(params);
var jsonStr = JSON.stringify(result);
this.showMsg("删除结果:" + jsonStr);

• 查

var params = {};
params.sql = "select * from user";
// params.sql = "select account from user";

// params.sql = "select * from user where update_time BETWEEN '2023-08-20 08:23:00' AND '2023-08-20 08:25:00'";
var result = sqlite.query(params);
this.showMsg("query:\n" + JSON.stringify(result));
var columnNames = result.columnNames;
var data = result.data;
if (null != columnNames && columnNames.length > 0) {
    var result = "";
    var columnNamesStr = JSON.stringify(columnNames);
    var dataStr = JSON.stringify(data);
    result = result + "表字段:" + columnNamesStr + "\n";
    result = result + "表数据:" + dataStr + "\n";
} else {
    this.showMsg("查询所有表名为空");
}

• 分页复杂查询(limit、orderBy、groupBy、having、where)

var params = {
    table: "user",
    orderBy: "account desc",
    limit:"1,3", // 第1条开始,返回3条数据
    where: "account = '张三'"
};
var result = sqlite.query(params);  
var columnNames = result.columnNames;
var data = result.data;
if (null != columnNames && columnNames.length > 0) {
    var result = "";
    var columnNamesStr = JSON.stringify(columnNames);
    var dataStr = JSON.stringify(data);
    result = result + "表字段:" + columnNamesStr + "\n";
    result = result + "表数据:" + dataStr + "\n";
    this.showMsg("查询结果:\n" + result);
} else {
    this.showMsg("查询结果为空");
}

• 查询数量

var params = {};
params.sql = "select count(*) from user";
var result = sqlite.query(params);
var columnNames = result.columnNames;
var data = result.data;
if (null != columnNames && columnNames.length > 0) {
    var result = "";
    var columnNamesStr = JSON.stringify(columnNames);
    var dataStr = JSON.stringify(data);
    result = result + "表字段:" + columnNamesStr + "\n";
    result = result + "表数据:" + dataStr + "\n";
    this.showMsg("查询数据条数结果:\n" + result);
} else {
    this.showMsg("查询数据条数为空");
}

• 改(支持批量) 如果需要把某个表字段修改为设置null值，因为uts会把null值过滤掉，所以请使用自定义SQL语句execSQL来修改

var params = {};
params.sqls = [{
    table: "user",
    keyValues: {
        account: "zhangsan"
    },
    where: "password = ?",
    whereValues: ["aabbcc123456"]
}];
// params.transaction = false; // 是否开启事务
var result = sqlite.update(params);
var jsonStr = JSON.stringify(result);
this.showMsg("更新结果:" + jsonStr);

• 执行原始的sql语句

var params = {};
params.sqls = [{
    sql: "delete from user where account=?",
    values: ["张三"]
}]; 
let result = sqlite.execSQL(params)
this.showMsg("execSQL:" + JSON.stringify(result))

• 修改表结构

var params = {};
params.sqls = [{
    sql: "ALTER TABLE user ADD idcard text"
}];
sqlite.execSQL(params);

• 查询所有表名

var params = {};
params.sql = "select name from sqlite_master where type='table' order by name";
var result = sqlite.query(params);
this.showMsg("查询所有表名结果:\n" + JSON.stringify(result));
var columnNames = result.columnNames;
var data = result.data;
if (null != columnNames && columnNames.length > 0) {
    var result = "";
    var columnNamesStr = JSON.stringify(columnNames);
    var dataStr = JSON.stringify(data);
    result = result + "表字段:" + columnNamesStr + "\n";
    result = result + "表数据:" + dataStr + "\n";
    this.showMsg("查询所有表名结果:\n" + result);
} else {
    this.showMsg("查询所有表名为空");
}

• 获取数据库文件路径

var path = sqlite.getDBPath();
if(path) {
    this.showMsg("数据库文件路径为:" + path);
} else {
    this.showMsg("数据库文件路径为NULL");
}

• 数据库是否已经打开

var isOpen = sqlite.isOpen();
if(isOpen) {
    this.showMsg("数据库已经打开");
} else {
    this.showMsg("数据库没有打开");
}

• 关闭数据库

sqlite.close();